Re: ORA-01555: snapshot too old

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 21 Jul 2001 05:58:01 -0700
Message-ID: <9jbu8p0mig@drn.newsguy.com>

In article <3b58f911_at_usenet.per.paradox.net.au>, "Howard says...
>
>Quite simply, that graph is based on Oracle 6 data, when transactions
>couldn't share *extents* -so naturally, you'd need lots of extents to stop
>the segment wrapping back on top of itself. That's not been true since
>Oracle 7.1, though, so 20 is unnecessary.
>

the reasons for having 20 is because we cannot wrap into an extent if an open transaction remains in it.

The probability of having a transaction open in a set of 6 big extents is higher then having a transaction open in a set of 20 smaller

      My transaction                                   Rollback
      is here                                          is used to here
         |                                                 |
         v                                                 v

+---------+---------+---------+---------+---------+---------+ +--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+

when the RBS wraps back the head, the RBS with 6 extents will have to extend. The RBS will 20 still has two or three more exents (more time) before it'll have to extend. If I commit in the near future, the rbs with 20 extents will not be forced to extend.

the support notes and education material to which people are refering were written in the decade after 6.0

Eg: <Note:69464.1> for example

>Regards
>HJR
>
>"Stephen Bell" <stephen.bell_at_cgi.ca> wrote in message
>news:3B56D3E7.748C3C00_at_cgi.ca...
>> Hi Howard,
>>
>> I agree with your comments....but in the Oracle Education material for the
 DBA
>> course they talk about setting minextents to 20 (header contention i
>> assume)..they go to great lengths (even a nice little graph if I recall
>> correctly) to explain why...am I totally off base here? Or perhaps this is
 an
>> "it depends on what you're doing" scenario...
>>
>> Any thoughts appreciated..
>>
>> Steve
>>
>> "Howard J. Rogers" wrote:
>>
>> > Oh go on, I'll add my 2 cents'-worth...
>> >
>> > Dave's right in saying that increasing minextents will help cure 1555's.
>> > But that's frankly a daft way to increase the size of rollback segments
>> > (well, OK, not daft, but not entirely wholesome, either).
>> >
>> > If you're using dictionary managed tablespace, then no segment should
>> > (ideally) have more than around half a dozen extents or so, and that
 goes
>> > for rollback segments, too.
>> >
>> > Why not simply recreate your rollback segments with the same number of
>> > extents, but make the extent sizes bigger? In other words, play around
 with
>> > INITIAL and NEXT, not MINEXTENTS.
>> >
>> > Regards
>> > HJR
>> >
>> > "Dave Wotton" <Dave.Wotton_at_dwotton.nospam.clara.co.uk> wrote in message
>> > news:8Qa57.85596$Do6.3950598_at_nnrp4.clara.net...
>> > >
>> > > "Vincent Ventrone" <vav_at_brandeis.edu> wrote in message
 news:9ivkms$qni$1_at_new-news.cc.brandeis.edu...
>> > > >>"Guang Mei" <gmei_at_proteome.com> wrote in message
 news:kcI47.30B6.4620_at_news.shore.net...
>> > > >> We have a cron job every night to exp some schemas(oracle 8.05 on
 Sun).
 We
>> > > >> got the following error during last night's exp for the first time:
>> > > >>
>> > > >> EXP-00008: ORACLE error 1555 encountered
>> > > >>
>> > > > I'm figuring that you did, indeed, have active transactions going on
 in
 the
>> > > > database during the export & one or more of these transactions
 issued a
>> > > > COMMIT before the export ended. When a transaction COMMITs, Oracle
 makes
 the
>> > > > undo records available to be overwritten by other transactions even
 though
>> > > > it also considers these undo records to be "inactive, in-use" if
 some
 other
>> > > > operation needs them for read-consistency. The text of the error
 message --
>> > > > "rollback segment too small" -- is misleading. Once a transaction
 commits,
>> > > > the space it was using in the rollback segment is now up for
 rabs --
 size
>> > > > is not the issue. n other words it's a scheduling problem. You
 have
 two
>> > > > options I think:
>> > > >
>> > > > 1. Find a different time for the export or figure out some way to
 ensure
>> > > > that it has exclusive use of the database (ould be hard to do.)
>> > > >
>> > > > 2. Run the export with the parameter CONSISTENT=Y. This parameter
 sets
 a
>> > > > consistency point for the *entire* export operation and it will then
 have
>> > > > its own undo records to maintain consistency as of the begining of
 the
>> > > > export operation regardless of what else is going on in the
 database. I
>> > > > haven't tested this proposition myself, but I think this would solve
 your
>> > > > rpoblem. Just make sure that your rollback segments can grow
 becuase
 the
>> > > > export itself will end up generating a lot of undo if there is a lot
 of
 DML
>> > > > going on while it is running, since the entire export operation is
 now
 one
>> > > > big transaction.
>> > >
>> > > Vincent's explanation of why snapshot too old has occurred is correct,
 but
>> > > his second recommendation is wrong.
>> > >
>> > > Using the consistent=y option will indeed make the whole of the export
>> > > internally consistent, but it won't avoid the snapshot too old
 message -
>> > > in fact it will make it more likely. The export is basically only
 running
>> > > selects, it isn't doing any DML and so doesn't generate any undo
 records
 to
>> > > maintain consistency - it is relying on the undo records created by
 other
>> > > DML statements not being overwritten, just as it does when the
 consistent=y
>> > > option is not used. Since consistent=y means the database has to
 maintain
 a consistent view of the entire database from
>> > > the time the export starts
>> > > (rather than just a consistent view of each table, from the time each
>> > > table is exported), it is much more likely that a required undo record
 will
>> > > be overwritten in that time. Using consistent=y is a good idea,
 particularly
>> > > if you hope to be able to import more than one table from the export
 and
>> > > have them consistent, but it won't help your snapshot too old problem.
>> > >
>> > > As you know, increasing max-extents for your rollback segments won't
 help,
>> > > because they're not extending anyway, for the reasons Vincente pointed
 out.
>> > > However, recreating your rollback segments with *min-extents*
 significantly
>> > > bigger *will* help because you are pre-allocating them, so there is
 more
>> > > space in the rollback segment to use for undo records before they are
>> > > overwritten. If there is sufficient space in the rollback segment to
>> > > accommodate all the undo generated by any DML running during the
 export,
>> > > no undo records will be overwritten, so no snapshot too old will
 occur.
>> > >
>> > > Dave.
>> > > --
>> > > If you reply to this newsgroup posting by email, remove the "nospam"
>> > > from my email address first.
>> > >
>> > >
>> > >
>> > >
>> > >
>>
>
>

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Sat Jul 21 2001 - 07:58:01 CDT