About used blocks in the answer
Om Lalwani, October 12, 2001 - 5:54 pm UTC
I have read the answer and have got good idea as to what
cn be done to make prevent rollback segment error .
Can you please explain how should I interpret USED-BLKS
in the Query mentioned.What is the relation between the
no of extents ,used_blocks and the size of the rollback
segment.
Also you have mentioned that you make use of more than
one segment to achieve big results.
With
SET ROLLBAK SEGMENT R1 FOR LARGE_RBS
OPTION WE CAN SPCIFY ONLY 1 ROLLBACK SEGMENT.Therefore
how can I make sure that for my current BIG transaction
more than 1 Rollback segment should be used.
Warm Regards,
Om
October 12, 2001 - 6:47 pm UTC
An extent is made up of blocks. Say you have 640k extents and an 8k blocksize. You have 80 blocks / extent. If your delete will need 8,000 blocks -- you need 10 extents at least in your RBS to handle it.
I mentioned I made use of more then one rollback segment -- period. I didn't say "to achieve big results".
You can only use 1 rbs/ transaction. Period.... Well -- if you PARTITION the data and do a parallel delete -- each thread/process doing the delete can actually use a different RBS.
used_ublk is negative for some transactions
Sameer Utrankar, October 16, 2001 - 11:00 am UTC
When I query v$transaction to see how current transactions are using Rollback Segments, I sometimes transactions with used_ublk < 0. What does that mean ?
October 16, 2001 - 7:07 pm UTC
Rolling back to a savepoint in 8i and before can cause negative values here.
ops$tkyte@ORA717DEV.US.ORACLE.COM> create table t ( x int );
Table created.
ops$tkyte@ORA717DEV.US.ORACLE.COM> savepoint a;
Savepoint created.
ops$tkyte@ORA717DEV.US.ORACLE.COM> insert into t values ( 1 );
1 row created.
ops$tkyte@ORA717DEV.US.ORACLE.COM> rollback to a;
Rollback complete.
ops$tkyte@ORA717DEV.US.ORACLE.COM> insert into t values ( 1 );
1 row created.
ops$tkyte@ORA717DEV.US.ORACLE.COM> rollback to a;
Rollback complete.
ops$tkyte@ORA717DEV.US.ORACLE.COM>
ops$tkyte@ORA717DEV.US.ORACLE.COM> select used_ublk
2 from v$transaction
3 where addr = ( select taddr
4 from v$session
5 where sid = ( select sid
6 from v$mystat
7 where rownum = 1 ) )
8 /
USED_UBLK
----------
-1
that behavior is corrected in 9i
Reader
Reader, October 16, 2001 - 8:28 pm UTC
Tom,
"
An extent is made up of blocks. Say you have 640k extents and an 8k blocksize. You have 80 blocks / extent. If your delete will need 8,000 blocks -- you need 10 extents at least in your RBS to handle it.
"
Is this the rule of thumb to have at least 1/10 the size of
undo?
"
You can only use 1 rbs/ transaction.
"
Is there a downside in configuring say 4 concurrent TXs
per RBS
Thanks
October 16, 2001 - 8:45 pm UTC
No -- its not the rule of thumb (and how i hate rules of thumb).
It was simply an example with numbers that were easy to divide/multiply with.
When I said "you can only use 1 rbs/transaction" I meant that YOUR transaction can only use 1 rbs -- it cannot span an RBS.
It is very very very common to have many (dozens) of transactions all happily using the same rbs concurrently.
create rollback segment statment
olaf, October 17, 2001 - 10:01 am UTC
Tom, to create rollback segmemt you have recommended early:
create tablespace RBS_01 datafile '..' size 25064 K reuse autoextend on maxsize 100 MB extent management local uniform size 1M;
create rollback segment rbs_01 storage (minextents 20 maxextents 255) tablespace RBS_01;
I don't undestand the use of maxextents clause hier. I think Oracle should ignore this for LMT's.
October 17, 2001 - 11:31 am UTC
It does (well, it appears to limit them to 32k max extents, not 2billion like other segment types) - and in this case, the filesize is the gating factor (we could never get 255 extents since the maxsize is 100m)
It is not relevant in this case because the file will stop it from exceeding 100 extents.
Olaf, October 17, 2001 - 11:43 am UTC
What is with minextents hier? Will Oracle create 20X1MB extents by creating rollback segment?
October 17, 2001 - 12:42 pm UTC
Yes, it will create 20, 1 meg extents.
Rollback segments
atul, May 26, 2003 - 11:42 am UTC
Hi Tom,
We are going to upgrade database from 8.1.6 to 8.1.7
by running upgrade script...
Before running script i want to check is there sufficient rollback space ...,
What is the best way to do that??
Also what other space related precautions i have to take?
Thanks.
atul
May 26, 2003 - 12:36 pm UTC
You should read the upgrade readmes, see what they say as far as "minimums" go and then query the data dictionary to see that you have that much space.
If you observe the documented limits -- in the OS specific docs for the upgrade, that is what you need to do.
undo segs vs rollback segs?
trinh, May 27, 2003 - 12:20 am UTC
Statspack for 9i, there are shown two catagories:
1)Rollback Segment Stats for DB: ....
2)Undo Segment Stats for DB: ....
Why are there the two? I thought they should be the same?
Thanks
May 27, 2003 - 7:37 am UTC
undo segments have different metrics then rollback segments. undo segments are sized and grow based on a retention period which is just a suggestion. So, an undo segment might "expire" an extent before its time -- that is something you want to be aware of (that you are expiring extents before their time -- you have insufficient space for the retention period you specified). A rollback segment would have no such metric for example.
Same things, measured with different yardsticks -- to different metrics.