Focus On Oracle

Installing, Backup & Recovery, Performance Tuning,
Troubleshooting, Upgrading, Patching

Oracle Engineered System


当前位置: 首页 » 技术文章 » 性能优化

19c中新增dbms_xplan.compare_plans

在19c中,DBMS_XPLAN又增加了一个函数COMPARE_PLANS。通过该函数可以生成报表,格式可以是TEXT、HTML、XML。通过名字我们可以知道,这个是用来比较执行计划的,这些计划可以是存在在plan_table、Cursor Cache、AWR、SQLTuningSets、SQL Baseline,亦或SQL Profile、Advisor Taks等。通过这个工具,我们可以比较一个参考计划和一个任意的测试计划列表,并突出它们之间的区别。比较的是计划逻辑的,而不是逐行的进行比较。

COMPARE_PLANS使用场景

您希望将性能正在下降的查询的当前计划与AWR中捕获的旧计划进行比较
SQL计划基线无法重现最初的预期计划,您需要确定新计划和预期计划之间的差异
您需要确定添加HINT、更改参数或创建索引将如何影响计划
您需要确定基于SQL profile或由SQL Performance Analyzer生成的计划与原始计划的区别
测试COMPARE_PLANS

create table ohs_test as select rownum rnum,lpad('*',20) rlevel from dual connect by level <= 100000;
create index ind_ohs_test on ohs_test(rnum);

set feedback on sql_id

select /*+ full(ohs_test) */ rnum from ohs_test where rnum<3;
select /*+ index(ohs_test ind_ohs_test) */ rnum from ohs_test where rnum<3;
set feedback on
VARIABLE v_report CLOB

BEGIN
  :v_report := DBMS_XPLAN.COMPARE_PLANS(
    reference_plan    => cursor_cache_object('0fzczbagvfb5f', NULL),
    compare_plan_list => plan_object_list(cursor_cache_object('1jndx3tv9wsnm', NULL)),
    type              => 'TEXT',
    level             => 'TYPICAL',
    section           => 'ALL');
END;
/

SET PAGESIZE 50000
SET LONG 100000
SET LINESIZE 210
COLUMN report FORMAT a200
SELECT :v_report REPORT FROM DUAL;
REPORT
----------------------------------------------------------------------------------------

COMPARE PLANS REPORT
----------------------------------------------------------------------------------------
  Current user           : OHSDBA
  Total number of plans  : 2
  Number of findings     : 1
----------------------------------------------------------------------------------------

COMPARISON DETAILS
----------------------------------------------------------------------------------------
 Plan Number            : 1 (Reference Plan)
 Plan Found             : Yes
 Plan Source            : Cursor Cache
 SQL ID                 : 0fzczbagvfb5f
 Child Number           : 0
 Plan Database Version  : 19.0.0.0
 Parsing Schema         : "OHSDBA"
 SQL Text               : select /*+ full(ohs_test) */ rnum from ohs_test where
                        rnum<3

Plan
-----------------------------

 Plan Hash Value  : 1441081977

-------------------------------------------------------------------------
| Id  | Operation           | Name     | Rows | Bytes | Cost | Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |          |      |       |  119 |          |
| * 1 |   TABLE ACCESS FULL | OHS_TEST |    2 |    10 |  119 | 00:00:01 |
-------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 1 - filter("RNUM"<3)

----------------------------------------------------------------------------------------
 Plan Number            : 2
 Plan Found             : Yes
 Plan Source            : Cursor Cache
 SQL ID                 : 1jndx3tv9wsnm
 Child Number           : 0
 Plan Database Version  : 19.0.0.0
 Parsing Schema         : "OHSDBA"
 SQL Text               : select /*+ index(ohs_test ind_ohs_test) */ rnum from
                        ohs_test where rnum<3

Plan
-----------------------------

 Plan Hash Value  : 1486512149

----------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows | Bytes | Cost | Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |      |       |    2 |          |
| * 1 |   INDEX RANGE SCAN | IND_OHS_TEST |    2 |    10 |    2 | 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 1 - access("RNUM"<3)

Comparison Results (1):
-----------------------------
 1. Query block SEL$1, Alias "OHS_TEST"@"SEL$1": Some columns (OPERATION,
    OPTIONS, OBJECT_NAME) do not match between the reference plan (id: 1) and
    the current plan (id: 1).
----------------------------------------------------------------------------------------
1 row selected.
SQL>
白皮书上更复杂的用法

This example prints the summary section only. The program compares the plan of child cursor number 2 for the SQL ID 8mkxm7ur07za0 with every plan in the following list:
   All plans in the shared SQL area that are generated for the SQL ID 8mkxm7ur07za0
   All plans generated in the SQL tuning set SH. SQLT_WORKLOAD for the SQL ID 6vfqvav0rgyad
   All plans in AWR that are captured for database ID 5 and SQL ID 6vfqvav0rgyad
   The plan baseline for the query with handle SQL_024d0f7d21351f5d with name SQL_PLAN_sdfjkd
   The plan stored in sh.plan_table identified by plan_id=38
   The plan identified by the SQL profile name pe3r3ejsfd
   All plans stored in SQL advisor identified by task name TASK_1228, execution name EXEC_1928, and SQL ID 8mkxm7ur07za0

VAR v_report CLOB
BEGIN
  :v_report := DBMS_XPLAN.COMPARE_PLANS(
    reference_plan    => CURSOR_CACHE_OBJECT('8mkxm7ur07za0', 2),
    compare_plan_list => plan_object_list(
         cursor_cache_object('8mkxm7ur07za0'),
         sqlset_object('SH', 'SQLT_WORKLOAD', '6vfqvav0rgyad'),
         awr_object('6vfqvav0rgyad', 5),
         spm_object('SQL_024d0f7d21351f5d', 'SQL_PLAN_sdfjkd'),
         plan_table_object('SH', 'plan_table', 38),
         sql_profile_object('pe3r3ejsfd'),
         advisor_object('TASK_1228', 'EXEC_1928', '8mkxm7ur07za0')),
    type              => 'XML',
    level             => 'ALL',
    section => 'SUMMARY');
END;
/
PRINT v_report

查询AWR中相同sql_id的不同执行计划

select distinct a.sql_id, a.plan_hash_value, b.plan_hash_value
from dba_hist_sql_plan a, dba_hist_sql_plan b
where a.sql_id = b.sql_id
and a.plan_hash_value <> b.plan_hash_value
and a.timestamp > sysdate-1
and b.timestamp > sysdate-1
order by 1;

19c中的DBMS_XPLAN


SQL> desc dbms_xplan
FUNCTION BUILD_PLAN_XML RETURNS XMLTYPE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 TABLE_NAME                     VARCHAR2                IN     DEFAULT
 STATEMENT_ID                   VARCHAR2                IN     DEFAULT
 PLAN_ID                        NUMBER                  IN     DEFAULT
 FORMAT                         VARCHAR2                IN     DEFAULT
 FILTER_PREDS                   VARCHAR2                IN     DEFAULT
 PLAN_TAG                       VARCHAR2                IN     DEFAULT
 REPORT_REF                     VARCHAR2                IN     DEFAULT
FUNCTION COMPARE_PLANS RETURNS CLOB
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 REFERENCE_PLAN                 GENERIC_PLAN_OBJECT     IN
 COMPARE_PLAN_LIST              PLAN_OBJECT_LIST        IN
 TYPE                           VARCHAR2                IN     DEFAULT
 LEVEL                          VARCHAR2                IN     DEFAULT
 SECTION                        VARCHAR2                IN     DEFAULT
FUNCTION DIFF_PLAN RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SQL_TEXT                       CLOB                    IN
 OUTLINE                        CLOB                    IN
 USER_NAME                      VARCHAR2                IN     DEFAULT
FUNCTION DIFF_PLAN_AWR RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SQL_ID                         VARCHAR2                IN
 PLAN_HASH_VALUE1               NUMBER                  IN
 PLAN_HASH_VALUE2               NUMBER                  IN
FUNCTION DIFF_PLAN_CURSOR RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SQL_ID                         VARCHAR2                IN
 CURSOR_CHILD_NUM1              NUMBER                  IN
 CURSOR_CHILD_NUM2              NUMBER                  IN
FUNCTION DIFF_PLAN_OUTLINE RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SQL_TEXT                       CLOB                    IN
 OUTLINE1                       CLOB                    IN
 OUTLINE2                       CLOB                    IN
 USER_NAME                      VARCHAR2                IN     DEFAULT
FUNCTION DIFF_PLAN_SQL_BASELINE RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 BASELINE_PLAN_NAME1            VARCHAR2                IN
 BASELINE_PLAN_NAME2            VARCHAR2                IN
FUNCTION DISPLAY RETURNS DBMS_XPLAN_TYPE_TABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 TABLE_NAME                     VARCHAR2                IN     DEFAULT
 STATEMENT_ID                   VARCHAR2                IN     DEFAULT
 FORMAT                         VARCHAR2                IN     DEFAULT
 FILTER_PREDS                   VARCHAR2                IN     DEFAULT
FUNCTION DISPLAY_AWR RETURNS DBMS_XPLAN_TYPE_TABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SQL_ID                         VARCHAR2                IN
 PLAN_HASH_VALUE                NUMBER(38)              IN     DEFAULT
 DB_ID                          NUMBER(38)              IN     DEFAULT
 FORMAT                         VARCHAR2                IN     DEFAULT
 CON_ID                         NUMBER(38)              IN     DEFAULT
 AWR_LOCATION                   VARCHAR2                IN     DEFAULT
FUNCTION DISPLAY_CURSOR RETURNS DBMS_XPLAN_TYPE_TABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SQL_ID                         VARCHAR2                IN     DEFAULT
 CURSOR_CHILD_NO                NUMBER(38)              IN     DEFAULT
 FORMAT                         VARCHAR2                IN     DEFAULT
 SHARD_ID                       NUMBER                  IN
FUNCTION DISPLAY_CURSOR RETURNS DBMS_XPLAN_TYPE_TABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SQL_ID                         VARCHAR2                IN     DEFAULT
 CURSOR_CHILD_NO                NUMBER(38)              IN     DEFAULT
 FORMAT                         VARCHAR2                IN     DEFAULT
 SHARD_IDS                      NUM_TAB_TYPE            IN
FUNCTION DISPLAY_CURSOR RETURNS DBMS_XPLAN_TYPE_TABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SQL_ID                         VARCHAR2                IN     DEFAULT
 CURSOR_CHILD_NO                NUMBER(38)              IN     DEFAULT
 FORMAT                         VARCHAR2                IN     DEFAULT
FUNCTION DISPLAY_PLAN RETURNS CLOB
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 TABLE_NAME                     VARCHAR2                IN     DEFAULT
 STATEMENT_ID                   VARCHAR2                IN     DEFAULT
 FORMAT                         VARCHAR2                IN     DEFAULT
 FILTER_PREDS                   VARCHAR2                IN     DEFAULT
 TYPE                           VARCHAR2                IN     DEFAULT
FUNCTION DISPLAY_SHARD_PLANS RETURNS DBMS_XPLAN_TYPE_TABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FORMAT_FLAGS                   NUMBER                  IN
 DIAG_REPOS_CUR                 REF CURSOR              IN     DEFAULT
 SQL_ID                         VARCHAR2                IN     DEFAULT
 CURSOR_CHILD_NO                NUMBER(38)              IN     DEFAULT
 SHARD_IDS                      NUM_TAB_TYPE            IN     DEFAULT
FUNCTION DISPLAY_SQLSET RETURNS DBMS_XPLAN_TYPE_TABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SQLSET_NAME                    VARCHAR2                IN
 SQL_ID                         VARCHAR2                IN
 PLAN_HASH_VALUE                NUMBER(38)              IN     DEFAULT
 FORMAT                         VARCHAR2                IN     DEFAULT
 SQLSET_OWNER                   VARCHAR2                IN     DEFAULT
FUNCTION DISPLAY_SQL_PATCH_PLAN RETURNS DBMS_XPLAN_TYPE_TABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NAME                           VARCHAR2                IN
 FORMAT                         VARCHAR2                IN     DEFAULT
FUNCTION DISPLAY_SQL_PLAN_BASELINE RETURNS DBMS_XPLAN_TYPE_TABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SQL_HANDLE                     VARCHAR2                IN     DEFAULT
 PLAN_NAME                      VARCHAR2                IN     DEFAULT
 FORMAT                         VARCHAR2                IN     DEFAULT
FUNCTION DISPLAY_SQL_PROFILE_PLAN RETURNS DBMS_XPLAN_TYPE_TABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NAME                           VARCHAR2                IN
 FORMAT                         VARCHAR2                IN     DEFAULT
FUNCTION DISPLAY_WORKLOAD_REPOSITORY RETURNS DBMS_XPLAN_TYPE_TABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SQL_ID                         VARCHAR2                IN
 PLAN_HASH_VALUE                NUMBER(38)              IN     DEFAULT
 FORMAT                         VARCHAR2                IN     DEFAULT
 DBID                           NUMBER(38)              IN     DEFAULT
 CON_DBID                       NUMBER(38)              IN     DEFAULT
 AWR_LOCATION                   VARCHAR2                IN     DEFAULT
FUNCTION FORMAT_NUMBER RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NUM                            NUMBER                  IN
FUNCTION FORMAT_NUMBER2 RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NUM                            NUMBER                  IN
FUNCTION FORMAT_SIZE RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NUM                            NUMBER                  IN
FUNCTION FORMAT_SIZE2 RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NUM                            NUMBER                  IN
FUNCTION FORMAT_TIME_S RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NUM                            NUMBER                  IN
FUNCTION GET_CURSOR_ROWS RETURNS PLAN_TABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SQL_ID                         VARCHAR2                IN     DEFAULT
 CURSOR_CHILD_NO                NUMBER(38)              IN     DEFAULT
 FORMAT                         VARCHAR2                IN     DEFAULT
FUNCTION GET_FINAL_PLAN RETURNS PLAN_TABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 PLAN_ROWS                      PLAN_TABLE              IN
 FORMAT                         VARCHAR2                IN     DEFAULT
FUNCTION GET_PLANDIFF_REPORT_XML RETURNS XMLTYPE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 REPORT_REF                     VARCHAR2                IN     DEFAULT
 TID                            NUMBER                  IN
 METHOD                         VARCHAR2                IN
FUNCTION GET_PLAN_ROWS RETURNS PLAN_TABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 TABLE_NAME                     VARCHAR2                IN     DEFAULT
 STATEMENT_ID                   VARCHAR2                IN     DEFAULT
 PLAN_ID                        VARCHAR2                IN     DEFAULT
 FORMAT                         VARCHAR2                IN     DEFAULT
 FILTER_PREDS                   VARCHAR2                IN     DEFAULT
 MASK_COST                      NUMBER                  IN     DEFAULT
 OWNER_NAME                     VARCHAR2                IN     DEFAULT
FUNCTION I_DISPLAY_CURSOR RETURNS DBMS_XPLAN_TYPE_TABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SQL_ID                         VARCHAR2                IN     DEFAULT
 CURSOR_CHILD_NO                NUMBER(38)              IN     DEFAULT
 FORMAT                         VARCHAR2                IN     DEFAULT
 SHARD_IDS                      NUM_TAB_TYPE            IN     DEFAULT
FUNCTION I_DISPLAY_SMB_PLAN RETURNS DBMS_XPLAN_TYPE_TABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NAME                           VARCHAR2                IN
 FORMAT                         VARCHAR2                IN     DEFAULT
 OBJ_TYPE                       NUMBER                  IN
FUNCTION PREPARE_PLAN_XML_QUERY RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 PLAN_QUERY                     VARCHAR2                IN
FUNCTION PREPARE_RECORDS RETURNS DBMS_XPLAN_TYPE_TABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 PLAN_CUR                       REF CURSOR              IN
 IP_FORMAT_FLAGS                NUMBER                  IN
 DIAG_REPOS_CUR                 REF CURSOR              IN     DEFAULT
 SQL_ID                         VARCHAR2                IN     DEFAULT
 CURSOR_CHILD_NO                NUMBER(38)              IN     DEFAULT
 SHARD_IDS                      NUM_TAB_TYPE            IN     DEFAULT
FUNCTION VALIDATE_FORMAT RETURNS BOOLEAN
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 HASPLANSTATS                   BOOLEAN                 IN
 FORMAT                         VARCHAR2                IN
 FORMAT_FLAGS                   NUMBER                  OUT

SQL>



Reference

https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/generating-and-displaying-execution-plans.html#GUID-0023D232-5695-4BA8-89C5-88672B7647E2
https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_XPLAN.html#GUID-BAD480AA-351A-48FE-A8E7-F0D8EF643EBF

关键词:dbms_stats 19c 

相关文章

Oracle Database 19c with UCP(Universal Connection Pool)
19c中新增dbms_xplan.compare_plans
Oracle 19c新特性之Automatic Flashback
Install Oracle RAC Database 19c Step by Step
19c新特性之Automatic In-memory
19c新特性之SQL语句隔离
19c新特性之实时统计信息收集
19c新特性之自动索引
Oracle事务卫士(Transaction Guard)和应用连续性(Application Continuity)
在Oracle RAC ADG中通过opatchauto应用补丁(19.5.0-->19.5.1)
Upgrade to Oracle Database 19c Hands-on
Install Oracle Database 19c on X86
Top