oracle12c执行计划固化,SP2-0618 SP2-0611 ORA-01919 开执行计划错误（oracle12c）

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！

原文：http://adelazhu.blog.51cto.com/9455045/1680892