|
|
Find related information on Google: 
Custom Search
|
|
Oracle Applications EBS 11i, R12 Oracle Database Data Warehouse & BI Oracle Middleware PL/SQL Linux ... |
|
Oracle DBA |
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 ... |