SQL> SET AUTOTRACE ON
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report
SQL> conn system/oracle
Connected.
SQL> grant PLUSTRACE to adela;
grant PLUSTRACE to adela
*
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist
解决:
drop role c##plustrace;
create role c##plustrace;
grant select on v_$sesstat to c##plustrace;
grant select on v_$statname to c##plustrace;
grant select on v_$mystat to c##plustrace;
grant c##plustrace to c##adela with admin option;
conn c##adela/oracle
set autotrace on
查看下效果
SQL> set line 100
SQL> select cname from c where cno in (select cno from sc where sno in (select sno from s where sname='adela'));
CNAME
--------------------
OS
C++
Java
DB
Execution Plan
----------------------------------------------------------
Plan hash value: 2310661688
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 22 | 5 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 1 | 22 | 5 (0)| 00:00:01 |
| 3 | VIEW | VW_NSO_1 | 2 | 10 | 4 (0)| 00:00:01 |
| 4 | HASH UNIQUE | | 1 | 48 | | |
| 5 | NESTED LOOPS | | 2 | 48 | 4 (0)| 00:00:01 |
|* 6 | TABLE ACCESS FULL | S | 1 | 13 | 3 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | SC_PK | 2 | 22 | 1 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | SYS_C009862 | 1 | | 0 (0)| 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID| C | 1 | 17 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter("SNAME"='adela')
7 - access("SNO"="SNO")
8 - access("CNO"="CNO")
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- this is an adaptive plan
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
16 consistent gets
0 physical reads
0 redo size
612 bytes sent via SQL*Net to client
543 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed
ok!