Oracle Database - Autotrace
About
Autotrace is:
- a system variable of sql plus
- can also be found in SQL Developer
AUTOTRACE provides you:
- an execution plan (such as explain plan)
- and some important Oracle Database - Optimizer Statistics regarding its actual execution.
Autotrace is fully accessible to each developer while tkprof relies on access to a trace file.
“set autotrace on” doesn’t work with a PL/SQL procedure.
Articles Related
Autotrace Output
Autotrace output has two parts :
- the query plan report
- the statistics
The query plan report
Execution Plan
----------------------------------------------------------
Plan hash value: 4015478428
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15 | 1755 | 13 (16)| 00:00:01 |
| 1 | VIEW | | 15 | 1755 | 13 (16)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
|* 3 | HASH JOIN OUTER | | 14 | 798 | 7 (15)| 00:00:01 |
| 4 | TABLE ACCESS FULL | EMP | 14 | 518 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | DEPT | 4 | 80 | 3 (0)| 00:00:01 |
| 6 | MERGE JOIN ANTI | | 1 | 23 | 6 (17)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 80 | 2 (0)| 00:00:01 |
| 8 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 9 | SORT UNIQUE | | 14 | 42 | 4 (25)| 00:00:01 |
| 10 | TABLE ACCESS FULL | EMP | 14 | 42 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
In this query plan output, the optimizer information represents the following :
- cost : The cost assigned to each step of the query plan by the optimizer. The Optimizer Oracle Database - The Query Plan Generator generate many different Sql Engine - (Physical|Execution) Plan for the same query and the Oracle Database - The Query Plan Estimator assigns a cost to each and every one. The execution plan with the lowest cost wins. In the full outer join example, we can see the total for this query is 13.
- Rows (of Card as Cardinality for 9i) : It's the estimated number of rows that will flow out of a given query plan step. In the full outer join example, we can see the optimizer expects there to be 518 rows in EMP and 80 in DEPT.
- Bytes : The size in bytes of the data the optimizer expects each step of the plan to return. This is dependent on the number of rows (card) and the estimated width of the rows.
In 9i and below, if the cost, card and bytes is not present, it's a clear indicator that the query was executed using the RBO and not the CBO.
Statistics
The second part of the autotrace output are the session statistics.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
24 consistent gets
0 physical reads
0 redo size
1598 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
15 rows processed
How to install autotrace to a user
Below the action to perform in order to set autotrace to a user :
C:\Documents and Settings\Nicolas>sqlplus SYS/password@ORCL as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Mon Nov 10 14:57:48 2008
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> grant PLUSTRACE to scott;
grant PLUSTRACE to scott
*
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist
SQL> @C:\oracle\product\10.2.0\db_1\sqlplus\admin\plustrce.sql
SQL> grant plustrace to scott;
Grant succeeded.
SQL>