Oracle SQL Plus Pocket Reference, 2nd Edition

Tuning SQL

SQL*Plus can help tune SQL statements. You can use SQL’s EXPLAIN PLAN facility to get the execution plan for a statement into a table. You can then query that table using SQL*Plus to display that plan. If you don’t like the plan Oracle is using, you can add optimizer hints to your SQL statement that specify how you want the statement to be executed.

Creating the Plan Table

Before you can use the EXPLAIN PLAN statement, you need to create a plan table to hold the results. Oracle provides a script named utlxplan.sql to create the plan table, and you’ll find it in your $ORACLE_HOME/rdbms/admin directory. Execute the script as follows:

SQL> @c:\oracle\ora92\rdbms\admin\utlxplan
Table created.

The resulting table, PLAN_TABLE, looks like this:

 Name               Null?    Type
 ------------------ -------- ----------------
 STATEMENT_ID                VARCHAR2(30)
 TIMESTAMP                   DATE
 REMARKS                     VARCHAR2(80)
 OPERATION                   VARCHAR2(30)
 OPTIONS                     VARCHAR2(255)
 OBJECT_NODE                 VARCHAR2(128)
 OBJECT_OWNER                VARCHAR2(30)
 OBJECT_NAME                 VARCHAR2(30)
 OBJECT_INSTANCE             NUMBER(38)
 OBJECT_TYPE                 VARCHAR2(30)
 OPTIMIZER                   VARCHAR2(255)
 SEARCH_COLUMNS              NUMBER
 ID                          NUMBER(38)
 PARENT_ID                   NUMBER(38)
 POSITION                    NUMBER(38)
 COST                        NUMBER(38)
 CARDINALITY                 NUMBER(38)
 BYTES                       NUMBER(38)
 OTHER_TAG                   VARCHAR2(255)
 PARTITION_START             VARCHAR2(255)
 PARTITION_STOP              VARCHAR2(255)
 PARTITION_ID                NUMBER(38)
 OTHER                       LONG
 DISTRIBUTION                VARCHAR2(30)
 CPU_COST                    NUMBER(38)
 IO_COST                     NUMBER(38)
 TEMP_SPACE                  NUMBER(38)
 ACCESS_PREDICATES           VARCHAR2(4000)
 FILTER_PREDICATES           VARCHAR2(4000)

The columns in the plan ...