X

An Oracle blog about Oracle Cloud

Same SELECT used in an INSERT has different execution plan

Abel Macias
Senior Principal Support Engineer
A customer complained that a query and its INSERT counterpart had different execution plans, and of course, the INSERT was slower.

First lets look at the SELECT :

SELECT ua_tr_rundatetime, 
       ua_ch_treatmentcode, 
       ua_tr_treatmentcode, 
       ua_ch_cellid, 
       ua_tr_cellid 
FROM   (SELECT DISTINCT CH.treatmentcode AS UA_CH_TREATMENTCODE, 
                        CH.cellid        AS UA_CH_CELLID 
        FROM    CH, 
                DL 
        WHERE  CH.contactdatetime > SYSDATE - 5 
               AND CH.treatmentcode = DL.treatmentcode) CH_CELLS, 
       (SELECT DISTINCT T.treatmentcode AS UA_TR_TREATMENTCODE, 
                        T.cellid        AS UA_TR_CELLID, 
                        T.rundatetime   AS UA_TR_RUNDATETIME 
        FROM    T, 
                DL 
        WHERE  T.treatmentcode = DL.treatmentcode) TRT_CELLS 
WHERE  CH_CELLS.ua_ch_treatmentcode(+) = TRT_CELLS.ua_tr_treatmentcode; 



The query has 2 DISTINCT subqueries. 
The execution plan shows one with DISTICT Placement transformation applied and not the other.
The view in Step 5 has the prefix VW_DTP which means DISTINCT Placement.

--------------------------------------------------------------------
| Id  | Operation                    | Name            | Cost (%CPU)
--------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                 |   272K(100)
|*  1 |  HASH JOIN OUTER             |                 |   272K  (1)
|   2 |   VIEW                       |                 |  4408   (1)
|   3 |    HASH UNIQUE               |                 |  4408   (1)
|*  4 |     HASH JOIN                |                 |  4407   (1)
|   5 |      VIEW                    | VW_DTP_48BAF62C |  1660   (2)
|   6 |       HASH UNIQUE            |                 |  1660   (2)
|   7 |        TABLE ACCESS FULL     | DL              |  1644   (1)
|   8 |      TABLE ACCESS FULL       | T               |  2744   (1)
|   9 |   VIEW                       |                 |   267K  (1)
|  10 |    HASH UNIQUE               |                 |   267K  (1)
|* 11 |     HASH JOIN                |                 |   267K  (1)
|  12 |      PARTITION RANGE ITERATOR|                 |   266K  (1)
|* 13 |       TABLE ACCESS FULL      | CH              |   266K  (1)
|  14 |      TABLE ACCESS FULL       | DL              |  1644   (1)
--------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   2 - SEL$AF418D5F / TRT_CELLS@SEL$1
   3 - SEL$AF418D5F
   5 - SEL$F6AECEDE / VW_DTP_48BAF62C@SEL$48BAF62C
   6 - SEL$F6AECEDE
   7 - SEL$F6AECEDE / DL@SEL$3
   8 - SEL$AF418D5F / T@SEL$3
   9 - SEL$2        / CH_CELLS@SEL$1
  10 - SEL$2
  13 - SEL$2        / CH@SEL$2
  14 - SEL$2        / DL@SEL$2

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

   1 - access("CH_CELLS"."UA_CH_TREATMENTCODE"="TRT_CELLS"."UA_TR_TREATMENTCODE")
   4 - access("T"."TREATMENTCODE"="ITEM_1")
  11 - access("CH"."TREATMENTCODE"="DL"."TREATMENTCODE")
  13 - filter("CH"."CONTACTDATETIME">SYSDATE@!-5)


The outline shows PLACE_DISTINCT(@"SEL$3" "DL"@"SEL$3")
indicating that the QB3 is the one that got the transformation.

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$2")
      OUTLINE_LEAF(@"SEL$F6AECEDE")
      OUTLINE_LEAF(@"SEL$AF418D5F")
PLACE_DISTINCT(@"SEL$3" "DL"@"SEL$3")
      OUTLINE_LEAF(@"SEL$1")
      OUTLINE(@"SEL$48BAF62C")
      OUTLINE(@"SEL$3")
      NO_ACCESS(@"SEL$1" "TRT_CELLS"@"SEL$1")
      NO_ACCESS(@"SEL$1" "CH_CELLS"@"SEL$1")
      LEADING(@"SEL$1" "TRT_CELLS"@"SEL$1" "CH_CELLS"@"SEL$1")
      USE_HASH(@"SEL$1" "CH_CELLS"@"SEL$1")
      FULL(@"SEL$2" "CH"@"SEL$2")
      FULL(@"SEL$2" "DL"@"SEL$2")
      LEADING(@"SEL$2" "CH"@"SEL$2" "DL"@"SEL$2")
      USE_HASH(@"SEL$2" "DL"@"SEL$2")
      USE_HASH_AGGREGATION(@"SEL$2")
      NO_ACCESS(@"SEL$AF418D5F" "VW_DTP_48BAF62C"@"SEL$48BAF62C")
      FULL(@"SEL$AF418D5F" "T"@"SEL$3")
      LEADING(@"SEL$AF418D5F" "VW_DTP_48BAF62C"@"SEL$48BAF62C" "T"@"SEL$3")
      USE_HASH(@"SEL$AF418D5F" "T"@"SEL$3")
      USE_HASH_AGGREGATION(@"SEL$AF418D5F")
      FULL(@"SEL$F6AECEDE" "DL"@"SEL$3")
      USE_HASH_AGGREGATION(@"SEL$F6AECEDE")
      END_OUTLINE_DATA
  */


The 10053 shows there is a comparative of cost with and without the transformation.
This means the transformation belongs to Cost-Based Query Transformations (CBQT).

In SEL$3 the optimization of the query block without the transformation is 6659.73
and with the transformation is 4408.41 so the transformation is kept.

GBP/DP: Checking validity of GBP/DP for query block SEL$3 (#3)
DP: Checking validity of distinct placement for query block SEL$3 (#3)
DP: Using search type: linear
DP: Considering distinct placement on query block SEL$3 (#3)
DP: Starting iteration 1, state space = (5) : (0)
DP: Original query
DP: Costing query block.
DP: Updated best state, Cost = 6659.73
DP: Starting iteration 2, state space = (5) : (1)
DP: Using DP transformation in this iteration.
DP: Transformed query
DP: Costing query block.
DP: Updated best state, Cost = 4408.41
DP: Doing DP on the original QB.
DP: Doing DP on the preserved QB.


In SEL$2 the cost without the transformation is less than with it so it is not kept.

GBP/DP: Checking validity of GBP/DP for query block SEL$2 (#2)
DP: Checking validity of distinct placement for query block SEL$2 (#2)
DP: Using search type: linear
DP: Considering distinct placement on query block SEL$2 (#2)
DP: Starting iteration 1, state space = (3) : (0)
DP: Original query
DP: Costing query block.
DP: Updated best state, Cost = 267936.93
DP: Starting iteration 2, state space = (3) : (1)
DP: Using DP transformation in this iteration.
DP: Transformed query
DP: Costing query block.
DP: Not update best state, Cost = 267951.66


To the same query an INSERT INTO is added and the result is a very different execution plan.

INSERT  INTO cc
               (ua_tr_rundatetime,
                ua_ch_treatmentcode,
                ua_tr_treatmentcode,
                ua_ch_cellid,
                ua_tr_cellid)
SELECT ua_tr_rundatetime,
       ua_ch_treatmentcode,
       ua_tr_treatmentcode,
       ua_ch_cellid,
       ua_tr_cellid
FROM   (SELECT DISTINCT CH.treatmentcode AS UA_CH_TREATMENTCODE,
                        CH.cellid        AS UA_CH_CELLID
        FROM    CH,
                DL
        WHERE  CH.contactdatetime > SYSDATE - 5
               AND CH.treatmentcode = DL.treatmentcode) CH_CELLS,
       (SELECT DISTINCT T.treatmentcode AS UA_TR_TREATMENTCODE,
                        T.cellid        AS UA_TR_CELLID,
                        T.rundatetime   AS UA_TR_RUNDATETIME
        FROM    T,
                DL
        WHERE  T.treatmentcode = DL.treatmentcode) TRT_CELLS
WHERE  CH_CELLS.ua_ch_treatmentcode(+) = TRT_CELLS.ua_tr_treatmentcode;

----------------------------------------------------------
| Id  | Operation                     | Name | Cost (%CPU)
----------------------------------------------------------
|   0 | INSERT STATEMENT              |      |   274K(100)
|   1 |  LOAD TABLE CONVENTIONAL      |      |            
|*  2 |   HASH JOIN OUTER             |      |   274K  (1)
|   3 |    VIEW                       |      |  6660   (1)
|   4 |     SORT UNIQUE               |      |  6660   (1)
|*  5 |      HASH JOIN                |      |  6659   (1)
|   6 |       TABLE ACCESS FULL       | DL   |  1644   (1)
|   7 |       TABLE ACCESS FULL       | T    |  2744   (1)
|   8 |    VIEW                       |      |   267K  (1)
|   9 |     SORT UNIQUE               |      |   267K  (1)
|* 10 |      HASH JOIN                |      |   267K  (1)
|  11 |       PARTITION RANGE ITERATOR|      |   266K  (1)
|* 12 |        TABLE ACCESS FULL      | CH   |   266K  (1)
|  13 |       TABLE ACCESS FULL       | DL   |  1644   (1)
----------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   3 - SEL$3 / TRT_CELLS@SEL$1
   4 - SEL$3
   6 - SEL$3 / DL@SEL$3
   7 - SEL$3 / T@SEL$3
   8 - SEL$2 / CH_CELLS@SEL$1
   9 - SEL$2
  12 - SEL$2 / CH@SEL$2
  13 - SEL$2 / DL@SEL$2

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("CH_CELLS"."UA_CH_TREATMENTCODE"="TRT_CELLS"."UA_TR_TREATMENTCODE")
   5 - access("T"."TREATMENTCODE"="DL"."TREATMENTCODE")
  10 - access("CH"."TREATMENTCODE"="DL"."TREATMENTCODE")
  12 - filter("CH"."CONTACTDATETIME">SYSDATE@!-5)

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$2")
      OUTLINE_LEAF(@"SEL$3")
      OUTLINE_LEAF(@"SEL$1")
      OUTLINE_LEAF(@"INS$1")
      FULL(@"INS$1" "CC"@"INS$1")
      NO_ACCESS(@"SEL$1" "TRT_CELLS"@"SEL$1")
      NO_ACCESS(@"SEL$1" "CH_CELLS"@"SEL$1")
      LEADING(@"SEL$1" "TRT_CELLS"@"SEL$1" "CH_CELLS"@"SEL$1")
      USE_HASH(@"SEL$1" "CH_CELLS"@"SEL$1")
      FULL(@"SEL$2" "CH"@"SEL$2")
      FULL(@"SEL$2" "DL"@"SEL$2")
      LEADING(@"SEL$2" "CH"@"SEL$2" "DL"@"SEL$2")
      USE_HASH(@"SEL$2" "DL"@"SEL$2")
      USE_HASH_AGGREGATION(@"SEL$2")
      FULL(@"SEL$3" "DL"@"SEL$3")
      FULL(@"SEL$3" "T"@"SEL$3")
      LEADING(@"SEL$3" "DL"@"SEL$3" "T"@"SEL$3")
      USE_HASH(@"SEL$3" "T"@"SEL$3")
      USE_HASH_AGGREGATION(@"SEL$3")
      END_OUTLINE_DATA
  */

There is no DISTINCT Placement view and no hint.
The 10053 trace shows a new legend "DP: Bypassed: Not SELECT"
implying that this is a transformation that it is possible only for SELECTs.


GBP/DP: Checking validity of GBP/DP for query block SEL$3 (#4)
DP: Checking validity of distinct placement for query block SEL$3 (#4)
DP: Bypassed: Not SELECT.
GBP/DP: Checking validity of GBP/DP for query block SEL$2 (#3)
DP: Checking validity of distinct placement for query block SEL$2 (#3)
DP: Bypassed: Not SELECT.

In 12.1 (and hopefully in 11.2.0.4 when released) the restriction on applying CBQT to some DMLs and DDLs (like CTAS) is lifted.
This is documented in BugTag Note:10013899.8 Allow CBQT for some DML / DDL
And interestingly enough, it is possible to have a one-off patch in 11.2.0.3.

SQL> select DESCRIPTION,OPTIMIZER_FEATURE_ENABLE,IS_DEFAULT  
  2  from v$system_fix_control where BUGNO='10013899';
DESCRIPTION
----------------------------------------------------------------
OPTIMIZER_FEATURE_ENABLE  IS_DEFAULT
------------------------- ----------
enable some transformations for DDL and DML statements
11.2.0.4                           1


Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha