Enable AUTOTRACE
To get full AUTOTRACE feature in your session, you should take the following steps.
- Need PLAN_TABLE table to be accessed in user's schema.
- Need PLUSTRACE role to be granted to user.
If you don't have it, 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.