• 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
    • Oracle database
    • Oracle Ebusiness Suite
    • Oracle weblogic
    • Oracle Performance Tuning
    • Oracle Hyperion
    • Oracle Cloud Tutorials
  • SQL
  • interview questions
  • Linux
  • PHP and HTML
  • Downloads
  • General
Home » Oracle » Autotrace Utility

Autotrace Utility

June 27, 2014 by techgoeasy 2 Comments

Table of Contents

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

What is Autotrace Utility

  • Autotrace is 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 query 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 suppress the explain plan output
SET AUTOTRACE TRACEONLY EXPLAIN : This is like SET AUTOTRACE TRACEONLY but it shows the explain plan only and suppress the statistics . This does not execute the select statement ,just parse the statement and shows the explain. 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 of Autotrace in 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

  • How to enable 10053 trace in Oracle
  • How to find the bind variable of the sql id
  • How to list parameter set at session level in Oracle
  • How to generate tkprof in EBS in 19c
  • Oracle tkprof utility

Copyright © 2023 : TechGoEasy

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