Oracle9i Database Performance Guide and Reference Release 1 (9.0.1) Part Number A87503-02 |
|
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:
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.
See Also:
|
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 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 |
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 |
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
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.
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.
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
).
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.
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.
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|