Oracle 12c Pluggable Database（PDB）中查看执行计划的方法

在Oracle 12c 中启动了Pluggable Database后，通过SQL Plus查看SQL语句的执行计划时，可能会遇到不能查看的情况，尤其是使用下面的方法，没有什么有效的显示信息，这个问题可以采用如下方法来解决。

问题描述

环境：Oracle 12c 12.2.0.1 Database，配置了一个PDB，名字为 coffeebean

SQL> select con_id,dbid,name,open_mode from v$pdbs;

    CON_ID       DBID NAME                                     OPEN_MODE
---------- ---------- ---------------------------------------- --------------------
         2 3070021761 PDB$SEED                                 READ ONLY
         3 1729418692 COFFEEBEAN                               READ WRITE

设置容器为 PDB

SQL> alter session set container=coffeebean;

以SYS用户，进行授权：

SQL> show user;
USER 为 "SYS"
SQL> grant select on v_$sql to oe;

授权成功。

已用时间:  00: 00: 00.15
SQL> grant select on v_$session to oe;

授权成功。

已用时间:  00: 00: 00.03
SQL> grant select on v_$sql_plan_statistics_all to oe;

授权成功。

已用时间:  00: 00: 00.14

修改参数statistics_level，注意一定需要从 TYPICAL修改为 ALL。

SQL> show parameter statistics_level;

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
statistics_level                     string                 TYPICAL

SQL> alter system set statistics_level=ALL;

系统已更改。

已用时间:  00: 00: 00.11

OE模式下，执行一条SQL语句。

然后执行语句：

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  7cfz5wy9caaf4, child number 0
-------------------------------------
SELECT NAME NAME_COL_PLUS_SHOW_PARAM,DECODE(TYPE,1,'boolean',2,'string',
3,'integer',4,'file',5,'number',        6,'big integer', 'unknown')
TYPE,DISPLAY_VALUE VALUE_COL_PLUS_SHOW_PARAM FROM V$PARAMETER WHERE
UPPER(NAME) LIKE UPPER(:NMBIND_SHOW_OBJ) ORDER BY
NAME_COL_PLUS_SHOW_PARAM,ROWNUM

Plan hash value: 3852611832

-------------------------------------------------------------------------------------------
| Id  | Operation                  | Name             | E-Rows |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                  |        |       |       |          |
|   1 |  SORT ORDER BY             |                  |      1 |  2048 |  2048 | 2048  (0)|
|   2 |   COUNT                    |                  |        |       |       |          |
|   3 |    NESTED LOOPS            |                  |      1 |       |       |          |
|*  4 |     FIXED TABLE FULL       | X$KSPPI          |     12 |       |       |          |
|*  5 |     FIXED TABLE FIXED INDEX| X$KSPPCV (ind:1) |      1 |       |       |          |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter((UPPER("KSPPINM") LIKE UPPER(:NMBIND_SHOW_OBJ) AND
              TRANSLATE("KSPPINM",'_','$') NOT LIKE '$$%' AND "X"."INST_ID"=USERENV('INSTANCE')
              AND BITAND("KSPPIFLG",268435456)=0))
   5 - filter(("X"."INDX"="Y"."INDX" AND (TRANSLATE("KSPPINM",'_','$') NOT LIKE
              '$%' OR "KSPPSTDF"='FALSE' OR BITAND("KSPPSTVF",5)>0) AND
              INTERNAL_FUNCTION("Y"."CON_ID")))

Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level


已选择 37 行。

（完）