Re: Auto stats gathering is not sufficient - what now?

You could consider not gathering statistics at all -- delete current statistics 
and lock the table statistics -- and rely on dynamic sampling. The usual 
duration of reporting queries against large tables, particularly the 
consequences for the duration if the execution plan is incorrect, generally make 
the dynamic sampling overhead acceptable.




________________________________
From: TJ Kiernan <tkiernan_at_pti-nps.com>
To: oracle-l_at_freelists.org
Cc: TJ Kiernan <tkiernan_at_pti-nps.com>
Sent: Wed, 9 February, 2011 19:50:07
Subject: Auto stats gathering is not sufficient - what now?


10.2.0.3 on 32-bit Windows 2003 R2
I have a reporting table that is too large to grow by 10% very often - 66 
million records growing by 250k per week = 24 weeks before stats go stale and 
are gathered, meanwhile queries against relatively recent data (last month, last 
quarter) get horrible execution plans unless we hint them.  For instance, from 
the example below, we have an index on (GROUP_KEY, DATE_PROCESSED) that would 
return this query in <1 second.  

 
If my predicate values were in range of the statistics, then I expect to get 
better plans, so the first thing I’m considering is a periodic job (probably 
weekly) to gather stats on this table.  

 
My question: What sorts of considerations should I make when setting up a 
non-standard stats gathering job?  Particularly METHOD_OPT, but with other 
parameters as well, what prompts you to step away from defaults?
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------

SQL_ID  0udsqttt83syw, child number 0
-------------------------------------
  SELECT /*+ gather_plan_statistics */        
         field1,
         field2,
         DATE_PROCESSED   
    FROM REPORTING_TABLE  
   WHERE GROUP_KEY = 1234 
     AND DATE_PROCESSED > to_date('25-DEC-2010', 'DD-MON-YYYY')
ORDER BY GROUP_KEY, DATE_PROCESSED
 
Plan hash value: 3444608443
 
--------------------------------------------------------------------------------------------------------------------

| Id  | Operation                   | Name              | Starts | E-Rows | 
A-Rows |   A-Time   | Buffers | Reads  |
--------------------------------------------------------------------------------------------------------------------

|*  1 |  TABLE ACCESS BY INDEX ROWID| REPORTING_TABLE   |      1 |      1 |     
28 |00:00:05.84 |     617K|    148K|
|*  2 |   INDEX RANGE SCAN          | RT_DATE_IDX       |      1 |      2 |   
1599K|00:00:28.81 |    6065 |   5828 |
--------------------------------------------------------------------------------------------------------------------

 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("GROUP_KEY"=1234)
   2 - access("DATE_PROCESSED">TO_DATE('2010-12-25 00:00:00', 'yyyy-mm-dd 
hh24:mi:ss'))
       filter("DATE_PROCESSED">TO_DATE('2010-12-25 00:00:00', 'yyyy-mm-dd 
hh24:mi:ss'))
 
****10053 trace*****
  Access Path: index (RangeScan)
    Index: RT_DATE_IDX
    resc_io: 4.00  resc_cpu: 31027
    ix_sel: 2.4406e-008  ix_sel_with_filters: 2.4406e-008
    Cost: 4.01  Resp: 4.01  Degree: 1
  Using prorated density: 2.4406e-008 of col #2 as selectivity of out-of-range 
value pred
  Using prorated density: 2.4406e-008 of col #2 as selectivity of out-of-range 
value pred
  Access Path: index (RangeScan)
    Index: RT_GROUP_DP_IDX
    resc_io: 5.00  resc_cpu: 36837
    ix_sel: 3.9615e-010  ix_sel_with_filters: 3.9615e-010
    Cost: 5.01  Resp: 5.01  Degree: 1
  Using prorated density: 2.4406e-008 of col #2 as selectivity of out-of-range 
value pred
 
T. J. Kiernan
Database Administrator
Pharmaceutical Technologies, Inc.
(402) 965-8800 ext 1039
tkiernan_at_pti-nps.com

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