9
Tuning SQL*Plus

This chapter provides information about how to tune SQL*Plus for better performance. It discusses the following topics:

For information about tuning Oracle9i, see the Oracle9i Database Performance Tuning Guide and Reference and Oracle9i Database Performance Planning.

Tracing Statements

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.

Controlling the Report

You can control the report by setting the AUTOTRACE system variable.

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 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.

Example 9-1 Creating a PLAN_TABLE

Run the following commands from your SQL*Plus session to create the PLAN_TABLE in the HR schema:



CONNECT HR/your_password 
@$ORACLE_HOME/RDBMS/ADMIN/UTLXPLAN.SQL 



Table created.

Example 9-2 Creating the PLUSTRACE Role

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.

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; 


Grant succeeded.

Execution Plan

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:

Column Name	Description
ID_PLUS_EXP	Shows the line number of each execution step.
PARENT_ID_PLUS_EXP	Shows the relationship between each step and its parent. This column is useful for large reports.
PLAN_PLUS_EXP	Shows each step of the report.
OBJECT_NODE_PLUS_EXP	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 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 Database Performance Tuning Guide and Reference.

Statistics

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.

The results include the following statistics. For a more complete list of database statistics, see the Oracle9i Database Reference. For more information about the statistics and how to interpret them, see the Oracle9i Database Performance Tuning Guide and Reference.

Database Statistic Name	Description
recursive calls	Number of recursive calls generated at both the user and system level. Oracle maintains tables used for internal processing. When Oracle 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 via SQL*Net to client	Total number of bytes sent to the client from the foreground processes.
bytes received via SQL*Net from client	Total number of bytes received from the client over Oracle Net.
SQL*Net roundtrips 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

Example 9-4 Tracing Statements for Performance Statistics and Query Execution Path

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

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 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.

Example 9-6 Tracing Statements Using a Database Link

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.

Collecting Timing Statistics

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.

See the TIMING command and the Oracle installation and user's guide provided for your operating system for more information. See also "Tracing Statements" for information about using AUTOTRACE to collect statistics.

To delete all timers, enter CLEAR TIMING at the command prompt.

Tracing Parallel and Distributed Queries

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 Database Performance Tuning 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

Column Name	Description
ID_PLUS_EXP	Shows the line number of each execution step.
OTHER_TAG_PLUS_EXP	Describes the function of the SQL statement in the OTHER_PLUS_EXP column.
OTHER_PLUS_EXP	Shows the text of the query for the parallel server or remote database.

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).

Example 9-7 Tracing Statements With Parallel Query Option

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 (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:

   4 PARALLEL_COMBINED_WITH_PARENT

The 4 refers to the "4 3 TABLE ACCESS*..." line in the parent report.

Example 9-8 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.

Note:

You can also monitor disk reads and buffer gets using V$SQL or TKPROF.

System Variables Influencing SQL*Plus Performance

The following system variables can influence SQL*Plus performance.

SET APPINFO OFF

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.

SET ARRAYSIZE

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.

SET DEFINE OFF

Controls whether SQL*Plus parses scripts for substitution variables. If DEFINE is OFF, SQL*Plus does not parse scripts for substitution variables. If a script does not use substitution variables, setting DEFINE OFF may give some performance gains.

SET FLUSH OFF

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 input and output.

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 SERVEROUTPUT

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 ON

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 ON

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

The active statistics from the iSQL*Plus Server Statistics report provide useful feedback for tuning the iSQL*Plus Server.

To run the report, 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 syntax of the URL to run the iSQL*Plus Server Statistics report is

http://machine_name.domain:port/isqlplusdba?statistics={active|full}

[&refresh=number]

To maximize resource availability it is recommended that each user of iSQL*Plus have a database schema profile with appropriately defined limits.

For the full syntax, and for further information about the full iSQL*Plus Server Statistics report, see "iSQL*Plus Server Statistics". Only active statistics are shown in the following list:

Active Statistics

The active statistics report shows the current values for the following:

`Sessions active`	The number of concurrent active sessions, or the number of sessions currently logged in to iSQL*Plus.
`Sessions since startup`	The cumulative count of sessions established since the iSQL*Plus Server started.
`Maximum concurrent sessions`	The maximum or peak number of concurrent sessions since the iSQL*Plus Server started.
`Sessions expired since startup`	The cumulative count of sessions timed-out due to inactivity since the iSQL*Plus Server started.
`Requests active`	The number of concurrent active HTTP requests. Each request corresponds to a user action such as clicking a button, and the processing of that request by iSQL*Plus. Requests active has a maximum value set by `iSQLPlusNumberOfThreads`. If it reaches this limit and user response time is poor, then response time may be improved by increasing `iSQLPlusNumberOfThreads`.
`Requests since startup`	The cumulative count of active HTTP requests since the iSQL*Plus server was started.
`Next expiry operation (minutes)`	The number of minutes (rounded down) until the next expiry process.
`Expiry operations since startup`	The number of times the expiry process has run since the iSQL*Plus Server started.
`Hash table collisions`	The number of active sessions that currently have a hash table collision. Compare this with `Sessions active` to see if there is a current problem with collisions.
`Hash table collisions since startup`	The cumulative count of the sessions that have had a hash table collision since the iSQL*Plus Server started. Compare this with `Sessions since startup` to see if there is a problem with collisions.

Interpreting Active Statistics

The following notes provide some interpretation of the active statistics.

Increasing Number of Threads

If users have more idle time compared to active time, then a higher value of iSQLPlusNumberOfThreads may be needed. Each thread can handle one user request.

A request begins when a user clicks a button or follows a command link in iSQL*Plus, and finishes when all results have been returned to the user. Active time is when a user request is in progress, and a thread to process it is consumed. Idle time is when a user request has been processed, and the associated processing thread is available for use by another iSQL*Plus session.

Note that if iSQLPlusHashTableSize is not specified in the isqlplus.conf, its value will increase when iSQLPlusNumberOfThreads is increased.

Increasing Hash Table Size

If users typically have more idle time compared to active time, then a higher value of iSQLPlusHashTableSize is needed for a given value of iSQLPlusNumberOfThreads. Each user session consumes one entry in the hash table even if the session is idle.

Reducing Timeout Interval

If large numbers of sessions are being timed out, it is an indication that users may not be 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.

Idle Timeout

The idle timeout is the time the Oracle HTTP Server waits for results from iSQL*Plus. The parameter value for the FastCGI timeout parameter, -idle-timeout, is set to 3600 seconds. This value 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 the resources used by an idle user's session.

iSQL*Plus Tuning Notes

If the following iSQL*Plus errors appear frequently in your log at ORACLE_HOME/Apache/Apache/logs/error_log:

[Mon Feb 14 17:08:09 2005] [error] [client 148.87.9.44]
(146)Connection refused: FastCGI: failed to connect to server
"/oracle/sqlplus/bin/isqlplus": connect() failed

or if your users see the following error page generated by the Oracle HTTP Server:

Internal Server Error
The server encountered an internal error or misconfiguration and was unable to 
complete your request.

This indicates that iSQL*Plus has not been able to handle the request load. The common cause of this problem is that iSQLPlusNumberOfThreads is set to too small a value.

9 Tuning SQL*Plus