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-88672B7647E2https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_XPLAN.html#GUID-BAD480AA-351A-48FE-A8E7-F0D8EF643EBF
