In Oracle : Oracle

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

Oracle DBA

Real Application Cluster (RAC) Maintenance tasks Backup and Recovery Database Architecture Replication Oracle Performance Tuning Oracle DBA - Other articles Oracle scripts/ Selects for DBA Oracle Errors (ORA-nnnnn)

The last articles in the site (HOT)

The most visited articles in the site

In-Oracle.com  -> Oracle DBA -> Oracle Performance Tuning

-> AUTOTRACE in Oracle

 

 AUTOTRACE in Oracle

 

 

Here are the main topics for this article:

 

1.  What is the AUTOTRACE feature ?

2.  How could I enable the AUTOTRACE feature ?

3.  How could I use AUTOTRACE feature?

4.  Which are the AUTOTRACE options?

1. What is the AUTOTRACE feature ?

 

This feature enable/ show a report on the execution path used by the SQL optimizer and the statement execution statistics AFTER the statement (SELECT, DELETE, UPDATE and INSERT) is executed. 

 

 

2.  How could I enable the AUTOTRACE feature ?

 

     a)  connected as SYS

     b)  run $ORACLE_HOME/sqlplus/admin/plustrce.sql

     c)  Grant PLUSTRACE to <the users who will use autotrace>  (a good idea is to let all the users use this feature, so this role could be granted to PUBLIC)

     d) If the PLAN_TABLE dosn't exist, we have to run $ORACLE_HOME/rdbms/admin/utlxplan.sql

     e) SQL> SET AUTOTRACE ON

 

3.  How could I use AUTOTRACE feature?

 

One time we set the AUTOTRACE to ON we can use this feature: 

 

 

 

4.  Which are the AUTOTRACE options?

 

AUTOTRACE setting  AUTOTRACE Output 
SET AUTOTRACE OFF  No AUTOTRACE report is generated. ( default value when connected to the SQL*Plus)
SET AUTOTRACE ON The AUTOTRACE report includes both the execution path and the SQL statement execution statistics.
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 TRACEONLY Like SET AUTOTRACE ON, but suppresses the printing of the user's query output (if this exists). 

 

 

In-Oracle.com  -> Oracle DBA -> Oracle Performance Tuning

-> AUTOTRACE in Oracle

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

 

     Copyright (c) 2011-2015  www.in-oracle.com  |  Disclaimer: The views expressed on this web site are my own and do not reflect the views of Oracle Corporation. You may use the information from this site only at your risk.