Autotrace Utility in Oracle

Autotrace is a utility in Oracle that can be used to analyze the execution plan and performance of SQL statements. It is a command-line tool that provides detailed information about how a SQL statement is executed, including the number of rows returned, the amount of time it takes to execute, and the resources used during execution

What is Autotrace Utility

• Autotrace is a beautiful tool provided by Oracle for getting the explain plan and execution statistics.
• You need to know the query and its bind variable if any and with autotrace access, we can get all the useful information about
• Similar to Explain Plan
• Provides plan – without having to perform separate queries from PLAN_TABLE
• Provides statistics
• Note: Does not support bind variables

Autotrace Utility installation

1. cd [ORACLE_HOME]/rdbms/admin
2. log into SQL*Plus as SYSTEM
3. Run @utlxplan
4. Run CREATE PUBLIC SYNONYM PLAN_TABLE FOR PLAN_TABLE;
5. Run GRANT ALL ON PLAN_TABLE TO PUBLIC;
6. Log in to SQL*Plus as SYS or as SYSDBA
7. Run @plustrce
8. Run GRANT PLUSTRACE TO PUBLIC;

Autotrace options

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: This is like SET AUTOTRACE ON, but it suppresses the printing of the user’s query output, if any. This is very useful for queries returning large rows, so we dont need to scroll down that much
SET AUTOTRACE TRACEONLY STATISTICS: This is like SET AUTOTRACE TRACEONLY but it shows the statistics only and suppresses the explain plan output
SET AUTOTRACE TRACEONLY EXPLAIN: This is like SET AUTOTRACE TRACEONLY but it shows the explain plan only and suppresses the statistics. This does not execute the select statement, just parses the statement and shows the explain plan. INSERT/UPDATE are executed and then explain plan shown

Understanding Autotrace Output

Autotrace shows two things
(a) Explain plan: Explain plan shows the plan for query and it shows exactly how the query will be executed in Database.It will shows the cost ,cardinality and bytes for the each step in the explain plan.Execution Plan produced by Autotrace contains the following:
Line number of each execution step
The relationship number between each step and its parent
Each step of the plan
shows any database links or parallel servers used
(b) Statistics: Lots of statistics will be shown.Some of the statistics are
(i) Recursive calls: Number of sql statement executed in order to execute the statement
(ii) DB block gets: The no of blocks read from buffer cache in current mode
(iii) Consistent gets: The no of blocks read from buffer cache in consistent reads
(iv)redo size: redo generated by sql
(v) physical reads: No of disks reads

Syntax

set autotrace { off | on | traceonly } [explain] [statistics]

Example

SQL> create table TEST as select * from dba_objects where rownum < 100000; 
SQL> set autotrace traceonly
SQL> select * from TEST where object_name = 'FND_USER';
Execution Plan
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 8 | 1416 | 156 (2)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| VIRTUAL_TEST_T | 8 | 1416 | 156 (2)| 00:00:02 |

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

How to Read the Statistics

Logical Reads = Consistent Gets + DB Block Gets
Logical Reads = 15 + 0
Logical Reads = 15
Rows Processed = 1

That means we had to read 15 blocks to find 1 rows

Statistics Definition is given below

Hope you like this post on Autotrace in the Oracle database. This is quite a useful utility

Also Reads

Virtual Index in Oracle
tkprof
Sql tuning Advisor
https://docs.oracle.com/cd/B10501_01/server.920/a96533/autotrac.htm