RE: "snapshot too old" and undo_retention

  • To: <roger_xu@xxxxxxxxxxx>
  • Date: Tue, 4 Oct 2005 11:54:11 -0400

Roger,
9.2.0.6    Tru64 5.1     SAP R/3 4.7
Our BSIS table is only about 1/4 the size of yours.  When we run
Brconnect the sample size is 3% (from the DBSTATC table) and no
histograms are collected.  Also, the table is stable/slow growing enough
so that stats haven't been refreshed in more than 6 months (and that was
due to a DMT to LMT conversion).

I realize each SAP install has its own unique conditions, but your
statistics gathering method on this table seems extreme.  Some
suggestions before considering partitions: 1) look into archiving as
Jared mentioned, 2) change Brconnect parameters to use dbms_stats
instead of analyze, 3) reduce the frequency that stats are collected
(the default is 50% change in # of rows), 5) validate need for
histograms & 100% sample size (change DBSTATC),  lastly, if 5) can not
be reduced, look into collecting stats on another box and import them.

Regards,
Mike

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Roger Xu
Sent: Friday, September 30, 2005 5:35 PM
To: Robyn
Cc: Oracle-L@Freelists. Org (E-mail)
Subject: 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.

-- 
This transmission is intended only for use by the addressee(s) named herein and 
may contain information that is proprietary, confidential and/or legally 
privileged. If you are not the intended recipient, you are hereby notified that 
any disclosure, copying, distribution, or use of the information contained 
herein (including any reliance thereon) is STRICTLY PROHIBITED. If you received 
this transmission in error, please immediately contact the sender and destroy 
the material in its entirety, whether in electronic or hard copy format. Thank 
you.


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

Other related posts: