这些示例演示了如何为sh模式中的表查询生成比较计划报告。
1. 将解释性计划 与 游标中的计划进行比较
本示例说明了在sh架构中查询表的计划，然后执行查询：
EXPLAIN PLAN
SET STATEMENT_ID='TEST' FOR
SELECT c.cust_city, SUM(s.quantity_sold)
FROM customers c, sales s, products p
WHERE c.cust_id=s.cust_id
AND p.prod_id=s.prod_id
AND prod_min_price>100
GROUP BY c.cust_city;
SELECT c.cust_city, SUM(s.quantity_sold)
FROM customers c, sales s, products p
WHERE c.cust_id=s.cust_id
AND p.prod_id=s.prod_id
AND prod_min_price>100
GROUP BY c.cust_city;假设已执行查询的SQL ID为9mp7z6qq83k5y。下面的PL / SQL程序比较PLAN_TABLE计划和共享SQL区域中的计划：
BEGIN
:v_rep := DBMS_XPLAN.COMPARE_PLANS(
reference_plan => plan_table_object('SH', 'PLAN_TABLE', 'TEST', NULL),
compare_plan_list => plan_object_list(cursor_cache_object('9mp7z6qq83k5y')),
type => 'TEXT',
level => 'TYPICAL',
section => 'ALL');
END;
/
PRINT v_rep
以下样本报告显示计划是相同的：
COMPARE PLANS REPORT
-------------------------------------------------------------------------
Current user : SH
Total number of plans : 2
Number of findings : 1
-------------------------------------------------------------------------
COMPARISON DETAILS
-------------------------------------------------------------------------
Plan Number : 1 (Reference Plan)
Plan Found : Yes
Plan Source : Plan Table
Plan Table Owner : SH
Plan Table Name : PLAN_TABLE
Statement ID : TEST
Plan ID : 52
Plan Database Version : 19.0.0.0
Parsing Schema : "SH"
SQL Text : No SQL Text
Plan
-----------------------------
Plan Hash Value : 3473931970
--------------------------------------------------------------------------
| Id| Operation | Name | Rows | Bytes |Cost| Time |
--------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 620| 22320|1213| 00:00:01 |
| 1| HASH GROUP BY | | 620| 22320|1213| 00:00:01 |
|* 2| HASH JOIN | |160348| 5772528|1209| 00:00:01 |
| 3| TABLE ACCESS FULL |CUSTOMERS| 55500| 832500| 414| 00:00:01 |
|* 4| HASH JOIN | |160348| 3367308| 472| 00:00:01 |
|* 5| TABLE ACCESS FULL |PRODUCTS | 13| 117| 2| 00:00:01 |
| 6| PARTITION RANGE ALL | |918843|11026116| 467| 00:00:01 |
| 7| TABLE ACCESS FULL |SALES |918843|11026116| 467| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("C"."CUST_ID"="S"."CUST_ID")
* 4 - access("P"."PROD_ID"="S"."PROD_ID")
* 5 - filter("PROD_MIN_PRICE">100)
Notes
-----
- This is an adaptive plan
--------------------------------------------------------------------------------
Plan Number : 2
Plan Found : Yes
Plan Source : Cursor Cache
SQL ID : 9mp7z6qq83k5y
Child Number : 0
Plan Database Version : 19.0.0.0
Parsing Schema : "SH"
SQL Text : select c.cust_city, sum(s.quantity_sold) from
customers c, sales s, products p where
c.cust_id=s.cust_id and p.prod_id=s.prod_id and
prod_min_price>100 group by c.cust_city
Plan
-----------------------------
Plan Hash Value : 3473931970
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost| Time |
----------------------------------------------------------------------------
| 0| SELECT STATEMENT | | | |1213 | |
| 1| HASH GROUP BY | | 620| 22320|1213 | 00:00:01 |
|* 2| HASH JOIN | |160348| 5772528|1209 | 00:00:01 |
| 3| TABLE ACCESS FULL |CUSTOMERS | 55500| 832500| 414 | 00:00:01 |
|* 4| HASH JOIN | |160348| 3367308| 472 | 00:00:01 |
|* 5| TABLE ACCESS FULL |PRODUCTS | 13| 117| 2 | 00:00:01 |
| 6| PARTITION RANGE ALL | |918843|11026116| 467 | 00:00:01 |
| 7| TABLE ACCESS FULL |SALES |918843|11026116| 467 | 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("C"."CUST_ID"="S"."CUST_ID")
* 4 - access("P"."PROD_ID"="S"."PROD_ID")
* 5 - filter("PROD_MIN_PRICE">100)
Notes
-----
- This is an adaptive plan
Comparison Results (1):
-----------------------------
1. The plans are the same.2.比较基准 和 SQL调整集中的计划
假设您要比较以下查询的计划，这些查询的区别仅在于NO_MERGE子查询中包含的提示不同：
SELECT c.cust_city, SUM(s.quantity_sold)
FROM customers c, sales s,
(SELECT prod_id FROM products WHERE prod_min_price>100) p
WHERE c.cust_id=s.cust_id
AND p.prod_id=s.prod_id
GROUP BY c.cust_city;
SELECT c.cust_city, SUM(s.quantity_sold)
FROM customers c, sales s,
(SELECT /*+ NO_MERGE */ prod_id FROM products WHERE prod_min_price>100)
WHERE c.cust_id=s.cust_id
AND p.prod_id=s.prod_id
GROUP BY c.cust_city;第一个查询的计划在具有SQL句柄的SQL计划管理基线中捕获SQL_c522f5888cc4613e。第二个查询的计划存储在名为MYSTS1的SQL调优集中，并且SQL ID为d07p7qmrm13nc。您运行以下PL / SQL程序以比较计划：
VAR v_rep CLOB
BEGIN
v_rep := DBMS_XPLAN.COMPARE_PLANS(
reference_plan => spm_object('SQL_c522f5888cc4613e'),
compare_plan_list => plan_object_list(sqlset_object('SH', 'MYSTS1', 'd07p7qmrm13nc', null)),
type => 'TEXT',
level => 'TYPICAL',
section => 'ALL');
END;
/
PRINT v_rep
以下输出显示，唯一的参考计划（与没有提示的查询相对应）使用了视图合并：
---------------------------------------------------------------------------------------------
COMPARE PLANS REPORT
---------------------------------------------------------------------------------------------
Current user : SH
Total number of plans : 2
Number of findings : 1
---------------------------------------------------------------------------------------------
COMPARISON DETAILS
---------------------------------------------------------------------------------------------
Plan Number : 1 (Reference Plan)
Plan Found : Yes
Plan Source : SQL Plan Baseline
SQL Handle : SQL_c522f5888cc4613e
Plan Name : SQL_PLAN_ca8rpj26c8s9y7c2279c4
Plan Database Version : 19.0.0.0
Parsing Schema : "SH"
SQL Text : select c.cust_city, sum(s.quantity_sold) from customers c, sales s, (select
prod_id from products where prod_min_price>100) p where c.cust_id=s.cust_id and p.prod
id=s.prod_id group by c.cust_city
Plan
-----------------------------
Plan Hash Value : 2082634180
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 22 | |
| 1 | HASH GROUP BY | | 300 | 11400 | 22 | 00:00:01 |
| 2 | HASH JOIN | | 718 | 27284 | 21 | 00:00:01 |
| 3 | TABLE ACCESS FULL | CUSTOMERS | 630 | 9450 | 5 | 00:00:01 |
| 4 | HASH JOIN | | 718 | 16514 | 15 | 00:00:01 |
| 5 | TABLE ACCESS FULL | PRODUCTS | 573 | 5730 | 9 | 00:00:01 |
| 6 | PARTITION RANGE ALL | | 960 | 12480 | 5 | 00:00:01 |
| 7 | TABLE ACCESS FULL | SALES | 960 | 12480 | 5 | 00:00:01 |
------------------------------------------------------------------------------
------------------------------------------------------------------------------
Plan Number : 2
Plan Found : Yes
Plan Source : SQL Tuning Set
SQL Tuning Set Owner : SH
SQL Tuning Set Name : MYSTS1
SQL ID : d07p7qmrm13nc
Plan Hash Value : 655891922
Plan Database Version : 19.0.0.0
Parsing Schema : "SH"
SQL Text : select c.cust_city, sum(s.quantity_sold) from customers c, sales s, (select
/*+ NO_MERGE */ prod_id from products where prod_min_price>100) p where
c.cust_id=s.cust_id and p.prod_id=s.prod_id group by c.cust_city
Plan
-----------------------------
Plan Hash Value : 655891922
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 23 | |
| 1 | HASH GROUP BY | | 300 | 9900 | 23 | 00:00:01 |
| 2 | HASH JOIN | | 718 | 23694 | 21 | 00:00:01 |
| 3 | HASH JOIN | | 718 | 12924 | 15 | 00:00:01 |
| 4 | VIEW | | 573 | 2865 | 9 | 00:00:01 |
| 5 | TABLE ACCESS FULL | PRODUCTS | 573 | 5730 | 9 | 00:00:01 |
| 6 | PARTITION RANGE ALL | | 960 | 12480 | 5 | 00:00:01 |
| 7 | TABLE ACCESS FULL | SALES | 960 | 12480 | 5 | 00:00:01 |
| 8 | TABLE ACCESS FULL | CUSTOMERS | 630 | 9450 | 5 | 00:00:01 |
------------------------------------------------------------------------------
Notes
-----
- This is an adaptive plan
Comparison Results (1):
-----------------------------
1. Query block SEL$1: Transformation VIEW MERGE occurred only in the reference plan (result query block: SEL$F5BB74E1).
3. 在添加索引之前和之后比较计划
在此示例中，您将测试索引对查询计划的影响：
EXPLAIN PLAN
SET STATEMENT_ID='TST1' FOR
SELECT COUNT(*) FROM products WHERE prod_min_price>100;
CREATE INDEX newprodidx ON products(prod_min_price);
EXPLAIN PLAN
SET STATEMENT_ID='TST2' FOR
SELECT COUNT(*) FROM products WHERE prod_min_price>100;您执行以下PL / SQL程序以生成报告：
VAR v_rep CLOB
BEGIN
:v_rep := DBMS_XPLAN.COMPARE_PLANS(
reference_plan => plan_table_object('SH', 'PLAN_TABLE', 'TST1', NULL),
compare_plan_list => plan_object_list(plan_table_object('SH', 'PLAN_TABLE', 'TST2', NULL)),
TYPE => 'TEXT',
level => 'TYPICAL',
section => 'ALL');
END;
/
PRINT v_rep
以下报告表明两个计划中的操作不同：
COMPARE PLANS REPORT
--------------------------------------------------------------------------
Current user : SH
Total number of plans : 2
Number of findings : 1
--------------------------------------------------------------------------
COMPARISON DETAILS
--------------------------------------------------------------------------
Plan Number : 1 (Reference Plan)
Plan Found : Yes
Plan Source : Plan Table
Plan Table Owner : SH
Plan Table Name : PLAN_TABLE
Statement ID : TST1
Plan ID : 56
Plan Database Version : 19.0.0.0
Parsing Schema : "SH"
SQL Text : No SQL Text
Plan
-----------------------------
Plan Hash Value : 3421487369
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 2 | 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
| * 2 | TABLE ACCESS FULL | PRODUCTS | 13 | 65 | 2 | 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 2 - filter("PROD_MIN_PRICE">100)
--------------------------------------------------------------------------
Plan Number : 2
Plan Found : Yes
Plan Source : Plan Table
Plan Table Owner : SH
Plan Table Name : PLAN_TABLE
Statement ID : TST2
Plan ID : 57
Plan Database Version : 19.0.0.0
Parsing Schema : "SH"
SQL Text : No SQL Text
Plan
-----------------------------
Plan Hash Value : 2694011010
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 1 | 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
| * 2 | INDEX RANGE SCAN | NEWPRODIDX | 13 | 65 | 1 | 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("PROD_MIN_PRICE">100)
Comparison Results (1):
-----------------------------
1. Query block SEL$1, Alias PRODUCTS@SEL$1: Some columns (OPERATION, OPTIONS,
OBJECT_NAME) do not match between the reference plan (id: 2) and the
current plan (id: 2).
查询块SEL $ 1，别名PRODUCTS@SEL $ 1：参考计划（id：2）和当前计划（id：2）之间的某些列（OPERATION，OPTIONS，OBJECT_NAME）不匹配。4. 将计划与可见索引 和 不可见索引进行比较
在此示例中，应用程序执行以下查询：
select count(*)
from products p, sales s
where p.prod_id = s.prod_id
and p.prod_status = 'obsolete';
该查询的计划使用两个索引：sales_prod_bix和products_prod_status_bix。数据库使用两个索引的可见和不可见的所有组合生成四个计划。假定SQL计划管理在查询的基线中接受以下计划：
sales_prod_bix可见和products_prod_status_bix可见sales_prod_bix可见和products_prod_status_bix不可见sales_prod_bix无形和products_prod_status_bix有形
您使两个索引都不可见，然后再次执行查询。无法使用不可见索引的优化器将生成新计划。所有这三个基准计划都依赖于至少一个可见的索引，但无法重现。因此，优化器将使用新计划并将其添加到查询的SQL计划基准中。要将当前在共享SQL区域中的计划（即参考计划）与基线中的所有四个计划进行比较，请执行以下PL / SQL代码：
VAR v_rep CLOB
BEGIN
:v_rep := DBMS_XPLAN.COMPARE_PLANS(
reference_plan => cursor_cache_object('45ns3tzutg0ds'),
compare_plan_list => plan_object_list(spm_object('SQL_aec814b0d452da8a')), <----未指定plan_name ,将与该SQL在受计划管理保护的计划的所有SQL对比
TYPE => 'TEXT',
level => 'TYPICAL',
section => 'ALL');
END;
/
PRINT v_rep以下报告比较了所有五个计划：
-----------------------------------------------------------------------------
COMPARE PLANS REPORT
-----------------------------------------------------------------------------
Current user : SH
Total number of plans : 5
Number of findings : 19
-----------------------------------------------------------------------------
COMPARISON DETAILS
-----------------------------------------------------------------------------
Plan Number : 1 (Reference Plan)
Plan Found : Yes
Plan Source : Cursor Cache
SQL ID : 45ns3tzutg0ds
Child Number : 0
Plan Database Version : 19.0.0.0
Parsing Schema : "SH"
SQL Text : select count(*) from products p, sales s where p.prod_id = s.prod_id and
p.prod_status = 'obsolete'
Plan
-----------------------------
Plan Hash Value : 1136711713
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 15 | |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
| * 2 | HASH JOIN | | 320 | 9600 | 15 | 00:00:01 |
| 3 | JOIN FILTER CREATE | :BF0000 | 255 | 6375 | 9 | 00:00:01 |
| * 4 | TABLE ACCESS FULL | PRODUCTS | 255 | 6375 | 9 | 00:00:01 |
| 5 | JOIN FILTER USE | :BF0000 | 960 | 4800 | 5 | 00:00:01 |
| 6 | PARTITION RANGE ALL | | 960 | 4800 | 5 | 00:00:01 |
| * 7 | TABLE ACCESS FULL | SALES | 960 | 4800 | 5 | 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("P"."PROD_ID"="S"."PROD_ID")
* 4 - filter("P"."PROD_STATUS"='obsolete')
* 7 - filter(SYS_OP_BLOOM_FILTER(:BF0000,"S"."PROD_ID"))
Notes
-----
- baseline_repro_fail = yes
---------------------------------------------------------------------------------------------
Plan Number : 2
Plan Found : Yes
Plan Source : SQL Plan Baseline
SQL Handle : SQL_aec814b0d452da8a
Plan Name : SQL_PLAN_axk0nq3a55qna6e039463
Plan Database Version : 19.0.0.0
Parsing Schema : "SH"
SQL Text : select count(*) from products p, sales s where p.prod_id = s.prod_id and
p.prod_status = 'obsolete'
Plan
-----------------------------
Plan Hash Value : 1845728355
-------------------------------------------------------------------------------------------------
| Id | Operation | Name |Rows| Bytes | Cost | Time |
-------------------------------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 1 | 30 | 11 |00:00:01 |
| 1| SORT AGGREGATE | | 1 | 30 | | |
| * 2| HASH JOIN | |320 | 9600 | 11 |00:00:01 |
| 3| JOIN FILTER CREATE | :BF0000 |255 | 6375 | 5 |00:00:01 |
| * 4| VIEW | index$_join$_001 |255 | 6375 | 5 |00:00:01 |
| * 5| HASH JOIN | | | | | |
| 6| BITMAP CONVERSION TO ROWIDS | |255 | 6375 | 1 |00:00:01 |
| * 7| BITMAP INDEX SINGLE VALUE | PRODUCTS_PROD_STATUS_BIX | | | | |
| 8| INDEX FAST FULL SCAN | PRODUCTS_PK |255 | 6375 | 4 |00:00:01 |
| 9| JOIN FILTER USE | :BF0000 |960 | 4800 | 5 |00:00:01 |
| 10| PARTITION RANGE ALL | |960 | 4800 | 5 |00:00:01 |
|* 11| TABLE ACCESS FULL | SALES |960 | 4800 | 5 |00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("P"."PROD_ID"="S"."PROD_ID")
* 4 - filter("P"."PROD_STATUS"='obsolete')
* 5 - access(ROWID=ROWID)
* 7 - access("P"."PROD_STATUS"='obsolete')
* 11 - filter(SYS_OP_BLOOM_FILTER(:BF0000,"S"."PROD_ID"))
Comparison Results (4):
-----------------------------
1. Query block SEL$1, Alias P@SEL$1: Some lines (id: 4) in the reference plan are missing in the
current plan.
2. Query block SEL$1, Alias S@SEL$1: Some columns (ID) do not match between the reference plan (id:
5) and the current plan (id: 9).
3. Query block SEL$1, Alias S@SEL$1: Some columns (ID, PARENT_ID, PARTITION_ID) do not match
between the reference plan (id: 6) and the current plan (id: 10).
4. Query block SEL$1, Alias S@SEL$1: Some columns (ID, PARENT_ID, PARTITION_ID) do not match
between the reference plan (id: 7) and the current plan (id: 11).
---------------------------------------------------------------------------------------------
Plan Number : 3
Plan Found : Yes
Plan Source : SQL Plan Baseline
SQL Handle : SQL_aec814b0d452da8a
Plan Name : SQL_PLAN_axk0nq3a55qna43c0d821
Plan Database Version : 19.0.0.0
Parsing Schema : "SH"
SQL Text : select count(*) from products p, sales s where p.prod_id = s.prod_id and
p.prod_status = 'obsolete'
Plan
-----------------------------
Plan Hash Value : 1136711713
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 15 | 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
| * 2 | HASH JOIN | | 320 | 9600 | 15 | 00:00:01 |
| 3 | JOIN FILTER CREATE | :BF0000 | 255 | 6375 | 9 | 00:00:01 |
| * 4 | TABLE ACCESS FULL | PRODUCTS | 255 | 6375 | 9 | 00:00:01 |
| 5 | JOIN FILTER USE | :BF0000 | 960 | 4800 | 5 | 00:00:01 |
| 6 | PARTITION RANGE ALL | | 960 | 4800 | 5 | 00:00:01 |
| * 7 | TABLE ACCESS FULL | SALES | 960 | 4800 | 5 | 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("P"."PROD_ID"="S"."PROD_ID")
* 4 - filter("P"."PROD_STATUS"='obsolete')
* 7 - filter(SYS_OP_BLOOM_FILTER(:BF0000,"S"."PROD_ID"))
Comparison Results (1):
-----------------------------
1. The plans are the same.
------------------------------------------------------------------------------
Plan Number : 4
Plan Found : Yes
Plan Source : SQL Plan Baseline
SQL Handle : SQL_aec814b0d452da8a
Plan Name : SQL_PLAN_axk0nq3a55qna1b7aea6c
Plan Database Version : 19.0.0.0
Parsing Schema : "SH"
SQL Text : select count(*) from products p, sales s where p.prod_id = s.prod_id and
p.prod_status = 'obsolete'
Plan
-----------------------------
Plan Hash Value : 461040236
-------------------------------------------------------------------------------------
| Id | Operation | Name |Rows|Bytes | Cost | Time |
--------------------------------------------------------- ---------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 10 | 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
| 2 | NESTED LOOPS | |320 | 9600 | 10 | 00:00:01 |
|* 3 | TABLE ACCESS FULL | PRODUCTS |255 | 6375 | 9 | 00:00:01 |
| 4 | PARTITION RANGE ALL | | 1 | 5 | 10 | 00:00:01 |
| 5 | BITMAP CONVERSION COUNT | | 1 | 5 | 10 | 00:00:01 |
|* 6 | BITMAP INDEX SINGLE VALUE | SALES_PROD_BIX | | | | |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 3 - filter("P"."PROD_STATUS"='obsolete')
* 6 - access("P"."PROD_ID"="S"."PROD_ID")
Comparison Results (7):
-----------------------------
1. Query block SEL$1, Alias P@SEL$1: Some lines (id: 3) in the reference plan are missing in the
current plan.
2. Query block SEL$1, Alias S@SEL$1: Some lines (id: 5) in the reference plan are missing in the
current plan.
3. Query block SEL$1, Alias S@SEL$1: Some lines (id: 7) in the reference plan are missing in the
current plan.
4. Query block SEL$1, Alias S@SEL$1: Some lines (id: 5,6) in the current plan are missing in the
reference plan.
5. Query block SEL$1, Alias P@SEL$1: Some columns (OPERATION) do not match between the reference
plan (id: 2) and the current plan (id: 2).
6. Query block SEL$1, Alias P@SEL$1: Some columns (ID, PARENT_ID, DEPTH) do not match between the
reference plan (id: 4) and the current plan (id: 3).
7. Query block SEL$1, Alias S@SEL$1: Some columns (ID, PARENT_ID, DEPTH, POSITION, PARTITION_ID) do
not match between the reference plan (id: 6) and the current plan (id: 4).
---------------------------------------------------------------------------------------------
Plan Number : 5
Plan Found : Yes
Plan Source : SQL Plan Baseline
SQL Handle : SQL_aec814b0d452da8a
Plan Name : SQL_PLAN_axk0nq3a55qna0628afbd
Plan Database Version : 19.0.0.0
Parsing Schema : "SH"
SQL Text : select count(*) from products p, sales s where p.prod_id = s.prod_id and
p.prod_status = 'obsolete'
Plan
-----------------------------
Plan Hash Value : 103329725
-------------------------------------------------------------------------------------------
|Id| Operation | Name | Rows|Bytes|Cost|Time |
-------------------------------------------------------------------------------------------
| 0| SELECT STATEMENT | | | | 5 | |
| 1| SORT AGGREGATE | | 1 | 30 | | |
| 2| NESTED LOOPS | |320 |9600 | 5 |00:00:01|
| 3| VIEW | index$_join$_001 |255 |6375 | 5 |00:00:01|
| 4| HASH JOIN | | | | | |
| 5| BITMAP CONVERSION TO ROWIDS | |255 |6375 | 1 |00:00:01|
| 6| BITMAP INDEX SINGLE VALUE | PRODUCTS_PROD_STATUS_BIX | | | | |
| 7| INDEX FAST FULL SCAN | PRODUCTS_PK |255 |6375 | 4 |00:00:01|
| 8| PARTITION RANGE ALL | | 1 | 5 | 5 |00:00:01|
| 9| BITMAP CONVERSION TO ROWIDS | | 1 | 5 | 5 |00:00:01|
|10| BITMAP INDEX SINGLE VALUE | SALES_PROD_BIX | | | | |
-------------------------------------------------------------------------------------------
Comparison Results (7):
-----------------------------
1. Query block SEL$1, Alias P@SEL$1: Some lines (id: 3) in the reference plan are missing in the
current plan.
2. Query block SEL$1, Alias P@SEL$1: Some lines (id: 4) in the reference plan are missing in the
current plan.
3. Query block SEL$1, Alias S@SEL$1: Some lines (id: 5) in the reference plan are missing in the
current plan.
4. Query block SEL$1, Alias S@SEL$1: Some lines (id: 7) in the reference plan are missing in the
current plan.
5. Query block SEL$1, Alias S@SEL$1: Some lines (id: 9,10) in the current plan are missing in the
reference plan.
6. Query block SEL$1, Alias P@SEL$1: Some columns (OPERATION) do not match between the reference
plan (id: 2) and the current plan (id: 2).
7. Query block SEL$1, Alias S@SEL$1: Some columns (ID, PARENT_ID, DEPTH, POSITION, PARTITION_ID) do
not match between the reference plan (id: 6) and the current plan (id: 8).
上一个报告显示以下内容：
- 计划1是共享SQL区域中的参考计划。该计划不使用不可见的索引，并且不复制基线计划。
- 计划2在基线中，并假定
sales_prod_bix为不可见且products_prod_status_bix可见。 - 计划3在基线中，并假定两个索引都不可见。计划1和计划3相同。
- 计划4在基线中，并假定
sales_prod_bix可见和products_prod_status_bix不可见。 - 计划5在基线中，并假定两个索引均可见。
比较报告显示，计划1无法从该基准复制计划。原因是将游标中的计划（计划1）添加到了基线，因为在执行时没有基线计划可用，因此数据库对语句进行了软解析并生成了无索引计划。如果要使当前游标无效，并且如果要再次执行查询，则比较报告将显示游标计划确实重现了基线计划。
也可以看看：
《 Oracle数据库PL / SQL软件包和类型参考》以获取有关该DBMS_XPLAN软件包的 更多信息
5.比较无法重现的基线
一个用例是将基于成本的计划与SQL计划基线进行比较。在本示例中，您将创建一个唯一索引。数据库捕获使用该索引的计划基线。然后，使索引不可见，然后再次执行查询。基线计划无法重现，因为索引不可见，从而迫使优化器选择其他计划。基线计划和基于成本的计划之间的比较计划报告显示了两个计划之间访问路径的差异。
- 以user身份登录数据库
hr，然后创建一个计划表：CREATE TABLE PLAN_TABLE ( STATEMENT_ID VARCHAR2(30), PLAN_ID NUMBER, TIMESTAMP DATE, REMARKS VARCHAR2(4000), OPERATION VARCHAR2(30), OPTIONS VARCHAR2(255), OBJECT_NODE VARCHAR2(128), OBJECT_OWNER VARCHAR2(30), OBJECT_NAME VARCHAR2(30), OBJECT_ALIAS VARCHAR2(65), OBJECT_INSTANCE NUMBER(38), OBJECT_TYPE VARCHAR2(30), OPTIMIZER VARCHAR2(255), SEARCH_COLUMNS NUMBER, ID NUMBER(38), PARENT_ID NUMBER(38), DEPTH NUMBER(38), POSITION NUMBER(38), COST NUMBER(38), CARDINALITY NUMBER(38), BYTES NUMBER(38), OTHER_TAG VARCHAR2(255), PARTITION_START VARCHAR2(255), PARTITION_STOP VARCHAR2(255), PARTITION_ID NUMBER(38), OTHER LONG, DISTRIBUTION VARCHAR2(30), CPU_COST NUMBER(38), IO_COST NUMBER(38), TEMP_SPACE NUMBER(38), ACCESS_PREDICATES VARCHAR2(4000), FILTER_PREDICATES VARCHAR2(4000), PROJECTION VARCHAR2(4000), TIME NUMBER(38), QBLOCK_NAME VARCHAR2(30), OTHER_XML CLOB); - 执行以下DDL语句，这些语句在表中创建一个名为的表
staff和一个索引staff.employee_id：CREATE TABLE staff AS (SELECT * FROM employees); CREATE UNIQUE INDEX staff_employee_id ON staff (employee_id); - 执行以下语句将查询
staff置于SQL计划管理的保护下，然后使索引不可见：ALTER SESSION SET optimizer_capture_sql_plan_baselines = TRUE; SELECT COUNT(*) FROM staff WHERE employee_id = 20; -- execute query a second time to create a baseline SELECT COUNT(*) FROM staff WHERE employee_id = 20; ALTER SESSION SET optimizer_capture_sql_plan_baselines = FALSE; ALTER INDEX staff_employee_id INVISIBLE; - 解释计划，然后查询计划表（包括示例输出）：
EXPLAIN PLAN SET STATEMENT_ID='STAFF' FOR SELECT COUNT(*) FROM staff WHERE employee_id = 20; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(FORMAT=>'TYPICAL')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 1778552452 ---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 4 | 2 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 4 | | | |* 2 | TABLE ACCESS FULL| STAFF | 1 | 4 | 2 (0)| 00:00:01 | ---------------------------------------------------------------------------- Predicate Information (identified by operation id): PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- 2 - filter("EMPLOYEE_ID"=20) Note ----- - dynamic statistics used: dynamic sampling (level=2) - Failed to use SQL plan baseline for this statement如前面的输出所示，由于索引不可见，优化器选择了全表扫描。因为SQL计划基线使用索引，所以优化器无法重现计划。
- 在一个单独的会话中，以as身份登录
SYS并查询SQL计划基线的句柄和计划名称（包括示例输出）：SET LINESIZE 120 COL SQL_HANDLE FORMAT a25 COL PLAN_NAME FORMAT a35 SELECT DISTINCT SQL_HANDLE,PLAN_NAME,ACCEPTED FROM DBA_SQL_PLAN_BASELINES WHERE PARSING_SCHEMA_NAME = 'HR'; SQL_HANDLE PLAN_NAME ACC ------------------------- ----------------------------------- --- SQL_3fa3b23c5ba1bf60 SQL_PLAN_3z8xk7jdu3gv0b7aa092a YES - 比较计划，指定从上一步获得的SQL句柄和计划基线名称：
VAR v_report CLOB BEGIN :v_report := DBMS_XPLAN.COMPARE_PLANS( reference_plan => plan_table_object('HR', 'PLAN_TABLE', 'STAFF'), compare_plan_list => plan_object_list (SPM_OBJECT('SQL_3fa3b23c5ba1bf60','SQL_PLAN_3z8xk7jdu3gv0b7aa092a')), type => 'TEXT', level => 'ALL', section => 'ALL'); END; / - 查询比较计划报告（包括示例输出）：
SET LONG 1000000 SET PAGESIZE 50000 SET LINESIZE 200 SELECT :v_report rep FROM DUAL; REP -------------------------------------------------------------------------------- COMPARE PLANS REPORT -------------------------------------------------------------------------------- Current user : SYS Total number of plans : 2 Number of findings : 1 -------------------------------------------------------------------------------- COMPARISON DETAILS -------------------------------------------------------------------------------- Plan Number : 1 (Reference Plan) Plan Found : Yes Plan Source : Plan Table Plan Table Owner : HR Plan Table Name : PLAN_TABLE Statement ID : STAFF Plan ID : 72 Plan Database Version : 19.0.0.0 Parsing Schema : "HR" SQL Text : No SQL Text Plan ----------------------------- Plan Hash Value : 1766070819 -------------------------------------------------------------------- | Id | Operation | Name |Rows| Bytes | Cost | Time | -------------------------------------------------------------------- | 0| SELECT STATEMENT | | 1 | 13 | 2 | 00:00:01 | | 1| SORT AGGREGATE | | 1 | 13 | | | | * 2| TABLE ACCESS FULL | STAFF | 1 | 13 | 2 | 00:00:01 | -------------------------------------------------------------------- Predicate Information (identified by operation id): ------------------------------------------ * 2 - filter("EMPLOYEE_ID"=20) Notes ----- - Dynamic sampling used for this statement ( level = 2 ) - baseline_repro_fail = yes -------------------------------------------------------------------- Plan Number : 2 Plan Found : Yes Plan Source : SQL Plan Baseline SQL Handle : SQL_3fa3b23c5ba1bf60 Plan Name : SQL_PLAN_3z8xk7jdu3gv0b7aa092a Plan Database Version : 19.0.0.0 Parsing Schema : "HR" SQL Text : SELECT COUNT(*) FROM staff WHERE employee_id = 20 Plan ----------------------------- Plan Hash Value : 3081373994 -------------------------------------------------------------------------------- | Id | Operation | Name |Rows| Bytes | Cost | Time | -------------------------------------------------------------------------------- | 0| SELECT STATEMENT | | 1 | 13 | 0 | 00:00:01 | | 1| SORT AGGREGATE | | 1 | 13 | | | | * 2| INDEX UNIQUE SCAN | STAFF_EMPLOYEE_ID | 1 | 13 | 0 | 00:00:01 | -------------------------------------------------------------------------------- Predicate Information (identified by operation id): ------------------------------------------ * 2 - access("EMPLOYEE_ID"=20) Comparison Results (1): ----------------------------- 1. Query block SEL$1, Alias "STAFF"@"SEL$1": Some columns (OPERATION, OPTIONS, OBJECT_NAME) do not match between the reference plan (id: 2) and the current plan (id: 2) ----------------------------------------------------------------------------
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
