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.
Yu wrote: > 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.
"Vincent Ventrone" <v...@brandeis.edu> wrote in message news:9ivkms$qni$1@new-news.cc.brandeis.edu... >>"Guang Mei" <g...@proteome.com> wrote in message news:kcI47.30B6.4620@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 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.
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.Wot...@dwotton.nospam.clara.co.uk> wrote in message
> >> 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 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
> 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.
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...
"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.
> > "Vincent Ventrone" <v...@brandeis.edu> wrote in message > news:9ivkms$qni$1@new-news.cc.brandeis.edu... > > >>"Guang Mei" <g...@proteome.com> wrote in message > news:kcI47.30B6.4620@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 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.
In article <3b566...@usenet.per.paradox.net.au>, "Howard says...
>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?
>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.
>> "Vincent Ventrone" <v...@brandeis.edu> wrote in message >news:9ivkms$qni$1@new-news.cc.brandeis.edu... >> >>"Guang Mei" <g...@proteome.com> wrote in message >news:kcI47.30B6.4620@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 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.
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.
Regards HJR
"Stephen Bell" <stephen.b...@cgi.ca> wrote in message
> 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.
> > > "Vincent Ventrone" <v...@brandeis.edu> wrote in message > > news:9ivkms$qni$1@new-news.cc.brandeis.edu... > > > >>"Guang Mei" <g...@proteome.com> wrote in message > > news:kcI47.30B6.4620@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.
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
> In article <3b566...@usenet.per.paradox.net.au>, "Howard says...
> >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?
> >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.
> >> "Vincent Ventrone" <v...@brandeis.edu> wrote in message > >news:9ivkms$qni$1@new-news.cc.brandeis.edu... > >> >>"Guang Mei" <g...@proteome.com> wrote in message > >news:kcI47.30B6.4620@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.
In article <3b58f...@usenet.per.paradox.net.au>, "Howard says...
>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" <tk...@us.oracle.com> wrote in message >news:9j6rk902rpg@drn.newsguy.com... >> In article <3b566...@usenet.per.paradox.net.au>, "Howard says...
>> >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?
>> >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.
>> >> "Vincent Ventrone" <v...@brandeis.edu> wrote in message >> >news:9ivkms$qni$1@new-news.cc.brandeis.edu... >> >> >>"Guang Mei" <g...@proteome.com> wrote in message >> >news:kcI47.30B6.4620@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.
In article <3b58f...@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
>> 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.
>> > > "Vincent Ventrone" <v...@brandeis.edu> wrote in message >> > news:9ivkms$qni$1@new-news.cc.brandeis.edu... >> > > >>"Guang Mei" <g...@proteome.com> wrote in message >> > news:kcI47.30B6.4620@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:
>> > > > 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.
> In article <3b58f...@usenet.per.paradox.net.au>, "Howard says...
> >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.
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.
> >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 sp ace > 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. > .....
> >HJR
> >"Thomas Kyte" <tk...@us.oracle.com> wrote in message > >news:9j6rk902rpg@drn.newsguy.com... > >> In article <3b566...@usenet.per.paradox.net.au>, "Howard says...
> >> >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?
> >> >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.
> >> >> "Vincent Ventrone" <v...@brandeis.edu> wrote in message > >> >news:9ivkms$qni$1@new-news.cc.brandeis.edu... > >> >> >>"Guang Mei" <g...@proteome.com> wrote in message > >> >news:kcI47.30B6.4620@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:
> >> >> > 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
|
