在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 行。
(完)