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 can not 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.


Have a question on this subject?

Ask questions to our expert community members and clear your doubts. Asking question or engaging in technical discussion is both easy and rewarding.

Are you on Twitter?

Start following us. This way we will always keep you updated with what's happening in Data Analytics community. We won't spam you. Promise.

  • Understanding Oracle QUERY PLAN - Part2 (Exploring SQL Joins)

    This is the second part of the article Understanding Oracle Query Plan. In this part we will deal with SQL Joins.

  • Different Methods to move data from Oracle to External File

    How to push data from Oracle Table to external files? Well, external tables in Oracle provides a way to move data in as well as out of the database with the help of SQL*Loader and Data Pump functionality.

  • Oracle Analytic Functions

    Oracle Analytic Functions compute an aggregate value based on a group of rows. It opens up a whole new way of looking at the data. This article explains how we can unleash the full potential of this.

  • Oracle Spool

    Oracle Spool for SQLplus command line utility, can be used in conjunction with OS to export data from Oracle to flat file.

  • How to find out Which User is Running what SQL Query in Oracle database?

    Do you wonder how to get information on all the active query in the Oracle database? Do you want to know what query is executed by which user and how long is it running? Here is how to do it!

  • Database Performance Tuning

    This article tries to comprehensively list down many things one needs to know for Oracle Database Performance Tuning. The ultimate goal of this document is to provide a generic and comprehensive guideline to Tune Oracle Databases from both...

  • How to find out Expected Time of Completion for an Oracle Query

    Too often we become impatient when Oracle Query executed by us does not seem to return any result. But Oracle (10g onwards) gives us an option to check how long a query will run, that is, to find out expected time of completion for a query.

  • Oracle External Tables

    The Oracle external tables feature allows us to access data in external sources as if it is a table in the database. This is a very convenient and fast method to retrieve data from flat files outside Oracle database.

  • Learn Oracle Server Architecture in 10 minutes

    Here is an easy to understand primer on Oracle architecture. Read this first to give yourself a head-start before you read more advanced articles on Oracle Server Architecture.

  • Oracle AUTOTRACE Explained - A 10 Minute Guide

    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!