RE: Tbs READ ONLY and Snapshot too old
- From: "Goulet, Dick" <DGoulet@xxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 11 Jun 2004 10:38:15 -0400
Philippe,
If you look at the error your getting and the table/tablespace setup you've
described as well as the processing environment, it is immediately obvious that
the database is NOT the problem, but the application. What you might do to
band-aid their application is to take those small tables and turn them into
global temporary tables. The real problem here is that the application is
creating, modifying, and deleting data that other sessions are have an interest
in. Couple that with those massive tables that you've mentioned and getting an
ORA-01555 under these circumstances is a guarantee. What is happening that I
believe your duhvelopers can understand is that one session is modifying a
block, not an insert in most cases, and has issued a commit. Now that block is
resident in the SGA as a dirty block, but either the current session or another
session is requesting data from those massive tables. So Oracle needs a place
to put new data blocks and that modified one is low on the LRU list, so off to
disk it goes & the rollback information heads for the trash. Another session
now comes along to either modify or read that block, with an earlier SCN, and
bingo, no rollback available, ORA-01555.
Therefore, possible band-aids:
1) Make the smaller tables Global Temps
2) Increase the db_block_buffers or their 9i equivalent.
3) If your on 9i or better increase Undo retention to some insane value
like 24 hours. BTW: Increase your UNDO tablespace by a factor of 100. (That
ought to get damanagements attention.)
4) Scan Metalink for ORA-01555. Note:269814.1 should be of help.
Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA
-----Original Message-----
From: NGUYEN Philippe (Cetelem) [mailto:philippe.nguyen@xxxxxxxxxx]
Sent: Friday, June 11, 2004 7:03 AM
To: 'oracle-l@xxxxxxxxxxxxx'
Subject: RE: Tbs READ ONLY and Snapshot too old
thank Justin,
Errors occurs during the month but we have a very bad application that
generate sql scripts with a lot of cursors opened on thoses big tables ( more
than 20Go per tables) thoses script create small tables (10 - 200 Mo) with
datas pick up from the different big tables.
The problem is that the generated script are often launch in the same time and
I we can't touch the code.
____________________________________________________
Philippe Nguyen
CETELEM - Administration, Architecture Décisionnelle
Direction Customer Relationship Management
E-Mail : philippe.nguyen@xxxxxxxxxx
Tel : (+33) 1 46 39 92 16 - Fax : (+33) 1 46 39 59 88
-----Message d'origine-----
De : oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]De la
part de Justin Cave
Envoyé : vendredi 11 juin 2004 12:15
À : oracle-l@xxxxxxxxxxxxx
Objet : RE: Tbs READ ONLY and Snapshot too old
ORA-01555 indicates that Oracle was attempting to reconstruct the state of a
block and the earlier state was not available in the undo segments. By
definition, you cannot get an ORA-01555 error when trying to access a read-only
object.
Do you get these ORA-01555 errors shortly after doing the load or do you get
them throughout the day? The only thing I can think is that these errors are
caused by delayed block cleanout, but that should take care of itself soon
after the load. If you get errors throughout the month, I would suspect that
the big tables aren't the cause.
Justin Cave
Distributed Database Consulting, Inc.
http://www.ddbcinc.com <http://www.ddbcinc.com/askDDBC> /askDDBC
_____
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of NGUYEN Philippe (Cetelem)
Sent: Friday, June 11, 2004 4:15 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Tbs READ ONLY and Snapshot too old
Hi List,
just a little question about read only tbs :
We have big tables that are loaded once and never updated during the month ,
so I think it's a good thing to switch their tbs to read only mode.
Web have a lot of script that request thoses heavy tables to build small tables
and we often encounter ORA-01555.
Do you think that "in general" this operation could enhance our ORA-01555
problems ?
TIA
Philippe
Other related posts:
