bind peeking not happen in 9.2.0.8 and cause using default selectivity
- From: "LS Cheng" <exriscer@xxxxxxxxx>
- To: oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 2 Sep 2007 10:31:32 +0200
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
Other related posts:
