AUTOTRACE is a facility within SQL*Plus that shows us the explain plan of the queries that we have executed and the resources they used. This topic makes extensive use of the AUTOTRACE facility.There is more than one way to get AUTOTRACE configured. This is what I like to do to get AUTOTRACE working :
1.) cd $ORACLE_HOME\rdbms\admin
2.) log into SQL*Plus as SYSTEM
3.) Run @utlxplan
4.) run the below command
SQL> create public synonym plan_table for plan_table ;
3.) Run @utlxplan
4.) run the below command
SQL> create public synonym plan_table for plan_table ;
SQL> grant all on plan_table to public ;
We can automatically get a report on the execution path used by the SQL optimizer and the statement execution statistics. The report is generated after successful SQL DML ( i.e., select,delete,update,merge and insert ) statements. It is useful for monitoring and tuning the performance of these statements. We can control the report by setting AUTOTRACE system variable.
We can automatically get a report on the execution path used by the SQL optimizer and the statement execution statistics. The report is generated after successful SQL DML ( i.e., select,delete,update,merge and insert ) statements. It is useful for monitoring and tuning the performance of these statements. We can control the report by setting AUTOTRACE system variable.
1.) SET AUTOTRACE OFF : No AUTOTRACE report is generated. This is the default.
2.) SET AUTOTRACE ON : The AUTOTRACE report includes both the optimizer execution path and the SQL statement execution statistics. Here is an demo
SQL> set autotrace on
SQL> set autotrace on
SQL>select e.last_name,e.salary,j.job_title from employees e , jobs j where e.job_id=j.job_id and e.salary> 12000 ;
LAST_NAME SALARY JOB_TITLE
6 rows selected.
The statement can be automatically traced when it is run :
Statistics
----------------------------------------------------------
----------------------------------------------------------
6 rows processed
3.) SET AUTOTRACE ON STATISTICS : The AUTOTRACE report shows only the SQL statement execution statistics.
3.) SET AUTOTRACE ON STATISTICS : The AUTOTRACE report shows only the SQL statement execution statistics.
SQL>set autotrace on statistics
SQL>select e.last_name,e.salary,j.job_title from employees e , jobs j where e.job_id=j.job_id and e.salary> 12000 ;
LAST_NAME SALARY JOB_TITLE
6 rows selected.
Statistics
----------------------------------------------------------
0 recursive calls
2 db block gets
34 consistent gets
0 physical reads
0 redo size
848 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6 rows processed
4.) SET AUTOTRACE ON EXPLAIN : The AUTOTRACE report shows only the optimizer execution Path.
SQL> set autotrace on explain
SQL> select e.last_name,e.salary,j.job_title from employees e , jobs j where e.job_id=j.job_id and e.salary> 12000 ;
LAST_NAME SALARY JOB_TITLE
---------------- ---------- -----------------------
King 24000 President
Kochhar 17000 Administration Vice President
De Haan 17000 Administration Vice President
Russell 14000 Sales Manager
Partners 13500 Sales Manager
Hartstein 13000 Marketing Manager
6 rows selected.
Execution Plan :
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMPLOYEES'
2 1 NESTED LOOPS
3 2 TABLE ACCESS (FULL) OF 'JOBS'
4 2 INDEX (RANGE SCAN) OF 'EMP_JOB_IX' (NON-UNIQUE)
5.) SET AUTOTRACE TRACEONLY : This is like SET AUTOTRACE ON, but it suppresses the printing of the user’s query output, if any.
SQL > set autotrace traceonly
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMPLOYEES'
2 1 NESTED LOOPS
3 2 TABLE ACCESS (FULL) OF 'JOBS'
4 2 INDEX (RANGE SCAN) OF 'EMP_JOB_IX' (NON-UNIQUE)
Statistics :
---------------------------------------------------------
0 recursive calls
2 db block gets
34 consistent gets
0 physical reads
0 redo size
848 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6 rows processed
---------------------------------------------------------
0 recursive calls
2 db block gets
34 consistent gets
0 physical reads
0 redo size
848 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6 rows processed
Enjoy :)
No comments:
Post a Comment