Oracle 19C 比较执行计划：教程（比较两个共享SQL区的执行计划）

select count(*) 
from   products p, sales s 
where  p.prod_id = s.prod_id 
and    p.prod_min_price > 200;

select count(*) 
from   products p, sales s 
where  p.prod_id = s.prod_id 
and    s.quantity_sold = 43;
复制

1. 启动SQL * Plus，然后以管理特权登录数据库。
2. 查询V$SQL以确定两个查询的SQL ID。
   

   以下查询查询V$SQL包含字符串的查询products：

   SET LINESIZE 120
   COL SQL_ID FORMAT a20
   COL SQL_TEXT FORMAT a60
   
   SELECT SQL_ID, SQL_TEXT
   FROM   V$SQL
   WHERE  SQL_TEXT LIKE '%products%'
   AND    SQL_TEXT NOT LIKE '%SQL_TEXT%'
   ORDER BY SQL_ID;
   
   SQL_ID               SQL_TEXT
   -------------------- ------------------------------------------------------------
   0hxmvnfkasg6q        select count(*) from   products p, sales s where  p.prod_id
                        = s.prod_id and    s.quantity_sold = 43
   
   10dqxjph6bwum        select count(*) from   products p, sales s where  p.prod_id
                        = s.prod_id and    p.prod_min_price > 200
   复制

3. 以user身份登录数据库sh。
4. 执行DBMS_XPLAN.COMPARE_PLANS函数，指定在上一步中获得的SQL ID。 （进行比较两SQL的执行计划）
   

   例如，执行以下程序：

   VARIABLE v_rep CLOB
   
   BEGIN
     :v_rep := DBMS_XPLAN.COMPARE_PLANS( 
       reference_plan    => cursor_cache_object('0hxmvnfkasg6q', NULL),
       compare_plan_list => plan_object_list(cursor_cache_object('10dqxjph6bwum', NULL)),
       type              => 'TEXT',
       level             => 'TYPICAL', 
       section           => 'ALL');
   END;
   /
   复制

5. 打印报告。
   

   例如，运行以下查询：

   SET PAGESIZE 50000
   SET LONG 100000
   SET LINESIZE 210
   COLUMN report FORMAT a200
   
   SELECT :v_rep REPORT FROM DUAL;
   复制

   Comparison Results以下示例报告的这一节显示只有第一个查询使用了联接消除转换：

   REPORT
   ---------------------------------------------------------------------------------------------
   
   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            : Cursor Cache
    SQL ID                 : 0hxmvnfkasg6q
    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 s.quantity_sold = 43
   
   Plan
   -----------------------------
   
    Plan Hash Value  : 3519235612
   
   -------------------------------------------------------------------------
   | Id  | Operation              | Name  | Rows | Bytes | Cost | Time     |
   -------------------------------------------------------------------------
   |   0 | SELECT STATEMENT       |       |      |       |  469 |          |　　　　<--- 从查询块中删除转换JOIN（结果查询块：SEL $ A43D1678）
   |   1 |   SORT AGGREGATE       |       |    1 |     3 |      |          |       查当于把  select count(*) from products p, sales s where  p.prod_id = s.prod_id and s.quantity_sold = 43 
   |   2 |    PARTITION RANGE ALL |       |    1 |     3 |  469 | 00:00:01 |　　    改写成了 　 select count(*)   sales s where  s.quantity_sold = 43
   | * 3 |     TABLE ACCESS FULL  | SALES |    1 |     3 |  469 | 00:00:01 |
   -------------------------------------------------------------------------
   
   Predicate Information (identified by operation id):
   ------------------------------------------
   * 3 - filter("S"."QUANTITY_SOLD"=43)
   
   ---------------------------------------------------------------------------------------------
    Plan Number            : 2
    Plan Found             : Yes
    Plan Source            : Cursor Cache
    SQL ID                 : 10dqxjph6bwum
    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_min_price > 200
   
   Plan
   -----------------------------
   
    Plan Hash Value  : 3037679890
   
   --------------------------------------------------------------------------------------------------
   | Id  | Operation                         | Name           | Rows   | Bytes    | Cost | Time     |
   --------------------------------------------------------------------------------------------------
   |   0 | SELECT STATEMENT                  |                |        |          |   34 |          |
   |   1 |   SORT AGGREGATE                  |                |      1 |       13 |      |          |
   | * 2 |    HASH JOIN                      |                | 781685 | 10161905 |   34 | 00:00:01 |
   | * 3 |     TABLE ACCESS FULL             | PRODUCTS       |     61 |      549 |    2 | 00:00:01 |
   |   4 |     PARTITION RANGE ALL           |                | 918843 |  3675372 |   29 | 00:00:01 |
   |   5 |      BITMAP CONVERSION TO ROWIDS  |                | 918843 |  3675372 |   29 | 00:00:01 |
   |   6 |       BITMAP INDEX FAST FULL SCAN | SALES_PROD_BIX |        |          |      |          |
   --------------------------------------------------------------------------------------------------
   
   Predicate Information (identified by operation id):
   ------------------------------------------
   * 2 - access("P"."PROD_ID"="S"."PROD_ID")
   * 3 - filter("P"."PROD_MIN_PRICE">200)
   
   
   Notes
   -----
   - This is an adaptive plan
   
   
   Comparison Results (1):
   -----------------------------
    1. Query block SEL$1: Transformation JOIN REMOVED FROM QUERY BLOCK occurred    only in the reference plan (result query block: SEL$A43D1678).
   　  查询块1：  仅在参考计划中发生了从查询块中删除转换JOIN（结果查询块：SEL $ A43D1678）。
   复制