oracle dbms_xplan display,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报告中看到谓词信息。

Note:Information will be captured in each snapshot for sqls marked using DBMS_WORKLOAD_REPOSITORY.ADD_COLORED_SQL. All the information related to the specific sql (sql plan, executions statistics, etc) will be stored in the AWR tables. However the SQLs will not neccesarily appear in the AWR Report sections related to SQLS unless they are indeed amongst the top SQLs for that sections as determined by the setting for DBMS_WORKLOAD_REPOSITORY.AWR_SET_REPORT_THRESHOLDS

打赏

微信扫一扫，打赏作者吧～