Liren Large Software Subsidy 5/05.iso/a/a524/28.ddi/doc/explain.doc

Text File | 1991-03-04 | 13.6 KB | 403 lines

...
... $Header: explain.doc,v 6002700.1 89/05/23 00:39:22 rcs Exp $ explain.doc Copyr (c) 1989 Oracle
...


NOTE: The following is subject to change and describes an unsupported
feature of Oracle v6.0.25 and above.

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 [=] <string literal>]
    [INTO <table_name>]
    FOR <sql_statement>

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 <user_id>.PLAN_TABLE .  If
      the explicit or implicit table does not exist the EXPLAIN
      command will fail.

    <sql statement> - 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 analyzed.

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.

       Operation    Description
    ---------------------------------------------------------------
    And-Equal    A retreival utilizing intersection of
              rowids from index searches
    Connect by    A retrieval that is based on a tree walk
    Concatenation    A retrieval from a group of tables.  It is
            essentially a UNION ALL operation of the
            sources.  Used for OR operations.
    Counting    A node that is used to count the number of
              rows returned from a table.  Used for queries
              that use the ROWNUM meta-column.
    Filter        A restriction of the rows returned from a table
    First Row    A retrieval of only the first row
    For Update      A retreval that is used for updating
    Index        A retrieval from an index
    Intersection    A retrieval of rows common to two tables
    Merge Join    A join utilizing merge scans
    Minus        A retrieval of rows in Source 1 table but not in
              Source 2 table
    Nested Loops    A join utilizing 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.
    Project        A retrieval of a subset of columns from a table
    Remote        A retrieval from a database other than the current
              database
    Sequence    An operation involving a sequence table
    Sort        A retrieval of rows ordered on some column or group
              of columns
    Table        A retrieval from a base table
    Union        A retrieval of unique rows from two tables
    View        A retrieval from a virtual table
    -------------------------------------------------------------------

Note 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.

    OPERATION    OPTIONS               DESCRIPTION
        ---------------------------------------------------------------------
        Index        UNIQUE KEY         Unique key lookup on index
                     RANGE              Index range scan
        Merge Join   OUTER              Join is an outer join
        Nested Loops OUTER              Join is an outer join
        Sort         DISTINCT           Sort is to produce distinct values
                     GROUP BY           Sort is for grouping operation
                     JOIN               Sort is for merge join
                     ORDER BY           Sort is for order by
        Table        BY ROWID           Table scan is by rowid
                     FULL               Sequential table scan
                     CLUSTER            Table 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 the
    object 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
    recieves 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:

OPERATION  OPTIONS     OBJECT_NAME     ID          PARENT_ID      POSITION
--------------------------------------------------------------------------
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

The following example is based on the previous query.  It illustrates the
use of the treewalking capability in Oracle's version of SQL.

        SELECT LPAD(' ',2*LEVEL)||OPERATION,OPTIONS, OBJECT_NAME
    FROM PLAN_TABLE WHERE STATEMENT_ID='query7'
    CONNECT BY PRIOR ID = PARENT_ID
           and STATEMENT_ID = 'query7'
     START WITH ID = 1
           and STATEMENT_ID = 'query7'
    ORDER BY ID;


LPAD(' ',2*LEVEL)||OPERATION
------------------------------------------------------------------------------
                 OPTIONS   OBJECT_NAME
-------------------------------------
PROJECTION

  UNION

    SORT
                 DISTINCT
      NEST LOOP

        TABLE SCAN
                 BY ROWID      T1
              INDEX SCAN
                 RANGE         IT1
        TABLE SCAN
             FULL          T2
    SORT
                 DISTINCT
      MERGE JOIN

        SORT
                 JOIN
          TABLE SCAN
                 FULL           T2
        SORT
                 JOIN
          TABLE SCAN
                  FULL          T3

13 RECORDS selected