Friday, March 23, 2012

Set autotrace (SP2-0612: Error generating AUTOTRACE EXPLAIN report)

Wanted to give permission of Auto Trace of other User for tuning purposes?

SQL> create role plustrace;

Role created.

SQL> grant select on v_$sesstat to plustrace;

Grant succeeded.

SQL> grant select on v_$statname to plustrace;

Grant succeeded.

SQL> grant select on v_$mystat to plustrace;

Grant succeeded.

SQL> grant  plustrace to test with admin option;

Grant succeeded.

SQL> conn test/test
Connected.
SQL> set autotrace traceonly explain
SQL> select * from T1;

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  4000 |   968K|    44   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T1   |  4000 |   968K|    44   (0)| 00:00:01 |
--------------------------------------------------------------------------

There is a sql script in "D:\app\rnagi\product\11.2.0\dbhome_1\sqlplus\admin\plustrce.sql" which we can execute to create this role and grant role to any user who need trace.
Enjoy!

1 comment: