Kbase 15518: How Oracle Server execute SQL statements - use EXPLAIN PLAN
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
5/10/1998 |
|
How Oracle Server execute SQL statements - use EXPLAIN PLAN
Oracle provides a tool called EXPLAIN PLAN to assist in performance
evaluation of SQL statements. Use EXPLAIN PLAN to show how a statement
will be processed.
The EXPLAIN PLAN statement puts into a table information about the
execution plan chosen by the Oracle optimizer.
Steps to use the Explain tool:
1. Create the plan output table.
2. Execute the Explain Plan command.
3. Retrieve the plan steps using SQL statements.
4. Interpret the Execution Plan
1. Create The Plan Output Table
The syntax for creating the plan table is:
create table PLAN_TABLE (
statement_id varchar2(30),
timestamp date,
remarks varchar2(80),
object_instance numeric,
object_type varchar2(30),
optimizer varchar2(255),
search_columns numeric,
id numeric,
parent_id numeric,
position numeric,
other long);
This script named utlxplan.sql is provided by Oracle and is located in
$ORACLE_HOME/rdbms/admin. The default output table is name PLAN_TABLE.
You can create a similar table with any name you wish. If you chose
not to; you may share the PLAN_TABLE with others. utlxplan.sql must be
executed at least once to create the PLAN_TABLE table.
2. Execute the Explain Plan command
The Explain Plan command inserts a row into the plan table for each
step of the execution plan.
The Syntax diagram for the explain plan command:
>>----EXPLAIN PLAN-------------------------------------------->
|_______SET STATEMENT_ID = 'text'_____|
>----------------------------------------------FOR statement---><
|__INTO____________table_____|
|__schema_|
text = This is an optional identifier for the statement. You should
enter a value to identify each statement, as you may share
the PLAN TABLE with others.
schema.table = This is the optional name of the output table (default
is PLAN_TABLE).
statement = This is the text of the SQL statement.
Example:
sql> EXPLAIN PLAN INTO emp_plan_table FOR
2 SELECT * FROM emp
3 WHERE job = 'ANALYST'
4 AND deptno = 20
5 /
3. Retrieve The Plan Steps Using SQL Statements
Write a select statement to display the plan steps.
Example:
col Operation format a30
col Options format a20
col Object format a20
SELECT LPAD (' ', 2*LEVEL) || Operation ||
DECODE (id, 0, ' Cost = '||position) "Operation",
options "Options",
object_name "Object"
FROM emp_plan_table
CONNECTED BY PRIOR id = parent_id
START WITH id = 0
ORDER BY id
/
4. Interpret The Execution Plan
Example:
ID Operation Options Object
-- --------- ------- -----------
0 SELECT STATEMENT Cost =
1 TABLE ACCESS BY ROWID EMP
2 AND-EQUAL
3 INDEX RANGE SCAN JOB_INDEX
4 INDEX RANGE SCAN DEPTNO_INDEX
For further information on the subject see:
Oracle7 Server Application Developer's Guide
Progress Software Technical Support Note # 15518