Tuesday Jul 02, 2013

Same SELECT used in an INSERT has different execution plan

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


About

I am Abel Macias email, an Oracle support engineer that specialized in Performance that belongs to Exadata Support.
Disclaimer This blog looks to broadcast my experiences while diagnosing performance problems on Oracle's RDBMS, mostly on Exadata.
The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today