Oracle autotrace что это такое
SET AUTOT RACE
Displays a report on the execution of successful SQL DML statements ( SELECT , INSERT , UPDATE or DELETE ).
Prerequisites:
| ♦ Must have PLUSTRCE role. Run $ORACLE_HOME/sqlplus/admin/plustrce.sql as SYS to create. |
| ♦ Must have PLAN_TABLE in user schema. Run $ORACLE_HOME/rdbms/admin/ utlxplan.sql To set up for use by non-dba users: create public synonym plan_table for system.PLAN_TABLE and GRANT all on plan_table to public or have them set up a PLAN_TABLE in their own schema. |
| Related Commands: | |
| EXPLAIN PLAN | |
| SHOW TIMI NG | Shows the value of a SQLPlus system variable(s) or current SQLPlus environment. |
| CLEAR TIMI NG | |
| SET TIMI NG | Reports the elapsed time to execute every query in milliseconds or 1/10 of a second. |
| Related Views: | |
| V$TIMER | |
| V$SESSION | Lists session information for each current session. |
| Related Packages: |
| DBMS_UTILITY .GET_TIME |
Результаты выполнения плана:
Physical reads Physical reads - количество блоков данных, прочитанных с жесткого диска во время выполнения SQL
Размер повторов Число повторов —— размер журнала повторов, сгенерированного во время выполнения SQL
Байты, установленные через sql * net для клиента Байты, отправленные клиенту через sql * net
Получено байтов через sql * net от клиента
Сортировка (память) Сортировка, которая происходит в памяти
Сортировки (дисковые) Сортировки, которые не могут происходить в памяти, требуют помощи жесткого диска
Строки обработанные записи результатов
Количество рекурсивных вызовов SQL: когда Oracle выполняет этот SQL, иногда он генерирует много дополнительных операторов SQL, которые становятся рекурсивным вызовом
This chapter provides information on using the Autotrace feature in SQL*Plus and the iSQL*Plus statistics report.
This chapter contains the following sections:
Overview of the Autotrace Report
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 a successful SQL DML statement, such as SELECT , DELETE , UPDATE or INSERT . It is useful for monitoring and tuning the performance of these DML statements.
Configuring the Autotrace Report
You can control the report by setting the AUTOTRACE system variable. See Table 11-1.
Table 11-1 Autotrace Settings
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
Similar to 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.
Setups Required for the Autotrace Report
To use this feature, the PLUSTRACE role must be granted to the user, such as HR . DBA privileges are required to grant the PLUSTRACE role.
Additionally, a PLAN_TABLE table must be created in the user's schema, such as the HR schema. For information on creating the PLAN_TABLE , see "Creating the PLAN_TABLE Output Table" .
To create the PLUSTRACE role and grant it to the DBA, run the commands in Example 11-1 from a SQL*Plus session.
Example 11-1 Creating the PLUSTRACE Role
To grant the PLUSTRACE role to the HR user, run the commands in Example 11-2 from a SQL*Plus session.
Example 11-2 Granting the PLUSTRACE Role
-
Oracle9i SQL Reference for more information about granting roles and creating the PLAN_TABLE table SQL*Plus User's Guide and Reference for more information about the PLUSTRACE role and about the AUTOTRACE variable of the SET command Oracle9i Database Administrator's Guide for information about roles
Execution Plans for SQL Statements
An 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. For a discussion and an example of an execution plan, see "Understanding Execution Plans" .
The execution plan output is generated using the EXPLAIN PLAN command.The format of the output can vary depending on your setups. The format of the columns of the PLAN_TABLE may be altered with the COLUMN command. For example, to stop the PARENT_ID column being displayed, enter the following:
The default formats can be found in the site profile, such as, GLOGIN . SQL .
Chapter 9, "Using EXPLAIN PLAN" for more information about generating and interpreting the output of EXPLAIN PLAN
Database Statistics for SQL Statements
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 statistics listed in Table 11-2.
Table 11-2 Database Statistics
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.
Number of times a CURRENT block was requested.
Number of times a consistent read was requested for a block.
Total number of data blocks read from disk. This number equals the value of "physical reads direct" plus all reads into buffer cache.
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.
Number of sort operations that were performed completely in memory and did not require any disk writes.
Number of sort operations that required at least one disk write.
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.
Tracing Statements Examples
This section shows examples of the use of the AUTOTRACE feature.
Tracing Statements for Performance Statistics and Query Execution Path
If the SQL buffer contains the following statement:
The statement can be automatically traced when it is run with the following:
The output is similar to the following:
Your output may vary depending on the version of the server to which you are connected and the configuration of the server.
Tracing Statements Without Displaying Query Data
To trace the same statement without displaying the query data, enter the following:
This option is useful when you are tuning a large query, but do not want to see the query output.
Tracing Statements Using a Database Link
To trace a statement using a database link, enter:
The execution plan shows that the table being accessed on line 1 of the execution plan is through 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.
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, called 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.
Example 11-3 is an example of tracing statements with the parallel query option. The output varies depending on the configuration of a system. In the execution plan output, items marked with an asterisk (*) denote a parallel or remote operation. Each operation is explained in the second part of the report. The execution plan in Example 11-3 consists of columns that contain information such as:
-
Line number of each execution step Function of the SQL statement Text of the query for the Oracle Real Application Clusters database or remote database
For more information about the columns in the PLAN_TABLE , see "PLAN_TABLE Columns" .
Example 11-3 Tracing Statements With Parallel Query Option
To trace a parallel query running the parallel query option:
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:
The 4 refers to the 4 3 TABLE ACCESS *. line in the execution plan.
Monitoring Disk Reads and Buffer Gets
To monitor disk reads and buffer gets, execute the following command:
Example 11-4 shows typical results.
Example 11-4 Monitoring Disk Reads and Buffer Gets
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.
You can also monitor disk reads and buffer gets using V$SQL or TKPROF .
SYSTEM Variables Influencing SQL*Plus Performance
The following 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 your script does not use substitution variables, setting DEFINE OFF may result in some performance gains.
SET FLUSH OFF
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 FLUSH is not supported in iSQL*Plus.
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
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 TRIMOUT is not supported in iSQL*Plus.
SET TRIMSPOOL ON
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.
SET TRIMSPOOL is not supported in iSQL*Plus.
iSQL*Plus Server Statistics Report
The iSQL*Plus Server statistics report provides static environment information as well as dynamic information about iSQL*Plus sessions. The active statistics in the report are useful for monitoring and tuning iSQL*Plus. You can display the iSQL*Plus Server statistics report with:
For more information about the iSQL*Plus Server statistics report, including the full set of statistics, see SQL*Plus User's Guide and Reference
Active Statistics
The active statistics report shows the current values for the statistics listed in Table 11-3. These statistics provide useful feedback for tuning the iSQL*Plus Server.
Table 11-3 Active Statistics
The number of concurrent active sessions, or the number of people 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 the number of sessions timed-out due to inactivity since the iSQL*Plus Server started.
Requests since startup
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 an ongoing 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.
-
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.
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.
When setting the value of iSQLPlusNumberOfThreads , note the following:
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 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.
Idle Timeout
The idle timeout should not be confused with the iSQLPlusTimeOutInterval which manages the lifetime of a user's session.
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:
Controlling the Autotrace 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.
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.
Example 11-1 Creating a PLAN_TABLE
Run the following commands from your SQL*Plus session to create the PLAN_TABLE in the HR schema:
Example 11-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:
Example 11-3 Granting the PLUSTRACE Role
Run the following commands from your SQL*Plus session to grant the PLUSTRACE role to the HR user:
-
Oracle9i SQL Reference for more information about PLAN_TABLE SQL*Plus User's Guide and Reference for more information about the PLUSTRACE role and about the AUTOTRACE variable of the SET command Oracle9i Database Administrator's Guide for information about granting roles
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:
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 the 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 the following:
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.
Chapter 9, "Using EXPLAIN PLAN" for more information about interpreting the output of EXPLAIN PLAN
Statistics
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:
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.
Number of times a CURRENT block was requested.
Number of times a consistent read was requested for a block
Total number of data blocks read from disk. This number equals the value of "physical reads direct" plus all reads into buffer cache.
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
Number of sort operations that were performed completely in memory and did not require any disk writes
Number of sort operations that required at least one disk write
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.
Example 11-4 Tracing Statements for Performance Statistics and Query Execution Path
If the SQL buffer contains the following statement:
The statement can be automatically traced when it is run:
Your output may vary depending on the version of the server to which you are connected and the configuration of the server.
Example 11-5 Tracing Statements Without Displaying Query Data
To trace the same statement without displaying the query data, enter:
This option is useful when you are tuning a large query, but do not want to see the query report.
Example 11-6 Tracing Statements Using a Database Link
To trace a statement using a database link, enter:
The Execution Plan shows that the table being accessed on line 1 is through the database link MY_LINK . DB_DOMAIN .
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.
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 Oracle Real Application Clusters database 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 11-7 Tracing Statements With Parallel Query Option
To trace a parallel query running the parallel query option:
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:
The 4 refers to the 4 3 TABLE ACCESS *. line in the parent report.
Example 11-8 Monitoring Disk Reads and Buffer Gets
To monitor disk reads and buffer gets:
The following shows typical results:
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.
You can also monitor disk reads and buffer gets using V$SQL or TKPROF .
SET Variables Influencing SQL*Plus Performance
The following SET 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 your script does not use substitution variables, setting DEFINE OFF may result in 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 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.
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
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:
where port is the port number used by the iSQL*Plus Server. The default is 7777.
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.
The iSQL*Plus Server statistics report has the following sections.
All possible statistics for the full report are shown in the following list:
Server Details
This section displays the following information about the iSQL*Plus Server:
Server Environment
This section shows settings for iSQL*Plus Server environment variables:
Configuration Parameters
This section shows settings for iSQL*Plus Server parameters:
Client Details
This section shows the type of web browser used by the iSQL*Plus Client:
Active Statistics
This section shows the current values for the following iSQL*Plus Server statistics:
The number of concurrent active sessions, or the number of people 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 the number of sessions timed-out due to inactivity since the iSQL*Plus Server started.
Requests since startup
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 an ongoing problem with collisions.
Interpreting Active Statistics
To maximize resource availability it is recommended that each user of iSQL*Plus have a database schema profile with appropriately defined limits.
Increasing Number of Threads
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.
Increasing Hash Table Size
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.
Reducing Timeout Interval
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.
Idle Timeout
The idle timeout should not be confused with the iSQLPlusTimeOutInterval which manages the lifetime of a user's session.
Monitoring Disk Reads and Buffer Gets
Monitor disk reads and buffer gets by executing the following statement in SQL*Plus:
Typical results returned are shown as follows:
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.
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 .
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.
SQL*Plus report output may differ for DML if dynamic sampling is in effect.
Controlling the Autotrace Report
You can control the report by setting the AUTOTRACE system variable.
| Autotrace Setting | Result |
|---|---|
| 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 Language Reference .
For more information about the roles and the PLAN_TABLE, see the Oracle Database SQL Language Reference and the AUTOTRACE variable of the SET command.
Run the following commands from your SQL*Plus session to create the PLAN_TABLE in the HR schema:
Run the following commands from your SQL*Plus session to create the PLUSTRACE role and grant it to the DBA:
Run the following commands from your SQL*Plus session to grant the PLUSTRACE role to the HR user:
Execution Plan
The Execution Plan shows the SQL optimizer's query execution path. Execution Plan output is generated using EXPLAIN PLAN and DBMS_XPLAN.
For information about interpreting the output of DBMS_XPLAN, see the Oracle Database Performance Tuning Guide .
Statistics
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.
| 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 .
If the SQL buffer contains the following statement:
The statement can be automatically traced when it is run:
To trace the same statement without displaying the query data, enter:
This option is useful when you are tuning a large query, but do not want to see the query report.
Your output may vary depending on the server version and configuration.
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 Tracing Statements for information about using AUTOTRACE to collect statistics.
To delete all timers, enter CLEAR TIMING.
Tracing Parallel and Distributed Queries
When you trace a statement in a parallel or distributed query, the Execution Plan output depends on the statement you use.
To trace a parallel query running the parallel query option:
The following shows typical results:
If consistent gets or physical reads are 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.
You can also monitor disk reads and buffer gets using V$SQL or TKPROF.
Execution Plan Output in Earlier Databases
Execution Plan output from Oracle Database 9 i Release 2 (9.2) or earlier is different.
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
The Execution Plan output is generated using the EXPLAIN PLAN command.
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
| 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.
SQL*Plus Script Tuning
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.
COLUMN NOPRINT
COLUMN NOPRINT turns off screen output and printing of the column heading and all values selected for the column.
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.
SET APPINFO OFF
Sets automatic registering of scripts through the DBMS_APPLICATION_INFO package. Setting APPINFO OFF prevents administrators monitoring the performance and resource usage of scripts.
If many SQL scripts are being called, then turning APPINFO OFF stops internal SQL*Plus calls to the database DBMS_APPLICATION_INFO package.
SET ARRAYSIZE
Sets the number of rows that SQL*Plus will fetch from the database at one time. Valid values are 1 to 5000.
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 DEFINE OFF
SET DEFINE OFF disables the parsing of commands to replace substitution variables with their values.
SET FLUSH OFF
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 LINESIZE
SET LINESIZE sets the total number of characters that SQL*Plus displays on one line before beginning a new line.
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.
SET LONGCHUNKSIZE
SET LONGCHUNKSIZE sets the size of the increments SQL*Plus uses to retrieve a BLOB , BFILE, CLOB , LONG , NCLOB or XMLType value.
Experiment with different sizes if LONGS or LOBs are being fetched.
SET PAGESIZE
Sets the number of lines on each page of output.
Increase PAGESIZE to avoid printing headings frequently, or set it to 0 to prevent headings being displayed.
SET SERVEROUTPUT
SET SERVEROUTPUT OFF suppresses the display output ( DBMS_OUTPUT.PUT_LINE) of stored procedures or PL/SQL blocks in SQL*Plus.
Setting SERVEROUTPUT OFF stops internal SQL*Plus calls to the DBMS_OUTPUT package done after user SQL statements.
SET SQLPROMPT
Sets the SQL*Plus command prompt.
Use the default prompt, "SQL> " to stop variable substitution occurring each time the prompt is displayed.
SET TAB
Determines how SQL*Plus formats white space in terminal output.
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 TERMOUT
SET TERMOUT OFF suppresses the display so that you can spool output from a script without seeing it on the screen.
If both spooling to file and writing to terminal are not required, use SET TERMOUT OFF in SQL scripts to disable terminal output.
SET TRIMOUT ON SET TRIMSPOOL ON
SET TRIMOUT ON or SET TRIMSPOOL ON removes trailing blanks at the end of each displayed or spooled line.
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.
UNDEFINE
Deletes substitution variables that you defined either explicitly (with the DEFINE command) or implicitly (with an argument to the START command or COLUMN NEW_VAL|OLD_VAL).
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.
Читайте также: