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