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@(protected) [mailto:oracle-l-bounce@(protected)] On Behalf Of Roger Xu Sent: Friday, September 30, 2005 5:35 PM To: Robyn Cc: Oracle-L@(protected) 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.
