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-YYYY: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. > > > > > 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 > > > === message truncated === > > > __________________________________________________ > 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). > -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- > This e-mail was scanned by the eSafe Mail Gateway > -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-8?Q?=E0=E3=F8_=E9=E7=E9=E0=EC?= 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).
