ANBOB™

执行计划中的谓词信息非常的重要，有助于我们判断是否进行了隐式转换，为什么没有使用索引等， 使用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

打赏

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