14.2 How do I…Use EXPLAIN PLAN to analyze query execution paths?

Problem

Some of the queries issued against my database do not seem to perform acceptably. I want to use the EXPLAIN PLAN statement to determine what physical operations Oracle8 is using to execute these queries. How do I analyze a query using EXPLAIN PLAN? Technique

When DBAs and developers use the EXPLAIN PLAN statement with an SQL statement, Oracle8 reports the statement’s execution path. This is the sequence of physical operations that Oracle8 uses to execute the statement.

The EXPLAIN PLAN statement stores its results in a table called PLAN_TABLE. This table must exist prior to EXPLAIN PLAN execution and fortunately Oracle provides a script file to create this table. The script’s name is system-dependent; under Windows NT, it resides in the \ORANT\RDBMS80\ADMIN subdirectory. After invocation of EXPLAIN PLAN, query the PLAN_TABLE to reveal the execution of a particular SQL statement.

Table 14.3 shows the structure of PLAN_TABLE. The most important output of EXPLAIN PLAN resides in PLAN_TABLE’s OPERATION column, which reveals how Oracle has processed the statement on a physical level. The possible values of this attribute appear in Table 14.4.

Table 14.3 The structure of PLAN_TABLE

Column Column Description
STATEMENT_ID Statement ID (user-specified)
TIMESTAMP Date and time of EXPLAIN PLAN execution
REMARKS User-specified comments for EXPLAIN PLAN steps
OPERATION Oracle-performed operation in this step
OPTIONS More information on OPERATION
OBJECT_NODE Database link name used to reference the object
OBJECT_OWNER Username of schema owner containing the object
OBJECT_NAME Table or index name
OBJECT_INSTANCE Object’s position
OBJECT_TYPE Object descriptive information
OPTIMIZER Optimizer mode in effect at execution time
SEARCH_COLUMNS Not used
ID Number of step in the execution plan
PARENT_ID Number of the next step
POSITION Processing order for each step with this PARENT_ID
OTHER Other step-related information
OTHER_TAG Further description of OTHER
PARTITION_START Starting partition
PARTITION_STOP Last partition accessed
PARTITION_ID Number of step that computed PARTITION_START and PARTITION STOP
COST If optimizer is in COST mode, this is the relative cost of the operation, and it is null if optimizer is in rule mode
CARDINALITY Estimated number of rows accessed by OPERATION
BYTES Estimated number of bytes accessed by OPERATION

Table 14.4 Possible values of the OPERATION column in PLAN_TABLE

Operation Option Operation Description
AND-EQUAL   Returns intersection of multiple ROWIDs without duplicates
CONNECT BY   Tree walk (hierarchical access)
CONCATENATION   UNION of multiple rows
COUNT   Row count
COUNT STOPKEY Row count limited by WHERE clause ROWNUM expression
FILTER   Row elimination based on a WHERE clause
FIRST ROW   Query that returns the first row only
FOR UPDATE   Row locking via the FOR UPDATE statement
INDEX* UNIQUE SCAN Index used to return one row
INDEX* RANGE SCAN Ascending index-based retrieval of multiple ROWIDs
INDEX* RANGE SCAN DESC Descending index-based retrieval of multiple ROWIDs
INTERSECTION   Accepts two sets of rows, returns intersection without duplicates
MERGE JOIN   Accepts and combines two sorted row sets and returns matching rows
MERGE JOIN OUTER MERGE-JOIN with outer join
MINUS   Accepts two row sets and returns rows in first set but not in second via MINUS operator
NESTED LOOPS   Accepts outer and inner rows sets and compares each outer row with each inner row
NESTED LOOPS OUTER NESTED LOOPS with outer join
PROJECTION   Returns subset of table columns
REMOTE   Use of database link
SEQUENCE   Access sequence values
SORT AGGREGATE Returns single row from group operation
SORT UNIQUE Returns set of rows, duplicates eliminated
SORT GROUP BY Accepts and groups set of rows for GROUP BY processing
SORT JOIN Accepts and sorts a row set in preparation for a merge-join operation.
SORT ORDER BY Accepts and sorts a row set in preparation for an ORDER BY operation.
TABLE ACCESS FULL Row retrieval via a full table scan
TABLE ACCESS CLUSTER Row retrieval via indexed cluster
TABLE ACCESS HASH Row retrieval via hash cluster
TABLE ACCESS BY ROWID Row retrieval based on ROWID
UNION   Accepts two sets of rows and returns union; eliminates duplicates.
VIEW   Performs view-based retrieval

Steps

1. Run SQL*Plus and connect as the WAITE user. Use the start command with a fully qualified path name describing the location of the UTLXPLAN script. The script creates a version of PLAN_TABLE in the schema of the invoking user. The invocation syntax under Windows NT appears below.

SQL> start \orant\rdbms80\admin\utlxplan.sql

Table created.

2. Use the START command to load and execute the script CHP14_2.SQL. The script and output appear in Listing 14.1.

Listing 14.1 Running the CHP14_2.SQL script in SQL*Plus to create some sample objects

SQL> start chp14_2

SQL>

SQL> CREATE TYPE emp_type14 AS OBJECT

2 (empno number(4),

3 ename varchar2(10)

4 );

Type created.

SQL>

SQL> CREATE TYPE emp_table14 AS TABLE OF emp_type14;

Type created.

SQL>

SQL> CREATE TABLE dept14

2 (

3 deptno number(2),

4 dname varchar2(14),

5 emps emp_table14

6 )

7 NESTED TABLE emps STORE AS store_dept_emps14;

Table created.

SQL>

SQL> CREATE INDEX idx_dept14

2 ON dept14 (deptno);

Index created.

SQL>

SQL> CREATE INDEX idx_emp_table14

2 ON store_dept_emps14 (empno);

Index created.

SQL>

SQL> INSERT INTO dept14 VALUES

2 (1, ‘DATA MGMT’, emp_table14(emp_type14(1, ‘MARKIE’),

3 emp_type14(2, ‘SARA’),

emp_type14(3, ‘DEBBIE’))

4 );

1 row created.

SQL>

SQL> INSERT INTO dept14 VALUES

2 (2, ‘CHEMISTRY’, emp_table14(emp_type14(4, ‘STEVE’),

3 emp_type14(5, ‘NANCY’))

4 );

The CHP14_2.SQL script creates a table called DEPT14 containing the nested table EMPS (you’ll read more about these in Chapter 16). EMPS is based on the EMP_TYPE14 object type that stores information about individual employees. The script creates two indexes. The first is a commonplace index on the DEPT14 table, but the second index is more interesting; it is on the column EMPNO of the nested table called EMP. To index a column of a nested table, use the storage clause in the CREATE INDEX statement.

3. Use the START command to load and execute the CHP14_3.SQL script. This issues the EXPLAIN PLAN statement for a query that selects data from the DEPT14 table and the EMPS nested table. The query and its output appear in Figure 14.2.

The query appears in lines 4 through 8. The nuances of the syntax are not important for this discussion; they are explained more fully in Chapter 16. The output that EXPLAIN PLAN generates is labeled in the PLAN_TABLE by the value of the STATEMENT_ID column. The first statement in the script deletes any existing EXPLAIN PLAN output from PLAN_TABLE for statement ID CHP14_3. The next three lines invoke EXPLAIN PLAN, set the STATEMENT_ID, and tell Oracle to insert EXPLAIN PLAN output into PLAN_TABLE.

4. A straight SELECT from PLAN_TABLE is not particularly useful. Its output is too difficult to interpret. Use the START command to load and execute the script CHP14_4.SQL, which produces more useful output. Figure 14.3 shows the script and its output.

The script appears in the Oracle8 online documentation and is a tree walk through the PLAN_TABLE. One useful modification to the script is to have it prompt for the value of the STATEMENT_ID using a SQL*Plus substitution variable in lines 5 and 6. The double ampersands confine SQL*Plus to a single prompt for the value of STATID.

In Figure 14.4, the value of STATID is CHP14_3. Reading the output from the query is not intuitive; the general rule is to read PLAN_TABLE output from the inside out and from the top to the bottom. This only makes sense after a few practice attempts, but application of this rule to the output in Figure 14.3 reveals the following execution plan:

4 A range scan of the IDX_EMP_TABLE14 index

4 A table access by ROWID of the STORE_DEPT_EMPS 14 nested

table storage table

4 A range scan of the IDX_DEPT14 index

4 A table access by ROWID of the DEPT14 table

4 An execution of a WHERE clause

How It Works

Create the plan table by running the UTLXPLAN script. When DBAs or developers invoke the EXPLAIN_PLAN command, it populates this table. Query the PLAN_TABLE using the script in Step 4 to illustrate the execution plan.

Comments

Every developer can write better SQL code through use of the EXPLAIN PLAN command, and there is no reason why a PLAN_TABLE cannot exist in every developer’s schema to achieve this end. Oracle8 does not actually execute queries appearing in EXPLAIN PLAN; it only processes them enough to output the relevant execution plan. This is both an advantage and a drawback. The benefit is that if a developer knows a query performs poorly, then EXPLAIN PLAN provides a low resource analysis technique. On the other hand, looking at EXPLAIN PLAN output prior to actual query execution can lead users to incorrect conclusions about SQL statement performance. For this reason, most developers and DBAs confine EXPLAIN PLAN analysis to queries which are proven poor performers.