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
adary_at_mehish.co.il

> -----Original Message-----
> From: Walter K [SMTP:alden14004_at_yahoo.com]
> 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"
> <bbaker_at_denvernewspaperagency.com> 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:alden14004_at_yahoo.com]
> > > Reply To: ORACLE-L_at_fatcity.com
> > > 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
> > >
> > >
> > > --- Jared.Still_at_radisys.com 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 <treegarden_at_YAHOO.COM>
> > > > Sent by: root_at_fatcity.com
> > > > 01/25/02 09:30 AM
> > > > Please respond to ORACLE-L
> > > >
> > > >
> > > > To: Multiple recipients of list
> > ORACLE-L
> > > > <ORACLE-L_at_fatcity.com>
> > > > 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"
> > > > <bbaker_at_denvernewspaperagency.com> 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:alden14004_at_yahoo.com]
> > > > > > Reply To: ORACLE-L_at_fatcity.com
> > > > > > 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: alden14004_at_yahoo.com
>
> 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: ListGuru_at_fatcity.com (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: adary_at_mehish.co.il

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: ListGuru_at_fatcity.com (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: Sinard Xing
  INET: sinardyxing_at_bcs-ach.com.sg

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: ListGuru_at_fatcity.com (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).