0557-0559

Previous Table of Contents Next

Page 557

• The addition or deletion of new indexes on one of the tables in the SQL statement when running the database under the Rule-Based Optimizer
• Subtle changes in the WHERE or FROM clause of a SQL SELECT statement when running the database under the Rule-Based Optimizer
• The presence of database objects with the same name as the object being referenced in the schema of the user executing the query

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.

SQL*Trace and TKPROF

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

Previous Table of Contents Next