11
Using Autotrace in SQL*Plus

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.

This chapter contains the following sections:

• Controlling the Autotrace Report

• Tracing Parallel and Distributed Queries

Controlling the Autotrace 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 have the PLUSTRACE role granted to you and a PLAN_TABLE table created in your schema.

See Also: Oracle9i SQL Reference for more information about the PLUSTRACE role and the PLAN_TABLE SQL*Plus User's Guide and Reference for more information about the AUTOTRACE variable of the SET command

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 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 the following:

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.

See Also: Chapter 9, "Using EXPLAIN PLAN" for more information about interpreting the output of EXPLAIN PLAN

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.

See Also: Chapter 3, "Gathering Optimizer Statistics" for more information about the statistics and how to interpret them

Example: Tracing Statements for Performance Statistics and Query Execution Path

If the SQL buffer contains the following statement:

SQL> SELECT D.DNAME, E.ENAME, E.SAL, E.JOB
  2  FROM EMP E, DEPT D
  3  WHERE E.DEPTNO = D.DEPTNO

The statement can be automatically traced when it is run:

SQL> SET AUTOTRACE ON
SQL> /

DNAME          ENAME             SAL JOB
-------------- ---------- ---------- ---------
ACCOUNTING     CLARK            2450 MANAGER
ACCOUNTING     KING             5000 PRESIDENT
ACCOUNTING     MILLER           1300 CLERK
RESEARCH       SMITH             800 CLERK
RESEARCH       ADAMS            1100 CLERK
RESEARCH       FORD             3000 ANALYST
RESEARCH       SCOTT            3000 ANALYST
RESEARCH       JONES            2975 MANAGER
SALES          ALLEN            1600 SALESMAN
SALES          BLAKE            2850 MANAGER
SALES          MARTIN           1250 SALESMAN
SALES          JAMES             950 CLERK
SALES          TURNER           1500 SALESMAN
SALES          WARD             1250 SALESMAN

14 rows selected.

Execution Plan
-----------------------------------------------------------
0      SELECT STATEMENT Optimizer=CHOOSE
1    0   MERGE JOIN
2    1     SORT (JOIN)
3    2       TABLE ACCESS (FULL) OF 'DEPT'
4    1     SORT (JOIN)
5    4       TABLE ACCESS (FULL) OF 'EMP'

Statistics
----------------------------------------------------------
148  recursive calls
4  db block gets
24  consistent gets
6  physical reads
43  redo size
591  bytes sent via Oracle Net to client
256  bytes received via Oracle Net from client
3  Oracle Net roundtrips to/from client
2  sort (memory)
0  sort (disk)
14  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: Tracing Statements Without Displaying Query Data

To trace the same statement without displaying the query data:

SQL> SET AUTOTRACE TRACEONLY
SQL> /

Execution Plan
-----------------------------------------------------------
0      SELECT STATEMENT Optimizer=CHOOSE
1    0   MERGE JOIN
2    1     SORT (JOIN)
3    2       TABLE ACCESS (FULL) OF 'DEPT'
4    1     SORT (JOIN)
5    4       TABLE ACCESS (FULL) OF 'EMP'

Statistics
-----------------------------------------------------------
0  recursive calls
4  db block gets
2  consistent gets
0  physical reads
0  redo size
599  bytes sent via Oracle Net to client
256  bytes received via Oracle Net from client
3  Oracle Net roundtrips to/from client
2  sort (memory)
0  sort (disk)
14  rows processed

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

Example: Tracing Statements Using a Database Link

To trace a statement using a database link:

SQL> SET AUTOTRACE TRACEONLY EXPLAIN
SQL> SELECT * FROM EMP@MY_LINK;

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

The execution plan shows 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.

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 Oracle Real Application Clusters database 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: Tracing Statements With Parallel Query Option

To trace a parallel query running the parallel query option:

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

Table created.

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

Table created.

SQL> create unique index d2_i_unique1 on d2_t1(unique1);

Index created.

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

SQL*Plus displays the following output:

Execution Plan
-----------------------------------------------------------
0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 
                        Card=263 Bytes=5786)
1    0   SORT (AGGREGATE)
2    1     NESTED LOOPS* (Cost=1 Card=263 Bytes=5785)
                                                  :Q8200
3    2       TABLE ACCESS* (FULL) OF 'D2_T2'      :Q8200
4    2       INDEX* (UNIQUE SCAN) OF 'D2_I_UNIQUE1'
                    (UNIQUE)                      :Q8200
2 PARALLEL_TO_SERIAL  SELECT /*+ ORDERED NO_EXPAND 
                      USE_NL(A2) INDEX(A2) PIV_SSF */ 
                      COUNT(A1.C0) FROM (SELECT/*+ 
                      ROWID(A3) */ A3."UNIQUE1" FROM 
                      "D2_T2" A3 WHERE ROWID BETWEEN :1 
                      AND :2) A1, "D2_T1" A2 WHERE 
                      A1.C0=A2."UNIQUE1"
3 PARALLEL_COMBINED_WITH_PARENT
4 PARALLEL_COMBINED_WITH_PARENT

Line 0 of the execution plan shows the cost based optimizer estimates the number of rows at 263, taking 5786 bytes. The total cost of the statement is 1.

Lines 2, 3 and 4 are marked with asterisks, denoting parallel operations. For example, the nested loops step on line 2 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 Q8200.

Monitoring Disk Reads and Buffer Gets

Monitor disk reads and buffer gets by executing the following statement in SQL*Plus:

SQL> SET AUTOTRACE ON [explain] [stat]

Typical results returned are shown as follows:

Statistics
----------------------------------------------------------
         70  recursive calls
          0  db block gets
       591  consistent gets
      404  physical reads
          0  redo size
        315  bytes sent via SQL*Net to client
        850  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
          0  rows processed

If 'consistent gets' or 'physical reads' is high relative to the amount of data returned, then this is a sign that the query is expensive and needs to be reviewed for optimization.

For example, if you are expecting less than 1,000 rows back and 'consistent gets' is 1,000,000 and 'physical reads' is 10,000, then this query needs to be further optimized.

Note: You can also monitor disk reads and buffer gets using V$SQL or TKPROF.