Skip to content

How to Enable AUTOTRACE

Enable AUTOTRACE

To get full AUTOTRACE feature in your session, you should take the following steps.

  1. You may need PLAN_TABLE table to be accessed in user's schema.
  2. 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.

  3. Need PLUSTRACE role to be granted to user.
  4. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *