Tbs READ ONLY and Snapshot too old 2004-06-11 - By NGUYEN Philippe (Cetelem)
Tahnks Dick, this application run on 8i database, is the Global Temp
available ? or any equivalence ?
__ ____ ____ ____ ____ ____ ____ ____ ____ ____ ____
Philippe Nguyen
CETELEM - Administration, Architecture D¨¦cisionnelle
Direction Customer Relationship Management
E-Mail : philippe.nguyen@(protected)
Tel : (+33) 1 46 39 92 16 - Fax : (+33) 1 46 39 59 88
-- --Message d 'origine-- --
De : oracle-l-bounce@(protected) [mailto:oracle-l-bounce@(protected)]De
la part de Goulet, Dick
Envoy¨¦ : vendredi 11 juin 2004 16:38
? : oracle-l@(protected)
Objet : RE: Tbs READ ONLY and Snapshot too old
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 (See ORA-01555.ora-code.com) 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 (See ORA-01555.ora-code.com).
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 (See ORA-01555.ora-code.com). 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@(protected)]
Sent: Friday, June 11, 2004 7:03 AM
To: 'oracle-l@(protected) '
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@(protected)
Tel : (+33) 1 46 39 92 16 - Fax : (+33) 1 46 39 59 88
-- --Message d 'origine-- --
De : oracle-l-bounce@(protected) [mailto:oracle-l-bounce@(protected)]De
la part de Justin Cave
Envoy¨¦ : vendredi 11 juin 2004 12:15
? : oracle-l@(protected)
Objet : RE: Tbs READ ONLY and Snapshot too old
ORA-01555 (See ORA-01555.ora-code.com) 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 (See ORA-01555.ora-code.com) error when trying to access a
read-only object.
Do you get these ORA-01555 (See ORA-01555.ora-code.com) 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@(protected) [mailto:oracle-l-bounce@(protected)]
On Behalf Of NGUYEN Philippe (Cetelem)
Sent: Friday, June 11, 2004 4:15 AM
To: oracle-l@(protected)
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 (See ORA-01555.ora-code.com).
Do you think that "in general " this operation could enhance our ORA-01555 (See ORA-01555.ora-code.com)
problems ?
TIA
Philippe
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN " >
<HTML > <HEAD >
<META HTTP-EQUIV= "Content-Type " CONTENT= "text/html; charset=iso-8859-1 " >
<TITLE >Tbs READ ONLY and Snapshot too old </TITLE >
<META content= "MSHTML 6.00.2600.0 " name=GENERATOR > </HEAD >
<BODY >
<DIV > <SPAN class=488540716-11062004 > <FONT face=Arial color=#0000ff size=2 >Tahnks
Dick, </FONT > </SPAN > <SPAN class=488540716-11062004 > <FONT face=Arial
color=#0000ff size=2 >this application run on 8i database, is the Global Temp
available ? or any equivalence ? </FONT > </SPAN > </DIV >
<DIV > </DIV >
<P > <FONT face=Arial color=#000000
size=2 >__ ____ ____ ____ ____ ____ ____ ____ ____ ____ ____ <B > </B > </FONT > <B > </B >
<BR > <B > <FONT face=Arial color=#000000 >Philippe Nguyen </FONT > </B > <BR > <FONT
face=Arial color=#000000 size=2 >CETELEM - Administration, Architecture
D¨¦cisionnelle </FONT > <BR > <FONT face=Arial color=#000000 size=2 >Direction
Customer Relationship Management </FONT > <BR > <FONT face=Arial color=#000000
size=1 >E-Mail : philippe.nguyen@(protected) </FONT > <BR > <FONT face=Arial
color=#000000 size=1 >Tel : (+33) 1 46 39 92 16 - Fax : (+33) 1 46 39 59
88 </FONT > </P >
<BLOCKQUOTE dir=ltr style= "MARGIN-RIGHT: 0px " >
<DIV class=OutlookMessageHeader dir=ltr align=left > <FONT face=Tahoma
size=2 >-- --Message d 'origine-- -- <BR > <B >De : </B >
oracle-l-bounce@(protected) [mailto:oracle-l-bounce@(protected)] <B >De la
part de </B > Goulet, Dick <BR > <B >Envoy¨¦ : </B > vendredi 11 juin 2004
16:38 <BR > <B >? : </B > oracle-l@(protected) <BR > <B >Objet : </B > RE: Tbs
READ ONLY and Snapshot too old <BR > <BR > </FONT > </DIV >
<DIV > <SPAN class=388403313-11062004 > <FONT face=Arial color=#0000ff
size=2 >Philippe, </FONT > </SPAN > </DIV >
<DIV > <SPAN class=388403313-11062004 > <FONT face=Arial color=#0000ff
size=2 > </FONT > </SPAN > </DIV >
<DIV > <SPAN class=388403313-11062004 > <FONT face=Arial
color=#0000ff size=2 >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 (See ORA-01555.ora-code.com) 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 (See ORA-01555.ora-code.com). </FONT > </SPAN > </DIV >
<DIV > <FONT face=Arial color=#0000ff size=2 > </FONT > </DIV >
<DIV > <SPAN class=388403313-11062004 > <FONT face=Arial color=#0000ff
size=2 > Therefore, possible band-aids: </FONT > </SPAN > </DIV >
<DIV > <SPAN class=388403313-11062004 > <FONT face=Arial color=#0000ff
size=2 > </FONT > </SPAN > </DIV >
<DIV > <SPAN class=388403313-11062004 > <FONT face=Arial
color=#0000ff size=2 >1) Make the smaller tables Global
Temps </FONT > </SPAN > </DIV >
<DIV > <SPAN class=388403313-11062004 > <FONT face=Arial
color=#0000ff size=2 >2) Increase the db_block_buffers or their 9i
equivalent. </FONT > </SPAN > </DIV >
<DIV > <SPAN class=388403313-11062004 > <FONT face=Arial
color=#0000ff size=2 >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.) </FONT > </SPAN > </DIV >
<DIV > <SPAN class=388403313-11062004 > <FONT face=Arial color=#0000ff
size=2 > 4) Scan Metalink for ORA-01555 (See ORA-01555.ora-code.com). <!--StartFragment -- > <FONT size=3 > <FONT
color=#000000 > <FONT face= "Times New Roman " > </FONT > </FONT > <FONT
size=2 >Note:269814.1 should be of help. </FONT > </FONT > </FONT > </SPAN > </DIV >
<P > <FONT size=2 >Dick Goulet <BR >Senior Oracle DBA <BR >Oracle Certified 8i
DBA </FONT > </P >
<BLOCKQUOTE dir=ltr style= "MARGIN-RIGHT: 0px " >
<DIV class=OutlookMessageHeader dir=ltr align=left > <FONT face=Tahoma
size=2 >-- --Original Message-- -- <BR > <B >From: </B > NGUYEN Philippe (Cetelem)
[mailto:philippe.nguyen@(protected)] <BR > <B >Sent: </B > Friday, June 11, 2004
7:03 AM <BR > <B >To: </B > 'oracle-l@(protected) ' <BR > <B >Subject: </B > RE: Tbs
READ ONLY and Snapshot too old <BR > <BR > </FONT > </DIV >
<DIV > <SPAN class=569465010-11062004 > <FONT face=Arial color=#0000ff
size=2 >thank Justin, </FONT > </SPAN > </DIV >
<DIV > <SPAN class=569465010-11062004 > <FONT face=Arial color=#0000ff
size=2 >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. </FONT > </SPAN > </DIV >
<DIV > <SPAN class=569465010-11062004 > <FONT face=Arial color=#0000ff
size=2 >The problem is that the generated script are often launch in the same
time and I we can 't touch the code. </FONT > </SPAN > </DIV >
<DIV > <SPAN class=569465010-11062004 > <FONT face=Arial color=#0000ff
size=2 > </FONT > </SPAN > </DIV >
<P > <FONT face=Arial color=#000000
size=2 >__ ____ ____ ____ ____ ____ ____ ____ ____ ____ ____ <B > </B > </FONT > <B > </B >
<BR > <B > <FONT face=Arial color=#000000 >Philippe Nguyen </FONT > </B > <BR > <FONT
face=Arial color=#000000 size=2 >CETELEM - Administration, Architecture
D¨¦cisionnelle </FONT > <BR > <FONT face=Arial color=#000000 size=2 >Direction
Customer Relationship Management </FONT > <BR > <FONT face=Arial color=#000000
size=1 >E-Mail : philippe.nguyen@(protected) </FONT > <BR > <FONT face=Arial
color=#000000 size=1 >Tel : (+33) 1 46 39 92 16 - Fax : (+33) 1 46 39
59 88 </FONT > </P >
<BLOCKQUOTE dir=ltr style= "MARGIN-RIGHT: 0px " >
<DIV class=OutlookMessageHeader dir=ltr align=left > <FONT face=Tahoma
size=2 >-- --Message d 'origine-- -- <BR > <B >De : </B >
oracle-l-bounce@(protected) [mailto:oracle-l-bounce@(protected)] <B >De
la part de </B > Justin Cave <BR > <B >Envoy¨¦ : </B > vendredi 11 juin 2004
12:15 <BR > <B >? : </B > oracle-l@(protected) <BR > <B >Objet : </B > RE:
Tbs READ ONLY and Snapshot too old <BR > <BR > </FONT > </DIV >
<DIV dir=ltr align=left > <FONT face=Arial color=#0000ff size=2 > <SPAN
class=359271110-11062004 >ORA-01555 (See ORA-01555.ora-code.com) 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 (See ORA-01555.ora-code.com)
error when trying to access a read-only object. </SPAN > </FONT > </DIV >
<DIV dir=ltr align=left > <FONT face=Arial color=#0000ff size=2 > <SPAN
class=359271110-11062004 > </SPAN > </FONT > </DIV >
<DIV dir=ltr align=left > <FONT face=Arial color=#0000ff size=2 > <SPAN
class=359271110-11062004 >Do you get these ORA-01555 (See ORA-01555.ora-code.com) 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. </SPAN > </FONT > </DIV >
<DIV > <FONT face=Arial color=#0000ff size=2 > </FONT > </DIV >
<DIV align=left > <FONT face=Arial size=2 >Justin Cave </FONT > </DIV >
<DIV align=left > <FONT face=Arial size=2 >Distributed Database Consulting,
Inc. </FONT > </DIV >
<DIV align=left > <FONT face=Arial > <FONT size=2 > <A
href= "http://www.ddbcinc.com/askDDBC " >http://www.ddbcinc.com <SPAN
class=359271110-11062004 >/askDDBC </A > </SPAN > </FONT > </FONT > </DIV >
<DIV > <FONT face=Arial color=#0000ff size=2 > </FONT > </DIV > <BR >
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left >
<HR tabIndex=-1 >
<FONT face=Tahoma size=2 > <B >From: </B > oracle-l-bounce@(protected)
[mailto:oracle-l-bounce@(protected)] <B >On Behalf Of </B >NGUYEN Philippe
(Cetelem) <BR > <B >Sent: </B > Friday, June 11, 2004 4:15 AM <BR > <B >To: </B >
oracle-l@(protected) <BR > <B >Subject: </B > Tbs READ ONLY and Snapshot too
old <BR > </FONT > <BR > </DIV >
<DIV > </DIV >
<P > <FONT size=2 >Hi List, </FONT > <BR > <FONT size=2 >just a little question
about read only tbs : </FONT > <BR > <FONT size=2 >We have big tables that are
loaded once and never updated during the month , </FONT > <BR > <FONT
size=2 >so I think it 's a good thing to switch their tbs to read only mode.
</FONT > <BR > <FONT size=2 >Web have a lot of script that request thoses heavy
tables to build small tables and we often encounter ORA-01555 (See ORA-01555.ora-code.com). </FONT >
<BR > <FONT size=2 >Do you think that "in general " this operation could
enhance our ORA-01555 (See ORA-01555.ora-code.com) problems ? </FONT > <BR > <FONT size=2 >TIA
</FONT > <BR > <FONT size=2 >Philippe </FONT >
</P > </BLOCKQUOTE > </BLOCKQUOTE > </BLOCKQUOTE > </BODY > </HTML >
|
|
