SET AUTOTRACE EXPLAIN or EXECUTION Plan Options

You can automatically get a report on the execution path used by the SQL optimizer and the statement execution statistics.  The report is generated after successful SQL DML (Data Manipulation Language - that is, SELECT, DELETE, UPDATE and INSERT) statements.  It is useful for monitoring and tuning the performance of these statements.

SET AUTOTRACE Options Usage

You can control the report by setting the AUTOTRACE system variable.  SET is the main keyword to change the options. These can be set in SQLPLUS prompt.

SET AUTOTRACE OFF           - No AUTOTRACE report is generated. This is the
                              default. 
SET AUTOTRACE ON EXPLAIN    - The AUTOTRACE report shows only the optimizer
                              execution path.
SET AUTOTRACE ON STATISTICS - The AUTOTRACE report shows only the SQL
                              statement execution statistics. 
SET AUTOTRACE ON            - The AUTOTRACE report includes both the
                              optimizer execution path and the SQL
                              statement execution statistics. 
SET AUTOTRACE TRACEONLY     - Like SET AUTOTRACE ON, but suppresses the
                              printing of the user's query output, if any.

The session must have PLUSTRACE role granted and a PLAN_TABLE table created in your schema. 


What is Query execution Plan?

The Execution Plan shows the SQL optimizer's query execution path. Each line of the Execution Plan has a sequential line number. SQL*Plus also displays the line number of the parent operation.
The Execution Plan consists of four columns displayed in the following order: 

Column Name              Description
------------------------------------------------------------------------
  
ID_PLUS_EXP              Shows the line number of each execution step.
PARENT_ID_PLUS_EXP       Shows the relationship between each step and its
                         parent.  This column is useful for large reports.
PLAN_PLUS_EXP            Shows each step of the report.
OBJECT_NODE_PLUS_EXP     Shows the database links or parallel query servers
                         used. 

The format of the columns may be altered with the COLUMN command. 
For example, to stop the PARENT_ID_PLUS_EXP column being displayed, enter:

SQL> COLUMN PARENT_ID_PLUS_EXP NOPRINT

The default formats can be found in the site profile (for example, glogin.sql).

The Execution Plan output is generated using the EXPLAIN PLAN command.

The following is an example of tracing statements for performance statistics and query execution path. 

If the SQL buffer contains the following statement:

Generate EXPLAIN PLAN example

SQL> set autotrace on
SQL> select * from emp;

NAME                        AGE
-------------------- ----------
James                        43
Toad                         52
John                         32


Execution Plan
----------------------------------------------------------
Plan hash value: 2872589290

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     3 |    24 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |     3 |    24 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
        665  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google