Previous | Table of Contents | Next

Page 142

After these steps are performed, the user can get a report on the execution path used by the optimizer and the execution statistics after the successful running of any DML statement
(SELECT, INSERT, DELETE, UPDATE). The output of the report is controlled by the AUTOTRACE system variable.

The allowed settings for the AUTOTRACE system variable and the results of using them are listed in Table 8.5.

Table 8.5 AUTOTRACE Values


Value Result
SET AUTOTRACE OFF The default. No report is generated.
SET AUTOTRACE ON EXPLAIN The trace report shows only the execution path; no execution statistics.
SET AUTOTRACE ON STATISTICS The trace report shows only the execution statistics; no execution path.
SET AUTOTRACE ON The trace report shows both the execution path and the execution statistics.
SET AUTOTRACE TRACEONLY Same as SET AUTOTRACE ON; however, the result of the query is not shown.

Understanding the Execution Plan

The execution plan shows the access paths the optimizer is using when executing a query. The execution plan output is generated using the EXPLAIN PLAN command. Each line displayed in the execution plan has a sequential line number. The line number of the parent operation is also displayed. The execution plan consists of four columns (for standard queries, only three columns are displayed; the fourth column is displayed only in the case of distributed queries or queries run using the Parallel Query Option (PQO)).

The name of the columns, the order in which they're displayed, and their description are shown in Table 8.6.

Table 8.6 Execution Plan Column Descriptions


Column Name Description
ID_PLUS_EXP Displays the line number of each execution step
PARENT_ ID_PLUS_EXP Displays the relationship between a step and its parent
PLAN_ PLUS_EXP Displays each step of the report; for example, TABLE ACCESS (FULL) OF `DEPT'

Page 143


Column Name Description
OBJECT_NODE _PLUS_EXP Displays the database link(s) or parallel query servers used (only when running distributed queries or queries using the Parallel Query Option (PQO)

The default column formats are usually set in the Site Profile (the glogin.sql file).

The format of the columns can be altered using the SQL*Plus command COLUMN. You can change the width of a column, rename it, or stop it from being displayed. For example, to prevent the ID_PLUS_EXP column from being displayed, enter

SQL> COLUMN ID_PLUS_EXP NOPRINT

The second part of the statement-tracing report displays the statement-execution statistics. They show the system resources required to execute the query and their usage. Unlike the execution path, the default format of the statistics report cannot be changed.

Using the AUTOTRACE Feature

This section shows in detail the steps required to enable user SCOTT to use the AUTOTRACE facility in SQL*Plus.

First, user SYSTEM logs on to SQL*Plus and grants user SCOTT the PLUSTRACE role (SYS has previously run the $ORACLE_HOME/sqlplus/admin/plustrce.sql script that creates the PLUSTRACE role in the database). Listing 8.16 shows this being done.

Listing 8.16 Granting the PLUSTRACE Role

$ sqlplus system
SQL*Plus: Release 3.3.3.0.0 - Production on Thu Oct 02 16:14:51 1997
Copyright (c) Oracle Corporation 1979, 1996.  All rights reserved.
Connected to:
Oracle7 Server Release 7.3.3.0.0 - Production Release
With the distributed, replication and parallel query options
PL/SQL Release 2.3.3.0.0 - Production
Enter password: *******
SQL>
SQL> GRANT plustrace TO scott;
Grant succeeded.

Next, user SCOTT logs on to SQL*Plus and creates the PLAN_TABLE (required by the EXPLAIN PLAN command) into his schema by running the script $ORACLE_HOME/rdbms73/admin/utlxplan.sql, as shown in Listing 8.17.

Page 144

Listing 8.17 Creating PLAN_TABLE

SQL> CONNECT scott
Enter password: *****
Connected.
SQL> @$ORACLE_HOME/rdbms73/admin/utlxplan
Table created.
SQL> L
  1  create table PLAN_TABLE (
  2     statement_id    varchar2(30),
  3     timestamp       date,
  4     remarks         varchar2(80),
  5     operation       varchar2(30),
  6     options         varchar2(30),
  7     object_node     varchar2(128),
  8     object_owner    varchar2(30),
  9     object_name     varchar2(30),
 10     object_instance numeric,
 11     object_type     varchar2(30),
 12     optimizer       varchar2(255),
 13     search_columns  numeric,
 14     id              numeric,
 15     parent_id       numeric,
 16     position        numeric,
 17     cost            numeric,
 18     cardinality     numeric,
 19     bytes           numeric,
 20     other_tag       varchar2(255),
 21*    other           long)

The L[IST] command lists the contents of the SQL buffer, which contains the last SQL command executed by the CREATE TABLE plan_table statement from the ORACLE_HOME/rdbms73/admin/utlxplan.sql script.

Next, SCOTT enters and executes the query he wants to trace. Listing 8.18 shows an example.

Listing 8.18 Tracing a Query

SQL>  SELECT t1.dname, t2.ename, t2.sal, t2.job
     2  FROM dept t1, emp t2
     3  WHERE t1.deptno = t2.deptno;
DNAME            ENAME              SAL   JOB
--------------   ----------   --------   --------
RESEARCH         SMITH              800   CLERK
SALES            ALLEN             1600   SALESMAN
SALES            WARD              1250   SALESMAN
RESEARCH         JONES             2975   MANAGER
SALES            MARTIN            1250   SALESMAN
SALES            BLAKE             2850   MANAGER
ACCOUNTING       CLARK             2450   MANAGER
RESEARCH         SCOTT             3000   ANALYST
ACCOUNTING       KING              5000   PRESIDENT
SALES            TURNER            1500   SALESMAN

Page 145


RESEARCH         ADAMS             1100   CLERK
SALES            JAMES              950   CLERK
RESEARCH         FORD              3000   ANALYST
ACCOUNTING       MILLER            1300   CLERK
14 rows selected.

To get information on both the execution path and the execution statistics, SCOTT has to set the AUTOTRACE variable appropriately. Listing 8.19 shows the commands used to set AUTOTRACE on and run the previous query from the buffer.

Listing 8.19 Tracing the Query in the Buffer

SQL> SET AUTOTRACE ON
SQL> /
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   NESTED LOOPS
   2    1     TABLE ACCESS (FULL) OF `EMP'
   3    1     TABLE ACCESS (BY ROWID) OF `DEPT'
   4    3       INDEX (UNIQUE SCAN) OF `DEPT_PRIMARY_KEY' (UNIQUE)
Statistics
----------------------------------------------------------
        0  recursive calls
        2  db block gets
       43  consistent gets
        0  physical reads
        0  redo size
      726  bytes sent via SQL*Net to client
      376  bytes received via SQL*Net from client
        3  SQL*Net roundtrips to/from client
        0  sorts (memory)
        0  sorts (disk)
       14  rows processed
To trace the same statement without displaying the query data:
SQL> SET AUTOTRACE TRACEONLY
SQL> /                    -- slash command: doesn't display query text
14 rows selected.
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   NESTED LOOPS
   2    1     TABLE ACCESS (FULL) OF `EMP'
   3    1     TABLE ACCESS (BY ROWID) OF `DEPT'
   4    3       INDEX (UNIQUE SCAN) OF `DEPT_PRIMARY_KEY' (UNIQUE)
Statistics
----------------------------------------------------------
        0  recursive calls
        2  db block gets
       43  consistent gets
        0  physical reads


Previous | Table of Contents | Next