Page 557
It is important to understand that the results of an EXPLAIN PLAN are, therefore, by no means fixed and finite. The DBA must be aware of changes made to database objects ”such as adding new indexes ”and how fast the tables are growing.
The Oracle RDBMS uses the EXPLAIN PLAN by storing information about how a query is executing in a table within the user's schema. The table must exist for the EXPLAIN PLAN to work. To create the table, the user must execute the following script. Of course, he must have the CREATE TABLE and RESOURCE or quota privileges on his default tablespace.
% svrmgrl SVRMGR> connect scott/tiger Connected. SVRMGR> @$ORACLE_HOME/rdbms/admin/utlxplan.sql Statement Processed.
After the table has been created, an EXPLAIN PLAN can be generated from a query by prefacing the query with the command to perform an EXPLAIN PLAN. The following script shows how to format a query for an EXPLAIN PLAN:
CONNECT / EXPLAIN PLAN SET STATEMENT_ID = `QUERY1' INTO PLAN_TABLE FOR SELECT O.ORDER_DATE, O.ORDERNO, O.PARTNO, P.PART_DESC, O.QTY FROM ORDER O, PART P WHERE O.PARTNO = P.PARTNO
Note the SET STATEMENT and INTO clauses of the EXPLAIN PLAN. The value of SET STATEMENT is used to make the execution of the EXPLAIN PLAN stored within the table unique; it can be virtually any string up to 30 characters in length. Specifying a table in the INTO clause, on the other hand, tells the EXPLAIN PLAN where to place information about the query execution. In the previous example, the execution of the query is identified as QUERY1 and has its information stored in the table PLAN_TABLE.
Now that the EXPLAIN PLAN has loaded the table with information, there is the obvious question of how to retrieve and interpret the information provided. Oracle provides a script in the Oracle7 Server Utilities Guide that displays information in a tree-like fashion. It is
SELECT LPAD(` `, 2*(LEVEL-1))operation' ` options, object_name "QUERY PLAN" FROM plan_table START WITH id = 0 AND statement_id = `QUERY1' CONNECT BY PRIOR id = parent_id /
Page 558
By running a SQL query through the EXPLAIN PLAN, a pseudo-graph similar to the following is produced:
QUERY PLAN ------------------------------------------------------------------------------ SORT ORDER BY NESTED LOOPS FILTER NESTED LOOPS OUTER TABLE ACCESS FULL HEADER TABLE ACCESS BY ROWID DETAIL INDEX RANGE SCAN DETAIL_PK INDEX RANGE SCAN DETAIL_PK TABLE ACCESS FULL HEADER TABLE ACCESS BY ROWID DETAIL INDEX RANGE SCAN DETAIL_PK INDEX RANGE SCAN DETAIL_PK NESTED LOOPS OUTER TABLE ACCESS FULL HEADER TABLE ACCESS BY ROWID DETAIL INDEX RANGE SCAN DETAIL_PK INDEX RANGE SCAN DETAIL_PK TABLE ACCESS FULL HEADER TABLE ACCESS BY ROWID DETAIL INDEX RANGE SCAN DETAIL_PK INDEX RANGE SCAN DETAIL_PK TABLE ACCESS BY ROWID DETAIL INDEX RANGE SCAN DETAIL_PK INDEX RANGE SCAN DETAIL_PK FILTER TABLE ACCESS FULL HEADER TABLE ACCESS BY ROWID DETAIL INDEX RANGE SCAN DETAIL_PK INDEX RANGE SCAN DETAIL_PK TABLE ACCESS FULL HEADER TABLE ACCESS BY ROWID DETAIL INDEX RANGE SCAN DETAIL_PK INDEX RANGE SCAN DETAIL_PK NESTED LOOPS OUTER TABLE ACCESS BY ROWID DETAIL INDEX RANGE SCAN DETAIL_PK INDEX RANGE SCAN DETAIL_PK TABLE ACCESS FULL HEADER TABLE ACCESS BY ROWID DETAIL INDEX RANGE SCAN DETAIL_PK INDEX RANGE SCAN DETAIL_PK
When you interpret the output, it is important to understand that all operations, as reported by the EXPLAIN PLAN, are basically operation/option combinations. There is no way to discuss all these combinations or the possible interpretations of all the EXPLAIN PLAN scenarios. As with many aspects of the IS industry ” especially relational databases ”the only true teacher is experience. However, here are some of the more common operation/option pairs that EXPLAIN PLANs returns:
Page 559
FILTER | Eliminates rows from a table by conditions specified in the WHERE clause of a SQL statement |
INDEX/RANGE SCAN | Accesses information in the table via a non-unique index (specified in the object_name column) |
INDEX/UNIQUE | Accesses information in the table via a unique or primary key index (specified in the object_name column) |
MERGE/JOIN | Combines two sorted lists of data into a single, sorted list; used on multi-table queries |
SORT/GROUP BY | Sorts table data as specified in a GROUP BY clause of the SQL statement |
SORT/JOIN | Performs a sort on the data from the tables before a MERGE JOIN operation |
SORT/ORDER BY | Sorts table data as specified in an ORDER BY clause of a SQL statement |
SORT/UNIQUE | Performs a sort on table data being returned and eliminates duplicate rows |
TABLE ACCESS/FULL | Performs a full scan of the database table to locate and return required data |
TABLE ACCESS/ROWID | Locates a row in a database table by using its unique row ID |
VIEW | Returns information from a database view |
NOTE |
In a database in which full table scans frequently occur (and are expected), the DB_FILE_MULTIBLOCK_READ_COUNT parameter in the INIT.ORA parameter file can be adjusted to read more blocks at once. The default value for this parameter is eight blocks. |
The EXPLAIN PLAN is a powerful tool for software developers because it enables them to ensure that their queries are properly tuned . Of course, changes made to database objects can adversely affect the results of the EXPLAIN PLAN, but they are useful in determining where the performance drains on an application will occur.
Oracle SQL*Trace and EXPLAIN PLAN are similar in that they are both used to do performance tuning at the application level and that they both show the manner in which the Oracle RDBMS executes a query. Unlike the EXPLAIN PLAN, which simply shows how the database optimizer chooses to execute a query to return specified information, SQL*Trace reveals the quantitative