X

An Oracle blog about Oracle Cloud

Follow up on "Same Select..."

Abel Macias
Senior Principal Support Engineer

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.

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