FreeLists / oracle-l / bind peeking not happen in 9.2.0.8 and cause using default selectivity

["LS Cheng" <exriscer@xxxxxxxxx>]

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
--------------------------------------------------------------------------------------
| Id  | Operation                      |  Name               | Rows  | Bytes
| Cost  |
--------------------------------------------------------------------------------------
|   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