• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
Techgoeasy

Techgoeasy

Learn Oracle, PHP, HTML,CSS,Perl,UNIX shell scripts

  • Home
  • Oracle database
  • Oracle Ebusiness Suite
  • Oracle weblogic
  • Oracle Performance Tuning
  • Oracle Hyperion
  • Oracle Cloud Tutorials
  • SQL
  • interview questions
  • Linux
  • PHP and HTML
  • Downloads
Home » Oracle » Oracle Database » Autotrace Utility in Oracle

Autotrace Utility in Oracle

April 7, 2023 by techgoeasy 2 Comments

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

Table of Contents

  • What is Autotrace Utility
  • Autotrace Utility installation
  • Autotrace options
  • Understanding Autotrace Output
  • Syntax
  • Example

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

Autotrace in oracle

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

Filed Under: Oracle, Oracle Database Tagged With: autotrace

Reader Interactions

Comments

  1. Mahesh Reddy says

    June 21, 2020 at 4:25 pm

    Thank you …

    Reply
    • techgoeasy says

      June 22, 2020 at 1:23 pm

      Thanks Mahesh

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar



Subscribe to our mailing list

Enter your email address to subscribe to this blog and receive notifications of new posts by email

Recent Posts

  • Top SQL Patch Queries in Oracle Database
  • how to find if the sql is using the SQL Patch
  • How to create SQL Patch for sql tuning in Oracle
  • How to Configure Auto Login TDE Wallet
  • how to find if the sql is using the sql profile

Copyright © 2023 : TechGoEasy

  • Hire me
  • Privacy Policy
  • Contact Us
  • New? Start Here
  • About Us