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

In 11g you can set your own cbo preferences on the objects, which will then be used by the gathering job instead of the defaults.

In 10g this is not yet possible, but there you can use a pl/sql package I have written: analyzethis. It allows you to store cbo parameters into a table and set these as defaults on various levels. More information and the packages themselves can be found here: http://freekdhooge.wordpress.com/analyzethis/

Regards,

Freek D'Hooge
Uptime
Oracle Database Administrator
email: freek.dhooge_at_uptime.be
tel +32(0)3 451 23 82
http://www.uptime.be
disclaimer: www.uptime.be/disclaimer

---
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of TJ Kiernan
Sent: woensdag 9 februari 2011 20:50
To: oracle-l_at_freelists.org
Cc: TJ Kiernan
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