SQL Tuning   «Prev  Next»

Lesson 3Basic SQL tuning tools
ObjectiveExplain the tool set for SQL tuning.

Oracle SQL Tuning Tools

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:

Utilities 
The EXPLAIN PLAN utility (utlxplan.sql) This utility allows the DBA to see the actual path to the data.
The TKPROF utility TKPROF allows the DBA to trace through the actual execution of an SQL statement.
The Oracle SQL ANALYZE utility This is a graphic tool that aids in the debugging of Oracle SQL.
We will be using screens from this tool extensively in this course.
The SQL*Plus utility SQL*Plus provides various commands (set timing on) that allow the DBA to get execution times for SQL statements.

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.

When to Use the EXPLAIN PLAN Statement

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:
  1. For information on the work areas used by SQL cursors, query V$SQL_WORKAREA.
  2. For information on the execution plan for a cached cursor, query V$SQL_PLAN.
  3. 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.
  4. For a selective precomputed join of the preceding three views, query V$SQL_PLAN_STATISTICS_ALL.
  5. 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.