|
ROLLBACK SEGMENT
|
Hello everyone. In this issue, I will analyze if transactions can be executed
by each rollback segment for the number of times specified by
CEIL(TRANSACTIONS/TRANSACTIONS_PER_ROLLBACK_SEGMENT)
I use ORACLE8.1.6 for NT and set DB_BLOCK_SIZE to 2K to analyze.
First of all, I examine the number of TRANSACTIONS_PER_ROLLBACK_SEGMENTS that I can
specify at the maximum.
After I set TRANSACTIONS_PER_ROLLBACK_SEGMENT to 55 and start Oracle, following
error message appears.
ORA-02093: TRANSACTIONS_PER_ROLLBACK_SEGMENT(55) more than maximum possible (21)
This error message suggests that TRANSACTIONS_PER_ROLLBACK_SEGMENT can be specified
up to 21. Based on this fact, I start today's analysis.
2-1. Set TRANSACTIONS_PER_ROLLBACK_SEGMENT to 4.
|
SVRMGR> SHOW PARAMETER TRANSACTIONS_PER_ROLLBACK_SEGMENT
NAME TYPE VALUE
----------------------------------- -------- -------
transactions_per_rollback_segment integer 4
|
The result indicates that one rollback segment can handle four transactions
at a time.
2-2. Create a table by using the following script.
|
|
----------------------------------POPSQL-------------------------------------
CONNECT SCOTT/TIGER
LOOP (l=0 ; l<50 ; l++)
REM Variable \|\ is converted to a value. Create fifty tables (A_0 to A_49)
SQL CREATE TABLE A_\l\ AS SELECT * FROM EMP;
ENDLOOP
-----------------------------------------------------------------------------
|
2-3. Create fifty concurrent transactions by using the following two scripts,
and then write these transactions to one rollback segment (rb_find50)
trun50.call executes trun50.job fifty times in the background. At the same time, trun50.call
passes variable l.
|
|
----------POPSQL script name trun50.call---------------
CONNECT SCOTT/TIGER
LOOP (l=o ; l<50 ; l++)
REM SYSTEM command is shell startup command
REM popsql is a binary program to start POPSQL program
REM Specify & to execute in the background
SYSTEM popsql trun50.job \|\ &
ENDLOOP
-------------------------------------------------------
|
trun50.job receives a variable l, and issues UPDATE statement to table A_0 to
A_49. Select rollback segment rb_find50.
|
|
---------------------------POPSQL script name trun50.job--------------------------
REM Receive a variable that is passed by argument
GETOPT l
CONNECT SCOTT/TIGER
REM PLSQL command executes PLSQL
REM Use specified rollback segment for DBMS_TRANSACTION. USE_ROLLBACK_SEGMENT
REM packages. (rb_find50)
REM Behave in the same way as SET TRANSACTION USE ROLLBACK SEGMENT rb_find50; does
PLSQL DBMS_TRANSACTION. USE_ROLLBACK_SEGMENT ('rb_find50');
REM UPDATE A_0 SET ENAME = 'KOBA';
SQL UPDATE A_\|\ SET ENAME = 'KOBA';
REM OERR is a delimiter to get Oracle error. If 0, process completes properly
REM With MESSAGE command, \|\ and \OERR\ are converted to values and are displayed
MESSAGE UPDATE A_\|\ SET ENAME = 'KOBA'; OERR=\OERR\
REM Sleep for 100 seconds to maintain the transaction
SLEEP 100
----------------------------------------------------------------------------------
|
2-4. Execute fifty transactions at a time. Let all of
fifty transactions use rollback segment rb_find50.
|
|
C:\> popsql trun50.call
|
Following is the result of MESSAGE command by script trun50.job.
|
|
First 21 transactions terminate successfully.
UPDATE A_0 SET ENAME = 'KOBA'; OERR=0
UPDATE A_2 SET ENAME = 'KOBA'; OERR=0
UPDATE A_1 SET ENAME = 'KOBA'; OERR=0
UPDATE A_3 SET ENAME = 'KOBA'; OERR=0
UPDATE A_4 SET ENAME = 'KOBA'; OERR=0
UPDATE A_5 SET ENAME = 'KOBA'; OERR=0
UPDATE A_9 SET ENAME = 'KOBA'; OERR=0
UPDATE A_11 SET ENAME = 'KOBA'; OERR=0
UPDATE A_10 SET ENAME = 'KOBA'; OERR=0
UPDATE A_13 SET ENAME = 'KOBA'; OERR=0
UPDATE A_15 SET ENAME = 'KOBA'; OERR=0
UPDATE A_18 SET ENAME = 'KOBA'; OERR=0
UPDATE A_19 SET ENAME = 'KOBA'; OERR=0
UPDATE A_12 SET ENAME = 'KOBA'; OERR=0
UPDATE A_8 SET ENAME = 'KOBA'; OERR=0
UPDATE A_6 SET ENAME = 'KOBA'; OERR=0
UPDATE A_7 SET ENAME = 'KOBA'; OERR=0
UPDATE A_17 SET ENAME = 'KOBA'; OERR=0
UPDATE A_14 SET ENAME = 'KOBA'; OERR=0
UPDATE A_16 SET ENAME = 'KOBA'; OERR=0
UPDATE A_22 SET ENAME = 'KOBA'; OERR=0
Remaining 29 transactions terminate with ORA-1554.
UPDATE A_21 SET ENAME = 'KOBA'; OERR=1554
UPDATE A_44 SET ENAME = 'KOBA'; OERR=1554
UPDATE A_24 SET ENAME = 'KOBA'; OERR=1554
UPDATE A_26 SET ENAME = 'KOBA'; OERR=1554
UPDATE A_35 SET ENAME = 'KOBA'; OERR=1554
UPDATE A_33 SET ENAME = 'KOBA'; OERR=1554
UPDATE A_38 SET ENAME = 'KOBA'; OERR=1554
UPDATE A_37 SET ENAME = 'KOBA'; OERR=1554
UPDATE A_42 SET ENAME = 'KOBA'; OERR=1554
UPDATE A_41 SET ENAME = 'KOBA'; OERR=1554
UPDATE A_20 SET ENAME = 'KOBA'; OERR=1554
UPDATE A_46 SET ENAME = 'KOBA'; OERR=1554
UPDATE A_45 SET ENAME = 'KOBA'; OERR=1554
UPDATE A_34 SET ENAME = 'KOBA'; OERR=1554
UPDATE A_39 SET ENAME = 'KOBA'; OERR=1554
UPDATE A_43 SET ENAME = 'KOBA'; OERR=1554
UPDATE A_28 SET ENAME = 'KOBA'; OERR=1554
UPDATE A_27 SET ENAME = 'KOBA'; OERR=1554
UPDATE A_29 SET ENAME = 'KOBA'; OERR=1554
UPDATE A_30 SET ENAME = 'KOBA'; OERR=1554
UPDATE A_47 SET ENAME = 'KOBA'; OERR=1554
UPDATE A_49 SET ENAME = 'KOBA'; OERR=1554
UPDATE A_23 SET ENAME = 'KOBA'; OERR=1554
UPDATE A_40 SET ENAME = 'KOBA'; OERR=1554
UPDATE A_48 SET ENAME = 'KOBA'; OERR=1554
UPDATE A_31 SET ENAME = 'KOBA'; OERR=1554
UPDATE A_25 SET ENAME = 'KOBA'; OERR=1554
UPDATE A_36 SET ENAME = 'KOBA'; OERR=1554
UPDATE A_32 SET ENAME = 'KOBA'; OERR=1554
ORA-01554: out of transaction slots in transaction tables
Cause: Too many concurrent transaction
|
Remember I set TRANSACTIONS_PER_ROLLBACK_SEGMENT to 4. The result of 2-4.
in fact suggests that twenty-one transactions are specified. Twenty-one transactions also match
the number of TRANSACTIONS_PER_ROLLBACK_SEGMENT that can be specified at the maximum.
As far as I analyze, one rollback segment can handle up to twenty-one transactions at a time,
regardless of TRANSACTIONS_PER_ROLLBACK_SEGMENT value.
This TRANSACTIONS_PER_ROLLBACK_SEGMENT parameter therefore is used to determine the number of
public rollback segments to start.
That's it for today. See you next week.
|
|
Ora! Ora! Oracle