Re: "snapshot too old" and undo_retention
- From: Robyn <robyn.sands@xxxxxxxxx>
- To: roger_xu@xxxxxxxxxxx
- Date: Fri, 30 Sep 2005 10:17:00 -0400
Roger,
found the note i referred to below (SAP note 797629). It mentions
issues with 'performance problems as of Oracle 9.2.0.6 because of the
corrected histogram bug' - basically SAP says they coded around this
bug, and now that Oracle has fixed it, their code will degrade in
performance if you use histograms. May also be true for 9.2.0.5 due
to bind variable peeking. Your stats statement will create a
histogram, so if you're on 9.2.0.5 or .6, i'd run a lot of tests
before gathering stats with that statement in production.
Robyn
On 9/30/05, Robyn <robyn.sands@xxxxxxxxx> wrote:
> 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.
> >
>
--
Robyn Anderson Sands
email: Robyn.Sands@xxxxxxxxxx
--
//www.freelists.org/webpage/oracle-l
Other related posts:
