RelationalDBDesign 





SQL Tuning   «Prev 

Oracle EXPLAIN PLAN utility

The explain plan utility is provided by Oracle to allow you to see the internal mechanics of how your SQL is executed. Using the explain plan to solve SQL tuning problems is central to Oracle Tuning.
The explain plan shows table access methods (FULL, INDEX), table join types (nested loops, hash join, merge join), and sorting.

Creating a SQL explain plan

For any specific SQL query you can see SQL execution plan. In SQL*Plus you can issue the set autotrace command and then issue your SQL statement:
SQL> set autotrace traceonly explain
 SQL> select ename from emp where empno = 12;

 Execution Plan
 ----------------------------------------------------------
 0 SELECT STATEMENT Optimizer=CHOOSE
 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
 2 1 INDEX (UNIQUE SCAN) OF 'PK_EMP' (UNIQUE)
To see the output of an explain plan, you must first create a plan table. Oracle provides a script in $ORACLE_HOME/rdbms/admin called utlxplan.sql. Execute utlxplan.sql and create a public synonym for the plan_table:
sqlplus > @utlxplan
 Table created.

 sqlplus > create public synonym plan_table for sys.plan_table;
 Synonym created.
Most relational databases use an explain utility that takes the SQL statement as input, runs the SQL optimizer, and outputs the access path information into a plan_table, which can then be interrogated to see the access methods.
EXPLAIN PLAN SET STATEMENT_ID = 'test1' FOR
 SET STATEMENT_ID = 'RUN1'
 INTO plan_table
 FOR
 SELECT 
 

Oracle Tuning: The Definitive ReferenceOracle Tuning: The Definitive Reference


The first section deletes all rows from the plan table.
The first section deletes all rows from the plan table
The main part of the SQL populates the plan table with the access path information.
The final step displays the access information from the plan table.
The query produces the following output. Here we see the actual access path to the data