RE: "snapshot too old" and undo_retention

It's not in DBSTATC.  

It's in the init<SID>.sap file (the stats_sample_size parameter).  

For example, stats_sample_size = P10, means sample using 10%  

There are a whole bunch more params in this file...  

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Roger Xu
Sent: Wednesday, October 05, 2005 1:52 PM To: Hand, Michael T
Cc: Oracle-L_at_Freelists. Org (E-mail)
Subject: RE: "snapshot too old" and undo_retention

We are on SAP 4.6C. Which field in table DBSTATC is for sample size?

SQL> select * from sapr3.DBSTATC where DBOBJ = 'BSIS';

DBOBJ                DO OBJOW                DBTYP      VW A OBJEC

-------------------- -- -------------------- ---------- -- - ----------
--------
     SIGNI AMET OPTIO T H TDDAT    DURAT  P
---------- ---- ----- - - -------- ------ -
BSIS                 01 SAPR3                ORACLE     A  A D_FA
19990219
         2 C            X          000000

SQL> desc sapr3.DBSTATC
 Name                                      Null?    Type
 ----------------------------------------- --------
----------------------------
 DBOBJ                                     NOT NULL VARCHAR2(20)
 DOTYP                                     NOT NULL VARCHAR2(2)
 OBJOW                                     NOT NULL VARCHAR2(20)
 DBTYP                                     NOT NULL VARCHAR2(10)
 VWTYP                                     NOT NULL VARCHAR2(2)
 ACTIV                                     NOT NULL VARCHAR2(1)
 OBJEC                                     NOT NULL VARCHAR2(10)
 AEDAT                                     NOT NULL VARCHAR2(8)
 SIGNI                                     NOT NULL NUMBER(3)
 AMETH                                     NOT NULL VARCHAR2(4)
 OPTIO                                     NOT NULL VARCHAR2(5)
 TOBDO                                     NOT NULL VARCHAR2(1)
 HISTO                                     NOT NULL VARCHAR2(1)
 TDDAT                                     NOT NULL VARCHAR2(8)
 DURAT                                     NOT NULL VARCHAR2(6)
 PLAND                                     NOT NULL VARCHAR2(1)

Thanks.

Roger

-----Original Message-----
From: Hand, Michael T [mailto:HANDM_at_polaroid.com] Sent: Tuesday, October 04, 2005 10:54 AM To: Roger Xu
Cc: Oracle-L_at_Freelists. Org (E-mail)
Subject: RE: "snapshot too old" and undo_retention

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Roger Xu
Sent: Friday, September 30, 2005 5:35 PM To: Robyn
Cc: Oracle-L_at_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.



For technical support please email tech_support_at_dp7uptx.com 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_at_dp7uptx.com.



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