Wrapping concept not clear
Andre Nasser, October 03, 2001 - 11:27 am UTC
Tom, something that is not clear to me yet is:
In case a transaction "walks" completely around a RBS and returns to the first extent in which it still "exists", and space can be used in this first extent (maybe due to other transactions committing or rolling back), will Oracle allocate a new extent even so ? (because in principle this extent is in use... ) Thanks
October 03, 2001 - 2:08 pm UTC
We will never wrap into a RBS extent that is in use by anyone, including ourselves.
We would extend the rbs in this case since we are still using the first extent.
why oracle cant wrap extents
A reader, September 14, 2002 - 4:47 pm UTC
hi tom
I was reading concept huide about rollback segments and read something sort of confusing.
In Concept guide 8.1.7 chapter 4 there is a example with figures of how rollback segments works, it says that an extent can have more than one active transaction but if the transactions has to extend it will never use an extent with active transactions. So I guess basically is
Rollback segment extents can be used by several transactions
But if transactions has to extend it has to use a brand new extents with no active transactions
My question is if I have 4 transactions going in extent 3, transaction 1 extends to extent 4 which is free of transactions, if transaction 2 later on has to extend will it able to use extent 4 since there is now an active transaction (transaction 1)? Or it will has to acquire another brand new extent with no active transaction?
Also is there any theory why when extending extents in RBS Oracle needs a transaction-free extent when normally an extent can accomodate more than one transaction?
September 14, 2002 - 5:19 pm UTC
I don't see that "but if the transactions has to extend it will never use an extent with active transactions."
When a transaction needs more space in a rollback segment and the current extent it was working on is full -- it'll look at the NEXT extent in the rollback segment (think of the extents in an RBS like a circular buffer -- there is a loop of them in a circle).
If the very NEXT extent is still being used, it'll allocate a NEW extent and insert it into the "loop" at that point. We cannot advance into an extent that contains an active transaction -- just like we cannot advance into an online redo log that is still protecting data in the buffer cache (gotta wait for that checkpoint to complete before overwriting the redo entries)
It is not "if a transaction has to extend" but rather "a rollback segment WILL allocate a new extent whenever the next extent in the circular chain of extents it has, has an active transaction"
As for the question -- yes, transaction 2 will use extent 4.
It is not the "transaction" but the "rollback segment". When a rollback segment extends itself..... Not a transaction.
the "theory" is that we cannot overwrite/reuse that which we still need -- we need the rollback generated by that active transaction.
what if next extent is 99% free?
A reader, September 14, 2002 - 5:41 pm UTC
Hi
If you said that we need to protect the rollback of an active transaction condier this situation
transaction 1 wraps (needs an extra extent), it is currently in extent 1, if extent 2 has an active transaction but very small transaction that barely uses 1% of extent size transaction cannot used extent 2?
it is a bit confusing because if extents can be used by several transactions at same time why when a transaction wraps it cannot use next extent which has an active transaction? What can go wrong? Cant Oracle just used the parts of that extent 2 which is not used by the active transaction for my transaction 1?
I can understand if extent is full of information of the active transaction but otherwise I dont see the point...
September 15, 2002 - 10:15 am UTC
Correct -- rollback is used like a circular buffer. We cannot wrap around into an active extent -- just like online redo logs. there might be just one block in the buffer cache protected by that redo log -- hence we cannot reused that redo log (which is likewise a circular buffer) until we are no longer protecting that block.
Rollback is not managed as a "heap" (which is hard and slow to manage) but rather as a high speed circular buffer which is very easy to manage.
I still dont get it
A reader, September 15, 2002 - 11:03 am UTC
Hi again Tom
if I have two transactions which needs to wrap cant they use the same next extent? Since if first transactions that wraps to next extent then the other wont be able to use it since it would be an extent with active transactions?
so I guess we better create RBS with small extent sizes to avoid wasting space???
I dont see the point, I mean if my extent size is 1MB and one transaction only uses 100K then it would be wasting 900K since no transaction can use it
September 15, 2002 - 11:57 am UTC
*transactions* don't need to wrap.
Rollback segments need to wrap.
I think that is your conceptual issue, don't thing TRANSACTION, thing "rollback segment" as an entity.
The rollback segment is servicing lots of people, it is the keeper of the space. The rollback segment is asked by SOMEONE "give me X blocks of space please". The rollback segment looks at its circular queue (say it currently has 20 extents and it is in extent #20, the last one). It finds there are less then X blocks of space and hence has to advance into another extent. If extent #1 has NO ACTIVE work in it, the rollback segment will simply roll into extent #1 and give that someone their space (and anyone else that asks for space will get it from extent #1.
If on the other hand, extent #1 was still active -- the ROLLBACK SEGMENT (not the transaction, not the somebody) will allocate a new extent #21 and add it into the circle. The rollback segment will advance into this new extent and give that somebody and in fact anybody that requests space blocks from this newly added extent #21.
Regardless of WHAT happens (advance into existing extent, advance into new extent) MORE THAN ONE transaction can use it.
So, your example of 1mb extent with 900k "not used" is false. the other 900k would be available to any other transaction in the system cause the ROLLBACK SEGMENT is managing the space, not the transaction!
blocking sessions
A reader, September 15, 2002 - 12:12 pm UTC
Hi
from your explanation
"If on the other hand, extent #1 was still active -- the ROLLBACK SEGMENT (not the transaction, not the somebody) will allocate a new extent #21 and add it
into the circle. The rollback segment will advance into this new extent and give that somebody and in fact anybody that requests space blocks from this newly added extent #21."
If extent #1 has only 100K (of 1MB) occupied we would still need extent #21??? I dont understand why RBS has to extend if next extent has free space for more transactions.
This is cause of blocking sessions from oracle university admin guide:
"When an extent in a rollback segment is full, the Oracle server attempts to reuse the next extent in the segment. If this new extent contains one active entrythat is, an
entry by a transaction that is still activeit cannot be used. In these cases, a rollback segment allocates an additional extent. The transaction cannot skip an extent in the ring and continue writing to a subsequent extent. A transaction that has made only a few changes, but has been idle for a long time, could cause rollback segments to grow
even though there are many free extents. In this situation, a lot of space is wasted and a database administrator may need to intervene to avoid excessive rollback segment
growth."
It is basically saying if one transaction (even it's tiny) is not committed or rollback it can cause RBS to grow since the extent which the transaction's is recorded cannot be used by others! And that's the thing I dont understand. Why it cannot be used by others?
September 15, 2002 - 1:05 pm UTC
If extent #1 is being used by any active transaction, we cannot advance into it. Circular buffer concept here -- you cannot advance forward if something is in the way.
Yes, an active transaction that is consuming 100k of 1m of extent #1 will prevent us from advancing into it UNTIL that transction commits or rolls back.
It cannot be used by others because of the basic premise of a circular buffer. We would get "stuck" because a rollback segment is just used in a big circle and all of a sudden there is this "active set of blocks" in our way that we cannot overwrite.
Managing a circular buffer is very fast, very efficient, requires very little overhead (we only need remember were we are in the circle after all).
Managing a "heap" (which is what you would be asking for) is hard, it would be less performant, it would be less concurrent due to that, it would inhibit scalability. It is not the way we do it.
Contradiction Detected, Pls verify answer....
Robert Chin, September 18, 2002 - 10:06 am UTC
Tom, don't mean to beat it to death, but I am reading contradictions in your answers in this thread,
so just some clarifications (PLEASE!) :
A)This one line in followup to 9/14 review:
"... yes, transaction 2 will use extent 4"
Do you really mean it Tom ?
But Tran #1 - an ongoing active transaction, just spanned into Ext #4 as "A Reader" describes and you keep stressing that "We will never wrap into a RBS extent that is in use by anyone...", "We can not advance into an extent that contains an active tran..."
If you really mean it, that RBS will let Tran #2 span into Ext #4,
If Yes, HOW does this fall in line with your statements I quited above ???
B) "If Ext #1 is being used by any active transaction, we cannot advance into it. Circular buffer concept here...."(9/15)
Question: so RBS will NOT "span/advance/wrap" ANOTHER
EXISTING, ONGOING transaction into Ext #1, regardless of space availability, BUT will stick a NEW transaction into Ext #1, Is my conclusion correct ?
September 18, 2002 - 3:10 pm UTC
read the review RIGHT above that one
*transactions* don't extend.
*rollback segments* extend.
when a ROLLBACK segment needs to get more space, to advance, it will look at the next extent in its circle. If no active transaction exist in there, the ROLLBACK segment will advance into it (note: I have not said the transaction will advance, the RBS will advance). Now that the RBS has advanced into this extent -- it is free to be used by ALL active transctions using that RBS.
Following your logic -- we could have but one transaction / RBS cause the first time a transaction needed space -- you would call that an "extend" but it is NOT. It is a transaction requesting space from this thing we call an RBS. The RBS is the thing that extends, not the transaction.
I see nothing in conflict here. Just drop all thoughts of "transactions" extending -- they do not. RBS's do.
An RBS manages space. A transaction requests it.
An RBS manages space in a circular buffer. As it moves through the extents, it gives space to transactions to use. When it has moved through all of the extents it had allocated (1,2,3,4 in this example) and needs to move on out of extent 4 -- it will first look at extent 1. If extent 1 is not being used by any transaction, the RBS (not the transactions) will move into extent 1 and start giving out space as needed (to ANYONE that needs it). if there was a single active transaction in extent 1 the RBS will instead allocate extent 5, add it to the circle, advance into it and start handing out space to anyone that asks for it.
Still have small doubt
Robert, September 18, 2002 - 5:30 pm UTC
Tom,
2 points, please...
(1) I think I understand this 99% but aren't you being a little bit 'overly precise' about the "*transactions* don't extend. *rollback segments* extend." distinction. When we are saying the *transaction* extends, we are meaning the transaction *invokes*, or *triggers*, or *causes* the rollback segment to extend... if there were no transaction that needed more rollback space, the rollback segment would not extend.
(2) If you will grant the above statement... will you please put to rest the observation by Robert C. regarding your statement: "... yes, transaction 2 will use extent 4"
Based on my understanding of your very good explanations transaction 2 would not be using extent 4, since transaction 1 is active and is currently using extent 4. I understand that the RBS would extend to extent 5 and put transaction 2 there. Correct?
Thanks,
Robert
September 18, 2002 - 7:01 pm UTC
1) overly precise -- no, I was trying to get you away of thinking of the transaction as the thing that extends into the new extent. It does not (it is the catalyst). The RBS does.
2) ARG! I don't know how to say it.
Transaction 2 is NOT extending the RBS into extent 4 or 1 or anything. the RBS is already *there*. The transaction DOES NOT EXTEND, the RBS does and it already did.
So no, i'm not being nearly precise.
Forget about transactions - just forget them. Just thing RBS, extents and whether an extent is still being used. Just think those three things.
RBS has 4 extents.
RBS is currently idle.
Someone asks RBS for space, RBS is in extent 1.
Time goes on, others ask for space, RBS fills extent 1.
RBS now needs to leave extent 1, it looks at the next extent in the circle. This is extent 2. Extent 2 is totally "inactive", no one is using it for anything. RBS extends into extent 2.
The same happens over time for 3 and 4 (they were empty after all, we just brough this RBS online for example).
NOW, extent 4 fills. Someone asks the RBS for more space. RBS looks in the circle -- looking at extent 1.
Is extend 1 still used? Yes -- then get a new extent 5 and put it in the circle and advance into there. No -- then use extent 1 and advance into there.
(there -- I explained it without transactions -- they are not meaningful here for this discussion. Think RBS, extents and "still being used, not being used" period...
Got it !...it's that RBS "guy"
Robert C, September 18, 2002 - 6:09 pm UTC
Thanks Tom ! you'r patience is appreciated....
I got it, I just re-read your emphasis on RBS and image RBS being a person pacing around in a "circle" of "KrispyKreme" shops, he skips those with people still gorging in there and pops into one with no customer and start handing them out free.....
Thanks!... but...
Robert, September 18, 2002 - 7:34 pm UTC
Tom,
Please, Are these true statements:
(1) The RBS will not extend into a extent that is currently being used by a [uncommitted] transaction.
(2) Only one [uncommitted] transaction can be in an RBS extent at a time.
Thanks you for your patience and precision!
Robert.
September 19, 2002 - 7:18 am UTC
1) true
2) very false
Oh, I can answer that.
Kevin, September 18, 2002 - 9:42 pm UTC
1. Yes.
2. No. In fact, Tom says:
"Regardless of WHAT happens (advance into existing extent, advance into new
extent) MORE THAN ONE transaction can use it."
Please clearify the definitions of the wraps and the extends of v$rollstat
Mike, September 18, 2002 - 9:54 pm UTC
Please clearify the definitions of wraps and extends of v$rollstat
Tom,
From yours:
"
An RBS manages space in a circular buffer. As it moves through the extents, it
gives space to transactions to use. When it has moved through all of the
extents it had allocated (1,2,3,4 in this example) and needs to move on out of
extent 4 -- it will first look at extent 1. If extent 1 is not being used by
any transaction, the RBS (not the transactions) will move into extent 1 and
start giving out space as needed (to ANYONE that needs it). if there was a
single active transaction in extent 1 the RBS will instead allocate extent 5,
add it to the circle, advance into it and start handing out space to anyone that
asks for it.
"
a WAPS occured as ?
"
When it has moved through all of the
extents it had allocated (1,2,3,4 in this example) and needs to move on out of
extent 4 -- it will first look at extent 1. If extent 1 is not being used by
any transaction, the RBS (not the transactions) will move into extent 1 and
start giving out space as needed (to ANYONE that needs it).
"
and an EXTENDS occured as?
"
if there was a
single active transaction in extent 1 the RBS will instead allocate extent 5,
add it to the circle, advance into it and start handing out space to anyone that
asks for it.
"
September 19, 2002 - 7:27 am UTC
wrap = reuse extent 1, we wrap the circle around
extend = we couldn't wrap, so we added another extent to the circle.
also the definition of hwmsize (highwater marks) of v$rollstat
Mike, September 18, 2002 - 10:02 pm UTC
TRANSACTIONS_PER_ROLLBACK_SEGMENT
Pasko, September 19, 2002 - 3:35 am UTC
Hi Tom,
Thanks for your Great Contribution to the Discussion above.
I would just like to ask what effect does the init File parameter "TRANSACTIONS_PER_ROLLBACK_SEGMENT" have on this circular Buffer .
Is it the max number of Transactions that a Rollback Segment can accomodate ?
In the init File Comment , they say 'number of Active Transactions per Rollback Segment.
Thanks in Advance.
September 19, 2002 - 7:46 am UTC
Nothing, as the transaction would never get assigned to this rbs if it was already at max transactions.
Sagi, September 19, 2002 - 6:41 am UTC
Hi Tom !
It was really very well explained.
I just wanted to summarize and see if my understanding with the above is correct.
Say our extents are of 1mb
Say the RBS is currently using #4 extent (Used Sofar 600k space). At this point User A's transaction requests for some space of 200k. Now RBS checks that still there is space of 424K still in extent #4. So it gives User-A's transaction extent #4. After a few Seconds User-B reqests for space of 500K for his transactions and now since 224K is left in extent #4. At this point it checks to see if extent #1.
extent #1 - No Active Transactions then allocate it.
extent #1 - Active Transaction (Could be using space 1k or greate does not matter as RBS works in Circular Fasion), So extent #5 is created.
Now if any other user's transactions requests for more space same checks as above. Right?
Q1. Is my understanding correct.
Q2. If there is active transactions in extent #1. and now since user-b requested some space. Would it give 224K of extent #4 and 226K from extent #5 or extent #5.
Thanx for your patience and explanation.
We all are luck to have you and this site.
Regards,
Sagi
September 19, 2002 - 8:04 am UTC
q1) i think so
q2) it would fill extent 4 and then move into extent 5
I think I've got it...I'm I trying your patience yet? Thank You.
Robert, September 19, 2002 - 9:51 am UTC
Tom,
RBS has 4 extents
RBS allocates space in extent 1 for transaction A.
RBS allocates space in extent 1 for transaction B.
RBS allocates space in extent 1 for transaction C.
RBS needs to allocate space for transaction D.
RBS sees extent 1 is fully allocated
so RBS allocates space in extent 2 for transaction D.
RBS needs to allocate space for transaction E.
RBS sees extent 2 is fully allocated
so RBS allocates space in extent 3 for transaction E.
RBS needs to allocate space for transaction F.
RBS sees extent 3 is fully allocated
so RBS allocates space in extent 4 for transaction F.
Transactions A and C (in extent 1) commit.
RBS needs to allocate space for transaction G.
RBS sees extent 4 is fully allocated
RBS sees extent 1 has an active transaction (B).
so RBS creates and allocates space in extent 5 for transaction G.
Transaction B (in extent 1) commits.
RBS needs to allocate space for transaction H.
RBS sees extent 5 is fully allocated.
RBS sees there are no active transactions in extent 1
so RBS allocates space in extent 1 for transaction H.
Is this scenario accurate?
Thanks,
Robert.
September 19, 2002 - 3:56 pm UTC
yes (and would be just as accurate without any mention of "for transaction A,...H"
One minor thing....
Robert C, September 19, 2002 - 4:48 pm UTC
>>so RBS allocates space in extent 2 for transaction D.
>>so RBS allocates space in extent 3 for transaction E.
>>so RBS allocates space in extent 4 for transaction F.
Bob, your RBS forgot to check if those extents have active transactions before diving in.....just splitting hairs ;)
Thanks Tom!
Robert, September 19, 2002 - 5:19 pm UTC
Now I will go to the Oracle documentation and attempt to completely digest your comment and previous emphasis
<quote>
(and would be just as accurate without any mention of "for transaction A,...H"
<quote>
...I sort of understand, but I want to fully understand this concept you are emphasizing of the RBS not allocating space *for* the transaction.
[Yes, Robert C., I that was implied! ;)]
Thanks again,
Robert.
September 20, 2002 - 7:49 am UTC
I'm just pointing out that the cause of misunderstanding here is the "transaction" part.
The transaction is the catalyst, the "end user", the client -- the "thing that wants space".
The RBS is the "server", the maker of space.
Will RBS keep growing
Logan Palanisamy, September 19, 2002 - 5:51 pm UTC
Tom,
Let's say,
We start with RBS1 having 4 initial extents.
Transactions fill up extenets 1 to 4.
One of the transactions that uses extent 1 doesn't commit for a long time.
All the transactions using extents 2 and 3 have committed.
Does it mean, the rollback segment will keep grabbing new extents 5, 6, 7 and so on, till the transaction using extent 1 commits? Will it keep growing till it reaches max extents or runs out of room, if one rogue transaction doesn't release extent 1, even if all the other extents after extent 1 are freed up?
Thanks
September 20, 2002 - 7:50 am UTC
Exactly.
How-to monitor wrap and do appropriate corrections?
Yogeeraj, October 30, 2002 - 8:59 am UTC
hi,
<quote>
The WRAPS column indicate the number of times the rollback segment wrapped to another extent to serve the transaction. If this number is significant then you
need to increase the extent size of the rollback segment. WRAPS is sort of a "bad" name as it implies a total wrap around but it is the measure of how many times we SPANNED extents.
</quote>
I have have gathered some statistics from my Oracle Database that will be soon be moved to a server with more resources(disk, memory, cpu). I need to analyze whether i am currently having rollback segment problems. Please advise.
========================================================
SQL>
1 select substr(owner,1,10) own, substr(segment_name,1,10) SEG, sum(bytes/1024/1024) mb
2 from dba_segments
3 where tablespace_name = 'RBS'
4* group by owner,segment_name
OWN SEG MB
---------- ---------- ----------
SYS RBS0 4
SYS RBS1 4
SYS RBS10 4
SYS RBS11 4
SYS RBS12 4
SYS RBS13 4
SYS RBS14 4
SYS RBS15 4
SYS RBS16 4
SYS RBS17 4
SYS RBS18 4
SYS RBS19 4
SYS RBS2 4
SYS RBS20 4
SYS RBS21 4
SYS RBS22 4
SYS RBS23 4
SYS RBS24 4
SYS RBS25 4
SYS RBS26 4
SYS RBS27 4
SYS RBS28 4
SYS RBS3 4
SYS RBS4 4
SYS RBS5 4
SYS RBS6 4
SYS RBS7 4
SYS RBS8 4
SYS RBS9 4
29 rows selected.
USN WRAPS
16:00 17:35 Difference
0 0 0 0
1 58 58 0
2 46 48 2
3 45 46 1
4 52 53 1
5 62 64 2
6 54 55 1
7 39 39 0
8 45 46 1
9 46 47 1
10 63 63 0
11 39 40 1
12 101 102 1
13 57 58 1
14 51 51 0
15 57 57 0
16 45 45 0
17 45 46 1
18 44 46 2
19 67 67 0
20 35 35 0
21 40 41 1
22 47 48 1
23 47 48 1
24 42 42 0
25 46 47 1
26 49 50 1
27 61 61 0
28 78 79 1
29 46 46 0
====================================
Regards
Yogeeraj
Mike, March 12, 2003 - 9:13 am UTC
Tom,
The database has been up 2 two days.
I have the WRAPS in 700 for each rollback segs (total 7), no shrinks. Do you think I shall increase the extent?
Thanks
March 12, 2003 - 9:28 am UTC
did you have any 1555's?
wraps are not bad, they are normal.
Snapshot too old
Vivek Sharma, March 13, 2003 - 3:04 am UTC
Dear Tom,
Very Good Input. Your Inputs have cleared many questions about rollback segments. Good work.
You say that wraps are not bad, they are normal. But Wraps can also cause Snapshot too old error. Am I correct ? So shall I avoid rollback segment to wrap and to do so, do i need to create more rollback segments or Increase the extent size of the rollback segments.
When do I consider increasing the no. of rollback segments ?
and when do i consider increasing the extent size.
Thanks in advance and Regards
Vivek
March 14, 2003 - 4:59 pm UTC
every rollback segment is going to wrap -- unless they are infinite in size.
wraps do not cause 1555's
1555's are caused by having rbs's that are too small. When they are too small they wrap faster then your longest running query takes to run.
You cannot avoid them wrapping.
You want to make sure they don't wrap TOO FAST.
You do that by permanently allocating then large enough so they take longer to wrap around then your queries do to execute. (minextents = more)
adding rbs's -- that would be another way (more rbs's to use, wrap less fast) but usually you add rbs's to reduce rbs contention (undo header gets and such).
Snapshot too old
Vivek Sharma, March 13, 2003 - 6:17 am UTC
Sorry Tom,
I have asked u the question for which u have already answered. To avoid wrapping, I need to increase the extent size.
So I have got the answer for this, but when should i consider increasing the no. of rollback segments.
My apologies.
Thanks and Regards
Vivek Sharma
Is the same true for Locally Managed UNDO Tablespaces ?(9i)
Devanshi Parikh, March 14, 2003 - 9:56 am UTC
By using UNDO_MANAGEMENT as AUTO and UNDO tablespace, we do do not control the size or the number of segments. How would Oracle figure out how many segments should be created and how many max extents should it have ? If it is not possible to extend(we truly reached a 100%), will it wrap into the other segment's extent ?
Could you please explain a little ?
Thanks,
March 15, 2003 - 8:25 am UTC
It figures out how many by watching concurrency -- as it goes up, it'll automagically add more, goes down -- takes them away.
As for how big -- the retention period you specify controls that -- it'll grow them until
o the retention period is satisfied
o you run out of space
which ever comes first.
I think I can answer devanshi parikh's question
Vivek Sharma, March 14, 2003 - 8:24 pm UTC
UNDO_RETENTION parameter takes care about for how much time the information be kept in the rollback segment.
Please correct if I am wrong
Vivek
Hats off to you Tom
A reader, June 19, 2003 - 3:41 pm UTC
Hi Tom,
I must sincerely appreciate the patience you have. With all the technical brilliance dont you at times think of all us users as *silly*. I mean this particular thread had so much of confusion created and all that was becuase we readers were making a fuss. You have answered all of them with such accurate reasoning and great patience. I really feel you are a boon to the ORACLE user community.
Thanks for all the help you extend.
Regards
A well-wisher
Space utilized by rollback segment
atul, October 09, 2003 - 1:27 pm UTC
Hi,
if following query give me output like :
select extents,wraps from v$rollstat where usn=3;
296 for extents, 302 for wraps
Only one rollback segment other than system is online.
Initial/next = 10M maxextents=1000
From output how can i know space utilized by rollback segment..
If we can't from this query then how to know that?
Thank,
Atul
October 09, 2003 - 6:11 pm UTC
look at dba_segments to see the space used by a rbs.
optsize
Atul, October 14, 2003 - 5:52 am UTC
Hi,
I read on this site that its not good to use optsize
parameter in rollback segment.
If i create rollback segment without optsize then also
i can shrink it back to whatever size i want??
So what are benifits by using optsize we get??
Why you don't use it,any reason??
Thank & Regards,
Atul
October 14, 2003 - 7:12 am UTC
in 9i, i just use AUM (automatic undo mgmt)
in 8i, i put every rbs in its own autoextend tablespace. when i wanted to shrink one -- i would simply drop it, shrink the datafile in that tablespace and recreate it.
I don't use it cause it kicks in when the system is busy and expires undo that others may well need. rather do it on saturday when the system wasn't busy and when people wouldn't need it as much.
it would be rare to actually shrink it -- i like to let them grow to be as big as they want to be for my system.
Brilliant as ever ..
Oracleo, January 22, 2004 - 7:34 am UTC
Just superb.
Brilliant as ever ..
Oracleo, January 22, 2004 - 7:34 am UTC
Just superb.
Rollback
READER, May 19, 2004 - 1:13 pm UTC
Tom ,
"Many transactions can be in one RBS/extent/block ".
Does it mean active transactions ?
How is this possible if a new transaction will never get allocated to any extent if there is any active transaction in it.
Pl help in getting this doubt clear .
Thanks
May 19, 2004 - 2:33 pm UTC
where did you read that -- about the block?
but anyway, a single RBS can have many active transactions
many active transactions can be in a single extent.
we will not WRAP into an extent that is still active -- but an active current extent that still has space will be used for many transactions at a time.
WRAP
A reader, July 29, 2004 - 6:39 am UTC
What is the negitive to have large extent size?, say NEXT=20M and MINEXTENTS=5, and I do not have the space issue.
July 29, 2004 - 11:52 am UTC
if you have 5 20m extents
vs
100 1m extents....
you could have a transaction that is using the "end of extent 1" in the 5x20 situation and has not committed -- if you try to enter extent 1 again (you used 2, 3, 4, 5 - going back into 1) you cannot. because of that transaction.
so you'll allocate another 20m extent.
if you had 100 1m extents, that same transaction would be in extent 19 or 20. As you advance from 100 back to 1, it would be "OK" and in fact -- all of the way up to 18 would be OK. It could well be that by the time you get to extent 19 -- that transaction did commit and you don't need to allocate anything new.
so, a large extent increased the unit of allocation and could potentially cause an extension to take place when a smaller extent size would not have.
Interesting Thread
sankar, October 29, 2004 - 1:14 pm UTC
old fashioned rollback segments
I have rollback segments with extent sizes at 10M
and min extents 2. Everytime a transaction spans across
the extents ( from extent1 to extent2 ) wraps is incremented. My question is what happens when you have a transaction that generated 13MB of rollback info and used 2 extents. Now the transaction is commited. The rollback segment has no active transactions. Next time another transaction comes in will start writing at extent 2 or at extent 1.
Mainly i want to know how does it affect having 10 extents of 1Mb versus 2 extents of 5MB ? I read the previous followup. I understood your explanation about how an active trans in extent 1 would cause the rbs to extend.
So is it always better to have bigger extent sizes ?
what is the downside of having bigger extent sizes?
October 29, 2004 - 4:59 pm UTC
next transaction will pick up at the current pointer in the rbs -- think of the rbs as being in a ring with a pointer pointing to "current", it just advances forward.
whether the 13m transaction committed or not -- the current pointer is in the same place -- it would not matter -- commit or not commit.
the example with the active transaction you refer to shows the opposite of
"is it always better to have bigger extent size", it shows a downside to bigger.
Reader
A reader, January 16, 2005 - 11:02 am UTC
"
Rollback is not managed as a "heap" (which is hard and slow to manage) but
rather as a high speed circular buffer which is very easy to manage.
"
If there are, for example 2 active TXs in an rbs extent.
Could you tell us how the blocks are allocated to these
2 TXs. ( as a linked list ? )
TX1: session1 update 1000 rows on table1. Not commit
TX2: session2 update 1000 rows on table2. Not commit
TX1: again session1 update 1000 rows on table3. Not commit
January 16, 2005 - 11:48 am UTC
in a circle -- sequentially. like a stack. each transaction would have their own "list" but their allocations would back from a stack.
An example to see if I understand RBS
Bob B, January 16, 2005 - 1:18 pm UTC
Simple hypothetical environment. 2 RBS (1, 2, 2) extents with 2 blocks each. 2 sessions (A, B) doing transactional work. A fourth session, D, will be used for
Inactive blocks in an RBS will be marked with '0'.
Active blocks in an RBS will be marked with the letter of the transaction using them.
Order of events
#0) RBSs brought online
RBS 1: 00
RBS 2: 00
#1) A needs 2 block for an update
RBS 1: AA
RBS 2: 00
#2) B needs 2 blocks for an insert
RBS 1: AA
RBS 2: BB
#3) C needs 2 block for a delete
RBS 1: AA
RBS 2: BB
RBS 3: CC <- added because next RBS (1) is still active
#4) A commits
RBS 1: 00
RBS 2: BB
RBS 3: CC
#5) A needs 2 blocks for an update
RBS 1: AA
RBS 2: BB
RBS 3: CC
Event #3 was when the RBS found an active transaction in the next RBS extent and thus had to auto-extend itself and insert a new extent (3) in the circular buffer. Event #5 was when the RBS found no active transactions in the next extent and thus went to the beginning of the circle.
If a cursor was opened before event #1 that queried the data updated by event #1 and was fetched from after event #5, we should get the ora-0155 because we would need the rollback in RBS 1 that was just overwritten.
January 16, 2005 - 1:30 pm UTC
I'm not following your naming convention.
2 RBS (1, 2, 2) extents with 2 blocks each "doesn't compute"
but if
RBS 1: 00
RBS 2: 00
is to represent a SINGLE rollback segement with two extents-- yes, #3 is correct assuming C was assigned to that rollback segment. And #4/5 are also accurate -- AA would have tried to go into extent 3 of that rollback segment, found it full and would have advanced into extent 1 which is now inactive.
rbs contention
shailu, July 26, 2007 - 4:44 pm UTC
Hey Tom,
I have read whole thread and it gave me very good understanding of rollback segments. I know this is the era of oracle 10g but still my company is using oracle8i. I know you would say strange!
This is what you said about rbs contention
<quote> adding rbs's -- that would be another way (more rbs's to use, wrap less fast) but usually you add
rbs's to reduce rbs contention (undo header gets and such).<quote>
I have collected some stats of my database from following query:-
SELECT CLASS, COUNT FROM V$WAITSTAT WHERE CLASS like '%undo%';
CLASS COUNT
save undo block 0
save undo header 0
system undo header 0
system undo block 0
undo header 1340009
undo block 977331
From this stats i think my database has serious rbs contention and i need to add more rollback segment...
Most important thing is that i already have 24 existing rollback segments all of them are of 2mb except two rbs which are big 341mb and 51mb...
can you please suggest something in this situation?
Thanks
shailu
July 27, 2007 - 9:15 am UTC
that is from the time the instance was started - impossible to say if you have an issue or not.
counts of waits - not useful
how long you waited - useful
rbs contention
Shailu, July 27, 2007 - 10:21 am UTC
Hello Tom,
Thank you very much for you response.
I am kind of confused here..you are saying that when a transaction starts it look for online undo segment and if it couldn't find space in that undo segmnent it waits and that is the wait time we need to consider.
Am i taking it right or wrong?
If right then where can i see how long is the wait time . Is it column seconds_in_wait in view v$session_wait..
I am sorry to bother you but i am just trying to understand rbs contention properly.
Thanks
July 27, 2007 - 3:20 pm UTC
no, i'm saying that count you are showing is the number of times someone waited SINCE THE INSTANCE WAS STARTED.
Let's say you started your database 3 months ago.
1* select 1340009/90/24/60/60 from dual
ops$tkyte%ORA9IR2> /
1340009/90/24/60/60
-------------------
.17232626
you have a wait every 5 seconds on average. big deal. Given the information you gave us, there is nothing indicating you do OR DO NOT have a problem here. A simple count of the number of times something has happened is virtually useless.
use statspack, monitor a representative 15-30 minute window, review the report with something that can work through it with you and explain what you are looking at.
Thank You Very Much
shailu, July 31, 2007 - 11:25 am UTC
relation between commit and wrap
karthik, August 06, 2007 - 2:12 am UTC
Tom,
I just tried to update a table having million of records where there is no other transaction going on(standalone).I could see wrap as 93 when extends as 113 and there is no commit statement.I just want to see how much my undo segment can grow when no commit occurs.
Q1,You quoted earlier in this article "wraps" is "The WRAPS column indicate the number of times the rollback segment wrapped to another extent to serve the transaction". Here i dont commit and expect wraps to be zero and only extends should be non zero.So does that mean EXTEND action also indirectly increments WRAPS
Q2,Does this WRAP value indicates "switching to new extents"
irrespective of RE-USE or new extent allocation.
Q3,If q2 is correct then how can i determine the exact value of wrap in terms of "reuse of circular extents"
August 06, 2007 - 11:50 am UTC
why do you expect zero.
you update a million rows
that'll generate undo for a million rows
it is highly unlikely the undo for a million rows would fit in an extent - it could, but the extents would be HUGE.
You wrapped from one extent to the next to the next to the next.... I don't know why you think this should not or would not be true?
wraps is not wrapping around the circular queue
http://docs.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2174.htm#sthref4159 that view can be used to understand your rate of undo generation (which will in turn describe how many times you might be spinning around the circular queue)
wrap
karthik, August 06, 2007 - 3:20 pm UTC
1.Ok.If "wrap" is not wrapping around in the circular buffer then how can we differentiate "wraps" and "extends".I thought wrap is incremented for circular reuse of extents and extends are incremented for "inserting" an extent into the circular buffer.
2.Also you refered to v$undostat to find out the "spinning" ones.Can you please explain this.is it EXPBLKREUCNT
which counts for stealing the expired blocks from the same rollback segment.
3.UNXPSTEALCNT-Number of attempts to obtain undo space by stealing unexpired extents from other transactions.
---is that possible to obtain an unexpired extent(active one)?under what condition.
Atlast,is there a problem with mozilla browser in writing a review.The last updated time for this question updated nearly after 12 hours(once when you replied)
August 07, 2007 - 10:16 am UTC
1) you'll need to be more precise in your question.
a transaction wraps from one extent to another - that is what we are counting.
a rollback/undo segment might extend, allocate another extent (you count extents to see that)
2) all undo will "spin", I'm not sure what you are looking for really.
You have undo of a certain size
You generate so much undo per unit of time.
simple math tells you how often you are likely to spin around (if you generate 1mb of undo per minute and you have 10mb of undo.... then every 10 minutes approximately, you'll spin)
3) you cannot take active extents from undo and relocate them - no.
there is no problem with the browser, I'm the one that sets the last updated time and until I update it, it hasn't be "last updated"
wrap
karthik, August 07, 2007 - 4:21 pm UTC
I had an illusion that "wrap" is wrapping around circular queue.Thanks for clear answer.
Can i sum up wrap and extends in an ideal condition where
only one fixed size transaction is using the rollback segment to set the max no. of extents in a rollback segment.
wont it tell me whether the minimum extents which i have set is right or not (if i see "extends" grow up)
How to find out which transaction is preventing my transaction of interest in a particular RBS by actively using it and causing my RBS to extend?We have a OLTP system where users often start small transaction and leave the terminal without commit.Finally when we run EOD, we have to extend a lot because of these tiny uncommited transactions.
August 10, 2007 - 2:55 pm UTC
v$transaction, query that to find what undo segment the transaction has been using and how long it has been using it.