O'Reilly logo

Oracle SQL*Plus Pocket Reference, 3rd Edition by Jonathan Gennick

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Tuning SQL

SQL*Plus can help you 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. You’ll find it in your $ORACLE_HOME/rdbms/admin directory. Execute the script as follows:

SQL>  @$ORACLE_HOME/rdbms/admin/utlxplan
Table created.

Windows users, refer to the Oracle home directory as follows:

@%ORACLE_HOME%/rdbms/admin/utlxplan

In particular, note the bracketing of ORACLE_HOME by percent signs (%).

The resulting table, PLAN_TABLE, looks like this:

Name Null? Type ----------------- ------ --------------- STATEMENT_ID VARCHAR2(30) PLAN_ID NUMBER TIMESTAMP DATE REMARKS VARCHAR2(4000) OPERATION VARCHAR2(30) OPTIONS VARCHAR2(255) OBJECT_NODE VARCHAR2(128) OBJECT_OWNER VARCHAR2(30) OBJECT_NAME VARCHAR2(30) OBJECT_ALIAS VARCHAR2(65) OBJECT_INSTANCE NUMBER(38) OBJECT_TYPE VARCHAR2(30) OPTIMIZER VARCHAR2(255) SEARCH_COLUMNS NUMBER ID NUMBER(38) PARENT_ID NUMBER(38) DEPTH 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) ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required