Enable AUTOTRACE
To get full AUTOTRACE feature in your session, you should take the following steps.
- You may need PLAN_TABLE table to be accessed in user's schema.
- Need PLUSTRACE role to be granted to user.
For 11g or later, PLAN_TABLE is a global temporary table for all users to store explain plan. So you don't have to create it.
For 10g or earlier, you need the table in your own schema, please create PLAN_TABLE table for yourself.
If you saw error, ORA-01919: role 'PLUSTRACE' does not exist, please install PLUSTRACE role first, then grant it to the user like this.
SQL> show user
USER is "SYS"
SQL> grant plustrace to hr;
Grant succeeded.Use AUTOTRACE
Here is an example that use AUTOTRACE.
SQL> conn hr/hr@orcl
Connected.
SQL> set autotrace on;
SQL> select count(*) from employees;
COUNT(*)
----------
107
Execution Plan
----------------------------------------------------------
Plan hash value: 3580537945
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| EMP_EMAIL_UK | 107 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------
Statistics
----------------------------------------------------------
100 recursive calls
0 db block gets
199 consistent gets
2 physical reads
0 redo size
551 bytes sent via SQL*Net to client
392 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
As you can see, the result includes both the optimizer execution path and the SQL statement execution statistics.