Monday Jul 22, 2013

Follow up on "Same Select..."

A follow up on the case of "Same SELECT used in an INSERT has different Execution Plan"

The query has the following predicate :

CH.CONTACTDATETIME > SYSDATE-5

The testcase I have has table,column,index statistics from 27-jun-2013.

If I run *today* the insert with the SYSDATE clause on my testcase with stats from 27-jun-2013 then I get a very poor cardinality estimation on access to CH because the stats reflect data there is little or no data present from after 27-jun-2013. This makes the CBO choose a poor execution plan, which is the one with only one DTP.
If in contrast I change the query to have :

CH.CONTACTDATETIME>to_date('22-JUN-13')

Then the cardinality estimation is closer to reality and the CBO chooses the better execution plan than has DISTINCT PLACEMENT transformation in step 7 and 15, where before it was only happening in step 7.

-----------------------------------------------------------------
Id|Operation                        |Name            |Rows |Cost
-----------------------------------------------------------------
 0|INSERT STATEMENT                |                 |     | 313K
 1|LOAD TABLE CONVENTIONAL         |                 |     |    
 2| HASH JOIN OUTER                |                 |   2 | 313K
 3|  VIEW                          |                 |   1 |2771
 4|   SORT UNIQUE                  |                 |   1 |2771
 5|    NESTED LOOPS                |                 |     |    
 6|     NESTED LOOPS               |                 |   1 |2770
 7|      VIEW                      |VW_DTP_BD4C95A1  | 524 |2238
 8|       SORT UNIQUE              |                 | 524 |2238
 9|        TABLE ACCESS FULL       |DL               | 636K|1776
10|      INDEX RANGE SCAN          |I_T              |   1 |   2
11|     TABLE ACCESS BY INDEX ROWID|T                |   1 |   2
12|  VIEW                          |                 |1940 | 310K
13|   SORT UNIQUE                  |                 |1940 | 310K
14|    HASH JOIN                   |                 |1940 | 310K
15|     VIEW                       |VW_DTP_22D53B8E  | 524 |2238
16|      SORT UNIQUE               |                 | 524 |2238
17|       TABLE ACCESS FULL        |DL               | 636K|1776
18|     PARTITION RANGE ITERATOR   |                 |1728K| 307K
19|      TABLE ACCESS FULL         |CH               |1728K| 307K
----------------------------------------------------------------- 


Since the query relies heavily on that SYSDATE predicate then there are a couple of possible options :

1) Use SQL Plan Management to fix the execution plan to have the DTP in step 7 and 15.
2) Before executing the insert, make sure the table CH has fresh statistics that reflect the latest data inserted in the table.
    For example, If this is done daily then you will have to do it daily.

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

Categories
Archives
« July 2013
SunMonTueWedThuFriSat
 
1
3
4
5
6
7
8
9
10
12
13
14
15
16
17
19
20
21
23
24
25
26
27
28
29
30
31
   
       
Today