14.3 How do I…Use the AUTOTRACE system variable to analyze query execution paths?

Problem

I want to analyze query execution paths, but I also want to see query execution statistics. How do I use AUTOTRACE to generate this kind of information?

Technique

SQL*Plus automatically generates query execution path information and performance statistics when users set the AUTOTRACE system variable to a valid value other than OFF. Table 14.5 lists the AUTOTRACE values and their effects.

Table 14.5 Possible values for the AUTOTRACE system variable

AUTOTRACE value Effect
OFF The default, SQL*Plus generates no report.
ON EXPLAIN SQL*Plus generates query execution path.
ON STATISTICS SQL*Plus generates query performance statistics.
ON SQL*Plus generates execution path and performance statistics.

Steps

1. Run SQL*Plus and connect as the WAITE user. Use the START command to load and execute the CHP14_5.SQL script. The script, which creates and populates some sample tables, appears in Listing 14.2.

Listing 14.2 Running the CHP14_5.SQL script in SQL*Plus to create some sample objects

SQL> start chp14_5

SQL>

SQL> CREATE TABLE emp14_3

2 ( empno number(4),

3 ename varchar2(10),

4 deptno number(2)

5 );

Table created.

SQL>

SQL> CREATE INDEX idx_emp14_3

2 ON emp14_3 (empno);

Index created.

SQL>

SQL> CREATE TABLE dept14_3

2 ( deptno number(2),

3 dname varchar2(14)

4 ); Table created.

SQL> SQL> CREATE INDEX idx_dept14_3

2 ON dept14_3 (deptno);

Index created.

SQL>

SQL> INSERT INTO dept14_3 VALUES

2 (1, ‘DATA MGMT’);

1 row created.

SQL>

SQL> INSERT INTO dept14_3 VALUES

2 (2, ‘CHEMISTRY’);

1 row created.

SQL>

SQL> INSERT INTO emp14_3 VALUES

2 (1, ‘MARKIE’, 1);

1 row created.

SQL>

SQL> INSERT INTO emp14_3 VALUES

2 (2, ‘DEBBIE’, 1);

1 row created.

SQL>

SQL> INSERT INTO emp14_3 VALUES

2 (3, ‘SARA’, 1);

1 row created.

SQL>

SQL> INSERT INTO emp14_3 VALUES\

2 (4, ‘STEVE’, 2);

1 row created.

SQL>

SQL> INSERT INTO emp14_3 VALUES

2 (5, ‘NANCY’, 2);

1 row created.

SQL>

2. Issue the SET AUTOTRACE ON command at the SQL*Plus prompt.

3. The script CHP14_6.SQL queries the tables created in Step 1 and includes a simple join. Since AUTOTRACE is on, SQL*Plus reports the query’s execution plan and generates performance statistics. Use the START command to execute CHP14_6.SQL and produce the output shown in Listing 14.3.

Listing 14.3 Running the CHP14_6.SQL script in SQL*Plus to produce an AUTOTRACE report

SQL> start chp14_6

SQL>

SQL> SELECT empno, ename

2 FROM emp14_3 e, dept14_3 d

3 WHERE e.deptno = d.deptno AND

4 d.dname = ‘CHEMISTRY’ and e.empno = 4;

EMPNO ENAME

--------- ----------

4 STEVE

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 NESTED LOOPS

2 1 TABLE ACCESS (BY INDEX ROWID) OF ‘EMP14_3’

3 2 INDEX (RANGE SCAN) OF ‘IDX_EMP14_3’ (NON-UNIQUE)

4 1 TABLE ACCESS (BY INDEX ROWID) OF ‘DEPT14_3’

5 4 INDEX (RANGE SCAN) OF ‘IDX_DEPT14_3’ (NON-UNIQUE)

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

5 consistent gets

0 physical reads

0 redo size

271 bytes sent via SQL*Net to client

339 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

Reading the execution plan from the inside out and from the top to the bottom indicates that Oracle performed an index range scan and a table access by ROWID for table EMP14_3, did the same for table DEPT14_3, and submitted the results of these operations to a NESTED LOOPS join. The statistics section indicates, among other things, two SQL*Net round trips to execute the query.

How It Works

Step 1 creates some sample objects. Step 2 enables AUTOTRACE and instructs SQL*Plus to generate execution path and query performance data. Step 3 executes a simple query and generates an AUTOTRACE report.

Comments

The AUTOTRACE command frees users from some of the maintenance associated with the EXPLAIN PLAN command presented in the previous How-To. These tasks include clearing the contents of PLAN_TABLE, assigning statement identifiers, and modifying queries to include the EXPLAIN PLAN clause. The potential disadvantage of AUTOTRACE is that Oracle actually executes the target query. If the query requires protracted execution times, then this could make analysis tedious.

Any user granted the DBA role (like the WAITE user) can set AUTOTRACE in SQL*Plus. Users without this high level of privilege, however, do not have access to the requisite dynamic performance views (the V$ tables). Oracle provides a way to grant this access using the PLUSTRACE role. The name and location of the script to create this role is system-dependent, but under Windows NT the script is named PLUSTRCE.SQL and resides in the \ORANT\PLUS40 subdirectory. You must connect to Oracle as the SYS user to run this script successfully. Once the PLUSTRACE role is available any user with the DBA role can grant it to future AUTOTRACE users.