|SQL*Plus® User's Guide and Reference
Part Number B12170-01
This chapter provides information about how to tune SQL*Plus for better performance. It discusses the following topics:
For information about tuning Oracle Database, see the Oracle Database Performance Tuning Guide.
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.
|SET AUTOTRACE OFF||No AUTOTRACE report is generated. This is the default.|
|SET AUTOTRACE ON EXPLAIN||The AUTOTRACE report shows only the optimizer execution path.|
|SET AUTOTRACE ON STATISTICS||The AUTOTRACE report shows only the SQL statement execution statistics.|
|SET AUTOTRACE ON||The AUTOTRACE report includes both the optimizer execution path and the SQL statement execution statistics.|
|SET AUTOTRACE TRACEONLY||Like SET AUTOTRACE ON, but suppresses the printing of the user's query output, if any. If STATISTICS is enabled, query data is still fetched, but not printed.|
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 Oracle Database SQL Reference.
Example 9-1 Creating a PLAN_TABLE
CONNECT HR/your_password @$ORACLE_HOME/RDBMS/ADMIN/UTLXPLAN.SQL
Example 9-2 Creating the PLUSTRACE Role
CONNECT / AS SYSDBA @$ORACLE_HOME/SQLPLUS/ADMIN/PLUSTRCE.SQL drop role plustrace;
create role plustrace;
grant plustrace to dba with admin option;
Example 9-3 Granting the PLUSTRACE Role
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;
The Execution Plan consists of four columns displayed in the following order:
||Shows the line number of each execution step.|
||Shows the relationship between each step and its parent. This column is useful for large reports.|
||Shows each step of the report.|
||Shows database links or parallel query servers used.|
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 Execution Plan output is generated using the EXPLAIN PLAN command. For information about interpreting the output of EXPLAIN PLAN, see the Oracle Database Performance Tuning Guide.
|Database Statistic Name||Description|
|recursive calls||Number of recursive calls generated at both the user and system level. Oracle Database maintains tables used for internal processing. When Oracle Database needs to make a change to these tables, it internally generates an internal SQL statement, which in turn generates a recursive call.|
|db block gets||Number of times a CURRENT block was requested.|
|consistent gets||Number of times a consistent read was requested for a block|
|physical reads||Total number of data blocks read from disk. This number equals the value of "physical reads direct" plus all reads into buffer cache.|
|redo size||Total amount of redo generated in bytes|
|bytes sent through SQL*Net to client||Total number of bytes sent to the client from the foreground processes.|
|bytes received through SQL*Net from client||Total number of bytes received from the client over Oracle Net.|
|SQL*Net round-trips to/from client||Total number of Oracle Net messages sent to and received from the client|
|sorts (memory)||Number of sort operations that were performed completely in memory and did not require any disk writes|
|sorts (disk)||Number of sort operations that required at least one disk write|
|rows processed||Number of rows processed during the operation|
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 a more complete list of database statistics, see the Oracle Database Reference. For more information about the statistics and how to interpret them, see Chapter 3, "Gathering Optimizer Statistics" in the Oracle Database Performance Tuning Guide.
Example 9-4 Tracing Statements for Performance Statistics and Query Execution Path
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 through SQL*Net to client 503 bytes received through SQL*Net from client 4 SQL*Net round-trips to/from client 0 sorts (memory) 0 sorts (disk) 6 rows processed
Example 9-5 Tracing Statements Without Displaying Query Data
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 through SQL*Net to client 503 bytes received through SQL*Net from client 4 SQL*Net round-trips 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.
Example 9-6 Tracing Statements Using a Database Link
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.
Note:Your output may vary depending on the server version and configuration.
Use the SQL*Plus TIMING command to collect and display data on the amount of computer resources used to run one or more commands or blocks. TIMING collects data for an elapsed period of time, saving the data on commands run during the period in a timer.
To delete all timers, enter CLEAR TIMING.
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 Oracle Database Performance 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
||Shows the line number of each execution step.|
||Describes the function of the SQL statement in the OTHER_PLUS_EXP column.|
||Shows the text of the query for the parallel server or remote database.|
Example 9-7 Tracing Statements With Parallel Query Option
create table D2_t1 (unique1 number) parallel - (degree 6);
create table D2_t2 (unique1 number) parallel - (degree 6);
create unique index d2_i_unique1 on d2_t1(unique1);
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 (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 above example:
The 5 refers to the "5 3 TABLE ACCESS*..." line in the parent report.
Example 9-8 To monitor disk reads and buffer gets.
SET AUTOTRACE 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 through SQL*Net to client 850 bytes received through SQL*Net from client 3 SQL*Net round-trips 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.
Note:You can also monitor disk reads and buffer gets using V$SQL or TKPROF.
Most performance benefit comes from tuning SQL queries executed in a script. This is done with tools like SQL*Plus's AUTOTRACE command. It involves restructuring queries to make best use of the Oracle Database SQL optimizer. For information about Tuning SQL statements, see the Oracle Database Performance Tuning Guide.
The performance gains made by tuning SQL*Plus-specific commands are smaller, but could be important for some applications. The following system variables and commands can influence SQL*Plus performance.
It is better to remove an unneeded column from a SELECT then it is to use COLUMN NOPRINT to stop it displaying. Removing the column from the query means the SQL engine does not need to process it, or need to transfer the column data back to SQL*Plus.
If many SQL scripts are being called, then turning APPINFO OFF stops internal SQL*Plus calls to the database DBMS_APPLICATION_INFO package.
The effectiveness of setting ARRAYSIZE depends on how well Oracle Database fills network packets and your network latency and throughput. In recent versions of SQL*Plus and Oracle Database, ARRAYSIZE may have little effect. Overlarge sizes can easily take more SQL*Plus memory which may decrease overall performance.
SET FLUSH OFF enables the operating system to buffer output. ON disables buffering and flushes output to the screen. Any benefit from setting FLUSH either ON or OFF depends on your operating system and data. The gain may be marginal.
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.
SET FLUSH is not supported in iSQL*Plus
Keep LINESIZE as small as possible to avoid extra memory allocations and memory copying.
However, if LINESIZE is too small, columns that cannot fit next to each other are put on separate lines. This may reduce performance significantly.
Experiment with different sizes if LONGS or LOBs are being fetched.
Increase PAGESIZE to avoid printing headings frequently, or set it to 0 to prevent headings being displayed.
Setting SERVEROUTPUT OFF stops internal SQL*Plus calls to the DBMS_OUTPUT package done after user SQL statements.
Use the default prompt, "SQL> " to stop variable substitution occurring each time the prompt is displayed.
SET SQLPROMPT is not supported in iSQL*Plus
Setting TAB ON causes multiple spaces to be compressed in terminal output. Unless this significantly reduces the written data, the processing required may marginally outweigh any benefit.
SET TAB is not supported in iSQL*Plus
If both spooling to file and writing to terminal are not required, use SET TERMOUT OFF in SQL scripts to disable terminal output.
SET TERMOUT is not supported in iSQL*Plus
Setting these variables ON can reduce the amount of data written. However, if LINESIZE is optimal, it may be faster to set the variables OFF. The SQL*Plus output line is blank filled throughout the query processing routines so removing the spaces could take extra effort.
SET TRIMOUT and SET TRIMSPOOL are not supported in iSQL*Plus.
Deletes substitution variables that you defined either explicitly (with the DEFINE command) or implicitly (with an argument to the START command or COLUMN NEWVAL|OLDVAL).
Use the UNDEFINE command to remove unnecessary substitution variables. This can reduce the time taken for any operation that uses '&', new_value or old_value variables.