Explain Facility

MOTIVATION

There is a need for users to be able to determine the steps the system takes in performing various operations on a database. The EXPLAIN facility provides users with a convenient mechanism for getting this information.

The facility stores this information in a standard database table that can be manipulated with standard SQL statements.

SYNTAX 

The following syntax is based on the syntax used by DB2 for their EXPLAIN facility:

EXPLAIN PLAN [SET STATEMENT_ID [=] ]
[INTO ]
FOR 
where
STATEMENT_ID - a unique optional identifier for the statement;
               
INTO - allows user to save the results of the analysis in the specified table. The table must conform to the format for the table used to store the analysis ( see TABLE FORMATS section for a description of the table format ).  If this clause is not specified, the system will then attempt to store the information in a table named .PLAN_TABLE .  If the explicit or implicit table does not exist the EXPLAIN command will fail.
                       
 - an insert, delete, update, or query statement;
                       
TABLE FORMATS

Core Table Format
               The core table used to represent the plan information consists of the following fields:

STATEMENT_ID - An identifier associated with the statement.  If not set by the user, the identifier will be NULL.  Note that a user may identify a statement by the timestamp field.

TIMESTAMP - The date and time when the statement was analysed.

REMARKS - Any comment the user wishes to associate with this step of the analysis.

OPERATION - the name of the operation being performed.  The following table provides a listing of the operations described by the facility.

OperationDescriptionAnd-EqualA retrieval utilising intersection of rowids from index searchesConnect byA retrieval that is based on a tree walkConcatenationA retrieval from a group of tables.  It is essentially a UNION ALL operation of the sources.  Used for OR operationsCountingA node that is used to count the number of rows returned from a table.  Used for queries that use the ROWNUM meta-column. FilterA restriction of the rows returned from a tableFirst 
RowA retrieval of only the first rowFor UpdateA retrieval that is used for updatingIndexA retrieval from an indexIntersectionA retrieval of rows common to two tablesMerge JoinA join utilising merge scansMinusA retrieval of rows in Source 1 table but not  inSource 2 tableNested  LoopsA join utilising  nested  loops.   Each value in the first 
subnode is looked up in the second  subnode. This is often used when one table in a join  is indexed and the other is not. ProjectA retrieval of a subset of columns from a tableRemoteA re! trieval from a database other than the                Current databaseSequenceAn operation involving a sequence tableSortA retrieval of rows ordered on some column or group of columnsTableA retrieval from a base tableUnionA retrieval of unique rows from two tablesViewA retrieval from a virtual tableNote that the operation shown when counting the number of rows returned by a query (i.e. select count(*)) is SORT.  This is due to the way that COUNT is implemented internally.  The table will not really be sorted.

OPTIONS - an option that modifies the operation, e.g., OUTER option on join operations, rationale for sorting, type of index scan, type of filter, etc.  The following table provides a list of the options for the operations that have options.

OPERATIONOPTIONSDESCRIPTIONIndexUNIQUE KEYUnique key lookup on indexRANGEIndex range scanMerge JoinOUTERJoin is an outer joinNested LoopsOUTERJoin is an outer joinSortDISTINCTSort is to produce distinct valuesGROUP BYSort is for grouping operationJOINSort is for merge joinORDER BYSort is for order byTableBY ROWIDTable scan is by rowidFULLSequential table scanCLUSTERTable scan by cluster key

OBJECT_NODE - the name of the node that owns the database object.

OBJECT_OWNER - the name of the schema the owns the database object.
               
OBJECT_NAME - the name of the database object.
               
OBJECT_TYPE - a modifier that provides descriptive information about the database object, e.g., NON-UNIQUE for indexes, etc.
               
OBJECT_INSTANCE - a number corresponding to the ordinal position of theobject as it appears in the original query.  The numbering proceeds from left to right, outer to inner with respect to the original query text.  Note that at this level, view expansion will result in rather interesting object instance numbers.  We will be addressing this issue fully in future releases.
                 
SEARCH_COLUMNS - the number of leading columns used when searching an index.

ID - a number assigned to this operation in the tree. Corresponds to a preorder traversal of the row source tree.
               
PARENT_ID - the number assigned to the previous operation that receives   information  from this  operation.   This   field combined with the ID field allows users to do a treewalk of the specified plan with the CONNECT BY statement.
               
POSITION - the position this database object occupies for the previous operation.
               
OTHER - other information that is specific to the row source that 
a user may find useful.  For example, the select statement to a remote node, etc.

Sample Table Definition
               
               
               create table PLAN_TABLE (
                     statement_id    char(30),
                     timestamp       date,
                     remarks         char(80),
                     operation       char(30),
                     options         char(30),
                     object_node     char(30),
                     object_owner    char(30),
                     object_name     char(30),
                     object_instance numeric,
                     object_type     char(30),
                     search_columns  numeric,
                     id              numeric,
                     parent_id       numeric,
                     position        numeric,
                     other           long);

An SQL script to create this table resides in file xplainpl.sql in the same directory containing the file catalog.sql.  This table must reside in the current schema unless you use the optional INTO clause of the EXPLAIN command.

EXAMPLES
               
Suppose we issue the following statements:
               
EXPLAIN PLAN 
SET STATEMENT_ID = 'query1'
INTO QUERY_PLANS
FOR SELECT * FROM T1,T2,T3 WHERE T1.F1 = T2.F1 AND T2.F2 = T3.F2;
               
SELECT OPERATION, OPTIONS, OBJECT_NAME, ID, PARENT_ID, POSITION
                       FROM QUERY_PLANS 
                       WHERE STATEMENT_ID = 'query1'
                       ORDER BY ID;

The following output would be created:
               
               OPERATION     OPTIONS  OBJECT_NAME      ID          PARENT_ID     POSITION
               >--------------------------------------------------------------------------
               >MERGE JOIN                              1
               >MERGE JOIN                              2              1           1
               >SORT          JOIN                      3              2           1
               >TABLE ACCESS  FULL    T1                4              3           1
               >SORT          JOIN                      5              2           2
               >TABLE ACCESS  FULL    T2                6              5           1
               >SORT          JOIN                      7              1           1
               >
               >TABLE ACCESS  FULL    T3                8              7           1
               >
               >8 RECORDS selected
               >
               >

Suppose that an index is created on field F1 on table T1 and the following statements are issued:
               
EXPLAIN PLAN
SET STATEMENT_ID = 'query2'
INTO QUERY_PLANS
FOR SELECT * FROM T1 WHERE F1 > 1;

SELECT OPERATION, OPTIONS, OBJECT_NAME, OBJECT_TYPE, SEARCH_COLUMNS, 
ID, PREVIOUS_ID
FROM QUERY_PLANS 
WHERE STATEMENT_ID='query2'
                        ORDER BY ID;

The following output is produced:
               >
               >OPERATION   OPTIONS  OBJECT_NAME OBJECT_TYPE SEARCH_COLUMNS    ID    
               POSITION
               >-----------------------------------------------------------------------------
               >TABLE SCAN   BY ROWID T1                                       1
               >INDEX SCAN   RANGE    IT1        NON-UNIQUE     1              2           
               1
               >
               >2 RECORDS selected
               >
               >
               
With the same conditions as before, suppose we issue the following which demonstrates an index only retrieval:
               
EXPLAIN PLAN
SET STATEMENT_ID = 'query3'
INTO QUERY_PLANS
FOR SELECT F1 FROM T1 WHERE F1 > 1;
               
SELECT OPERATION, OPTIONS, OBJECT_NAME, OBJECT_TYPE,SEARCH_COLUMNS, ID
                        FROM QUERY_PLANS WHERE
                        STATEMENT_ID='query3';
               
The following output is produced: 
               
               OPERATION   OPTIONS  OBJECT_NAME   OBJECT_TYPE  SEARCH_COLUMNS ID
               >--------------------------------------------------------------------------
               >INDEX SCAN   RANGE    IT1          NON-UNIQUE        1
               >
               >1 RECORDS selected
               >
               
The next example illustrates the output if a grouping operation is specified in the statement:

EXPLAIN PLAN
SET STATEMENT_ID = 'query4'
INTO QUERY_PLANS
FOR SELECT AVG(F1),F2 FROM T1 GROUP BY F2;
               

SELECT OPERATION, OPTIONS, OBJECT_NAME, ID, PARENT_ID
FROM QUERY_PLANS 
WHERE STATEMENT_ID = 'query4'
                        ORDER BY ID;
               
               >OPERATION     OPTIONS    OBJECT_NAME            ID          PARENT_ID
               >---------------------------------------------------------------------
               >SORT          GROUP BY                          1
               >TABLE SCAN    FULL      T1                      2             1
               >
               >2 RECORDS selected
               >

The next example illustrates the ouptut if DISTINCT is specified in the statement:
               
EXPLAIN PLAN
SET STATEMENT_ID = 'query5'
                      INTO QUERY_PLANS
                      FOR SELECT DISTINCT F1 FROM T1;
               
SELECT OPERATION, OPTIONS, OBJECT_NAME, ID, PREVIOUS_ID
FROM QUERY_PLANS 
WHERE STATEMENT_ID = 'query5'
                        ORDER BY ID;
               
               >OPERATION     OPTIONS    OBJECT_NAME           ID   PREVIOUS_ID
               >--------------------------------------------------------------
               >SORT          DISTINCT                         1
               >TABLE SCAN    FULL      T1                     2         1
               >
               >2 RECORDS selected
               >
               
The next example illustrates the output if a subquery is specified in the statement:
               
EXPLAIN PLAN
SET STATEMENT_ID = 'query6'
INTO QUERY_PLANS
FOR SELECT * FROM T1 WHERE F1 < ( SELECT F2 FROM T2 
WHERE F1=3);
               
SELECT OPERATION, OPTIONS, OBJECT_NAME, ID, PARENT_ID, POSITION
FROM QUERY_PLANS 
WHERE STATEMENT_ID = 'query6'
                        ORDER BY ID;
               
               >OPERATION     OPTIONS  OBJECT_NAME        ID          PARENT_ID  
               POSITION
               >-------------------------------------------------------------------------
               >FILTER          OUT                       1
               >TABLE SCAN      FULL  T1                  2            1          1
               >TABLE SCAN      FULL  T2                  3            1          2
               >
               >3 RECORDS selected
               >
               
The final example displays a complex query whose output is sent to the default plan table. ( It is assumed that this table has been created before issuing the statement.)
               
EXPLAIN PLAN
SET STATEMENT_ID = 'query7'
                      FOR SELECT * FROM T1,T2 
WHERE T1.F1 = T2.F1 
UNION
                          SELECT * FROM T2,T3 
WHERE T2.F1 = T3.F1;
               
SELECT OPERATION, OPTIONS, OBJECT_NAME, ID, PARENT_ID, POSITION
FROM PLAN_TABLE 
WHERE STATEMENT_ID = 'query7'
                        ORDER BY ID;

The following output is produced:
               
OPERATIONOPTIONSOBJECT_NAMEIDPARENT_IDPOSITION

               PROJECTION                             1
               UNION                                  2             1              1
               SORT        DISTINCT                   3             2              1
               NEST LOOP                              4             3              1
               TABLE SCAN  BY ROWID   T1              5             4              1
               INDEX SCAN  RANGE      IT1             6             5              1
               TABLE SCAN  FULL       T2              7             4              2
               SORT        DISTINCT                   8             2              2
               MERGE JOIN                             9             8              1
               SORT        JOIN                       10            9              1
               TABLE SCAN  FULL       T2              11            10             1
               SORT        JOIN                       12            9              2
               TABLE SCAN  FULL       T3              13            12             1
               
               >13 RECORDS selected
               >
               >

Oracle Database

See Also

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips

All the site contents are Copyright © www.erpgreat.com and the content authors. All rights reserved.
All product names are trademarks of their respective companies.
The site www.erpgreat.com is not affiliated with or endorsed by any company listed at this site.
Every effort is made to ensure the content integrity.  Information used on this site is at your own risk.
 The content on this site may not be reproduced or redistributed without the express written permission of
www.erpgreat.com or the content authors.