Release 2 (9.2)
Part Number A96533-01
 Home |
 Book List |
 Contents |
 Index |
 Master Index |
 Feedback |
| Oracle9i Database Performance Tuning Guide and Reference Release 2 (9.2) Part Number A96533-01 |
|
In SQL*Plus 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 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 SQL*Plus User's Guide and Reference.
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.
See Also:
|
The execution plan shows the SQL optimizer's query execution path. 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:
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 results include the following statistics:
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:
|
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
|
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, 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 through 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:
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.
Numbers identifying parallel report lines cross reference the line of the parent report. For example, in the last line of the example:
4 PARALLEL_COMBINED_WITH_PARENT
The 4 refers to the 4 3 TABLE ACCESS*... line in the parent report.
To monitor disk reads and buffer gets:
SET AUTOTRACE ON TRACEONLY STATISTICS
The following shows typical results:
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, it indicates 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, further optimization is needed.
The following SET variables can influence SQL*Plus performance.
Sets automatic registering of scripts through the DBMS_APPLICATION_INFO package. Setting APPINFO OFF disables the registering and monitoring of performance and resource usage of scripts. This reduction in overheads may improve performance.
Sets the number of rows--called a batch--that SQL*Plus will fetch from the database at one time. Valid values are 1 to 5000. A large value increases the efficiency of queries and subqueries that fetch many rows, but requires more memory. Values over approximately 100 provide little added performance. ARRAYSIZE has no effect on the results of SQL*Plus operations other than increasing efficiency.
Controls whether SQL*Plus parses scripts for substitution variables. If DEFINE is OFF, SQL*Plus does not parse scripts for substitution variables. If your script does not use substitution variables, setting DEFINE OFF may result in some performance gains.
SET FLUSH is not supported in iSQL*Plus
Controls when output is sent to the user's display device. OFF allows the host operating system to buffer output which may improve performance by reducing the amount of program I/O.
Use OFF only when you run a script that does not require user interaction and whose output you do not need to see until the script finishes running.
Controls whether SQL*Plus checks for and displays DBMS output. If SERVEROUTPUT is OFF, SQL*Plus does not check for DBMS output and does not display output after applicable SQL or PL/SQL statements. Suppressing this output checking and display may result in performance gains.
SET TRIMOUT is not supported in iSQL*Plus
Determines whether SQL*Plus allows trailing blanks at the end of each displayed line. ON removes blanks at the end of each line, which may improve performance especially when you access SQL*Plus from a slow communications device. TRIMOUT ON does not affect spooled output.
SET TRIMSPOOL is not supported in iSQL*Plus
Determines whether SQL*Plus allows trailing blanks at the end of each spooled line. ON removes blanks at the end of each line, which may improve performance especially when you access SQL*Plus from a slow communications device. TRIMSPOOL ON does not affect terminal output.
iSQL*Plus Server statistics provide static environment information as well as dynamic information about iSQL*Plus sessions. You can request a report containing iSQL*Plus Server statistics with:
http://machine_name.domain:port/isqlplusdba?statistics=[active|full]
[&refresh=number]
where machine_name.domain is the URL of the Oracle HTTP Server for which you want to generate iSQL*Plus Server statistics.
where port is the port number used by the iSQL*Plus Server. The default is 7777.
where ?statistics={active|full} specifies the level of detail to report.
where full gives all possible statistics and is the default.
and where active gives dynamically changing session statistics for the iSQL*Plus Server. These statistics are also included at the end of the full report.
where [&refresh=number] optionally specifies the time in seconds before the statistics report is automatically refreshed. The minimum value is 10 seconds.
You must have Oracle HTTP Server authentication to access the iSQL*Plus DBA URL, but as there is no connection to a database, no Oracle9i login is required.
The iSQL*Plus Server statistics report has the following sections.
All possible statistics for the full report are shown in the following list:
This section displays the following information about the iSQL*Plus Server:
Host name Host address Oracle HTTP Server port Host system iSQL*Plus Server start time iSQL*Plus version iSQL*Plus logfile
This section shows settings for iSQL*Plus Server environment variables:
ORACLE_HOME ORACLE_SID TNS_ADMIN NLS_LANG NLS_NCHAR
This section shows settings for iSQL*Plus Server parameters:
iSQLPlusNumberOfThreads iSQLPlusTimeOutInterval(minutes) iSQLPlusLogLevel iSQLPlusConnectIdList iSQLPlusHashTableSize
This section shows the type of web browser used by the iSQL*Plus Client:
HTTP client
This section shows the current values for the following iSQL*Plus Server statistics:
To maximize resource availability it is recommended that each user of iSQL*Plus have a database schema profile with appropriately defined limits.
If there are many people logged in doing long running queries, then response may be improved by increasing iSQLPlusNumberOfThreads to increase the number of threads available.
If there are a large number of collisions relative to the number of sessions, then consider increasing the hash table size by increasing iSQLPlusNumberOfThreads to improve response.
If large numbers of people are being timed out, it is an indication that users are not logging out cleanly, and sessions may be remaining idle. In this case, and if the iSQL*Plus Server load is high, you may want to consider reducing the iSQLPlusTimeOutInterval to more aggressively time out sessions.
The idle timeout is the time the Oracle HTTP Server waits for results from iSQL*Plus. It is set to 3600 seconds, which is likely to prevent iSQL*Plus timing out before the web browser. It is sufficient for many long queries to return results before iSQL*Plus times out.
The idle timeout should not be confused with the iSQLPlusTimeOutInterval which manages the lifetime of a user's session.
Monitor disk reads and buffer gets by executing the following statement in SQL*Plus:
SQL> SET AUTOTRACE TRACEONLY STATISTICS
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 © 2000, 2002 Oracle Corporation. All Rights Reserved. |
|
