ORA-01555: snapshot too old
Guang Mei
I know this question has been asked and answered many times. But I am still
not 100% clear.
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
ORA-01555: snapshot too old: rollback segment number 2 with name "R02" too
small
EXP-00000: Export terminated unsuccessfully
SQL> select SEGMENT_NAME,INITIAL_EXTENT,NEXT_EXTENT,
2 MIN_EXTENTS,MAX_EXTENTS from dba_rollback_segs;
SEGMENT_NAME INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS
------------------------------ -------------- ----------- -----------
MAX_EXTENTS
-----------
SYSTEM 51200 51200 2
121
R01 2097152 2097152 20
500
R02 2097152 2097152 20
500
SEGMENT_NAME INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS
------------------------------ -------------- ----------- -----------
MAX_EXTENTS
-----------
R03 2097152 2097152 20
500
R04 2097152 2097152 20
500
So in theory, each transaction could use up to 1024M.
SQL> select name,WRAPS ,EXTENDS,HWMSIZE,gets,waits
2 from v$rollstat, v$rollname
3 where v$rollstat.usn =v$rollname.usn;
NAME WRAPS EXTENDS HWMSIZE GETS
------------------------------ ---------- ---------- ---------- ----------
WAITS
----------
SYSTEM 0 0 1277952 14757
0
R01 297 16 75530240 3435839
14
R02 366 58 163729408 3635643
21
NAME WRAPS EXTENDS HWMSIZE GETS
------------------------------ ---------- ---------- ---------- ----------
WAITS
----------
R03 352 57 161636352 4369141
9
R04 310 21 86065152 3626204
14
According to doc, the error is due to smaller/fewer rollback segments. But
why R02 HWMSIZE is only 164M ( 163729408 ) when it could use 1024M? Or were
there too many other transactions going on (therefore overwrite data in
rollback segments) during the exp that cause this error?
What change(s) could I make to avoid this error? Add more rollback segments
or set larger MAX_EXTENTS values? Assuming I don't have extra disk space to
increase RBS tablespace.
TIA
Guang
Vincent Ventrone
> 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:
(snip)
> According to doc, the error is due to smaller/fewer rollback segments. But
> why R02 HWMSIZE is only 164M ( 163729408 ) when it could use 1024M? Or
were
> there too many other transactions going on (therefore overwrite data in
> rollback segments) during the exp that cause this error?
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 grabs -- 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.
Dave Wotton
"Vincent Ventrone" <v...@brandeis.edu> wrote in message news:9ivkms$qni$1...@new-news.cc.brandeis.edu...
>>"Guang Mei" <gm...@proteome.com> wrote in message news:kcI47.3...@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:
>>
>>
> 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 grabs -- 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.
Howard J. Rogers
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....@dwotton.nospam.clara.co.uk> wrote in message
news:8Qa57.85596$Do6.3...@nnrp4.clara.net...
Stephen Bell
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
Thomas Kyte
>
>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.
>
your kidding. What's the basis in reality for that comment about not have more
the 6 extents?
Most all rbs's should have more then 6 extents.
Having a couple of hundred extents is no big deal. Where is the scientific
proof, case study, example even that shows otherwise?
See
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:730289259844
--
Thomas Kyte (tk...@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
Howard J. Rogers
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.
Regards
HJR
"Stephen Bell" <stephe...@cgi.ca> wrote in message
news:3B56D3E7...@cgi.ca...
Howard J. Rogers
Not for dictionary managed tablespace, anyway (as I carefully pointed out
originally).
The clusters used to record the extents is sized for around half a dozen
extents. Any more than that, and you introduce chaining on the data
dictionary tables.
And, secifically in regard to rollback segments, since transactions can
share extents, can you tell me why extension would be more likely with 6
extents of 1000 blocks each, or 20 extents of 300 blocks each?
It makes no difference at all to the probability of having to extend the
rollback segment.
HJR
"Thomas Kyte" <tk...@us.oracle.com> wrote in message
news:9j6rk...@drn.newsguy.com...
Thomas Kyte
>
>No, I'm not kidding.
>
>Not for dictionary managed tablespace, anyway (as I carefully pointed out
>originally).
>
I saw that. Doesn't matter, the link and papers I pointed you to contain much
information the pre-dates LMTs alltoghter ("HOW TO STOP DEFRAGMENTING AND START
LIVING: THE DEFINITIVE WORD ON FRAGMENTATION" for example)
>The clusters used to record the extents is sized for around half a dozen
>extents. Any more than that, and you introduce chaining on the data
>dictionary tables.
>
again, I'd love to see the test case where we see material proof that this
nominally affects performance in real life.
I'd really like to see that, never have though.
Chaining in itself is not a horrific thing. Especially in a cluster which are
typically designed to not be full scanned (index access). Here we have a linked
list of all of the data we need.
Also, most of the extent info for the table is read from the segment header
anyhow. Given the dictionary caching that goes on....
The reason I'm hot on this is cause I see people get all paranoid, export data,
reorg, going nuts all of the time to get the extents down.... And their systems
run no faster, no slower - they just run with less availability cause they keep
scheduling all of this down time.
Give me the conclusive example that shows that hundreds of extents materially
affects performance in the real world and I'll not bring it up again.
>And, secifically in regard to rollback segments, since transactions can
>share extents, can you tell me why extension would be more likely with 6
>extents of 1000 blocks each, or 20 extents of 300 blocks each?
>
you have one transaction that modified 1 row. That guy went to lunch -- he'll
be back soon but until he does, that 1,000 block extent is wedged. You're RBS
will start extend, 1,000 blocks at a time.
>It makes no difference at all to the probability of having to extend the
>rollback segment.
>
Not true. It depends whether that little transaction was at the "beginnging of
the 1000 block extent or the end -- if it was at the "end", there is a good
chance he would commit before you got through the first two 300 block pieces of
the extent and then the rbs would not extend.
But, even if they do extend, one grows as a slower rate.
Consider the suggestions that has always been true (cut from various support
notes):
...
MINEXTENTS:
-----------
Set MINEXTENTS to 20, this will make it unlikely that the rollback segment
needs to grab another extent because the extent that should move into is still
being used by an active transaction
.........
Why size a rollback segment with a 'minimum' of twenty extents?
Rollback segments dynamically allocate space when required and deallocate space
when no longer needed (if the OPTIMAL parameter is used). The fewer extents
that a rollback segment consists of, the larger the less granular these space
allocations and deallocations are. For example, consider a 200 megabyte
rollback segment which consists of only two 100-megabyte extents. If this
segment were to require additional space, it would allocate another 100M
extent. This immediately increases the size of the rollback segment by 50% and
potentially acquires more space than is really needed. By contrast, if the
rollback segment consisted of twenty 10-megabyte extents, any additional space
required would be allocated in 10-megabyte pieces. When a rollback segment
consists of twenty or more extents, any single change in the number of extents
will not move the total size of the rollback segment by more than 5%, resulting
in a much smoother allocation and deallocation of space.
.....
Thomas Kyte
>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
--
Howard J. Rogers
Well, all that downtime's just plain daft, and I agree with you in that
regard. 6 is good. 12 won't kill you. Neither will 120. But the clusters
are sized for around 6, and that's what I'd be aiming for. Feel free to
chain your own data dictionary, but I'd rather not.
>
> Give me the conclusive example that shows that hundreds of extents
materially
> affects performance in the real world and I'll not bring it up again.
>
> >And, secifically in regard to rollback segments, since transactions can
> >share extents, can you tell me why extension would be more likely with 6
> >extents of 1000 blocks each, or 20 extents of 300 blocks each?
> >
>
> you have one transaction that modified 1 row. That guy went to lunch --
he'll
> be back soon but until he does, that 1,000 block extent is wedged. You're
RBS
> will start extend, 1,000 blocks at a time.
No it won't... it will start to extend, 1000 blocks at a time, only when the
other 5000 blocks have been filled up, and we start wishing to move back
into the first extent.
Given an appropriately sized rollback segment, the number of extents issue
is a dead one, and there is no need for 20 (unless you are doing OCP, in
which case 20 is the "right" answer).
Guys going to lunch and leaving blocking transactions floating around will
cause extension problems however many extents you go for, if you wait long
enough.
HJR