Oracle AUTOTRACE Explained

AUTOTRACE is a beautiful utility in Oracle that can help you gather vital performance statistics for a SQL Query. You need to understand and use it for SQL Query Tuning. Here is how!

When you fire an SQL query to Oracle, database performs a lot of tasks like PARSING the query, sorting the result and physically reading the data from the data files. AUTOTRACE provides you a summary statistics for these operations which are vital to understand how your query works. 

What is AUTOTRACE?

AUTOTRACE is a utility in SQL* PLUS, that generates a report on the execution path used by SQL optimizer after it successfully executes a DML statement. It instantly provides an automatic feedback that can be analyzed to understand different technical aspects on how Oracle executes the SQL. Such feedback is very useful for Query tuning. 

AUTOTRACE Explained

We will start with a very simple SELECT statement and try to interpret the result it produces. First we will require, SQL* PLUS software (Or any other Interface software that supports AUTOTRACE, e.g. SQL Developer etc.) and connectivity to Oracle database. We need to have either autotrace or DBA role enabled on the user using the AUTOTRACE command. I will use Oracle “emp” table to illustrate AUTOTRACE result.

AUTOTRACE Example

We can turn on AUTOTRACE by firing the following command,

SQL> set autotrace on

Next, fire the following simple SQL,

SQL> select ename from emp where empno = 9999;

no rows selected

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'

2 1 INDEX (UNIQUE SCAN) OF 'PK_EMP' (UNIQUE)

Statistics

----------------------------------------------------------

83 recursive calls

0 db block gets

21 consistent gets

3 physical reads

0 redo size

221 bytes sent via SQL*Net to client

368 bytes received via SQL*Net from client

1 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

0 rows processed

Off course, it shows a lot of details which we need to understand now. I will not be talking about the “Execution Plan” part here, since that will be dealt separately in different article. So let’s concentrate on the “Statistics” part of the result shown above. All these statistics are actually recorded in Server when the statement is executed and AUTOTRACE utility only digs out this information in presentable format. 

Recursive Calls

This is the number of SQL calls that are generated in User and System levels on behalf of our main SQL. Suppose in order to execute our main query, Oracle needs to PARSE the query. For this Oracle might generate further queries in data dictionary tables etc. Such additional queries will be counted as recursive calls. 

Db Block Gets and Consistent Gets

This is somewhat bigger subject to discuss. But I will not go to all the details of “db block gets”. I will try to put it as simply as possible without messing up the actual article. To understand this properly, first we need to know how Oracle maintains read consistency.

When a table is being queried and updated simultaneously, Oracle must provide a (read-) consistent set of table’s data to the user. This is to ensure that, unless the update is committed, any user who queries the table’s data, see only the original data value and not the updated one (uncommitted update). For this, when required, Oracle takes the original values of the changed data from the Roll-back segment and unchanged data (un-updated rows) from the SGA buffer to generate the full set of output.

This (read-consistency) is what is ensured in consistent gets. So a consistent get means block read in consistent mode (point in time mode) for which Oracle MAY or MAY NOT involve reconstruction from roll-back segment. This is the most normal get for Oracle and you may see some additional gets if Oracle at all needs to access the rollback data (which I generally rare, because not always table data will get updated and read simultaneously)

But in case of “db block get” Oracle only shows data from blocks read as-of-now (Current data). It seems Oracle uses db block get only for fetching internal information, like for reading segment header information for a table in FULL TABLE SCAN.

Normally one cannot do much to reduce the db block gets. 

Physical Reads

Oracle Physical Read means total number of data blocks read directly or from buffer cache. 

Redo Size

This is total number of Redo Log generated sized in bytes. 

Sorts

Sorts are performed either in memory (RAM) or in disk. These sorts are often necessary by Oracle to perform certain search algorithm. In memory sort is much faster than disk sort.

While tuning the performance of Oracle query, the basic thing we should concentrate on reducing the Physical IO, Consistent Gets and Sorts. Off course the less the values for these attributes, the better is the performance. 

One last thing, if you use SET AUTOTRACE TRACEONLY, the result will only show the trace statistics and will not show the actual query results.