RE: "snapshot too old" and undo_retention

First of thanks to everyone who replied my email.
We use SAP tools (brconnect) to collect statistics
and it comes out "ANALYZE TABLE ..COMPUTE STATISTICS FOR
TABLE  FOR ALL INDEXED COLUMNS SIZE 1 FOR ALL INDEXES".
We do not have a data warehouse and the table BSIS has
273 million rows. We are on 9.2.0.4 and this table is not
partitioned and the table itself is 80 gig in size.
Anybody partition this table? How? Thanks.

-----Original Message-----
From: Robyn [mailto:robyn.sands@xxxxxxxxx]
Sent: Friday, September 30, 2005 9:17 AM
To: Roger Xu
Cc: Oracle-L@Freelists. Org (E-mail)
Subject: Re: "snapshot too old" and undo_retention


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

For technical support please email tech_support@xxxxxxxxxxx or you can
call (972)721-8257. 
This email has been scanned for all viruses by the MessageLabs Email Security 
System.

This e-mail is intended solely for the person or entity to which it is 
addressed and may contain confidential and/or privileged information. Any 
review, dissemination, copying, printing or other use of this e-mail by persons 
or entities other than the addressee is prohibited. If you have received this 
e-mail in error, please contact the sender immediately and delete the material. 
____________________________________________________________________
This email has been scanned for all viruses by the MessageLabs Email Security 
System. Any questions please call 972-721-8257 or email your request to 
tech_support@xxxxxxxxxxxx
--
http://www.freelists.org/webpage/oracle-l

Other related posts: