One of the primary problems with tuning SQL is that we cannot easily see how Oracle is accessing the data tables and indexes.
Fortunately, Oracle has provided a host of utilities that can help us see what is happening within the database as the SQL is being executed.
These utilities include:
Click the link below to see how the
SQL Analyzer utility functions.
SQL Analyze Utility
In later lessons, we will be going deeper into SQL analyzer to show how it can be used to tune SQL queries.
In the next lesson we will look at how SQL is executed within the Oracle engine.
Use the EXPLAIN PLAN statement to determine the execution plan Oracle Database follows to execute a specified SQL statement. This statement inserts a row describing each step of the execution plan into a specified table.
You can also issue the EXPLAIN PLAN statement as part of the SQL trace facility. This statement also determines the cost of executing the statement. If any domain indexes are defined on the table, then user-defined CPU and I/O costs will also be
inserted.
The definition of a sample output table PLAN_TABLE is available in a SQL script on your distribution media. Your output table must have the same column names and datatypes as this table. The common name of this script is UTLXPLAN.SQL. The exact name and location depend on your operating system.
Oracle Database provides information on cached cursors through several dynamic performance views:
- For information on the work areas used by SQL cursors, query V$SQL_WORKAREA.
- For information on the execution plan for a cached cursor, query V$SQL_PLAN.
- For execution statistics at each step or operation of an execution plan of cached cursors (for example, number of produced rows, number of blocks read), query V$SQL_PLAN_STATISTICS.
- For a selective precomputed join of the preceding three views, query V$SQL_PLAN_STATISTICS_ALL.
- Execution statistics at each step or operation of an execution plan of cached cursors are displayed in V$SQL_PLAN_MONITOR if the statement execution is
monitored. You can force monitoring using the MONITOR hint.