SQL*Plus User's Guide and Reference Release 9.0.1 Part Number A88827-02 |
|
Manipulating Commands, 6 of 6
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 create a PLAN_TABLE table in your schema and then have the PLUSTRACE role granted to you. DBA privileges are required to grant the PLUSTRACE role. For information on how to grant a role and how to create the PLAN_TABLE table, see the Oracle9i SQL Reference. For more information about the roles and the PLAN_TABLE, see the Oracle9i SQL Reference and the AUTOTRACE variable of the SET command in Chapter 8.
Run the following commands from your SQL*Plus session to create the PLAN_TABLE in the HR schema:
CONNECT HR/HR @$ORACLE_HOME/RDBMS/ADMIN/UTLXPLAN.SQL Table created.
Run the following commands from your SQL*Plus session to create the PLUSTRACE role and grant it to the DBA:
CONNECT / AS SYSDBA @$ORACLE_HOME/SQLPLUS/ADMIN/PLUSTRCE.SQL drop role plustrace; Role dropped. create role plustrace; Role created. . . . grant plustrace to dba with admin option; Grant succeeded.
Run the following commands from your SQL*Plus session to grant the PLUSTRACE role to the HR user:
CONNECT / AS SYSDBA GRANT PLUSTRACE TO HR; Grant succeeded.
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
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 Oracle9i Performance Guide and Reference.
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.
For more information about the statistics and how to interpret them, see the Oracle9i Performance Guide and Reference.
If the SQL buffer contains the following statement:
SELECT E.LAST_NAME, E.SALARY, J.JOB_TITLE FROM EMPLOYEES E, JOBS J WHERE E.JOB_ID=J.JOB_ID AND E.SALARY>12000
The statement can be automatically traced when it is run:
SET AUTOTRACE ON/ LAST_NAME SALARY JOB_TITLE ------------------------- ---------- ----------------------------------- King 24000 President Kochhar 17000 Administration Vice President De Haan 17000 Administration Vice President Russell 14000 Sales Manager Partners 13500 Sales Manager Hartstein 13000 Marketing Manager 6 rows selected. Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMPLOYEES' 2 1 NESTED LOOPS 3 2 TABLE ACCESS (FULL) OF 'JOBS' 4 2 INDEX (RANGE SCAN) OF 'EMP_JOB_IX' (NON-UNIQUE) Statistics ---------------------------------------------------------- 0 recursive calls 2 db block gets 34 consistent gets 0 physical reads 0 redo size 848 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 6 rows processed
To trace the same statement without displaying the query data, enter:
SET AUTOTRACE TRACEONLY / 6 rows selected. Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMPLOYEES' 2 1 NESTED LOOPS 3 2 TABLE ACCESS (FULL) OF 'JOBS' 4 2 INDEX (RANGE SCAN) OF 'EMP_JOB_IX' (NON-UNIQUE) Statistics ---------------------------------------------------------- 0 recursive calls 2 db block gets 34 consistent gets 0 physical reads 0 redo size 848 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 6 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, enter:
SET AUTOTRACE TRACEONLY EXPLAIN SELECT * FROM EMPLOYEES@MY_LINK; Execution Plan ----------------------------------------------------------- 0 SELECT STATEMENT (REMOTE) Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF 'EMPLOYEES' MY_LINK.DB_DOMAIN
The Execution Plan shows that 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 Oracle9i Performance Guide and Reference 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).
To trace a parallel query running the parallel query option:
create table D2_t1 (unique1 number) parallel - (degree 6); Table created. create table D2_t2 (unique1 number) parallel - (degree 6); Table created. create unique index d2_i_unique1 on d2_t1(unique1); Index created. set long 500 longchunksize 500 SET AUTOTRACE ON EXPLAIN SELECT /*+ INDEX(B,D2_I_UNIQUE1) USE_NL(B) ORDERED - */ COUNT (A.UNIQUE1) FROM D2_T2 A, D2_T1 B WHERE A.UNIQUE1 = B.UNIQUE1; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=26) 1 0 SORT (AGGREGATE) 2 1 SORT* (AGGREGATE) :Q2000 3 2 NESTED LOOPS* (Cost=1 Card=41 Bytes=1066) :Q2000 4 3 TABLE ACCESS* (FULL) OF 'D2_T2' (Cost=1 Card=41 Byte :Q2000 s=533) 5 3 INDEX* (UNIQUE SCAN) OF 'D2_I_UNIQUE1' (UNIQUE) :Q2000 2 PARALLEL_TO_SERIAL SELECT /*+ PIV_SSF */ SYS_OP_MSR(COUNT(A1.C0 )) FROM (SELECT /*+ ORDERED NO_EXPAND USE_NL (A3) INDEX(A3 "D2_I_UNIQUE1") */ A2.C0 C0,A3 .ROWID C1,A3."UNIQUE1" C2 FROM (SELECT /*+ N O_EXPAND ROWID(A4) */ A4."UNIQUE1" C0 FROM " D2_T2" PX_GRANULE(0, BLOCK_RANGE, DYNAMIC) A4) A2,"D2_T1" A3 WHERE A2.C0=A3."UNIQUE1") A1 3 PARALLEL_COMBINED_WITH_PARENT 4 PARALLEL_COMBINED_WITH_PARENT 5 PARALLEL_COMBINED_WITH_PARENT
Line 0 of the Execution Plan shows the cost based optimizer estimates the number of rows at 1, taking 26 bytes. The total cost of the statement is 1.
Lines 2, 3, 4 and 5 are marked with asterisks, denoting parallel operations. For example, the NESTED LOOPS step on line 3 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 Q2000.
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|