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.

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