To set up Autotrace by using the PLUSTRACE ROLE in Oracle Database
Posted by Mir Sayeed Hassan on December 24th, 2017
To set up Autotrace by using the PLUSTRACE ROLE in Oracle Database
Follow the step by step procedure as shown below:
SQL> conn sys/sys as sysdba Connected.
SQL> set autotrace traceonly; SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled SP2-0611: Error enabling STATISTICS report
For execution AUTOTRACE the users need to have the PLUSTRACE role, which does not exist by default in Oracle database
SQL> GRANT PLUSTRACE TO scott; GRANT PLUSTRACE TO scott * ERROR at line 1: ORA-01919: role 'PLUSTRACE' does not exist
We need to logged-in as SYS & execute the below package to enable this feature in Oracle database for auto trace
SQL> @$ORACLE_HOME/sqlplus/admin/plustrce.sql;
SQL> drop role plustrace; Role dropped.
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 dba with admin option; Grant succeeded.
SQL> set echo off
Now grant the plustrace to user scott:
SQL> grant PLUSTRACE to scott; Grant succeeded.
SQL> conn scott/tiger Connected.
SQL> select table_name from user_tables; TABLE_NAME ------------------------------ ORDERS ABC DEPT EMP BONUS SALGRADE 6 rows selected.
SQL> set autotrace traceonly;
SQL> select table_name from user_tables; TABLE_NAME ------------------------------ ORDERS ABC DEPT EMP BONUS SALGRADE 6 rows selected.
Execution Plan ---------------------------------------------------------- ERROR: ORA-01039: insufficient privileges on underlying objects of the view SP2-0612: Error generating AUTOTRACE EXPLAIN report ----------- ERROR (Solution – Grant below Priv) Statistics ---------------------------------------------------------- 231 recursive calls 14 db block gets 2068 consistent gets 0 physical reads 11156 redo size 631 bytes sent via SQL*Net to client 524 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 6 rows processed
To resolve the above error as “ORA-01039 & SP2-0612”
SQL> conn sys/sys as sysdba Connected.
SQL> GRANT SELECT ANY DICTIONARY TO scott; Grant succeeded.
SQL> conn scott/tiger Connected.
SQL> select table_name from user_tables; TABLE_NAME ------------------------------ ORDERS ABC DEPT EMP BONUS SALGRADE 6 rows selected.
The above query is without the autotrace login
Set the autotrace & check the same query – will give you the complete details of execution plan:
SQL> set autotrace traceonly;
SQL> select table_name from user_tables; 6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1807080242
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Tim
e |--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2421 | 390K| 643 (1)| 00:
00:08 |
|* 1 | HASH JOIN RIGHT OUTER | | 2421 | 390K| 643 (1)| 00:
00:08 |
| 2 | INDEX FULL SCAN | I_USER2 | 113 | 452 | 1 (0)| 00:
00:01 |
|* 3 | HASH JOIN OUTER | | 2421 | 380K| 642 (1)| 00:
00:08 |
|* 4 | HASH JOIN OUTER | | 2421 | 361K| 554 (1)| 00:
00:07 |
|* 5 | HASH JOIN RIGHT OUTER | | 2421 | 349K| 467 (1)| 00:
00:06 |
| 6 | TABLE ACCESS FULL | SEG$ | 6213 | 68343 | 64 (0)| 00:
00:01 |
|* 7 | HASH JOIN | | 2421 | 323K| 402 (1)| 00:
00:05 |
| 8 | TABLE ACCESS FULL | TS$ | 28 | 84 | 9 (0)| 00:
00:01 |
| 9 | NESTED LOOPS | | 2421 | 316K| 393 (1)| 00:
00:05 |
| 10 | MERGE JOIN CARTESIAN| | 2421 | 245K| 308 (1)| 00:
00:04 |
|* 11 | HASH JOIN | | 1 | 68 | 0 (0)| 00:
00:01 |
|* 12 | FIXED TABLE FULL | X$KSPPI | 1 | 55 | 0 (0)| 00:
00:01 |
| 13 | FIXED TABLE FULL | X$KSPPCV | 100 | 1300 | 0 (0)| 00:
00:01 |
| 14 | BUFFER SORT | | 2421 | 87156 | 308 (1)| 00:
00:04 |
|* 15 | TABLE ACCESS FULL | OBJ$ | 2421 | 87156 | 308 (1)| 00:
00:04 |
|* 16 | TABLE ACCESS CLUSTER| TAB$ | 1 | 30 | 1 (0)| 00:
00:01 |
|* 17 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 0 (0)| 00:
00:01 |
| 18 | INDEX FAST FULL SCAN | I_OBJ1 | 87187 | 425K| 87 (0)| 00:
00:02 |
| 19 | INDEX FAST FULL SCAN | I_OBJ1 | 87187 | 681K| 87 (0)| 00:
00:02 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
--------------------------------------------------------------------------------
1 - access("CX"."OWNER#"="CU"."USER#"(+))
3 - access("T"."DATAOBJ#"="CX"."OBJ#"(+))
4 - access("T"."BOBJ#"="CO"."OBJ#"(+))
5 - access("T"."FILE#"="S"."FILE#"(+) AND "T"."BLOCK#"="S"."BLOCK#"(+) AND
"T"."TS#"="S"."TS#"(+))
7 - access("T"."TS#"="TS"."TS#")
11 - access("KSPPI"."INDX"="KSPPCV"."INDX")
12 - filter("KSPPI"."KSPPINM"='_dml_monitoring_enabled')
15 - filter("O"."OWNER#"=USERENV('SCHEMAID') AND BITAND("O"."FLAGS",128)=0)
16 - filter(BITAND("T"."PROPERTY",1)=0)
17 - access("O"."OBJ#"="T"."OBJ#")
Statistics
----------------------------------------------------------
231 recursive calls
14 db block gets
2068 consistent gets
0 physical reads
10980 redo size
631 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
6 rows processed
SQL> select * from abc; Execution Plan --------------------------------------------------------- Plan hash value: 1033171814 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 14 | 3 (0)| 00:00:01 | 1 | TABLE ACCESS FULL| ABC | 2 | 14 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 21 recursive calls 6 db block gets 4 consistent gets 0 physical reads 1580 redo size 660 bytes sent via SQL*Net to client 524 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 2 rows processed