Re: "snapshot too old" and undo_retention
- From: Robyn <robyn.sands@xxxxxxxxx>
- To: roger_xu@xxxxxxxxxxx
- Date: Fri, 30 Sep 2005 09:54:00 -0400
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@xxxxxxxxxx
On 9/29/05, Roger Xu <roger_xu@xxxxxxxxxxx> 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.
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
