1. Grant alter session
connect sys/sys as sysdba
SQL > startup
GRANT ALTER SESSION TO PULSEUSER;
1. Create trace role using sysdba role
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SET AUTOTRACE TRACEONLY command may sometimes fail with SP2-0618 and SP2-0611
exceptions. Which needs to be resolved by creating PLUSTRACE role and assigning
it to USER.
PROBLEM:
C:\> sqlplus scoot/tiger
SQL*Plus: Release 11.2.0.3.0 Production on Thu Dec 13 17:37:25 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Release 11.2.0.3.0 - Production
SQL> set autotrace traceonly;
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report
SOLUTION:
For execution AUTOTRACE the users needs to have the PLUSTRACE role, which does not
exist by default. PLUSTRACE role can be created using SYS user by executing
ORACLE_HOME\sqlplus\admin\plustrce.sql
The plustrace.sql creates the PLUSTRACE role and grants SELECT on V_$SESSTAT,
V_$STATNME and V_$MYSTAT. PLUSTRACE is granted to the DBA role with ADMIN OPTION.
For 9i and eariler databases you may also need to create the plan table by
executing following script
ORACLE_HOME\rdbms\admin\utlxplan.sql
The PLAN_TABLE already exists on database version 10g and higher.
SQL> connect sys/sys as sysdba
Connected.
SQL> @%oracle_home%\sqlplus\admin\plustrce.sql;
SQL>
SQL> drop role plustrace;
drop role plustrace
*
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist
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.
After executing ORACLE_HOME\sqlplus\admin\plustrce.sql, we need to grant the
PLUSTRACE role to the user.
SQL> grant plustrace to scott;
Grant succeeded.
SQL> connect scott/tiger
Connected.
SQL> set autotrace trace
SQL> select user from dual;
Execution Plan
----------------------------------------------------------
Plan hash value: 1388734953
-----------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
421 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
NOTE: For UNIX/LINUX environments ORACLE_HOME should be written as $ORACLE_HOME
%oracle_home% as is this post is for windows environments.
3. Run your query to get the execution plan. There will be no returned rows but only the execution plan with elapsed time.
SQL> set timing on;
SQL> set autotrace traceonly;
SQL> SELECT DOMAIN, NAME, DDS_DSO_ID, PE FROM DHG WHERE (SPS_SP_RIC = '.[SPSIDN02015' and MC_LABEL = 3015 and HashValue = 4);
52082 rows selected.
Elapsed: 00:00:00.27
Execution Plan
----------------------------------------------------------
Plan hash value: 4059394150
--------------------------------------------------------------------------------
----------------
| Id | Operation | Name | Rows | Bytes | Cost (%
CPU)| Time |
--------------------------------------------------------------------------------
----------------
| 0 | SELECT STATEMENT | | 1 | 56 | 4
(0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IX_DHG_ID_MCLABEL_HASH_FULL | 1 | 56 | 4
(0)| 00:00:01 |
--------------------------------------------------------------------------------
----------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("SPS_SP_RIC"='.[SPSIDN02015' AND "MC_LABEL"=3015 AND "HASHVALUE"=4
)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3884 consistent gets
0 physical reads
0 redo size
1833556 bytes sent via SQL*Net to client
38716 bytes received via SQL*Net from client
3474 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
52082 rows processed
SQL> SELECT DOMAIN, NAME, DDS_DSO_ID, PE FROM DHG WHERE (SPS_SP_RIC = '.[SPSIDN02015' and MC_LABEL = 3015 and HashValue = 4) OR (SPS_SP_RIC = '.[SPSSBSVAE3' AND MC_LABEL=2905);
52443 rows selected.
Elapsed: 00:00:00.26
Execution Plan
----------------------------------------------------------
Plan hash value: 2271364969
--------------------------------------------------------------------------------
-----------------
| Id | Operation | Name | Rows | Bytes | Cost (
%CPU)| Time |
--------------------------------------------------------------------------------
-----------------
| 0 | SELECT STATEMENT | | 1351 | 75656 | 19
(0)| 00:00:01 |
| 1 | CONCATENATION | | | |
| |
|* 2 | INDEX RANGE SCAN| IX_DHG_ID_MCLABEL_HASH_FULL | 1 | 56 | 4
(0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN| IX_DHG_ID_MCLABEL_HASH_FULL | 1350 | 75600 | 15
(0)| 00:00:01 |
--------------------------------------------------------------------------------
-----------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SPS_SP_RIC"='.[SPSIDN02015' AND "MC_LABEL"=3015 AND "HASHVALUE"=4
)
3 - access("SPS_SP_RIC"='.[SPSSBSVAE3' AND "MC_LABEL"=2905)
filter(LNNVL("SPS_SP_RIC"='.[SPSIDN02015') OR LNNVL("HASHVALUE"=4) OR
LNNVL("MC_LABEL"=3015))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3915 consistent gets
0 physical reads
0 redo size
1847024 bytes sent via SQL*Net to client
38980 bytes received via SQL*Net from client
3498 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
52443 rows processed
SQL> SELECT DOMAIN, NAME, DDS_DSO_ID, PE FROM DHG WHERE (SPS_SP_RIC = '.[SPSIDN02015' and MC_LABEL = 3015 and HashValue = 4) OR (SPS_SP_RIC = '.[SPSSBSVAE3' AND MC_LABEL=2905) OR (SPS_SP_RIC = '.[SPSIDN01020');
417018 rows selected.
Elapsed: 00:00:02.11
Execution Plan
----------------------------------------------------------
Plan hash value: 3621106669
--------------------------------------------------------------------------------
-----------------
| Id | Operation | Name | Rows | Bytes | Cost (
%CPU)| Time |
--------------------------------------------------------------------------------
-----------------
| 0 | SELECT STATEMENT | | 74129 | 4053K| 623
(1)| 00:00:08 |
| 1 | CONCATENATION | | | |
| |
|* 2 | INDEX RANGE SCAN| IX_DHG_ID_MCLABEL_HASH_FULL | 1 | 56 | 4
(0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN| IX_DHG_ID_MCLABEL_HASH_FULL | 1350 | 75600 | 15
(0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN| IX_DHG_ID_MCLABEL_HASH_FULL | 72778 | 3980K| 604
(1)| 00:00:08 |
--------------------------------------------------------------------------------
-----------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SPS_SP_RIC"='.[SPSIDN02015' AND "MC_LABEL"=3015 AND "HASHVALUE"=4
)
3 - access("SPS_SP_RIC"='.[SPSSBSVAE3' AND "MC_LABEL"=2905)
filter(LNNVL("SPS_SP_RIC"='.[SPSIDN02015') OR LNNVL("HASHVALUE"=4) OR
LNNVL("MC_LABEL"=3015))
4 - access("SPS_SP_RIC"='.[SPSIDN01020')
filter((LNNVL("SPS_SP_RIC"='.[SPSSBSVAE3') OR LNNVL("MC_LABEL"=2905)) AND
(LNNVL("SPS_SP_RIC"='.[SPSIDN02015') OR LNNVL("HASHVALUE"=4) OR LN
NVL("MC_LABEL"=3015)))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
31050 consistent gets
0 physical reads
0 redo size
14681768 bytes sent via SQL*Net to client
306335 bytes received via SQL*Net from client
27803 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
417018 rows processed
End
