Tracing Statements

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 (that is, SELECT, DELETE, UPDATE and INSERT) statements. It is useful for monitoring and tuning the performance of these statements.

Controlling the Report

You can control the report by setting the AUTOTRACE system variable.

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.

To use this feature, you must create a PLAN_TABLE table in your schema and then have the PLUSTRACE role granted to you. DBA privileges are required to grant the PLUSTRACE role. For information on how to grant a role and how to create the PLAN_TABLE table, see the Oracle9i SQL Reference. For more information about the roles and the PLAN_TABLE, see the Oracle9i SQL Reference and the AUTOTRACE variable of the SET command in Chapter 8.

Example 3-20 Creating a PLAN_TABLE

Run the following commands from your SQL*Plus session to create the PLAN_TABLE in the HR schema:

CONNECT HR/HR 
@$ORACLE_HOME/RDBMS/ADMIN/UTLXPLAN.SQL 


Table created.

Example 3-21 Creating the PLUSTRACE Role

Run the following commands from your SQL*Plus session to create the PLUSTRACE role and grant it to the DBA:

CONNECT / AS SYSDBA 
@$ORACLE_HOME/SQLPLUS/ADMIN/PLUSTRCE.SQL 


drop role plustrace;
Role dropped.
create role plustrace;
Role created.
.
.
.
grant plustrace to dba with admin option;
Grant succeeded.

Example 3-22 Granting the PLUSTRACE Role

Run the following commands from your SQL*Plus session to grant the PLUSTRACE role to the HR user:

CONNECT / AS SYSDBA 
GRANT PLUSTRACE TO HR; 


Grant succeeded.

Execution Plan

The Execution Plan shows the SQL optimizer's query execution path. Both tables are accessed by a full table scan, sorted, and then merged.

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

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. For information about interpreting the output of EXPLAIN PLAN, see the Oracle9i Performance Guide and Reference.

Statistics

The statistics are recorded by the server when your statement executes and indicate the system resources required to execute your statement.

The client referred to in the statistics is SQL*Plus. Oracle Net refers to the generic process communication between SQL*Plus and the server, regardless of whether Oracle Net is installed.

You cannot change the default format of the statistics report.

For more information about the statistics and how to interpret them, see the Oracle9i Performance Guide and Reference.

Example 3-23 Tracing Statements for Performance Statistics and Query Execution Path

If the SQL buffer contains the following statement:

SELECT E.LAST_NAME, E.SALARY, J.JOB_TITLE
FROM EMPLOYEES E, JOBS J
WHERE E.JOB_ID=J.JOB_ID AND E.SALARY>12000

The statement can be automatically traced when it is run:

SET AUTOTRACE ON/


LAST_NAME                     SALARY JOB_TITLE
------------------------- ---------- -----------------------------------
King                           24000 President
Kochhar                        17000 Administration Vice President
De Haan                        17000 Administration Vice President
Russell                        14000 Sales Manager
Partners                       13500 Sales Manager
Hartstein                      13000 Marketing Manager

6 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMPLOYEES'
   2    1     NESTED LOOPS
   3    2       TABLE ACCESS (FULL) OF 'JOBS'
   4    2       INDEX (RANGE SCAN) OF 'EMP_JOB_IX' (NON-UNIQUE)




Statistics
----------------------------------------------------------
          0  recursive calls
          2  db block gets
         34  consistent gets
          0  physical reads
          0  redo size
        848  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          6  rows processed

Note: Your output may vary depending on the version of the server to which you are connected and the configuration of the server.

Example 3-24 Tracing Statements Without Displaying Query Data

To trace the same statement without displaying the query data, enter:

SET AUTOTRACE TRACEONLY
/


6 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMPLOYEES'
   2    1     NESTED LOOPS
   3    2       TABLE ACCESS (FULL) OF 'JOBS'
   4    2       INDEX (RANGE SCAN) OF 'EMP_JOB_IX' (NON-UNIQUE)




Statistics
----------------------------------------------------------
          0  recursive calls
          2  db block gets
         34  consistent gets
          0  physical reads
          0  redo size
        848  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          6  rows processed

This option is useful when you are tuning a large query, but do not want to see the query report.

Example 3-25 Tracing Statements Using a Database Link

To trace a statement using a database link, enter:

SET AUTOTRACE TRACEONLY EXPLAIN
SELECT * FROM EMPLOYEES@MY_LINK;


Execution Plan
-----------------------------------------------------------
0      SELECT STATEMENT (REMOTE) Optimizer=CHOOSE
1    0   TABLE ACCESS (FULL) OF 'EMPLOYEES'  MY_LINK.DB_DOMAIN

The Execution Plan shows that the table being accessed on line 1 is via the database link MY_LINK.DB_DOMAIN.

Tracing Parallel and Distributed Queries

When you trace a statement in a parallel or distributed query, the Execution Plan shows the cost based optimizer estimates of the number of rows (the cardinality). In general, the cost, cardinality and bytes at each node represent cumulative results. For example, the cost of a join node accounts for not only the cost of completing the join operations, but also the entire costs of accessing the relations in that join.

Lines marked with an asterisk (*) denote a parallel or remote operation. Each operation is explained in the second part of the report. See the Oracle9i Performance Guide and Reference for more information on parallel and distributed operations.

The second section of this report consists of three columns displayed in the following order

Column Name	Description
ID_PLUS_EXP	Shows the line number of each execution step.
OTHER_TAG_PLUS_EXP	Describes the function of the SQL statement in the OTHER_PLUS_EXP column.
OTHER_PLUS_EXP	Shows the text of the query for the parallel server or remote database.

The format of the columns may be altered with the COLUMN command. The default formats can be found in the site profile (for example, glogin.sql).

Note: You must have Oracle7, Release 7.3 or greater to view the second section of this report.

Example 3-26 Tracing Statements With Parallel Query Option

To trace a parallel query running the parallel query option:

create table D2_t1 (unique1 number) parallel -
(degree 6);


Table created.


create table D2_t2 (unique1 number) parallel -
(degree 6);


Table created.


create unique index d2_i_unique1 on d2_t1(unique1);


Index created.


set long 500 longchunksize 500
SET AUTOTRACE ON EXPLAIN
SELECT /*+ INDEX(B,D2_I_UNIQUE1) USE_NL(B) ORDERED -
*/ COUNT (A.UNIQUE1)
FROM D2_T2 A, D2_T1 B
WHERE A.UNIQUE1 = B.UNIQUE1;



Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=26)
   1    0   SORT (AGGREGATE)
   2    1     SORT* (AGGREGATE)                                        :Q2000
   3    2       NESTED LOOPS* (Cost=1 Card=41 Bytes=1066)              :Q2000
   4    3         TABLE ACCESS* (FULL) OF 'D2_T2' (Cost=1 Card=41 Byte :Q2000
          s=533)

   5    3         INDEX* (UNIQUE SCAN) OF 'D2_I_UNIQUE1' (UNIQUE)      :Q2000

   2 PARALLEL_TO_SERIAL            SELECT /*+ PIV_SSF */ SYS_OP_MSR(COUNT(A1.C0
                                   )) FROM (SELECT /*+ ORDERED NO_EXPAND USE_NL
                                   (A3) INDEX(A3 "D2_I_UNIQUE1") */ A2.C0 C0,A3
                                   .ROWID C1,A3."UNIQUE1" C2 FROM (SELECT /*+ N
                                   O_EXPAND ROWID(A4) */ A4."UNIQUE1" C0 FROM "
                                   D2_T2" PX_GRANULE(0, BLOCK_RANGE, DYNAMIC)
                                   A4) A2,"D2_T1" A3 WHERE A2.C0=A3."UNIQUE1")
                                   A1

   3 PARALLEL_COMBINED_WITH_PARENT
   4 PARALLEL_COMBINED_WITH_PARENT
   5 PARALLEL_COMBINED_WITH_PARENT

Line 0 of the Execution Plan shows the cost based optimizer estimates the number of rows at 1, taking 26 bytes. The total cost of the statement is 1.

Lines 2, 3, 4 and 5 are marked with asterisks, denoting parallel operations. For example, the NESTED LOOPS step on line 3 is a PARALLEL_TO_SERIAL operation. PARALLEL_TO_SERIAL operations execute a SQL statement to produce output serially. Line 2 also shows that the parallel query server had the identifier Q2000.