bind peeking not happen in 9.2.0.8 and cause using default selectivity

From: LS Cheng <exriscer_at_gmail.com>
Date: Sun, 2 Sep 2007 10:31:32 +0200
Message-ID: <6e9345580709020131t3e39bcb9qf704856c08766ea3@mail.gmail.com>

Hi all

I have a query which involves three table joins a range predicate with dates with bind variables. The predicate asks for a 30 minutes range data.

num_rows t1 463220
num_rows t2 15
num_rows t3 460880

var a varchar2(20)
var b number
var c varchar2(20)
var d number

exec :a := '20070801 235000'
exec :b := 31
exec :c := '20070801 235000'
exec :d := -1

alter session set nls_date_format = 'YYYYMMDD HH24MISS';

SELECT *
FROM t1, t2, t3

 WHERE t1.c1 = t2.c1(+)
   AND t1.c2 = t2.c2(+)
   AND t1.c3 = t2.c3(+)
   AND t1.date_pre >= (TO_DATE (:a) - (:b) / 1440)
   AND t1.date_pre <= (TO_DATE (:c) - (:d) / 1440)
   AND t1.c1 = t3.c1(+)
   AND t1.c2 = t3.c2(+)
   AND t1.c3 = t3.c3(+)

9.2.0.8 plan

| 0 | SELECT STATEMENT | | 1158 |
313K| 849 |

|*  1 |  FILTER                        |                     |       |


|       |

|*  2 |   HASH JOIN OUTER              |                     |  1158 |
313K|   849 |
|*  3 |    HASH JOIN OUTER             |                     |  1158 |
170K|    17 |


|   4 |     TABLE ACCESS BY INDEX ROWID| T1                  |  1158 |

135K|    13 |
|*  5 |      INDEX RANGE SCAN          | IDX3T1              |  2084 |


|     8 |

|   6 |     TABLE ACCESS FULL          | T2                  |    15 |   465

|     2 |

|*  7 |    TABLE ACCESS FULL           | T3                  | 12942 |

1592K| 825 |

10.2.0.3 plan

--------------------------------------------------------------+-----------------------------------+


| Id  | Operation                       | Name                | Rows  |

Bytes | Cost  | Time      |
--------------------------------------------------------------+-----------------------------------+


| 0   | SELECT STATEMENT                |                     |

|       |    87 |           |

| 1   |  FILTER                         |                     |

|       |       |           |

| 2   |   NESTED LOOPS OUTER            |                     |    26 |

7202 |    87 |  00:00:02 |


| 3   |    NESTED LOOPS OUTER           |                     |    26 |

3926 |    26 |  00:00:01 |


| 4   |     TABLE ACCESS BY INDEX ROWID | T1                  |    26 |

3120 |    25 |  00:00:01 |


| 5   |      INDEX RANGE SCAN           | IDX3T1              |    26

|       |     3 |  00:00:01 |

| 6   |     TABLE ACCESS BY INDEX ROWID | T2                  |     1 |

31 |     1 |  00:00:01 |


| 7   |      INDEX UNIQUE SCAN          | PKT2                |     1

|       |     0 |           |

| 8   |    TABLE ACCESS BY INDEX ROWID  | T3                  |     1 |

126 |     3 |  00:00:01 |


| 9   |     INDEX RANGE SCAN            | IDX4T3              |     1

|       |     2 |  00:00:01 |

--------------------------------------------------------------+-----------------------------------+

As we can see the index cardinality (IDX3T1) in 9.2.0.8 is incorrect, the selectivity used is the default because of bind variables, 0.05 * 0.05. So it calculates a cardinality of 463220 * 0.05 * 0.05 which yields 1158 as plan shows.

So my question is, why is the default selectivity is used? Shouldnt bind peeking suppose to happen and get a proper selectivity? I tried restarted the database, flushed the shared_pool, invalidated the cursor to ensure a hard parse but no luck however in 10.2.0.3 it gets a proper 0.000055488selectivity and bind peeking happens seen from 10053 trace file.

Thanks

--
LSC

--
http://www.freelists.org/webpage/oracle-l

Received on Sun Sep 02 2007 - 03:31:32 CDT