hi ,
I am using Release 10.2.0.1.0 of Oracle. I am having a scenario in which i am getting poor execution plans due to stale stats , and how should i tackle the scenario. below is the part of my main query which deviates the execution path due to wrong cardinality estimation.
My column c1 of table tab1 holds javatimestamp values i.e. its NUMBER datatype which points to a date and time component only. And we gather stats each weekend on this table tab1.
below is my query:
select /*+gather_plan_statistics*/* from tab1
where c1 BETWEEN 1346300090668 AND 1346325539486 ;
Plan hash value: 3167980259
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
--------------------------------------------------------------------------------------------------------------------------
| 1 | TABLE ACCESS BY INDEX ROWID| tab1 | 1 | 1 | 167K|00:01:13.72 | 158K| 12390 |
|* 2 | INDEX RANGE SCAN | IDX_N1 | 1 | 1 | 167K|00:00:13.27 | 13880 | 1736 |
--------------------------------------------------------------------------------------------------------------------------
Above shows a big gap in actual and estimated cardinality estimation, and its due to the fact that the HIGH_VALUE (1346203206173 points to 8/29/2012 1:20:06 AM) in DBA_TAB_COLUMN for column C1 is well below the STARTRANGE(1346300090668 points to 8/30/2012 4:14:51 AM) and ENDRANGE(1346325539486 points to 8/30/2012 11:18:59 AM) of the BETWEEN clause.
So even gathering stats daily on the table wont help me as because, in morning again it will require updated maxvalue for the column C1 for estimating proper, So how to handle this situation? Dont want to go with 'hint' , want to make the stats proper so that optimizer will automatically pick the right path.
Edited by: 930254 on Aug 30, 2012 4:41 AM