|
ROLLBACK SEGMENT
|
Hello everyone. In this issue, I will talk about
TRANSACTION_PER_ROLLBACK_SEGMENT.
TRANSACTION_PER_ROLLBACK_SEGMENT
TRANSACTION_PER_ROLLBACK_SEGMENT specifies the number of concurrent
transactions you expect each rollback segment to have to handle. The minimum
number of rollback segments acquired at startup is TRANSACTIONS divided
by the value for this parameter. For example, if TRANSACTIONS is 101 and
this parameter is 10, then the minimum number of rollback segments acquired would
be the ratio 101/10, rounded up to 11. You can acquire more rollback segments by
naming them in the parameter ROLLBACK_SEGMENTS.
Based on the manual above, I will analyze the following two points.
1. How CEIL (TRANSACTIONS/TRANSACTIONS_PER_ROLLBACK_SEGMENT) is related to
the number of ONLINE public rollback segments?
(If CEIL (A), then the smallest integer after A)
2. Is it possible to process concurrent transactions in each rollback segment
for the number specified by TRANSACTIONS_PER_ROLLBACK_SEGMENT?
1.
1-1. Create fifty public rollback segments. As always, I use POPSQL script.
|
----------------------------POPSQL script----------------------------
CONNECT SYSTEM/MANAGER
LOOP (l=1; l<=50; l++)
REM deploy variable of \|\. Create rb_pub1, rb_pub2...rb_pub50
SQL create public rollback segment rb_pub\|\
storage (initial 10k next 10k minextents 2)
tablespace rbs;
ENDLOOP
---------------------------------------------------------------------
|
1-2. Check initialization parameter value.
|
|
SVRMGR> SHOW PARAMETER TRANSACTIONS
NAME TYPE VALUE
--------------------------------- -------- ------
transactions integer 126
transactions_per_rollback_segment integer 4
|
Private rollback segment is launched as specified below.
|
|
SVRMGR> SHOW PARAMETER ROLLBACK_SEGMENTS
NAME TYPE VALUE
----------------- ---------------- -----------------------------
rollback_segments character string rb_test1, rb_test2, rb_find50
|
(rb_test, rb_test2, rb_find50 -> specify three rollback segments)
Specify so that up to 100 rollback segments can be online.
|
|
SVRMGR> SHOW PARAMETER MAX_ROLLBACK_SEGMENTS
NAME TYPE VALUE
------------------------- --------- --------
max_rollback_segments integer 100
|
1-3. Launch database under this condition, and then use SELECT statement below
to check the status of rollback segment.
|
|
SELECT SEGMENT_NAME, OWNER, STATUS FROM DBA_ROLLBACK_SEGS ;
SEGMENT_NAME OWNER STATUS
--------------------------------------
SYSTEM SYS ONLINE
RB_TEST1 SYS ONLINE
RB_TEST2 SYS ONLINE
RB_FIND50 SYS ONLINE
RB_PUB1 PUBLIC ONLINE |
RB_PUB2 PUBLIC ONLINE |
RB_PUB3 PUBLIC ONLINE |
RB_PUB4 PUBLIC ONLINE |
. . . |
. . . |<- RB_PUB1 to RB_PUB29 are ONLINE
. . . |
RB_PUB27 PUBLIC ONLINE |
RB_PUB28 PUBLIC ONLINE |
RB_PUB29 PUBLIC ONLINE |
RB_PUB30 PUBLIC OFFLINE |
RB_PUB31 PUBLIC OFFLINE |
RB_PUB32 PUBLIC OFFLINE |
. . . |
. . . |<- RB_PUB30 to RB_PUB50 are OFFLINE
. . . |
RB_PUB48 PUBLIC OFFLINE |
RB_PUB49 PUBLIC OFFLINE |
RB_PUB50 PUBLIC OFFLINE |
ONLINE OFFLINE
private rollback segment 3 0
public rollback segment 29 21
----------------------------------------------------
Total 32 21
|
Now, determine the value.
CEIL(TRANSACTIONS/TRANSACTIONS_PER_ROLLBACK_SEGMENT)= CEIL (126/4)=CEIL(31.5)=32
You can see that this value matches the total number of online rollback segments.
CEIL(TRANSACTIONS/TRANSACTIONS_PER_ROLLBACK_SEGMENT)
- number of rollback segments specified by rollback_segmens
=32-3=29
Twenty-nine public rollback segments are therefore brought ONLINE.
Now, I drop the public rollback segments.
(Be sure to bring public rollback segments offline beforehand.)
|
|
--------------------POPSQL script--------------------
CONNECT SYSTEM/MANAGER
LOOP (l=1; l<=50; l++)
SQL alter rollback segment rb_pub\|\ offline;
SQL drop rollback segment rb_pub\|\;
ENDLOOP
-----------------------------------------------------
|
That's it for today. In the next issue, I will cover the second one. See you nect week.
|
|
Ora! Ora! Oracle