Using Autotrace

The most accessible benchmarking tool in Oracle is probably Autotrace, which allows basic statistics and execution plan information to be obtain for any query easily.

Something to test with

First, you need some data to test with. In an actual development scenario, you may have a test database already full of real data you can experiment with, but we can create something simple:

SQL11G> create table test1
as
select * from all_objects;

SQL11G> create index test1_uk1 on test1 (object_id);

SQL11G> exec dbms_stats.gather_table_stats(user, 'TEST1');

Now we have a fairly small table with a unique index, which gives us something to play with. On my database, the table contains about 65,000 rows.

What is Autotrace?

Autotrace is a tool built into SQLPlus that helps a developer evaluate how expensive a piece of SQL is at execution time. It is probably the easiest method of getting information about an SQL statement. Depending on what mode it is run in, it can display:

• The query results
• The query explain plan
• Statistics about the query execution

The usage information in the manual for Autotrace is:

Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]

This is a little confusing. The full commands are:

-- Turn everything on. Query results, stats and explain plan are returned
set autotrace on

-- Get stats and Explain plan, but suppress the query results
-- This can save a lot of time watching them 'page' past in the terminal window
set autotrace traceonly

-- Get stats only
set autotrace traceonly statistics

-- Get the query results and the explain plan
set autotrace on explain

So lets test this out in SQLPlus:

SQL11G> set autotrace on
SQL11G> select object_id from test1 where rownum = 1;

 OBJECT_ID
----------
         2


Execution Plan
----------------------------------------------------------
Plan hash value: 3474931915


------------------------------------------------------------------------------
| Id  | Operation        | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |           |     1 |     6 |     1   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY   |           |       |       |            |          |
|   2 |   INDEX FULL SCAN| TEST1_UK1 |     1 |     6 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM=1)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        516  bytes sent via SQL*Net to client
        488  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Notice that the query results come first, then the execution plan, which is also known as the explain plan. Finally the query statistics. The query statistics need some more explanation, although some of them are self explanatory.

• Recursive calls - Sometimes when you issue a query in Oracle, it needs to make further internal queries to process the SQL. Each of these is known as a recursive call. Often, the first time you run a query you get a non-zero value, and the second time you run the same query it will be zero. This is probably due to Parsing and is perfectly normal.
• DB Block Gets - For Oracle to execute and retrieve data for a query, it needs to read various data blocks from memory or disk. Some of these data blocks are special (file headers for example), and for each one of these Oracle reads, it adds one to the DB Block Gets count. In general the number here will be low, and there is little a developer can do to tune it.
• Consistent Gets - This is usually the most interesting stat given by Autotrace, and is the one to concentrate on when tuning. Each data block Oracle has to read to answer your query will be counted. These blocks can be from the buffer cache or disk, but the lower the number the better in general.
• Physical Reads - Similar to Consistent Gets, only this is the number of blocks Oracle needed to access a disk to read. Less is once again better. Often when testing, the first time a query is run, there will be a number of blocks reported here, and the second time it will be zero due to caching and depending on the size of the table and the query involved.
• Redo Size - This is generally small or zero for select queries, however it will always have a value for inserts and updates. A smaller number is always better.
• Bytes in and out - This is a number of bytes of data transmitted from the Oracle database server to your instance of SQLPlus, and is largely driven by the size of your query result set.
• SQL*Net roundtrips - This can be an interesting stat for an application running many queries one after the other, where the number of server round trips should be minimised. For a single query, the number of round trips is influenced by the batch size and by the size of the data set returned. In this case only a single row was returned, so only 2 round trips were required - one to send the query and a second to receive the results.
• Sorts - Often Oracle has to sort data to generate a result set. A complex query could require many sorts, and depending on their size they could contribute significantly to the query runtime. Aim to have your queries returning with as few sorts as possible, and try and avoid sorts on disk if at all possible

Tuning Queries

As you can see, autotrace is simple to use, and most of the stats it returns are easy to understand. The general rule of thumb for Autotrace results is:

Less is better

If you can reduce any of the stats it returns, then the query should be more efficient. Learning how to reduce the numbers is a topic for another day, as this section is focused on teaching you about the tools to compare one method to another. Query tuning skills can be developed after you learn how to measure what is better.