Oracle autotrace что это такое

Обновлено: 02.07.2024

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

SQL*Plus User's Guide and Reference

Oracle9i Database Administrator's Guide

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.

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

SQL*Plus User's Guide and Reference

Oracle9i Database Administrator's Guide

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

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

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

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

SET TRIMOUT ON

SET TRIMOUT is not supported in iSQL*Plus

SET TRIMSPOOL ON

SET TRIMSPOOL is not supported in iSQL*Plus

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

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.

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

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

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.

Читайте также: