Re: ORA-01555 Mystery (Help)
In Tom Kyte's book Expert 1-on-1 he says to ANALYZE the table BEFORE starting a big query. Read Chapter 5, starting on page 185 for a complete explanation. Walter K wrote: > > Hi, > > A user in our data warehousing group is running into > the old ORA-01555 (snapshot too old) error every time > she runs a massive (20 million rows) select against > one table via a view. I confirmed that the view only > translates to the one table. > > The user swears that no one would be making any > updates/deletes to the table she is selecting from. I > suggested she lock the table in exclusive mode, prior > to running her massive select to guarantee no one else > could change the data in the table and cause the > triggering of the 1555 error. Locking the table was a > viable option because it's a staging table in the > warehouse itself. She locked the table in exclusive > mode last night and it locked; fired off her query, > and it failed 5 hours later with the 1555 error again. > > I'm stumped on this. I just don't see how this is > possible. Any suggestions? > > Thanks!!! > -w > > __ > Do You Yahoo!? > Great stuff seeking new owners in Yahoo! Auctions! > http://auctions.yahoo.com > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Walter K > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). -- Charlie Mengler Maintenance Warehouse [EMAIL PROTECTED] 10641 Scripps Summit Ct. 858-831-2229 San Diego, CA 92131 Our customers are part of our team. They're the test department! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Charlie Mengler INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ORA-01555 Mystery (Help)
Don't believe your users. Somebody is causing oracle to read blocks in consistent mode, ie. reading them from rollback segments. If the user is right, then try locking the participating tables in the exclusive mode and see who will complain. Alternatively, go to V$ACCESS table, see who is accessing the table in question, and see who has a transaction lock (v$lock, id1=object id). -Original Message- Sent: Friday, January 25, 2002 11:15 AM To: Multiple recipients of list ORACLE-L Hi, A user in our data warehousing group is running into the old ORA-01555 (snapshot too old) error every time she runs a massive (20 million rows) select against one table via a view. I confirmed that the view only translates to the one table. The user swears that no one would be making any updates/deletes to the table she is selecting from. I suggested she lock the table in exclusive mode, prior to running her massive select to guarantee no one else could change the data in the table and cause the triggering of the 1555 error. Locking the table was a viable option because it's a staging table in the warehouse itself. She locked the table in exclusive mode last night and it locked; fired off her query, and it failed 5 hours later with the 1555 error again. I'm stumped on this. I just don't see how this is possible. Any suggestions? Thanks!!! -w __ Do You Yahoo!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ORA-01555 Mystery (Help)
I have a batch job that does this consistently. It's the only job in the database; it sets the transaction to a hugh rollback segment. And it eats its own tail. Depending on how the job is written, it may need a read consistent view itself (as opposed to some other query in the database needing that read consistent view.)In that case, it may well go try to read its own rollback segment, only to find that it's been overwritten. (Oddly enough, even when there's plenty of space to extend the rollback, Oracle will decide to overwrite the original rollback segments rather than extend if it thinks it doesn't need those segments any more.) I'd strongly suggest you get the stuff from Steve Adams' ixora site that places an uncommitted transaction in your rollback segments for the length of the run.This will guarantee that the rollback segments don't get overwritten. Good luck! Barb > -- > From: Walter K[SMTP:[EMAIL PROTECTED]] > Reply To: [EMAIL PROTECTED] > Sent: Friday, January 25, 2002 9:15 AM > To: Multiple recipients of list ORACLE-L > Subject: ORA-01555 Mystery (Help) > > Hi, > > A user in our data warehousing group is running into > the old ORA-01555 (snapshot too old) error every time > she runs a massive (20 million rows) select against > one table via a view. I confirmed that the view only > translates to the one table. > > The user swears that no one would be making any > updates/deletes to the table she is selecting from. I > suggested she lock the table in exclusive mode, prior > to running her massive select to guarantee no one else > could change the data in the table and cause the > triggering of the 1555 error. Locking the table was a > viable option because it's a staging table in the > warehouse itself. She locked the table in exclusive > mode last night and it locked; fired off her query, > and it failed 5 hours later with the 1555 error again. > > I'm stumped on this. I just don't see how this is > possible. Any suggestions? > > Thanks!!! > -w > > __ > Do You Yahoo!? > Great stuff seeking new owners in Yahoo! Auctions! > http://auctions.yahoo.com > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Walter K > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Baker, Barbara INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: ORA-01555 Mystery (Help)
Is it a simple select statement, or is it a cursor select in an PL/SQL block? Does her transaction itself perform any DML on those tables? Raj Walter K <[EMAIL PROTECTED]>@fatcity.com on 01/25/2002 11:15:26 AM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Hi, A user in our data warehousing group is running into the old ORA-01555 (snapshot too old) error every time she runs a massive (20 million rows) select against one table via a view. I confirmed that the view only translates to the one table. The user swears that no one would be making any updates/deletes to the table she is selecting from. I suggested she lock the table in exclusive mode, prior to running her massive select to guarantee no one else could change the data in the table and cause the triggering of the 1555 error. Locking the table was a viable option because it's a staging table in the warehouse itself. She locked the table in exclusive mode last night and it locked; fired off her query, and it failed 5 hours later with the 1555 error again. I'm stumped on this. I just don't see how this is possible. Any suggestions? Thanks!!! -w __ Do You Yahoo!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ORA-01555 Mystery (Help)
Precisely the point I was trying to make, when I put the question if it was a normal select, or if it was within a PL/SQL block? The myth is that snapshot too old happens only when some other transaction was in the process of performing an DML on a table, when you did a select on it. It can happen for other reasons too. Search on Metalink for "Delayed block cleanouts" and "fetch across commits". Raj "Baker, Barbara" <[EMAIL PROTECTED]>@fatcity.com on 01/25/2002 11:52:05 AM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: I have a batch job that does this consistently. It's the only job in the database; it sets the transaction to a hugh rollback segment. And it eats its own tail. Depending on how the job is written, it may need a read consistent view itself (as opposed to some other query in the database needing that read consistent view.)In that case, it may well go try to read its own rollback segment, only to find that it's been overwritten. (Oddly enough, even when there's plenty of space to extend the rollback, Oracle will decide to overwrite the original rollback segments rather than extend if it thinks it doesn't need those segments any more.) I'd strongly suggest you get the stuff from Steve Adams' ixora site that places an uncommitted transaction in your rollback segments for the length of the run.This will guarantee that the rollback segments don't get overwritten. Good luck! Barb > -- > From: Walter K[SMTP:[EMAIL PROTECTED]] > Reply To:[EMAIL PROTECTED] > Sent: Friday, January 25, 2002 9:15 AM > To: Multiple recipients of list ORACLE-L > Subject: ORA-01555 Mystery (Help) > > Hi, > > A user in our data warehousing group is running into > the old ORA-01555 (snapshot too old) error every time > she runs a massive (20 million rows) select against > one table via a view. I confirmed that the view only > translates to the one table. > > The user swears that no one would be making any > updates/deletes to the table she is selecting from. I > suggested she lock the table in exclusive mode, prior > to running her massive select to guarantee no one else > could change the data in the table and cause the > triggering of the 1555 error. Locking the table was a > viable option because it's a staging table in the > warehouse itself. She locked the table in exclusive > mode last night and it locked; fired off her query, > and it failed 5 hours later with the 1555 error again. > > I'm stumped on this. I just don't see how this is > possible. Any suggestions? > > Thanks!!! > -w > > __ > Do You Yahoo!? > Great stuff seeking new owners in Yahoo! Auctions! > http://auctions.yahoo.com > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Walter K > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Baker, Barbara INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ORA-01555 Mystery (Help)
Sure, but the original post concerns a *query*, not a transaction, and before running the query, the user locked the queried table in exclusive mode, to ensure that no other session could write to the queried table. How do we account for the query's need to read from rollback? --- "Baker, Barbara" <[EMAIL PROTECTED]> wrote: > > I have a batch job that does this consistently. It's the only job in > the > database; it sets the transaction to a hugh rollback segment. And it > eats > its own tail. > > Depending on how the job is written, it may need a read consistent > view > itself (as opposed to some other query in the database needing that > read > consistent view.)In that case, it may well go try to read its own > rollback segment, only to find that it's been overwritten. (Oddly > enough, > even when there's plenty of space to extend the rollback, Oracle will > decide > to overwrite the original rollback segments rather than extend if it > thinks > it doesn't need those segments any more.) > > I'd strongly suggest you get the stuff from Steve Adams' ixora site > that > places an uncommitted transaction in your rollback segments for the > length > of the run.This will guarantee that the rollback segments don't > get > overwritten. > Good luck! > > Barb > > > -- > > From: Walter K[SMTP:[EMAIL PROTECTED]] > > Reply To: [EMAIL PROTECTED] > > Sent: Friday, January 25, 2002 9:15 AM > > To: Multiple recipients of list ORACLE-L > > Subject:ORA-01555 Mystery (Help) > > > > Hi, > > > > A user in our data warehousing group is running into > > the old ORA-01555 (snapshot too old) error every time > > she runs a massive (20 million rows) select against > > one table via a view. I confirmed that the view only > > translates to the one table. > > > > The user swears that no one would be making any > > updates/deletes to the table she is selecting from. I > > suggested she lock the table in exclusive mode, prior > > to running her massive select to guarantee no one else > > could change the data in the table and cause the > > triggering of the 1555 error. Locking the table was a > > viable option because it's a staging table in the > > warehouse itself. She locked the table in exclusive > > mode last night and it locked; fired off her query, > > and it failed 5 hours later with the 1555 error again. > > > > I'm stumped on this. I just don't see how this is > > possible. Any suggestions? > > > > Thanks!!! > > -w > > > > __ > > Do You Yahoo!? > > Great stuff seeking new owners in Yahoo! Auctions! > > http://auctions.yahoo.com > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: Walter K > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > > San Diego, California-- Public Internet access / Mailing > Lists > > > > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE-L > > (or the name of mailing list you want to be removed from). You may > > also send the HELP command for other information (like > subscribing). > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Baker, Barbara > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing > Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Baumgartel INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: ORA-01555 Mystery (Help)
I was almost ready to subscribe to the idea of delayed cleanout, but I cannot understand why really. The necessity for reading a block from the rollback segments comes from encountering during the course of the SELECT a block the SCN of which is higher than the SCN when the query started. I have of course no certainty about it, but it would be logical to expect the block's SCN to be properly set irrespectively of the clean-out being immediate or delayed. In other words, even if a SELECT physically writes blocks, it should not have anything to do with rollback segments anyway. I share Mladen's opinion, somebody must be economical with the truth somewhere, and you should check V$ACCESS, V$SESSION and V$LOCK. Are you really sure that the code contains no 'just in case' commit ou rollback which would release the lock? And by the way, 5 hours look to me like an awfully long time, even for a 20 million row mega-select of death. [EMAIL PROTECTED] wrote: > > Precisely the point I was trying to make, when I put the question if it was > a normal select, or if it was within a PL/SQL block? The myth is that > snapshot too old happens only when some other transaction was in the > process of performing an DML on a table, when you did a select on it. It > can happen for other reasons too. Search on Metalink for "Delayed block > cleanouts" and "fetch across commits". > > Raj > > "Baker, Barbara" <[EMAIL PROTECTED]>@fatcity.com on > 01/25/2002 11:52:05 AM > > Please respond to [EMAIL PROTECTED] > > Sent by: [EMAIL PROTECTED] > > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > cc: > > I have a batch job that does this consistently. It's the only job in the > database; it sets the transaction to a hugh rollback segment. And it eats > its own tail. > > Depending on how the job is written, it may need a read consistent view > itself (as opposed to some other query in the database needing that read > consistent view.)In that case, it may well go try to read its own > rollback segment, only to find that it's been overwritten. (Oddly enough, > even when there's plenty of space to extend the rollback, Oracle will > decide > to overwrite the original rollback segments rather than extend if it thinks > it doesn't need those segments any more.) > > I'd strongly suggest you get the stuff from Steve Adams' ixora site that > places an uncommitted transaction in your rollback segments for the length > of the run.This will guarantee that the rollback segments don't get > overwritten. > Good luck! > > Barb > > > -- > > From: Walter K[SMTP:[EMAIL PROTECTED]] > > Reply To:[EMAIL PROTECTED] > > Sent: Friday, January 25, 2002 9:15 AM > > To: Multiple recipients of list ORACLE-L > > Subject: ORA-01555 Mystery (Help) > > > > Hi, > > > > A user in our data warehousing group is running into > > the old ORA-01555 (snapshot too old) error every time > > she runs a massive (20 million rows) select against > > one table via a view. I confirmed that the view only > > translates to the one table. > > > > The user swears that no one would be making any > > updates/deletes to the table she is selecting from. I > > suggested she lock the table in exclusive mode, prior > > to running her massive select to guarantee no one else > > could change the data in the table and cause the > > triggering of the 1555 error. Locking the table was a > > viable option because it's a staging table in the > > warehouse itself. She locked the table in exclusive > > mode last night and it locked; fired off her query, > > and it failed 5 hours later with the 1555 error again. > > > > I'm stumped on this. I just don't see how this is > > possible. Any suggestions? > > > > Thanks!!! > > -w > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ORA-01555 Mystery (Help)
Delayed block cleanouts can still cause the ORA-1555, even
after locking the table in exlusive mode.
That's the purpose of the analyze, to force the block cleanouts.
Jared
Paul Baumgartel <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
01/25/02 09:30 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject: RE: ORA-01555 Mystery (Help)
Sure, but the original post concerns a *query*, not a transaction, and
before running the query, the user locked the queried table in
exclusive mode, to ensure that no other session could write to the
queried table. How do we account for the query's need to read from
rollback?
--- "Baker, Barbara" <[EMAIL PROTECTED]> wrote:
>
> I have a batch job that does this consistently. It's the only job in
> the
> database; it sets the transaction to a hugh rollback segment. And it
> eats
> its own tail.
>
> Depending on how the job is written, it may need a read consistent
> view
> itself (as opposed to some other query in the database needing that
> read
> consistent view.)In that case, it may well go try to read its own
> rollback segment, only to find that it's been overwritten. (Oddly
> enough,
> even when there's plenty of space to extend the rollback, Oracle will
> decide
> to overwrite the original rollback segments rather than extend if it
> thinks
> it doesn't need those segments any more.)
>
> I'd strongly suggest you get the stuff from Steve Adams' ixora site
> that
> places an uncommitted transaction in your rollback segments for the
> length
> of the run.This will guarantee that the rollback segments don't
> get
> overwritten.
> Good luck!
>
> Barb
>
> > --
> > From:Walter K[SMTP:[EMAIL PROTECTED]]
> > Reply To:[EMAIL PROTECTED]
> > Sent:Friday, January 25, 2002 9:15 AM
> > To: Multiple recipients of list ORACLE-L
> > Subject: ORA-01555 Mystery (Help)
> >
> > Hi,
> >
> > A user in our data warehousing group is running into
> > the old ORA-01555 (snapshot too old) error every time
> > she runs a massive (20 million rows) select against
> > one table via a view. I confirmed that the view only
> > translates to the one table.
> >
> > The user swears that no one would be making any
> > updates/deletes to the table she is selecting from. I
> > suggested she lock the table in exclusive mode, prior
> > to running her massive select to guarantee no one else
> > could change the data in the table and cause the
> > triggering of the 1555 error. Locking the table was a
> > viable option because it's a staging table in the
> > warehouse itself. She locked the table in exclusive
> > mode last night and it locked; fired off her query,
> > and it failed 5 hours later with the 1555 error again.
> >
> > I'm stumped on this. I just don't see how this is
> > possible. Any suggestions?
> >
> > Thanks!!!
> > -w
> >
> > __
.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: ORA-01555 Mystery (Help)
Jared, would you elaborate more on this? Does this need to be a 'compute' or can it be an 'estimate' on the analyze? I read the info on Steve's site as suggested by Barb and it sounds like block cleanout may be the issue but I'm still trying to digest the concept/issue as it relates to my circumstance. For the others that have contributed to the thread, yes, the table is definitely locked in exclusive mode (via a different session) before the SELECT is performed and the lock is not released until the following day. I too was suspicious that the lock was accidentally being released. -w --- [EMAIL PROTECTED] wrote: > Delayed block cleanouts can still cause the > ORA-1555, even > after locking the table in exlusive mode. > > That's the purpose of the analyze, to force the > block cleanouts. > > Jared > > > > > > > Paul Baumgartel <[EMAIL PROTECTED]> > Sent by: [EMAIL PROTECTED] > 01/25/02 09:30 AM > Please respond to ORACLE-L > > > To: Multiple recipients of list ORACLE-L > <[EMAIL PROTECTED]> > cc: > Subject:RE: ORA-01555 Mystery (Help) > > > Sure, but the original post concerns a *query*, not > a transaction, and > before running the query, the user locked the > queried table in > exclusive mode, to ensure that no other session > could write to the > queried table. How do we account for the query's > need to read from > rollback? > > > --- "Baker, Barbara" > <[EMAIL PROTECTED]> wrote: > > > > I have a batch job that does this consistently. > It's the only job in > > the > > database; it sets the transaction to a hugh > rollback segment. And it > > eats > > its own tail. > > > > Depending on how the job is written, it may need a > read consistent > > view > > itself (as opposed to some other query in the > database needing that > > read > > consistent view.)In that case, it may well go > try to read its own > > rollback segment, only to find that it's been > overwritten. (Oddly > > enough, > > even when there's plenty of space to extend the > rollback, Oracle will > > decide > > to overwrite the original rollback segments rather > than extend if it > > thinks > > it doesn't need those segments any more.) > > > > I'd strongly suggest you get the stuff from Steve > Adams' ixora site > > that > > places an uncommitted transaction in your rollback > segments for the > > length > > of the run.This will guarantee that the > rollback segments don't > > get > > overwritten. > > Good luck! > > > > Barb > > > > > -- > > > From:Walter > K[SMTP:[EMAIL PROTECTED]] > > > Reply To:[EMAIL PROTECTED] > > > Sent:Friday, January 25, 2002 > 9:15 AM > > > To: Multiple recipients of list > ORACLE-L > > > Subject: ORA-01555 Mystery (Help) > > > > > > Hi, > > > > > > A user in our data warehousing group is running > into > > > the old ORA-01555 (snapshot too old) error every > time > > > she runs a massive (20 million rows) select > against > > > one table via a view. I confirmed that the view > only > > > translates to the one table. > > > > > > The user swears that no one would be making any > > > updates/deletes to the table she is selecting > from. I > > > suggested she lock the table in exclusive mode, > prior > > > to running her massive select to guarantee no > one else > > > could change the data in the table and cause the > > > triggering of the 1555 error. Locking the table > was a > > > viable option because it's a staging table in > the > > > warehouse itself. She locked the table in > exclusive > > > mode last night and it locked; fired off her > query, > > > and it failed 5 hours later with the 1555 error > again. > > > > > > I'm stumped on this. I just don't see how this > is > > > possible. Any suggestions? > > > > > > Thanks!!! > > > -w > > > > > > > __ > . > > > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: > (858) 538-5051 > San Diego, California
RE: ORA-01555 Mystery (Help)
Another fact, that should be mentioned, is that the table in question was built (loaded) two days ago. The nightly ETL processes for the warehouse are pretty substantial and the likelyhood of a block not getting cleaned/flushed out for a couple days should be nil. To summarize: 1. Tuesday Night: -truncate/load table 'A' (24 million rows) -Perform massive select from 'A', fails 5 hours later with 1555. NO DML BEING PERFORMED AGAINST 'A' BY ANY OTHER SESSION 2. Wednesday Night: -Perform massive select against 'A', fails 5 hours later with ORA-1555. NO DML BEING PERFORMED AGAINST 'A' BY ANY OTHER SESSION 3. Thursday night: -'lock table A in exclusive mode;' via session 123 -perform massive select against 'A', fails 5 hours later with ORA-1555 via session 124. NO DML BEING PERFORMED AGAINST 'A' BY ANY OTHER SESSION -session 123 still has exclusive lock on table 'A' the following morning 4. Friday morning: -Walter is stumped but still trying to figure out a solution! :) -w --- [EMAIL PROTECTED] wrote: > Delayed block cleanouts can still cause the > ORA-1555, even > after locking the table in exlusive mode. > > That's the purpose of the analyze, to force the > block cleanouts. > > Jared > > > > > > > Paul Baumgartel <[EMAIL PROTECTED]> > Sent by: [EMAIL PROTECTED] > 01/25/02 09:30 AM > Please respond to ORACLE-L > > > To: Multiple recipients of list ORACLE-L > <[EMAIL PROTECTED]> > cc: > Subject:RE: ORA-01555 Mystery (Help) > > > Sure, but the original post concerns a *query*, not > a transaction, and > before running the query, the user locked the > queried table in > exclusive mode, to ensure that no other session > could write to the > queried table. How do we account for the query's > need to read from > rollback? > > > --- "Baker, Barbara" > <[EMAIL PROTECTED]> wrote: > > > > I have a batch job that does this consistently. > It's the only job in > > the > > database; it sets the transaction to a hugh > rollback segment. And it > > eats > > its own tail. > > > > Depending on how the job is written, it may need a > read consistent > > view > > itself (as opposed to some other query in the > database needing that > > read > > consistent view.)In that case, it may well go > try to read its own > > rollback segment, only to find that it's been > overwritten. (Oddly > > enough, > > even when there's plenty of space to extend the > rollback, Oracle will > > decide > > to overwrite the original rollback segments rather > than extend if it > > thinks > > it doesn't need those segments any more.) > > > > I'd strongly suggest you get the stuff from Steve > Adams' ixora site > > that > > places an uncommitted transaction in your rollback > segments for the > > length > > of the run.This will guarantee that the > rollback segments don't > > get > > overwritten. > > Good luck! > > > > Barb > > > > > -- > > > From:Walter > K[SMTP:[EMAIL PROTECTED]] > > > Reply To:[EMAIL PROTECTED] > > > Sent:Friday, January 25, 2002 > 9:15 AM > > > To: Multiple recipients of list > ORACLE-L > > > Subject: ORA-01555 Mystery (Help) > > > > > > Hi, > > > > > > A user in our data warehousing group is running > into > > > the old ORA-01555 (snapshot too old) error every > time > > > she runs a massive (20 million rows) select > against > > > one table via a view. I confirmed that the view > only > > > translates to the one table. > > > > > > The user swears that no one would be making any > > > updates/deletes to the table she is selecting > from. I > > > suggested she lock the table in exclusive mode, > prior > > > to running her massive select to guarantee no > one else > > > could change the data in the table and cause the > > > triggering of the 1555 error. Locking the table > was a > > > viable option because it's a staging table in > the > > > warehouse itself. She locked the table in > exclusive > > > mode last night and it locked; fired off her > query, > > > and it failed 5 hours later with the 1555 error > again. > > > > > > I'm stumped on this. I just don't see how this > is
Re: ORA-01555 Mystery (Help)
I was going to write this myself, but this explanation fron
MetaLink Note 45895.1 means I can just cut and paste, and
Rachel won't get after me for typos. :)
Jared
Delayed block cleanout on old committed updates. An update operation
completes and commits; the updated blocks are not touched again until a
long-running query begins. Delayed Block Cleanout (DBC) has never been
done on the blocks. This can result in a scenario which happens only
under specific circumstances in VLDB, causing ORA-01555 errors when NO
updates or inserts are being committed on the same blocks a query is
retrieving.
All of the following must be true for an ORA-01555 to occur in this
case:
(i) An update completes and commits and the blocks are not
touched again until...
(ii) A long query begins against the previously updated blocks.
(iii) During the query, a considerable amount of DML takes place,
though not on the previously updated blocks which the query is
currently fetching.
(iv) Under condition (iii) there is so much DML relative to
available
rollback space that the rollback segment used in the first update
wraps around, probably several times.
(v) Under condition (iv), the commit SCN of the first update is
cycled out of the rollback segment.
(vi) Under condition (iv) the lowest SCN in the rollback segment is
pushed higher than the read consistent SCN in the query.
(Note: The read consistent SCN is what the query uses to construct
a read consistent view. Any block which has an SCN higher than
this
was obviously updated after the query started and requires
rollback).
The above conditions imply that when a query reaches a block that has
been
updated but not cleaned out, the query quickly learns that the update
committed, and accordingly cleans out the block. But because the update
SCN is no longer in the rollback segment (condition (v)), the query
doesn't
know WHEN the update committed. This is important because if the commit
happened before the query began, the current value in the block can be
used
by the query; but if the commit happened after, the old value must be
fetched
from the rollback segment. Now, because the rollback segment wrapped in
(iv),
we know that the update SCN can't be higher than the lowest SCN in the
rollback segment, which gives us a nice upper bound. If we only knew
that
the read consistent SCN was higher than this upper bound, we would know
that
the update committed before the query started. But we don't know this
because of condition (vi), so we can't even accurately "estimate" the
update
SCN. Hence, we get an ORA-01555.
Stephane Faroult <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
01/25/02 10:39 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject:Re: ORA-01555 Mystery (Help)
I was almost ready to subscribe to the idea of delayed cleanout, but I
cannot understand why really. The necessity for reading a block from the
rollback segments comes from encountering during the course of the
SELECT a block the SCN of which is higher than the SCN when the query
started. I have of course no certainty about it, but it would be logical
to expect the block's SCN to be properly set irrespectively of the
clean-out being immediate or delayed. In other words, even if a SELECT
physically writes blocks, it should not have anything to do with
rollback segments anyway.
I share Mladen's opinion, somebody must be economical with the truth
somewhere, and you should check V$ACCESS, V$SESSION and V$LOCK. Are you
really sure that the code contains no 'just in case' commit ou rollback
which would release the lock? And by the way, 5 hours look to me like an
awfully long time, even for a 20 million row mega-select of death.
[EMAIL PROTECTED] wrote:
>
> Precisely the point I was trying to make, when I put the question if it
was
> a normal select, or if it was within a PL/SQL block? The myth is that
> snapshot too old happens only when some other transaction was in the
> process of performing an DML on a table, when you did a select on it. It
> can happen for other reasons too. Search on Metalink for "Delayed block
> cleanouts" and "fetch across commits".
>
> Raj
>
> "Baker, Barbara" <[EMAIL PROTECTED]>@fatcity.com on
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-M
RE: ORA-01555 Mystery (Help)
I don't have a definitive answer for that.
My guess would be that 'compute' would be required
so that all blocks are touched.
Another way of dealing with delayed block cleanouts
is to do a 'select * from table;'.As long as you are
going to touch every block anyway, you might as
well compute the stats.
But now I'm speculating. :)
Jared
Walter K <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
01/25/02 11:20 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject:RE: ORA-01555 Mystery (Help)
Jared, would you elaborate more on this? Does this
need to be a 'compute' or can it be an 'estimate' on
the analyze?
I read the info on Steve's site as suggested by Barb
and it sounds like block cleanout may be the issue but
I'm still trying to digest the concept/issue as it
relates to my circumstance.
For the others that have contributed to the thread,
yes, the table is definitely locked in exclusive mode
(via a different session) before the SELECT is
performed and the lock is not released until the
following day. I too was suspicious that the lock was
accidentally being released.
-w
--- [EMAIL PROTECTED] wrote:
> Delayed block cleanouts can still cause the
> ORA-1555, even
> after locking the table in exlusive mode.
>
> That's the purpose of the analyze, to force the
> block cleanouts.
>
> Jared
>
>
>
>
>
>
> Paul Baumgartel <[EMAIL PROTECTED]>
> Sent by: [EMAIL PROTECTED]
> 01/25/02 09:30 AM
> Please respond to ORACLE-L
>
>
> To: Multiple recipients of list ORACLE-L
> <[EMAIL PROTECTED]>
> cc:
> Subject:RE: ORA-01555 Mystery (Help)
>
>
> Sure, but the original post concerns a *query*, not
> a transaction, and
> before running the query, the user locked the
> queried table in
> exclusive mode, to ensure that no other session
> could write to the
> queried table. How do we account for the query's
> need to read from
> rollback?
>
>
> --- "Baker, Barbara"
> <[EMAIL PROTECTED]> wrote:
> >
> > I have a batch job that does this consistently.
> It's the only job in
> > the
> > database; it sets the transaction to a hugh
> rollback segment. And it
> > eats
> > its own tail.
> >
> > Depending on how the job is written, it may need a
> read consistent
> > view
> > itself (as opposed to some other query in the
> database needing that
> > read
> > consistent view.)In that case, it may well go
> try to read its own
> > rollback segment, only to find that it's been
> overwritten. (Oddly
> > enough,
> > even when there's plenty of space to extend the
> rollback, Oracle will
> > decide
> > to overwrite the original rollback segments rather
> than extend if it
> > thinks
> > it doesn't need those segments any more.)
> >
> > I'd strongly suggest you get the stuff from Steve
> Adams' ixora site
> > that
> > places an uncommitted transaction in your rollback
> segments for the
> > length
> > of the run.This will guarantee that the
> rollback segments don't
> > get
> > overwritten.
> > Good luck!
> >
> > Barb
> >
> > > --
> > > From:Walter
> K[SMTP:[EMAIL PROTECTED]]
> > > Reply To:[EMAIL PROTECTED]
> > > Sent:Friday, January 25, 2002
> 9:15 AM
> > > To: Multiple recipients of list
> ORACLE-L
> > > Subject: ORA-01555 Mystery (Help)
> > >
> > > Hi,
> > >
> > > A user in our data warehousing group is running
> into
> > > the old ORA-01555 (snapshot too old) error every
> time
> > > she runs a massive (20 million rows) select
> against
> > > one table via a view. I confirmed that the view
> only
> > > translates to the one table.
> > >
> > > The user swears that no one would be making any
> > > updates/deletes to the table she is selecting
> from. I
> > > suggested she lock the table in exclusive mode,
> prior
> > > to running her massive select to guarantee no
> one else
> > > could change the data in the table and cause the
> > > triggering of the 1555 error. Locking the table
> was a
> > > viable option because it's a staging table in
> the
> > > warehouse itself. She locked the table in
> exclusive
> > > mode last night and it locked; fired o
RE: ORA-01555 Mystery (Help)
No wonder you're mystified. This doesn't make sense. I can understand how you might have had a problem Tues night, but Wed night you should have sailed. Here's one more thing to add to your 'bag of tricks': try running this query (in batch every 15 minutes or so, if you can) to see what user(s) are accessing which rollbacks at any given time. Probably won't help (unless Mladen is right, and someone is not coming clean with the complete truth). But it can't hurt. select TO_CHAR(SYSDATE,'DD-MON-:HH24:MI:SS'), osuser o, username u, segment_name s, substr(sa.sql_text,1,500) txt from v$session s, v$transaction t, dba_rollback_segs r, v$sqlarea sa where s.taddr=t.addr and t.xidusn=r.segment_id(+) and s.sql_address=sa.address(+) / > -- > From: Walter K[SMTP:[EMAIL PROTECTED]] > Reply To: [EMAIL PROTECTED] > Sent: Friday, January 25, 2002 12:30 PM > To: Multiple recipients of list ORACLE-L > Subject: RE: ORA-01555 Mystery (Help) > > Another fact, that should be mentioned, is that the > table in question was built (loaded) two days ago. The > nightly ETL processes for the warehouse are pretty > substantial and the likelyhood of a block not getting > cleaned/flushed out for a couple days should be nil. > > To summarize: > > 1. Tuesday Night: > -truncate/load table 'A' (24 million rows) > -Perform massive select from 'A', fails 5 hours later > with 1555. NO DML BEING PERFORMED AGAINST 'A' BY ANY > OTHER SESSION > 2. Wednesday Night: > -Perform massive select against 'A', fails 5 hours > later with ORA-1555. NO DML BEING PERFORMED AGAINST > 'A' BY ANY OTHER SESSION > 3. Thursday night: > -'lock table A in exclusive mode;' via session 123 > -perform massive select against 'A', fails 5 hours > later with ORA-1555 via session 124. NO DML BEING > PERFORMED AGAINST 'A' BY ANY OTHER SESSION > -session 123 still has exclusive lock on table 'A' the > following morning > 4. Friday morning: > -Walter is stumped but still trying to figure out a > solution! :) > > -w > > > --- [EMAIL PROTECTED] wrote: > > Delayed block cleanouts can still cause the > > ORA-1555, even > > after locking the table in exlusive mode. > > > > That's the purpose of the analyze, to force the > > block cleanouts. > > > > Jared > > > > > > > > > > > > > > Paul Baumgartel <[EMAIL PROTECTED]> > > Sent by: [EMAIL PROTECTED] > > 01/25/02 09:30 AM > > Please respond to ORACLE-L > > > > > > To: Multiple recipients of list ORACLE-L > > <[EMAIL PROTECTED]> > > cc: > > Subject:RE: ORA-01555 Mystery (Help) > > > > > > Sure, but the original post concerns a *query*, not > > a transaction, and > > before running the query, the user locked the > > queried table in > > exclusive mode, to ensure that no other session > > could write to the > > queried table. How do we account for the query's > > need to read from > > rollback? > > > > > > --- "Baker, Barbara" > > <[EMAIL PROTECTED]> wrote: > > > > > > I have a batch job that does this consistently. > > It's the only job in > > > the > > > database; it sets the transaction to a hugh > > rollback segment. And it > > > eats > > > its own tail. > > > > > > Depending on how the job is written, it may need a > > read consistent > > > view > > > itself (as opposed to some other query in the > > database needing that > > > read > > > consistent view.)In that case, it may well go > > try to read its own > > > rollback segment, only to find that it's been > > overwritten. (Oddly > > > enough, > > > even when there's plenty of space to extend the > > rollback, Oracle will > > > decide > > > to overwrite the original rollback segments rather > > than extend if it > > > thinks > > > it doesn't need those segments any more.) > > > > > > I'd strongly suggest you get the stuff from Steve > > Adams' ixora site > > > that > > > places an uncommitted transaction in your rollback > > segments for the > > > length > > > of the run.This will guarantee that the > > rollback segments don't > > > get > > > overwritten. > >
RE: ORA-01555 Mystery (Help)
I think you might have to throw a full hint in there to insure you're really touching all the blocks (select /*+ FULL*/ count(*) from table_name). But Jared's correct. If you're gonna go to that much trouble, might as well compute statistics. > -- > From: [EMAIL PROTECTED][SMTP:[EMAIL PROTECTED]] > Reply To: [EMAIL PROTECTED] > Sent: Friday, January 25, 2002 3:05 PM > To: Multiple recipients of list ORACLE-L > Subject: RE: ORA-01555 Mystery (Help) > > I don't have a definitive answer for that. > > My guess would be that 'compute' would be required > so that all blocks are touched. > > Another way of dealing with delayed block cleanouts > is to do a 'select * from table;'.As long as you are > going to touch every block anyway, you might as > well compute the stats. > > But now I'm speculating. :) > > Jared > > > > > > Walter K <[EMAIL PROTECTED]> > Sent by: [EMAIL PROTECTED] > 01/25/02 11:20 AM > Please respond to ORACLE-L > > > To: Multiple recipients of list ORACLE-L > <[EMAIL PROTECTED]> > cc: > Subject:RE: ORA-01555 Mystery (Help) > > > Jared, would you elaborate more on this? Does this > need to be a 'compute' or can it be an 'estimate' on > the analyze? > > I read the info on Steve's site as suggested by Barb > and it sounds like block cleanout may be the issue but > I'm still trying to digest the concept/issue as it > relates to my circumstance. > > For the others that have contributed to the thread, > yes, the table is definitely locked in exclusive mode > (via a different session) before the SELECT is > performed and the lock is not released until the > following day. I too was suspicious that the lock was > accidentally being released. > > -w > > > > > --- [EMAIL PROTECTED] wrote: > > Delayed block cleanouts can still cause the > > ORA-1555, even > > after locking the table in exlusive mode. > > > > That's the purpose of the analyze, to force the > > block cleanouts. > > > > Jared > > > > > > > > > > > > > > Paul Baumgartel <[EMAIL PROTECTED]> > > Sent by: [EMAIL PROTECTED] > > 01/25/02 09:30 AM > > Please respond to ORACLE-L > > > > > > To: Multiple recipients of list ORACLE-L > > <[EMAIL PROTECTED]> > > cc: > > Subject:RE: ORA-01555 Mystery (Help) > > > > > > Sure, but the original post concerns a *query*, not > > a transaction, and > > before running the query, the user locked the > > queried table in > > exclusive mode, to ensure that no other session > > could write to the > > queried table. How do we account for the query's > > need to read from > > rollback? > > > > > > --- "Baker, Barbara" > > <[EMAIL PROTECTED]> wrote: > > > > > > I have a batch job that does this consistently. > > It's the only job in > > > the > > > database; it sets the transaction to a hugh > > rollback segment. And it > > > eats > > > its own tail. > > > > > > Depending on how the job is written, it may need a > > read consistent > > > view > > > itself (as opposed to some other query in the > > database needing that > > > read > > > consistent view.)In that case, it may well go > > try to read its own > > > rollback segment, only to find that it's been > > overwritten. (Oddly > > > enough, > > > even when there's plenty of space to extend the > > rollback, Oracle will > > > decide > > > to overwrite the original rollback segments rather > > than extend if it > > > thinks > > > it doesn't need those segments any more.) > > > > > > I'd strongly suggest you get the stuff from Steve > > Adams' ixora site > > > that > > > places an uncommitted transaction in your rollback > > segments for the > > > length > > > of the run.This will guarantee that the > > rollback segments don't > > > get > > > overwritten. > > > Good luck! > > > > > > Barb > > > > > > > -- > > > > From:Walter > > K[SMTP:[EMAIL PROTECTED]] > > > > Reply To:[EMAIL PROTECTED] > > > > Sent:
RE: ORA-01555 Mystery (Help)
Jared: The table was analyzed (via estimate) Wednesday night. I don't know if it was before or after Wednesday night's attempt at the extract but if the blocks are getting cleaned out via the 'analyze..estimate' then delayed block cleanout should definitely not have been an issue for Thursday night's attempt. Barb: I understand what Mladen is saying but it shouldn't be an issue because the table has been locked in exclusive mode prior to the running of the query and the lock took. So, even if some rogue process out there was attempting to change data in the table after the query started, it shouldn't matter because the rogue process(es) would get stuck waiting on the table lock. I was lucky that the query is being re-run today while I am still at the office so I looked at the DB activity and nothing is happening in the rollbacks, as I expected to see. Discrete transactions aren't an issue as the warehouse developers don't know about them. I also recreated all of the rollback segments for grins. We'll see what happens tonight. Unfortunately, if it works tonight I still won't really know why. :( -w --- "Baker, Barbara" <[EMAIL PROTECTED]> wrote: > No wonder you're mystified. This doesn't make > sense. > I can understand how you might have had a problem > Tues night, but Wed night > you should have sailed. > > Here's one more thing to add to your 'bag of > tricks': try running this > query (in batch every 15 minutes or so, if you can) > to see what user(s) are > accessing which rollbacks at any given time. > Probably won't help (unless > Mladen is right, and someone is not coming clean > with the complete truth). > But it can't hurt. > > select TO_CHAR(SYSDATE,'DD-MON-:HH24:MI:SS'), >osuser o, username u, >segment_name s, substr(sa.sql_text,1,500) txt > from v$session s, > v$transaction t, > dba_rollback_segs r, > v$sqlarea sa > where s.taddr=t.addr > and t.xidusn=r.segment_id(+) > and s.sql_address=sa.address(+) > / > > > > > ---------- > > From: Walter K[SMTP:[EMAIL PROTECTED]] > > Reply To: [EMAIL PROTECTED] > > Sent: Friday, January 25, 2002 12:30 PM > > To: Multiple recipients of list ORACLE-L > > Subject:RE: ORA-01555 Mystery (Help) > > > > Another fact, that should be mentioned, is that > the > > table in question was built (loaded) two days ago. > The > > nightly ETL processes for the warehouse are pretty > > substantial and the likelyhood of a block not > getting > > cleaned/flushed out for a couple days should be > nil. > > > > To summarize: > > > > 1. Tuesday Night: > > -truncate/load table 'A' (24 million rows) > > -Perform massive select from 'A', fails 5 hours > later > > with 1555. NO DML BEING PERFORMED AGAINST 'A' BY > ANY > > OTHER SESSION > > 2. Wednesday Night: > > -Perform massive select against 'A', fails 5 hours > > later with ORA-1555. NO DML BEING PERFORMED > AGAINST > > 'A' BY ANY OTHER SESSION > > 3. Thursday night: > > -'lock table A in exclusive mode;' via session 123 > > -perform massive select against 'A', fails 5 hours > > later with ORA-1555 via session 124. NO DML BEING > > PERFORMED AGAINST 'A' BY ANY OTHER SESSION > > -session 123 still has exclusive lock on table 'A' > the > > following morning > > 4. Friday morning: > > -Walter is stumped but still trying to figure out > a > > solution! :) > > > > -w > > > > > > --- [EMAIL PROTECTED] wrote: > > > Delayed block cleanouts can still cause the > > > ORA-1555, even > > > after locking the table in exlusive mode. > > > > > > That's the purpose of the analyze, to force the > > > block cleanouts. > > > > > > Jared > > > > > > > > > > > > > > > > > > > > > Paul Baumgartel <[EMAIL PROTECTED]> > > > Sent by: [EMAIL PROTECTED] > > > 01/25/02 09:30 AM > > > Please respond to ORACLE-L > > > > > > > > > To: Multiple recipients of list > ORACLE-L > > > <[EMAIL PROTECTED]> > > > cc: > > > Subject:RE: ORA-01555 Mystery > (Help) > > > > > > > > > Sure, but the original post concerns a *query*, > not > > > a transaction, and > > > before running the query, the user loc
RE: ORA-01555 Mystery (Help)
Title: RE: ORA-01555 Mystery (Help) would "Set transaction read only" help here? -Original Message- From: Baker, Barbara [mailto:[EMAIL PROTECTED]] Sent: Friday, January 25, 2002 2:35 PM To: Multiple recipients of list ORACLE-L Subject: RE: ORA-01555 Mystery (Help) I think you might have to throw a full hint in there to insure you're really touching all the blocks (select /*+ FULL*/ count(*) from table_name). But Jared's correct. If you're gonna go to that much trouble, might as well compute statistics. > -- > From: [EMAIL PROTECTED][SMTP:[EMAIL PROTECTED]] > Reply To: [EMAIL PROTECTED] > Sent: Friday, January 25, 2002 3:05 PM > To: Multiple recipients of list ORACLE-L > Subject: RE: ORA-01555 Mystery (Help) > > I don't have a definitive answer for that. > > My guess would be that 'compute' would be required > so that all blocks are touched. > > Another way of dealing with delayed block cleanouts > is to do a 'select * from table;'. As long as you are > going to touch every block anyway, you might as > well compute the stats. > > But now I'm speculating. :) > > Jared > > > > > > Walter K <[EMAIL PROTECTED]> > Sent by: [EMAIL PROTECTED] > 01/25/02 11:20 AM > Please respond to ORACLE-L > > > To: Multiple recipients of list ORACLE-L > <[EMAIL PROTECTED]> > cc: > Subject: RE: ORA-01555 Mystery (Help) > > > Jared, would you elaborate more on this? Does this > need to be a 'compute' or can it be an 'estimate' on > the analyze? > > I read the info on Steve's site as suggested by Barb > and it sounds like block cleanout may be the issue but > I'm still trying to digest the concept/issue as it > relates to my circumstance. > > For the others that have contributed to the thread, > yes, the table is definitely locked in exclusive mode > (via a different session) before the SELECT is > performed and the lock is not released until the > following day. I too was suspicious that the lock was > accidentally being released. > > -w > > > > > --- [EMAIL PROTECTED] wrote: > > Delayed block cleanouts can still cause the > > ORA-1555, even > > after locking the table in exlusive mode. > > > > That's the purpose of the analyze, to force the > > block cleanouts. > > > > Jared > > > > > > > > > > > > > > Paul Baumgartel <[EMAIL PROTECTED]> > > Sent by: [EMAIL PROTECTED] > > 01/25/02 09:30 AM > > Please respond to ORACLE-L > > > > > > To: Multiple recipients of list ORACLE-L > > <[EMAIL PROTECTED]> > > cc: > > Subject: RE: ORA-01555 Mystery (Help) > > > > > > Sure, but the original post concerns a *query*, not > > a transaction, and > > before running the query, the user locked the > > queried table in > > exclusive mode, to ensure that no other session > > could write to the > > queried table. How do we account for the query's > > need to read from > > rollback? > > > > > > --- "Baker, Barbara" > > <[EMAIL PROTECTED]> wrote: > > > > > > I have a batch job that does this consistently. > > It's the only job in > > > the > > > database; it sets the transaction to a hugh > > rollback segment. And it > > > eats > > > its own tail. > > > > > > Depending on how the job is written, it may need a > > read consistent > > > view > > > itself (as opposed to some other query in the > > database needing that > > > read > > > consistent view.) In that case, it may well go > > try to read its own > > > rollback segment, only to find that it's been > > overwritten. (Oddly > > > enough, > > > even when there's plenty of space to extend the > > rollback, Oracle will > > > decide > > > to overwrite the original rollback segments rather > > than extend if it > > > thinks > > > it doesn't need those segments any more.) > > > > > > I'd strongly suggest you get the stuff from Steve > > Adams' ixora site > > > that > > > places an uncommitted transaction in your rollback > > segments for the > > > length > > > of the run. This will guarantee that the > > rollback segments don't > > > get > > >
Re: ORA-01555 Mystery (Help)
I'm too tired to bug you tonight --- [EMAIL PROTECTED] wrote: > I was going to write this myself, but this explanation fron > MetaLink Note 45895.1 means I can just cut and paste, and > Rachel won't get after me for typos. :) > > Jared > > Delayed block cleanout on old committed updates. An update > operation > completes and commits; the updated blocks are not touched again > until a > long-running query begins. Delayed Block Cleanout (DBC) has never > been > done on the blocks. This can result in a scenario which happens > only > under specific circumstances in VLDB, causing ORA-01555 errors when > NO > updates or inserts are being committed on the same blocks a query > is > retrieving. > > All of the following must be true for an ORA-01555 to occur in this > > case: > > (i) An update completes and commits and the blocks are not > touched again until... > > (ii) A long query begins against the previously updated blocks. > > > (iii) During the query, a considerable amount of DML takes > place, > though not on the previously updated blocks which the query is > currently fetching. > > (iv) Under condition (iii) there is so much DML relative to > available > rollback space that the rollback segment used in the first > update > wraps around, probably several times. > > (v) Under condition (iv), the commit SCN of the first update is > > cycled out of the rollback segment. > > (vi) Under condition (iv) the lowest SCN in the rollback > segment is > pushed higher than the read consistent SCN in the query. > > (Note: The read consistent SCN is what the query uses to > construct >a read consistent view. Any block which has an SCN higher > than > this >was obviously updated after the query started and requires > rollback). > > The above conditions imply that when a query reaches a block that > has > been > updated but not cleaned out, the query quickly learns that the > update > committed, and accordingly cleans out the block. But because the > update > > SCN is no longer in the rollback segment (condition (v)), the query > > doesn't > know WHEN the update committed. This is important because if the > commit > > happened before the query began, the current value in the block can > be > used > by the query; but if the commit happened after, the old value must > be > fetched > from the rollback segment. Now, because the rollback segment > wrapped in > (iv), > we know that the update SCN can't be higher than the lowest SCN in > the > rollback segment, which gives us a nice upper bound. If we only > knew > that > the read consistent SCN was higher than this upper bound, we would > know > that > the update committed before the query started. But we don't know > this > because of condition (vi), so we can't even accurately "estimate" > the > update > SCN. Hence, we get an ORA-01555. > > > > > > Stephane Faroult <[EMAIL PROTECTED]> > Sent by: [EMAIL PROTECTED] > 01/25/02 10:39 AM > Please respond to ORACLE-L > > > To: Multiple recipients of list ORACLE-L > <[EMAIL PROTECTED]> > cc: > Subject:Re: ORA-01555 Mystery (Help) > > > I was almost ready to subscribe to the idea of delayed cleanout, but > I > cannot understand why really. The necessity for reading a block from > the > rollback segments comes from encountering during the course of the > SELECT a block the SCN of which is higher than the SCN when the query > started. I have of course no certainty about it, but it would be > logical > to expect the block's SCN to be properly set irrespectively of the > clean-out being immediate or delayed. In other words, even if a > SELECT > physically writes blocks, it should not have anything to do with > rollback segments anyway. > I share Mladen's opinion, somebody must be economical with the truth > somewhere, and you should check V$ACCESS, V$SESSION and V$LOCK. Are > you > really sure that the code contains no 'just in case' commit ou > rollback > which would release the lock? And by the way, 5 hours look to me like > an > awfully long time, even for a 20 million row mega-select of death. > > [EMAIL PROTECTED] wrote: > > > > Precisely the point I was trying to make, when I put the question > if it > was > > a normal select, or if it was within a PL/SQL block? Th
RE: ORA-01555 Mystery (Help)
IMO, FTS would be less costly(in terms of resources and may be time) and
will take care of dealyed block clean out issue, unless computed stats are
specifically needed.
- Kirti
-Original Message-
Sent: Friday, January 25, 2002 4:05 PM
To: Multiple recipients of list ORACLE-L
I don't have a definitive answer for that.
My guess would be that 'compute' would be required
so that all blocks are touched.
Another way of dealing with delayed block cleanouts
is to do a 'select * from table;'.As long as you are
going to touch every block anyway, you might as
well compute the stats.
But now I'm speculating. :)
Jared
Walter K <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
01/25/02 11:20 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject:RE: ORA-01555 Mystery (Help)
Jared, would you elaborate more on this? Does this
need to be a 'compute' or can it be an 'estimate' on
the analyze?
I read the info on Steve's site as suggested by Barb
and it sounds like block cleanout may be the issue but
I'm still trying to digest the concept/issue as it
relates to my circumstance.
For the others that have contributed to the thread,
yes, the table is definitely locked in exclusive mode
(via a different session) before the SELECT is
performed and the lock is not released until the
following day. I too was suspicious that the lock was
accidentally being released.
-w
--- [EMAIL PROTECTED] wrote:
> Delayed block cleanouts can still cause the
> ORA-1555, even
> after locking the table in exlusive mode.
>
> That's the purpose of the analyze, to force the
> block cleanouts.
>
> Jared
>
>
>
>
>
>
> Paul Baumgartel <[EMAIL PROTECTED]>
> Sent by: [EMAIL PROTECTED]
> 01/25/02 09:30 AM
> Please respond to ORACLE-L
>
>
> To: Multiple recipients of list ORACLE-L
> <[EMAIL PROTECTED]>
> cc:
> Subject:RE: ORA-01555 Mystery (Help)
>
>
> Sure, but the original post concerns a *query*, not
> a transaction, and
> before running the query, the user locked the
> queried table in
> exclusive mode, to ensure that no other session
> could write to the
> queried table. How do we account for the query's
> need to read from
> rollback?
>
>
> --- "Baker, Barbara"
> <[EMAIL PROTECTED]> wrote:
> >
> > I have a batch job that does this consistently.
> It's the only job in
> > the
> > database; it sets the transaction to a hugh
> rollback segment. And it
> > eats
> > its own tail.
> >
> > Depending on how the job is written, it may need a
> read consistent
> > view
> > itself (as opposed to some other query in the
> database needing that
> > read
> > consistent view.)In that case, it may well go
> try to read its own
> > rollback segment, only to find that it's been
> overwritten. (Oddly
> > enough,
> > even when there's plenty of space to extend the
> rollback, Oracle will
> > decide
> > to overwrite the original rollback segments rather
> than extend if it
> > thinks
> > it doesn't need those segments any more.)
> >
> > I'd strongly suggest you get the stuff from Steve
> Adams' ixora site
> > that
> > places an uncommitted transaction in your rollback
> segments for the
> > length
> > of the run.This will guarantee that the
> rollback segments don't
> > get
> > overwritten.
> > Good luck!
> >
> > Barb
> >
> > > --
> > > From:Walter
> K[SMTP:[EMAIL PROTECTED]]
> > > Reply To:[EMAIL PROTECTED]
> > > Sent:Friday, January 25, 2002
> 9:15 AM
> > > To: Multiple recipients of list
> ORACLE-L
> > > Subject: ORA-01555 Mystery (Help)
> > >
> > > Hi,
> > >
> > > A user in our data warehousing group is running
> into
> > > the old ORA-01555 (snapshot too old) error every
> time
> > > she runs a massive (20 million rows) select
> against
> > > one table via a view. I confirmed that the view
> only
> > > translates to the one table.
> > >
> > > The user swears that no one would be making any
> > > updates/deletes to the table she is selecting
> from. I
> > > suggested she lock the table in exclusive mode,
> prior
> > > to running her massive select to guarantee no
> one else
> > > could change the data in the table a
RE: ORA-01555 Mystery (Help)
Hello Walter You wrote : The table was analyzed (via estimate) IMHO Estimate will read only a small part of the blocks. You should try Compute table and Indexes. Yechiel Adar, Mehish Computer Services [EMAIL PROTECTED] > -Original Message- > From: Walter K [SMTP:[EMAIL PROTECTED]] > Sent: Sat, January 26, 2002 1:11 AM > To: Multiple recipients of list ORACLE-L > Subject: RE: ORA-01555 Mystery (Help) > > Jared: > The table was analyzed (via estimate) Wednesday night. > I don't know if it was before or after Wednesday > night's attempt at the extract but if the blocks are > getting cleaned out via the 'analyze..estimate' then > delayed block cleanout should definitely not have been > an issue for Thursday night's attempt. > > Barb: > I understand what Mladen is saying but it shouldn't be > an issue because the table has been locked in > exclusive mode prior to the running of the query and > the lock took. So, even if some rogue process out > there was attempting to change data in the table after > the query started, it shouldn't matter because the > rogue process(es) would get stuck waiting on the table > lock. > > I was lucky that the query is being re-run today while > I am still at the office so I looked at the DB > activity and nothing is happening in the rollbacks, as > I expected to see. Discrete transactions aren't an > issue as the warehouse developers don't know about > them. I also recreated all of the rollback segments > for grins. > > We'll see what happens tonight. Unfortunately, if it > works tonight I still won't really know why. :( > > -w > > --- "Baker, Barbara" > <[EMAIL PROTECTED]> wrote: > > No wonder you're mystified. This doesn't make > > sense. > > I can understand how you might have had a problem > > Tues night, but Wed night > > you should have sailed. > > > > Here's one more thing to add to your 'bag of > > tricks': try running this > > query (in batch every 15 minutes or so, if you can) > > to see what user(s) are > > accessing which rollbacks at any given time. > > Probably won't help (unless > > Mladen is right, and someone is not coming clean > > with the complete truth). > > But it can't hurt. > > > > select TO_CHAR(SYSDATE,'DD-MON-:HH24:MI:SS'), > >osuser o, username u, > >segment_name s, substr(sa.sql_text,1,500) txt > > from v$session s, > > v$transaction t, > > dba_rollback_segs r, > > v$sqlarea sa > > where s.taddr=t.addr > > and t.xidusn=r.segment_id(+) > > and s.sql_address=sa.address(+) > > / > > > > > > > > > -- > > > From: Walter K[SMTP:[EMAIL PROTECTED]] > > > Reply To: [EMAIL PROTECTED] > > > Sent: Friday, January 25, 2002 12:30 PM > > > To: Multiple recipients of list ORACLE-L > > > Subject: RE: ORA-01555 Mystery (Help) > > > > > > Another fact, that should be mentioned, is that > > the > > > table in question was built (loaded) two days ago. > > The > > > nightly ETL processes for the warehouse are pretty > > > substantial and the likelyhood of a block not > > getting > > > cleaned/flushed out for a couple days should be > > nil. > > > > > > To summarize: > > > > > > 1. Tuesday Night: > > > -truncate/load table 'A' (24 million rows) > > > -Perform massive select from 'A', fails 5 hours > > later > > > with 1555. NO DML BEING PERFORMED AGAINST 'A' BY > > ANY > > > OTHER SESSION > > > 2. Wednesday Night: > > > -Perform massive select against 'A', fails 5 hours > > > later with ORA-1555. NO DML BEING PERFORMED > > AGAINST > > > 'A' BY ANY OTHER SESSION > > > 3. Thursday night: > > > -'lock table A in exclusive mode;' via session 123 > > > -perform massive select against 'A', fails 5 hours > > > later with ORA-1555 via session 124. NO DML BEING > > > PERFORMED AGAINST 'A' BY ANY OTHER SESSION > > > -session 123 still has exclusive lock on table 'A' > > the > > > following morning > > > 4. Friday morning: > > > -Walter is stumped but still trying to figure out > > a > > > solution! :) > > > > > > -w > > > > > > > > > --- [EMAIL PROTECTED] wrote: > >
RE: ORA-01555 Mystery (Help)
Hi all, Sorry a bit site track How about the OPTIMAL parameter in CREATE ROLLBACK SEGMENT will this cause ORA 1555, since this option deallocating your rbs extents. Sinardy -Original Message- Sent: 27 January 2002 20:20 To: Multiple recipients of list ORACLE-L Hello Walter You wrote : The table was analyzed (via estimate) IMHO Estimate will read only a small part of the blocks. You should try Compute table and Indexes. Yechiel Adar, Mehish Computer Services [EMAIL PROTECTED] > -Original Message- > From: Walter K [SMTP:[EMAIL PROTECTED]] > Sent: Sat, January 26, 2002 1:11 AM > To: Multiple recipients of list ORACLE-L > Subject: RE: ORA-01555 Mystery (Help) > > Jared: > The table was analyzed (via estimate) Wednesday night. > I don't know if it was before or after Wednesday > night's attempt at the extract but if the blocks are > getting cleaned out via the 'analyze..estimate' then > delayed block cleanout should definitely not have been > an issue for Thursday night's attempt. > > Barb: > I understand what Mladen is saying but it shouldn't be > an issue because the table has been locked in > exclusive mode prior to the running of the query and > the lock took. So, even if some rogue process out > there was attempting to change data in the table after > the query started, it shouldn't matter because the > rogue process(es) would get stuck waiting on the table > lock. > > I was lucky that the query is being re-run today while > I am still at the office so I looked at the DB > activity and nothing is happening in the rollbacks, as > I expected to see. Discrete transactions aren't an > issue as the warehouse developers don't know about > them. I also recreated all of the rollback segments > for grins. > > We'll see what happens tonight. Unfortunately, if it > works tonight I still won't really know why. :( > > -w > > --- "Baker, Barbara" > <[EMAIL PROTECTED]> wrote: > > No wonder you're mystified. This doesn't make > > sense. > > I can understand how you might have had a problem > > Tues night, but Wed night > > you should have sailed. > > > > Here's one more thing to add to your 'bag of > > tricks': try running this > > query (in batch every 15 minutes or so, if you can) > > to see what user(s) are > > accessing which rollbacks at any given time. > > Probably won't help (unless > > Mladen is right, and someone is not coming clean > > with the complete truth). > > But it can't hurt. > > > > select TO_CHAR(SYSDATE,'DD-MON-:HH24:MI:SS'), > >osuser o, username u, > >segment_name s, substr(sa.sql_text,1,500) txt > > from v$session s, > > v$transaction t, > > dba_rollback_segs r, > > v$sqlarea sa > > where s.taddr=t.addr > > and t.xidusn=r.segment_id(+) > > and s.sql_address=sa.address(+) > > / > > > > > > > > > -- > > > From: Walter K[SMTP:[EMAIL PROTECTED]] > > > Reply To: [EMAIL PROTECTED] > > > Sent: Friday, January 25, 2002 12:30 PM > > > To: Multiple recipients of list ORACLE-L > > > Subject: RE: ORA-01555 Mystery (Help) > > > > > > Another fact, that should be mentioned, is that > > the > > > table in question was built (loaded) two days ago. > > The > > > nightly ETL processes for the warehouse are pretty > > > substantial and the likelyhood of a block not > > getting > > > cleaned/flushed out for a couple days should be > > nil. > > > > > > To summarize: > > > > > > 1. Tuesday Night: > > > -truncate/load table 'A' (24 million rows) > > > -Perform massive select from 'A', fails 5 hours > > later > > > with 1555. NO DML BEING PERFORMED AGAINST 'A' BY > > ANY > > > OTHER SESSION > > > 2. Wednesday Night: > > > -Perform massive select against 'A', fails 5 hours > > > later with ORA-1555. NO DML BEING PERFORMED > > AGAINST > > > 'A' BY ANY OTHER SESSION > > > 3. Thursday night: > > > -'lock table A in exclusive mode;' via session 123 > > > -perform massive select against 'A', fails 5 hours > > > later with ORA-1555 via session 124. NO DML BEING > > > PERFORMED AGAINST 'A' BY ANY OTHER SESSION > > > -session 123 still has exclusive lock on table 'A' > > the > > > following morning &g
RE: ORA-01555 Mystery (Help)
Hi all, Ya.. I don't think is the OPTIMAL parameter, Oracle only deallocating those contiguous "inactive" extents. Sorry miss leading you. Sinardy -Original Message- Sent: 28 January 2002 11:34 To: '[EMAIL PROTECTED]' Hi all, Sorry a bit site track How about the OPTIMAL parameter in CREATE ROLLBACK SEGMENT will this cause ORA 1555, since this option deallocating your rbs extents. Sinardy -Original Message- Sent: 27 January 2002 20:20 To: Multiple recipients of list ORACLE-L Hello Walter You wrote : The table was analyzed (via estimate) IMHO Estimate will read only a small part of the blocks. You should try Compute table and Indexes. Yechiel Adar, Mehish Computer Services [EMAIL PROTECTED] > -Original Message- > From: Walter K [SMTP:[EMAIL PROTECTED]] > Sent: Sat, January 26, 2002 1:11 AM > To: Multiple recipients of list ORACLE-L > Subject: RE: ORA-01555 Mystery (Help) > > Jared: > The table was analyzed (via estimate) Wednesday night. > I don't know if it was before or after Wednesday > night's attempt at the extract but if the blocks are > getting cleaned out via the 'analyze..estimate' then > delayed block cleanout should definitely not have been > an issue for Thursday night's attempt. > > Barb: > I understand what Mladen is saying but it shouldn't be > an issue because the table has been locked in > exclusive mode prior to the running of the query and > the lock took. So, even if some rogue process out > there was attempting to change data in the table after > the query started, it shouldn't matter because the > rogue process(es) would get stuck waiting on the table > lock. > > I was lucky that the query is being re-run today while > I am still at the office so I looked at the DB > activity and nothing is happening in the rollbacks, as > I expected to see. Discrete transactions aren't an > issue as the warehouse developers don't know about > them. I also recreated all of the rollback segments > for grins. > > We'll see what happens tonight. Unfortunately, if it > works tonight I still won't really know why. :( > > -w > > --- "Baker, Barbara" > <[EMAIL PROTECTED]> wrote: > > No wonder you're mystified. This doesn't make > > sense. > > I can understand how you might have had a problem > > Tues night, but Wed night > > you should have sailed. > > > > Here's one more thing to add to your 'bag of > > tricks': try running this > > query (in batch every 15 minutes or so, if you can) > > to see what user(s) are > > accessing which rollbacks at any given time. > > Probably won't help (unless > > Mladen is right, and someone is not coming clean > > with the complete truth). > > But it can't hurt. > > > > select TO_CHAR(SYSDATE,'DD-MON-:HH24:MI:SS'), > >osuser o, username u, > >segment_name s, substr(sa.sql_text,1,500) txt > > from v$session s, > > v$transaction t, > > dba_rollback_segs r, > > v$sqlarea sa > > where s.taddr=t.addr > > and t.xidusn=r.segment_id(+) > > and s.sql_address=sa.address(+) > > / > > > > > > > > > -- > > > From: Walter K[SMTP:[EMAIL PROTECTED]] > > > Reply To: [EMAIL PROTECTED] > > > Sent: Friday, January 25, 2002 12:30 PM > > > To: Multiple recipients of list ORACLE-L > > > Subject: RE: ORA-01555 Mystery (Help) > > > > > > Another fact, that should be mentioned, is that > > the > > > table in question was built (loaded) two days ago. > > The > > > nightly ETL processes for the warehouse are pretty > > > substantial and the likelyhood of a block not > > getting > > > cleaned/flushed out for a couple days should be > > nil. > > > > > > To summarize: > > > > > > 1. Tuesday Night: > > > -truncate/load table 'A' (24 million rows) > > > -Perform massive select from 'A', fails 5 hours > > later > > > with 1555. NO DML BEING PERFORMED AGAINST 'A' BY > > ANY > > > OTHER SESSION > > > 2. Wednesday Night: > > > -Perform massive select against 'A', fails 5 hours > > > later with ORA-1555. NO DML BEING PERFORMED > > AGAINST > > > 'A' BY ANY OTHER SESSION > > > 3. Thursday night: > > > -'lock table A in exclusive mode;' via session 123 > > > -perform massive select against 'A', fails
RE: RE: ORA-01555 Mystery (Help)
> Dick: > This makes the assumption that Walter can get to the code, find out what > it's doing, and make modifications. (In our case, we can't. The code is > vendor-supplied, unchangeable, and is written in Cobol). > > It also sounds like this might be happening in the middle of the night. > I'd guess there's a limit to how much information Walter can gather about > what happened 5 hours into the job at 3:00 am > > If he's desperate to get the data loaded and he can't change the sql, then > his options are limited. > > I believe all the possible causes for 1555 errors have been listed in this > thread. Hopefully he can identify which is causing the grief and find a > resolution. > > Barb > > > -- > From: [EMAIL PROTECTED][SMTP:[EMAIL PROTECTED]] > Sent: Friday, January 25, 2002 11:09 AM > To: Baker; Barbara; Multiple recipients of list ORACLE-L > Subject:Re:RE: ORA-01555 Mystery (Help) > > Barb, > > I've tried Steve's idea in the past and although it sorta fixed > the problem > with the large batch job, it created problems elsewhere. It also > did not > totally fix the problem when other applications updated parts of the > table(s) > and committed their transaction. Many folks believe that by > allocating a large > rollback segment to their session they have fixed the problem. > Wrong, this > particular issue can be caused by your own application plus anyone > else who is > using the database and other rollback segments. > > The real issue here is to either find out who or what is > updating the > underlying table or else speeding up the process. There were two > points that I > found easy to implement that fixed 90% of our errors. > > 1) Don't commit across a cursor. In this scenario look for > cases where your > pulling data from a table, updating that table, and then continuing > to read data > from the cursor. This one will pop a 1555 very regularly since the > cursor > depends on a read consistent view, but you just released the > rollback segments. > > 2) Use an order or group by in the select statement. This one > sounds odd, > but it does work. By placing either an order by or group by clause > in the > select statement you force Oracle to read all of the data at one > time, place it > in a temp segment, and then hand it over. The end result is that > when the first > row of data appears in your application you no longer need any > rollback to > create a read consistent view. If your just pulling from the table, > then Oracle > hands over a row as it satisfies the query criteria. OH, did you > just update > and commit a change? Well that is NOT going to be included in your > result set > since it is already locked in concrete. > > Try one of these & see if it fixes your problem. > > Dick Goulet > > Reply Separator > Subject:RE: ORA-01555 Mystery (Help) > Author: "Baker; Barbara" <[EMAIL PROTECTED]> > Date: 1/25/2002 8:52 AM > > > I have a batch job that does this consistently. It's the only job > in the > database; it sets the transaction to a hugh rollback segment. And > it eats > its own tail. > > Depending on how the job is written, it may need a read consistent > view > itself (as opposed to some other query in the database needing that > read > consistent view.)In that case, it may well go try to read its > own > rollback segment, only to find that it's been overwritten. (Oddly > enough, > even when there's plenty of space to extend the rollback, Oracle > will decide > to overwrite the original rollback segments rather than extend if it > thinks > it doesn't need those segments any more.) > > I'd strongly suggest you get the stuff from Steve Adams' ixora site > that > places an uncommitted transaction in your rollback segments for the > length > of the run.This will guarantee that the rollback segments don't > get > overwritten. > Good luck! > > Barb > > > -- > > From: Walter K[SMTP:[EMAIL PROTECTED]] > > Reply To: [EMAIL PROTECTED] > > Sent: Friday, January 25, 2002 9:15 AM > > To: Multiple recipients of list ORACLE-L > &
RE: RE: ORA-01555 Mystery (Help)
How about doing a set transaction to a large rollback before running this query if the analyze doesn't resolve the problem. Kathy -Original Message- Sent: Friday, January 25, 2002 10:40 AM To: Multiple recipients of list ORACLE-L > Dick: > This makes the assumption that Walter can get to the code, find out what > it's doing, and make modifications. (In our case, we can't. The code is > vendor-supplied, unchangeable, and is written in Cobol). > > It also sounds like this might be happening in the middle of the night. > I'd guess there's a limit to how much information Walter can gather about > what happened 5 hours into the job at 3:00 am > > If he's desperate to get the data loaded and he can't change the sql, then > his options are limited. > > I believe all the possible causes for 1555 errors have been listed in this > thread. Hopefully he can identify which is causing the grief and find a > resolution. > > Barb > > > -- > From: [EMAIL PROTECTED][SMTP:[EMAIL PROTECTED]] > Sent: Friday, January 25, 2002 11:09 AM > To: Baker; Barbara; Multiple recipients of list ORACLE-L > Subject:Re:RE: ORA-01555 Mystery (Help) > > Barb, > > I've tried Steve's idea in the past and although it sorta fixed > the problem > with the large batch job, it created problems elsewhere. It also > did not > totally fix the problem when other applications updated parts of the > table(s) > and committed their transaction. Many folks believe that by > allocating a large > rollback segment to their session they have fixed the problem. > Wrong, this > particular issue can be caused by your own application plus anyone > else who is > using the database and other rollback segments. > > The real issue here is to either find out who or what is > updating the > underlying table or else speeding up the process. There were two > points that I > found easy to implement that fixed 90% of our errors. > > 1) Don't commit across a cursor. In this scenario look for > cases where your > pulling data from a table, updating that table, and then continuing > to read data > from the cursor. This one will pop a 1555 very regularly since the > cursor > depends on a read consistent view, but you just released the > rollback segments. > > 2) Use an order or group by in the select statement. This one > sounds odd, > but it does work. By placing either an order by or group by clause > in the > select statement you force Oracle to read all of the data at one > time, place it > in a temp segment, and then hand it over. The end result is that > when the first > row of data appears in your application you no longer need any > rollback to > create a read consistent view. If your just pulling from the table, > then Oracle > hands over a row as it satisfies the query criteria. OH, did you > just update > and commit a change? Well that is NOT going to be included in your > result set > since it is already locked in concrete. > > Try one of these & see if it fixes your problem. > > Dick Goulet > > Reply Separator > Subject:RE: ORA-01555 Mystery (Help) > Author: "Baker; Barbara" <[EMAIL PROTECTED]> > Date: 1/25/2002 8:52 AM > > > I have a batch job that does this consistently. It's the only job > in the > database; it sets the transaction to a hugh rollback segment. And > it eats > its own tail. > > Depending on how the job is written, it may need a read consistent > view > itself (as opposed to some other query in the database needing that > read > consistent view.)In that case, it may well go try to read its > own > rollback segment, only to find that it's been overwritten. (Oddly > enough, > even when there's plenty of space to extend the rollback, Oracle > will decide > to overwrite the original rollback segments rather than extend if it > thinks > it doesn't need those segments any more.) > > I'd strongly suggest you get the stuff from Steve Adams' ixora site > that > places an uncommitted transaction in your rollback segments for the > length > of the run.This will guarantee that the rollback segments don't > get > overwritten. > Good luck! > > Barb > > >
RE: RE: ORA-01555 Mystery (Help)
On Fri, 25 Jan 2002, Kathy Duret wrote: > How about doing a set transaction to a large rollback before running > this query if the analyze doesn't resolve the problem. That will have no effect. http://www.speakeasy.org/~jwilton/oracle/snapshot-too-old.html -- Jeremiah Wilton http://www.speakeasy.net/~jwilton -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeremiah Wilton INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re:RE: RE: ORA-01555 Mystery (Help)
HUMMM, Sounds just like a certain PeopleSoft Cobol process we have. Am there, doing that, and just as frustrated. Damned third party software vendors!! If only they would admit that these errors can occur. Dick Goulet Reply Separator Author: "Baker; Barbara" <[EMAIL PROTECTED]> Date: 1/25/2002 10:39 AM > Dick: > This makes the assumption that Walter can get to the code, find out what > it's doing, and make modifications. (In our case, we can't. The code is > vendor-supplied, unchangeable, and is written in Cobol). > > It also sounds like this might be happening in the middle of the night. > I'd guess there's a limit to how much information Walter can gather about > what happened 5 hours into the job at 3:00 am > > If he's desperate to get the data loaded and he can't change the sql, then > his options are limited. > > I believe all the possible causes for 1555 errors have been listed in this > thread. Hopefully he can identify which is causing the grief and find a > resolution. > > Barb > > > -- > From: [EMAIL PROTECTED][SMTP:[EMAIL PROTECTED]] > Sent: Friday, January 25, 2002 11:09 AM > To: Baker; Barbara; Multiple recipients of list ORACLE-L > Subject:Re:RE: ORA-01555 Mystery (Help) > > Barb, > > I've tried Steve's idea in the past and although it sorta fixed > the problem > with the large batch job, it created problems elsewhere. It also > did not > totally fix the problem when other applications updated parts of the > table(s) > and committed their transaction. Many folks believe that by > allocating a large > rollback segment to their session they have fixed the problem. > Wrong, this > particular issue can be caused by your own application plus anyone > else who is > using the database and other rollback segments. > > The real issue here is to either find out who or what is > updating the > underlying table or else speeding up the process. There were two > points that I > found easy to implement that fixed 90% of our errors. > > 1) Don't commit across a cursor. In this scenario look for > cases where your > pulling data from a table, updating that table, and then continuing > to read data > from the cursor. This one will pop a 1555 very regularly since the > cursor > depends on a read consistent view, but you just released the > rollback segments. > > 2) Use an order or group by in the select statement. This one > sounds odd, > but it does work. By placing either an order by or group by clause > in the > select statement you force Oracle to read all of the data at one > time, place it > in a temp segment, and then hand it over. The end result is that > when the first > row of data appears in your application you no longer need any > rollback to > create a read consistent view. If your just pulling from the table, > then Oracle > hands over a row as it satisfies the query criteria. OH, did you > just update > and commit a change? Well that is NOT going to be included in your > result set > since it is already locked in concrete. > > Try one of these & see if it fixes your problem. > > Dick Goulet > > Reply Separator > Subject:RE: ORA-01555 Mystery (Help) > Author: "Baker; Barbara" <[EMAIL PROTECTED]> > Date: 1/25/2002 8:52 AM > > > I have a batch job that does this consistently. It's the only job > in the > database; it sets the transaction to a hugh rollback segment. And > it eats > its own tail. > > Depending on how the job is written, it may need a read consistent > view > itself (as opposed to some other query in the database needing that > read > consistent view.)In that case, it may well go try to read its > own > rollback segment, only to find that it's been overwritten. (Oddly > enough, > even when there's plenty of space to extend the rollback, Oracle > will decide > to overwrite the original rollback segments rather than extend if it > thinks > it doesn't need those segments any more.) > > I'd strongly suggest you get the stuff from Steve Adams' ixora site > that > places an uncommitted transaction in your rollback segments for the > length > of the run.This will guarantee that the rollback seg
