Auto stats gathering is not sufficient - what now?
- From: "TJ Kiernan" <tkiernan@xxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 9 Feb 2011 13:50:07 -0600
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@xxxxxxxxxxx
Other related posts:
