RE: "snapshot too old" and undo_retention
- From: "Khemmanivanh, Somckit" <somckit.khemmanivanh@xxxxxxxxxxxxxxxx>
- To: <roger_xu@xxxxxxxxxxx>, "Hand, Michael T" <HANDM@xxxxxxxxxxxx>
- Date: Wed, 5 Oct 2005 15:13:11 -0700
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Roger Xu
Sent: Wednesday, October 05, 2005 1:52 PM
To: Hand, Michael T
Cc: Oracle-L@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
AEDAT
-------------------- -- -------------------- ---------- -- - ----------
--------
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@xxxxxxxxxxxx]
Sent: Tuesday, October 04, 2005 10:54 AM
To: Roger Xu
Cc: Oracle-L@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@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.
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
Other related posts:
