Using autotrace – part 1

Database performance is always a hot issue for any application. It seems to me that whenever a user complains about performance, the database is the first place that people point their finger at. In many ways though this attitude is deserved… the performance of the database is often not considered early enough in system design and the impact of an inefficient query can be devastating. The focus of this post is on one of the simplest performance tools Oracle provides; autotrace.

Autotrace provides the database developer with a number of key pieces of information necessary to assess the performance characteristics of a query:

• The query result-set
• The proposed query execution plan
• Execution statistics, including I/O, network details and sorts

It’s important to understand that autotrace can only used for a single SQL statement only, i.e. SELECT, INSERT, UPDATE, DELETE or MERGE. It cannot be used for PL/SQL blocks. For more advanced statistics that can be used with PL/SQL then you may like to look at Tom Kyte’s runstats utility.

Accessing & Enabling Autotrace

A key advantage of autotrace is that it is available to every developer as part of the database and is accessible from within tools that are commonly used, such as Oracle SQL Developer and Quest Toad.

If autotrace hasn’t been configured on your database then you will find the installation script at:

$ORACLE_HOME/sqlplus/admin/plustrce.sql

Oracle SQL Developer

SQL Developer makes autorace easy; simply enter your SQL in the editor and press F6. Alternately, the following button on the toolbar will also work:

Interestingly, SQL Developer displays a lot more statistics than the SQL Plus facility does leading me to conclude it’s simply mimicking the true autotrace facility. Regardless, the key autotrace statistics are present.

Quest TOAD

Autotrace can be activated in TOAD by right-clicking in an editor window and selecting ‘autotrace’ from the context menu. The menu is on the rather long and you’ll find the autotrace option somewhere near the middle. Selecting the option will put a tick next to it and TOAD will then populate the autotrace output window when a query is run. You may need to enable the autotrace output window by right-clicking in the output panel and selecting the autotrace option.

A word of caution about autotrace in TOAD; it seems to only track a single query execution across all editor tabs for a session. If you have multiple editor tabs open and run different queries in them simultaneously then double check the autotrace output matches the query. Perhaps this has been fixed in the later versions of TOAD though…

SQL Plus

Simply issuing the command:

SET AUTOTRACE ON

enables autotrace within SQL Plus. Other variants of the command can be used to restrict the details that are displayed. For example,

SET AUTOTRACE TRACEONLY

suppresses the display of the query result set so that only the execution plan and run time statistics are shown. This can be further limited with:

SET AUTOTRACE TRACEONLY STATISTICS

which only displays the run time statistics only. I find these last options particularly useful for comparing similar statements since it removes the “clutter” of the query output allowing me to focus on the analysis details.

Information Displayed

As previously noted, the key pieces of information displayed by autotrace are:

• The query result-set
• The proposed query execution plan
• Execution statistics, including I/O, network details and sorts

The details that follow are written from the view of autotrace in SQL Plus.

Query Result-set

There’s not much to say about this section; it’s the results of the query. One point to note is that even if the query output is suppressed by using one of the options noted previously the data is still fetched from the database and transferred to the client. So, a query that returns millions of rows is still going to transfer all that data to your client; it just won’t get displayed to the screen.

In a similar vein, all of the data needs to be transferred before autotrace can report on the statistics so it will fetch the entire result-set. You may find that some queries return fast when autotrace is disabled in a GUI tool and take ages with autotrace enabled. This is probably due to the GUI client fetching only the first few rows of the result-set and displaying those immediately rather than waiting until all rows have been fetched before displaying anything.

Proposed Execution Plan

The query execution plan displayed by autotrace is the same that you would get by running:

EXPLAIN PLAN
FOR
<<insert query>>
/

SELECT *
FROM   TABLE(dbms_xplan.display())
/

I don’t intend to discuss execution plans in this post as it’s a rather large subject. The main point to note is that the execution plan is simply the proposed plan and in certain situations the database may choose to execute the query differently. For example, the execution plan may be altered as a result of bind variable peeking.

Execution Statistics

This is the section that I find most valuable. The execution statistics displayed are:

recursive calls	The number of queries the database runs in addition to the query specified. This includes any data dictionary queries when hard parsing the query, e.g. looking up table and column details used in the original query. Queries executed by functions used within the query will also show up as recursive calls. CURSOR expressions will also increase the number of recursive calls.
db block gets	the amount of logical I/O (logical I/O is I/O performed against the buffer cache) that is performed in “current mode”, i.e. the latest version of the block as opposed to “read consistent mode”
consistent gets	the amount of logical I/O that is performed in “read consistent mode”, i.e. as the data appeared at the start of the query execution.
physical reads	The amount of data that was read from storage. This may include data that was read for operations such as sorts that were too large to be performed in memory.
redo size	The amount of redo, in bytes, generated by the query. Generally this will be 0 for normal SELECT queries and have some value for INSERTs, UPDATEs, MERGEs and DELETEs.
bytes sent via SQL*Net to client	The amount of data sent to the client application by the database
bytes received via SQL*Net from client	The amount of data received by the database from the client application
SQL*Net roundtrips to/from client	The number of network communications from client to server and back again.
sorts (memory)	The number of data sort operations that were able to be performed in memory.
sorts (disk)	The number of data sort operations that were sufficiently large enough to require spilling to disk. These will perform more slowly than memory sorts.
rows processed	The number of rows returned by a SELECT query or the number of rows modified by an INSERT, UPDATE, MERGE or DELETE statement.

You’ll note that there is no statistic regarding physical writes. This is because the DB Writer process is responsible for writing modified data blocks to disk, not the session that modified the data. A session may perform physical writes for operations such as disk sorts but this would be only part of the overall I/O picture.

In the next part on this post we explore a couple of queries and look at what autotrace tells us about them…

---

Other autotrace references:
autotrace in the Oracle Database documentation
Tom Kyte has a good write-up of autotrace in his book Effective Oracle by Design

Advertisement