Statistics & Empty Partitions

[marist89]

I compute statistics every weekend.

On a table partioned by month, I have approximately 30 million rows in each partition, or about 1.3 million rows per business day. When statistics get computed on Sunday, the "December" partition is empty. However, when the data starts rolling in Monday morning, the CBO chooses a full table scan for all my queries. Once there is some data, I re-calculate statistics for just the "December" partition and the queries correctly choose the index again.

How do you handle this situation? I thought of a couple possibilities:
1. Use plan stability. Are the drawbacks to Plan Stability worth the extra effort?

2. Delete statistics for the partitioned table to force RBO. While I'm not using any of the features that need CBO to work (for this table), I'd prefer to keep my options open in the future.

[slimdave]

The RBO option is not viable -- the partitioning forces the use of CBO.

I would also stay away from plan stability for DW applications, as it would do nothing for ad-hoc queries, and you really want the plan to be sensitive to histograms -- i don't think that plan stability would work in this case.

How about turning on monitoring for the table? This works at the partition level, and would allow you to run DBMS_STATS.GATHER_SCHEMA_STATS() with the gather_stale option to get good stats as soon as the data is flowing in.

You might also try running DBMS_STATS.SET_TABLE_STATS() and DBMS_STATS.SET_INDEX_STATS() after your weekend analyze. You can setup expected statistics at the partition level for both table and index that way. I'm not sure how this interfaces with table monitoring, but if you got these forecasted stats close enough on sunday night, then you could defer gathering stale stats until Monday night.

[marist89]

Originally posted by slimdave

How about turning on monitoring for the table? This works at the partition level, and would allow you to run DBMS_STATS.GATHER_SCHEMA_STATS() with the gather_stale option to get good stats as soon as the data is flowing in.

This is an 8.1.7 database. I think table monitoring is a 9i feature?

You might also try running DBMS_STATS.SET_TABLE_STATS() and DBMS_STATS.SET_INDEX_STATS() after your weekend analyze. You can setup expected statistics at the partition level for both table and index that way. I'm not sure how this interfaces with table monitoring, but if you got these forecasted stats close enough on sunday night, then you could defer gathering stale stats until Monday night.

Excellent Idea!

[slimdave]

Oh right -- well i guess in your case you'd know which partition is stale, so it don't matter so much.

[jodie]

I like Dave's idea about Setting the statistics.

But just for future reference, GATHER_SCHEMA_STATS works in 8i. I use it every week.

Have a great day!
Jodie

[OracleDoc]

You know speaking of stats.
Anyone have an idea why Oracle keeps the DBMS_UTILITY version of running stats around? From all I've read the DBMS_STATS package is much better.