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.