Oracle 20C AWR中记录SQL执行计划的谓词信息

通过社区客户及ACE的反馈，Oracle在20c的AWR中记录了SQL执行计划的谓词信息，可以通过DBMS_XPLAN和AWRSQRPT来查看。

set feedback on sql_id echo on pagesize 1000

SQL> select * from dual where ascii(dummy)=42;

no rows selected

SQL_ID: g4gx2zqbkjwh1

SQL> exec dbms_workload_repository.add_colored_sql('g4gx2zqbkjwh1');

PL/SQL procedure successfully completed.

SQL> exec dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

SQL> select * from dual where ascii(dummy)=42;

no rows selected

SQL> exec dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

SQL> select * from dbms_xplan.display_awr('g4gx2zqbkjwh1');

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID g4gx2zqbkjwh1
--------------------
select * from dual where ascii(dummy)=42

Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     2 (100)|          |
|*  1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter(ASCII("DUMMY")=42)


18 rows selected.

20c之前版本的执行计划信息如下：

SQL> select * from dbms_xplan.display_awr('g4gx2zqbkjwh1');

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID g4gx2zqbkjwh1
--------------------
select * from dual where ascii(dummy)=42

Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

13 rows selected.