What does the following error message mean? I got it while executing a stored procedure.
ORA-01562: failed to extend rollback segment number 3 ORA-00604: error occurred at recursive SQL level 2 ORA-01581: attempt to use rollback segment (3) new extent (72) which is being allocated
A rollback segment is extended via a recursive space transaction when necessary.
That recursive transaction needs to write its rollback data into the rollback segment that it is trying to extend.
In order to facilitate this, the dynamic extension of the rollback segment is triggered early -
when there is only 1 remaining unused block in the current extent.
However, it is possible for another transaction running on another CPU
and writing to the same rollback segment to use up the last block of free space in the current extent.
This causes the undo for the recursive transaction to wrap into the new extent that is not yet fully allocated.
Thus the ORA-1581 error.
The same can also happen if a lot of work is needed in the recursive transaction to coalesce free space in the tablespace.
To avoid the error, use uniform large extents for your rollback segments and pre-fragment the tablespace into extents of that size. Use a large optimal setting. If running multiple concurrent heavy DML statements, you need to have as many rollback segments as DML streams.
Distributed queries need to be COMMITted
|A user connected to a local database issues a complex distributed query (select only). On the local database, a rollback segment is allocated for this query. When the query completes and all data is returned, the rollback segment is NOT released. If the user issues a COMMIT it releases the rollback segment. Why the use of rollbacks?|
|Distributed queries have to be turned into transactions implicitly so that the branches of the query can be related to one-another through the global transaction mapping table. Unfortunately, as you have discovered, the normal rules for transaction termination apply. I have seen a case where this caused all rollback segment transaction tables to be fully occupied by "active" distributed transactions, so that user transactions started trying to go into the SYSTEM rollback segment, which they can do safely as long as they don't do any DML, but not exactly what you want!|
Rollback segment extent size
|Currently we have 6 rollback segments with 40M extent sizes that have space enough to extend 5 times. Is there an overhead to getting a 40M extent for every transaction? If so, is there a way to monitor whether this is a problem?|
|There is no overhead in using large rollback segment extents instead of small ones. If anything, bigger is better because it reduces the number of wraps (not that the cost of wraps is very significant). The number of extents is not much more of an issue. The only factor is that the percentage of rollback segment space available for constructing read consistent images is reduced to only 50% after a wrap if you only have 2 extents. Most of the books and papers advise 10 to 20 extents per rollback segment. However, if space in the rollback segment tablespace is abundant, and you don't have a problem with ORA-1555 (snapshot too old) errors, 2 large extents is just fine. The real performance issue is whether you have enough rollback segments to support the peak number of concurrent transactions without contention for rollback segment header blocks. You can see the peak number of concurrent transactions in V$RESOURCE_LIMIT. If that is less than or equal to the 6 rollback segments you have available, then there is no risk of rollback segment header contention at all.|
Max concurrent transactions
|Apparently, the rule of thumb is one rollback segment for every four concurrent transactions. How can I find out the number of concurrent transactions so that I can calculate if we have enough rollback segments?|
You can look in V$RESOURCE_LIMIT to find in highest number of concurrent transactions at any time since instance startup.
Under Oracle7 there is no V$RESOURCE_LIMIT view, but you can get the same information, connected as SYS with:
select count(*) from x$ktcxb where ktcxbflg != 0;
Rollback segment wraps
|What is the definition of a rollback segment wrap? I thought it was when a transaction needed to "wrap" back to the first extent to carry on. Then how come I'm getting wraps on my 8M segments when my total writes are not even close to 8M?|
|It is a little counter-intuitive, but a wrap is defined as when the head of undo generation moves from any one extend into any other extent.|
Shrinking active rollback segments
|Would Oracle let me shrink a RBS that has active transactions on it? Sometimes when we start running one of our mid-size jobs, one of my rollback segments just grows like crazy (until it reaches the RBS tablespace limit). What could be the reason for this?|
|Rollback segment extents that are in use by an active transaction cannot be deallocated in a shrink. However, free extents in the same rollback segment can be deallocated. This fact notwithstanding, an attempt to shrink the rollback segment is most unlikely to solve your problem. Assuming you are already using a large rollback segment with SET TRANSACTION USE ROLLBACK SEGMENT, if you cannot allocate more rollback segment space, you may have to consider breaking the job into multiple stages that commit independently, or driving the job off a full table scan to reduce the undo generation, or some such trick as that.|
Rollback segment wraps
|What do you think of "wraps" from V$ROLLSTAT? Is there a cost to wrap? Our aveactive is about 10M and our extent size is 10M with 10 extents. Would there be a benefit to reduce extent size and increase extents to 20?|
|Wraps are not trivial, because they involve some recursive SQL, but the relative cost is nevertheless low. However, if you are proposing reducing the extent size to get an extra 5M of available inactive undo, then I doubt that that will have a significant impact of the risk of snapshot too old errors. I think that you would be better advised to make the rollback segments bigger.|
Shrinks and ORA-1555
|I've read your tip on preventing snapshot too old errors. It is clear that rollback data in extents below OPTIMAL will be protected, but if rollback data is placed above the OPTIMAL size, it may be lost when the rollback segments shrinks back to its OPTIMAL size. What about this?|
|Your concept of how extending and shrinking works is mistaken. Let us say that a rollback segment has 10 extents below OPTIMAL. Let us further say that at our start time, the head of the active undo is in extent 8, and the tail is in extent 1. At this point we begin a transaction that we will leave open indefinitely. Undo generation continues until extent 10 has been filled, but extent 1 is still in use, so we add extents 11 and 12. When undo generation reaches the end of extent 12, the transaction that began in extent 1 has committed. Therefore the rollback segment can and will now shrink two extents. The two extents that will be removed are the least recently used, with the exception of the extent including the segment header. If the least recently used extents were still in use, a shrink would not be possible. In this case, extents 2 and 3 will be deallocated in the shrink. The undo from extents 8 thru 12 is not affected. These extents cannot be deallocated in a shrink until the open transaction terminates.|
ORA-1555 and transaction slots
Currently I have got the problem with ORA-01555.
I think the problem is caused by transaction slot reuse.
Is there any way to confirm (or check out) the reason for an ORA-01555?
Also, what can I do in order to protect rollback segments from overwriting their transaction slots? Is it possible to set a higher number of transaction slots?
Yes, you can set the following event in the init.ora file
event = "1555 trace name processstate forever, level 10"
That will give you a process state dump for any process that gets an ORA-1555 error. The dump will show you which block the process was trying to rollback to its snapshot SCN. If it's a rollback segment header block, then you have your proof.
The number of transaction slots in each rollback segment header block is a function of the database block size. The only way to make more slots available is to add rollback segments, even though you may not need them for concurrency reasons.
ORA-1595 and ORA-1594
I'm getting the following intermittent error with various rollback segments.
ORA-01595: error freeing extent (1) of rollback segment (7) ORA-01594: attempt to wrap into rollback segment (7) extent (1) which is being freed
Two factors are necessary for this to happen.
The preferred solution is to have sufficient rollback segments to eliminate the sharing of rollback segments between processes. Look in V$RESOURCE_LIMIT for the high-water-mark of transactions. That is the number of rollback segments you need. The alternative solution is to raise OPTIMAL to reduce the risk of the error.
While looking something up in the manual I came across the following:
For each rollback segment, Oracle maintains a transaction table - a list of all transactions that use the associated rollback segment.Are these tables viewable and if so in what way? Are they related to the V$TRANSACTION table, and if so how? V$TRANSACTION of course shows all active transactions, but I couldn't figure out from the docs how those entries relate to the SCN for the associated transaction. It has the columns START_SCNB and START_SCNW which the docs helpfully define as:
Start system change number (SCN) base Start SCN wrapI have no idea what this means. On an otherwise unused instance, I started up two transactions and noticed that the numbers were the same for each of them. So they can't relate to the individual transaction.
The transaction table for each rollback segment is in the rollback segment header block.
You can see the slots in X$KTUXE, and under Oracle8 you can dump the block to your trace file with
alter system dump undo header R01;
Under Oracle7 you would need to use the equivalent blockdump syntax.
Active transactions are also visible in V$TRANSACTION. However, V$TRANSACTION is not merely a view onto the transaction tables. It is a view onto X$KTCXB which exposes one of the fixed arrays in the variable section of the SGA, and is sized by the transactions parameter.
SCNs are logically just big numbers, but they are maintained as two smaller components (the base and the wrap) to avoid precision problems. The wrap is incremented whenever an increment to the base would result in an overflow.
V$TRANSACTION is showing you the SCN at which the transaction started. There is no reason why multiple transactions cannot share the same start SCN. The rollback segment transaction tables show the commit SCN, which is always unique.
Rollback segment extension
|I have noticed an odd thing on 7.3.4 (at least) and onwards - the extent allocation for growing a rollback segment seems to take place BEFORE the last block in the extent is entered - possibly when the penultimate block is entered. I haven't had time to look closely into this though.|
|Yes, the last block in the extent is reserved for the undo that might be required for recursive transactions associated with the wrap.|
I've just found the following error in my alert.log.
Mon Oct 25 08:33:03 1999 Errors in file /opt/oracle/product/admin/M3i_p/bdump/smon_14824.trc: ORA-01595: error freeing extent (0) of rollback segment (3)) ORA-01551: extended rollback segment, pinned blocks releasedIs this bad?
This is nothing to be alarmed about.
SMON wanted to shrink the rollback segment.
The shrink involves change to the database, and so rollback information must be recorded.
The undo for such rollback segment changes always goes into the rollback segment itself.
In this case, SMON began to pin the rollback segment data blocks into which the undo was to be placed,
but was not able to obtain sufficient blocks in the current extent, and therefore aborted the attempt to do the shrink.
This is not of concern, because the shrink will be attempted again 12 hours later,
and will most likely succeed at that time, unless it happens before then when a user transaction triggers a wrap.
The fact that you have seen this error does, however, suggest that the rollback segment extent size is too small. If the extent size were larger, the shrink would generate less undo, and would more likely succeed.
Shrinking a rollback segment
|Can I use event 10048 to shrink a rollback segment in Oracle 7.1?|
|Event 10048 does not cause a rollback segment shrink, but can be used to take dumps when a rollback segment shrinks by other means. At 7.1 you will have to drop and recreate the rollback segment.|
Alternating rollback segments
Is it true that Oracle allocates rollback segments in the order that they are created,
rather than in the order specified in the rollback_segments parameter?
Your tip on Disk Configuration for Rollback Segments seems to suggest this,
but I have created them sequentially and then interleaved them in the parameter file.
By the way, I learned this technique from "Tuning Oracle" by Corey, et al., pp. 49-50.
|There is no disagreement here. The rollback segments are used in the order in which they are declared in the rollback_segments parameter. To spread the I/O, you can either create segments 1, 2 and 3 in tablespace A; 4, 5 and 6 in tablespace B; and 7, 8 and 9 in tablespace C, and then name them in the order 1, 4, 7, 2, 5, 8, 3, 6, 9 in the rollback_segments parameter. This is what the "Oracle Tuning" book was suggesting. Alternatively, you can create segments called 1, 4 and 7 in tablespace A; 2, 5 and 8 in tablespace B; and 3, 6 and 9 in C, and then name them in the natural order in the rollback_segments parameter. This is what I was recommending in my web tip. The intent is the same. The rationale for the different approach is that it reduces the risk of confusing people about the strange order of the segment names in the parameter setting. I have even heard a story of someone who "fixed" the order of the rollback segments in the parameter setting, and then noticed that performance was worse. It may be apocryphal, but it makes the point about the potential for confusion.|
Rollback segment extents
|Is it true that only one transaction can use each rollback segment extent?|
|This has not been true since version 6. From release 7.3, small serial OLTP transactions can even share the same undo block, not to mention extent!|
Rollback segment size for small transactions
|I'm planning to add some extra rollback segments to a database to improve performance and read consistency. I have a database in which the transactions are mostly small ones. I know that the current theory about rollback segment sizing is: run large transactions in large rollback segments -- small ones in small rollback segments -- and in case of a mixture of long and short transactions, create appropriately sized rollback segments and explicitly run transactions in those rollback segments as appropriate. If I suddenly introduce a few smaller sized rollback segments into the database, am I actually going to increase read consistency, or just run the risk of "snapshot too old" errors?|
|I disagree with what you say is the "current theory". The potential performance increase from using small rollback segments for small transactions is virtually nil. Rollback segment blocks are "newed" before each use, so there is no benefit in having the previous version of the block still in cache. In fact, there is a slight cost. Smaller rollback segments also increase the risk of ORA-1555 errors. The only potential benefit is that if there is contention for the rollback segment header blocks, having more rollback segments may eliminate or reduce that contention.|
Rollback segment shrinking
|Another DBA and I were discussing when rollback segments shrink back down to OPTIMAL. He has noticed that this happens nightly when the database is shut down and restarted, but I couldn't find a reference to this in the manuals.|
|Apart from manual shrinks, rollback segments normally only shrink automatically at wraps. However, SMON also shrinks rollback segments in the background every 12 hours, unless you set event 10512 to disable it. That is probably what is causing this overnight shrink.|
Undo for recursive transactions
|Does Oracle's recursive SQL being performed behind the scenes for shrink operations have it's own rollback segment (maybe SYSTEM), or does it use a hidden rollback segment somewhere?|
|In general, the undo for recursive transactions is constrained to use the same rollback segment as the parent transaction. In particular, that applies to recursive transactions for the space management of that rollback segment itself. When other rollback segments are available, the SYSTEM rollback segment is only used for the changes to UNDO$ associated with bringing other rollback segments online, or taking them offline.|
ST enqueue and undo block waits
|We were getting undo block waits as well as some ST enqueue waits. When I increased the number of rollback segments from 60 to 70, the ST enqueue waits improved, but the undo block waits did not. There is very little disk sorting going on on the system, so I think we need more rollback segments. Is there any hard and fast rule as to what the maximum number of rollback segment should be (Oracle suggested 50). In other words, if I increase them, is there any major performance risk?|
The undo block class waits suggest that
your cache (db_block_buffers) is not large enough to retain the undo for the most recent changes to hot blocks.
This may in fact mean that you have too many undo class blocks in cache because of your relatively large number of rollback segments.
The optimal number of rollback segments is shown by
select count(*) from x$ktcxb where ktcxbflg > 0;
The limit of 50 is an old Oracle 6 rule that no longer applies. Prior to version 7, the cost of a wrap was proportional to the number of rollback segments, and this guideline was designed to limit that cost. It could also be that your rollback segments and their extents are too small, given that you also have ST waits. If you still see waits for undo block class blocks after the number of rollback segments is fixed, then an increase in db_block_buffers is indicated.
SMON rollback shrinks
|Is it possible to discover exactly when SMON's 12-hour shrinking of rollback segments occurs?|
|I've only ever monitored this for one iteration (because of the long cycle time) but I believe that it is based on the instance startup time. That is, the action is scheduled every 12 hours after instance startup.|
|I have a table with 2 number columns, and only 1 row. When I update one of the numbers, V$ROLLSTAT.WRITES goes up by 120 bytes. My question is where does the 120 bytes come from?|
|The creation of an undo record requires the header for the undo block to be expanded accordingly. The undo record also has a header (of course) that identifies the transaction table slot being used (thus the transaction id implicitly), the database object number affected, the op code, and some flags. One of those flags is for the beginning of a transaction. If so, the first bits of data in the undo record are the previous values of the control SCN and control UBA (undo block address) from the control information section in the rollback segment header block, as well as the commit SCN of the previous transaction to have used that transaction table slot. Then there is information for the change to the ITL in the data block header, including the old UBA and commit SCN values and flags. Of course, the data block DBA and version information is also needed here. Finally, there is the undo for the update itself, containing just the old values for the columns affected, and enough control information to identify which columns those are.|
|You mentioned that SMON shrinks rollback segments every 12 hours - is SMON also responsible for the regular rollback segment shrinks that occur at wraps?|
Yes, under Oracle8, the foreground process posts SMON if a shrink is needed.
However, this was not the case under Oracle 7.
You can set event 10500, level 6 on SMON (which traces when SMON is posted) to confirm that it is posted at this time.
You can also use event 10046 on SMON to watch the recursive SQL for the shrink.
Having SMON perform the shrink benefits short transactions, because they are not delayed. However, if the foreground process that triggered the shrink continues to generate undo, and needs to allocate another undo block, it will suffer undo segment extension waits until SMON has finished the shrink operation.
Undo block reads
|Are rollback segment data blocks read into the database block buffers like those of any other segment?|
|Rollback segment data blocks are not normally read into the buffer cache prior to being changed. They are merely "newed". That is, a free buffer is requested and is formatted as the target block. The redo record contains a change vector with an opcode representing this operation. Other than this, there is nothing special about the treatment of undo blocks in the cache.|
Deferred rollback segments
|We want to create some more rollback segments to reduce rollback segment header block contention. We intend to increase the number of online rollback segments right up to the max_rollback_segments setting. The transactions_per_rollback_segment parameter is set to 1. What will happen if Oracle needs to create a deferred rollback segment, because of media failure for example? I assume that Oracle doesn't count a deferred rollback segment as an online rollback segment, otherwise there might be a problem. Is my assumption correct?|
Yes, that is correct.
A deferred rollback segment is not really a rollback segment and does not require a slot in the rollback segments array
(which we see as X$KTURD).
Deferred rollback segments use slots in X$KTTVS to track their existence,
and thus you don't need to allow for them when setting max_rollback_segments.
Incidentally, setting transactions_per_rollback_segment to 1 does not prevent rollback segment sharing. It only governs the number of public rollback segment to attempt to acquire at instance startup.
Multiple transactions in an undo block
|Your answer of 13 November 1999 says that multiple transactions can share the same undo block, whereas a recent answer on MetaLink says that "You can have multiple active transactions within a single extent, however only one transaction within a block". Which is right?|
|Multiple transactions can share an undo block, but only in series, not concurrently.|
Dumping rollback segment header blocks
|I am trying to dump the rollback segment headers. Is there any straight forward way to do it, or do I just dump the first block of the rollback segment?|
Under Oracle8 it is as simple as
alter system dump undo header <undo_segment_name>;Under Oracle7 you need to compute the decimal datablock address with DBMS_UTILITY.MAKE_DATA_BLOCK_ADDRESS and take an ordinary blockdump with
alter session set events 'immediate trace name blockdump level <decimal_dba>';
|According to the Oracle manuals, truncate will not use rollback segments. But I tried a truncate with all rollback segments offline, and got an error.|
|Yes, the rowcache changes for truncate must be flushed and that does generate undo. The documentation should say that truncate generates very little undo, rather than none.|
Rollback segment extents
|There seems to be a trade-off between having numerous rollback segment extents to minimize the probability of dynamic extension, and fewer, larger rollback segment extents to minimize the work done by user processes when shrinking, particularly if rollback segments regularly need to extend to many times their OPTIMAL size. What would you do?|
From 7.3, shrinking is actually done by SMON in all cases, and never by user processes.
Try event 10500, level 6.
You'll see that the foregrounds just detect the need for the shrink and post SMON to do the work in the background.
Also, the old chestnut about the probability of extension being inversely proportional to the number of extents is antediluvian.
It was rendered false by the Oracle7 feature whereby concurrent transactions could share rollback segment extents.
So both horns of your dilemma are illusory.
The work done by the user processes for dynamic extension is the real issue, although it is seldom a major one.
Just two extents below OPTIMAL is probably best in your case.
For what its worth, there is a newish script on our web site (called sub_optimal.sql) that checks for repeated shrinking and dynamic extension, and suggests a more appropriate OPTIMAL setting. It is not perfect, because it makes a number of assumptions that might not be true (most notably it assumes that enough space is available), but used with intelligence it may be helpful.
|Copyright © Ixora Pty Ltd||