Re: "snapshot too old" and undo_retention

Roger,

This is the approach I use for our warehouse, which contains snapshots of the bsis table:

1. dbms_stats to gather a complete set of statistics on the table (currently over 57 million rows in the bsis snap, 117 million in bsis itself). I tested using different sample sizes, but in our case, query performance was better with a larger sample size so I bit the bullet and collected full on everything over about a month (in subsets of course).
2. run nightly reports using the monitoring packages that show the number of rows changed since the last analyze. This will let you know which tables actually need to be analyzed and you have some advanced warning as to what will be gathered the next time the job runs.
3. gather stats (with dbms_stats) using the 'gather stale' option on a weekly basis. This will only gather stats on objects that have changed by more than 10%. Try to find a time when other processes are not competing for your undo space.

For our SAP database, we are still using the SAP tools for *most* objects. (not my first choice, but we tend to handle SAP per vendor recommendations until the reasons not to are so obvious change is unavoidable.) As I recall, SAP recommended *not* to use Oracle's statistics with our db/application version, but I could be off here. If you need more info on this, let me know and I'll dig through my notes.

hth ... Robyn

---
Robyn Anderson Sands
email: Robyn.Sands_at_SciAtl.com


On 9/29/05, Roger Xu <roger_xu_at_dp7uptx.com> wrote:


>

>

>

> "ANALYZE TABLE "SAPR3"."BSIS" COMPUTE STATISTICS FOR TABLE  FOR ALL INDEXED

> COLUMNS SIZE 1 FOR ALL INDEXES"

>

> How do I break this into pieces?

>

> Thanks.

>

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