Oracle 20c新特性: dbms_xplan.display_awr 增加了谓词信息

执行计划中的谓词信息非常的重要，有助于我们判断是否进行了隐式转换，为什么没有使用索引等， 使用dbms_xplan.display_cursor可以从shared_pool中取到sq cursor的谓词，但是在20c之前dbms_xplan.display_AWR 在之前的版本中并不能， 但是在Oracle社区的投票和诸多人的推动下，终于在oracle 20c所谓词信息也在dbms_xplan.display_AWR中显示。

— demo

[oracle@oel7db1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 20.0.0.0.0 - Production on Sun May 24 01:46:12 2020
Version 20.2.0.0.0
Copyright (c) 1982, 2020, Oracle.  All rights reserved.
Connected to:
Oracle Database 20c Enterprise Edition Release 20.0.0.0.0 - Production
Version 20.2.0.0.0


USERNAME             INST_NAME            HOST_NAME                  I# SID   SERIAL#  VERSION    STARTED  SPID       OPID  CPID            SADDR            PADDR
-------------------- -------------------- ------------------------- --- ----- -------- ---------- -------- ---------- ----- --------------- ---------------- ----------------
SYS                  CDB$ROOT-anbob20c    oel7db1                     1 78    32736    20.0.0.0.0 20200524 10981      49    10980           00000000707F5EF0 000000007058B6B0

SQL> create table tobj as select * from dba_objects where rownum<=1000; Table created. SQL> set feedback on sql_id
SQL> select owner,count(*) from tobj where owner='SYS' group by owner;

OWNER                            COUNT(*)
------------------------------ ----------
SYS                                   985

SQL_ID: gsmdpptgv34w5
SQL> set feedback off
SQL> exec  dbms_workload_repository.add_colored_sql('gsmdpptgv34w5');
PL/SQL procedure successfully completed.

SQL> exec dbms_workload_repository.create_snapshot;

SQL> select owner,count(*) from tobj where owner='SYS' group by owner;

OWNER                            COUNT(*)
------------------------------ ----------
SYS                                   985

SQL> exec dbms_workload_repository.create_snapshot;
SQL> @st DBA_HIST_COLORED_SQL;

      DBID SQL_ID        CREATE_TIME             CON_ID
---------- ------------- ------------------- ----------
4232312917 gsmdpptgv34w5 2020-05-24 02:06:22          0

SQL> select * from dbms_xplan.display_awr(sql_id=>'gsmdpptgv34w5');

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
SQL_ID gsmdpptgv34w5
--------------------
select owner,count(*) from tobj where owner='SYS' group by owner

Plan hash value: 1334607550

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |       |       |     6 (100)|          |
|   1 |  SORT GROUP BY NOSORT|      |   985 | 65010 |     6   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL  | TOBJ |   985 | 65010 |     6   (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

**   2 - filter("OWNER"='SYS') **《《《《《《《

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

当然，这也可以从?/rdbms/admin/awrsqrpt.sql报告中看到谓词信息。