SQL*Plus
User's Guide and Reference
Release 8.1.6 A75664-01 |
|
Manipulating Commands, 7 of 7
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.
You can control the report by setting the AUTOTRACE system variable.
To use this feature, you must have the PLUSTRACE role granted
to you and a PLAN_TABLE table created in your schema. For information on
how to grant the PLUSTRACE role and how to create the PLAN_TABLE table,
see the Oracle8i SQL Reference manual. For more information about
the PLUSTRACE role and the PLAN_TABLE, see the Oracle8i SQL Reference
manual and the AUTOTRACE variable of the
SET command in Chapter
8.
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
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
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. For information about interpreting the output of EXPLAIN
PLAN, see the Oracle8i Tuning guide.
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.
Net8 refers to the generic process communication between SQL*Plus
and the server, regardless of whether Net8 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 Oracle8i Tuning guide.
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 Net8 to client 256 bytes received via Net8 from client 3 Net8 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. |
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 Net8 to client 256 bytes received via Net8 from client 3 Net8 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.
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.
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 Oracle8i Tuning guide for more information on parallel and
distributed operations.
The second section of this report consists of three columns displayed in the following order
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. |
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.
|
![]() Copyright © 1999 Oracle Corporation. All Rights Reserved. |
|