Our database environment:
OS version: 2.6.18-194.el5 #1 SMP Tue Mar 16 21:52:39 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux
DB version: 11.2.0.2
CPU_COUNT = 64
I had used some internal script pari which uses gv$px_session view for finding number of parallel slave processes.
FAST_START_PARALLEL_ROLLBACK = LOW
From the Oracle documentation remember:
FALSEParallel rollback is disabled
LOWLimits the maximum degree of parallelism to 2 *CPU_COUNT
HIGHLimits the maximum degree of parallelism to 4 *CPU_COUNT
(http://docs.oracle.com/cd/E14072_01/server.112/e10820/initparams083.htm)
ADMIN@MYDB:MYDB2> @pari
INSTANCE USERNAME OSUSER ACTIVE INACTIVE SES_COUNT PERCENT
-------------------- ------------------------- --------------- --------- --------- --------- -------
MYDB1-MYDB1 ODI MYUSER 13 %5,07
oracle 12 116 128 %50
MYDB2-MYDB2 ODI MYUSER 14 11 31 %12,1
INSTANCE Username QC/Slave SID SERIAL OSUSER HASH_VALUE AUDSID Re Ac MACHINE STATUS
-------------------- ------------------------- ---------- ------ ------ --------------- ------------ ------------ -- -- ------------ ----------
MYDB1-MYDB1 ODI QC 1059 18677 MYUSER 3974800174 10212110 TURKCELL\T ACTIVE
QC 721 1 oracle 0 0 MYDB1.bi ACTIVE
QC 721 1 0 0 MYDB2.bi ACTIVE
MYDB2-MYDB2 ADMIN QC 1061 1393 MYUSER2 3551939252 10221613 TURKCELL\T ACTIVE
INSTANCE PARALLEL PROCESS INFO
-------------------- ----------------------------------------
MYDB2-MYDB2 USED : 31 AVAILABLE : 225 TOTAL : 256
MYDB1-MYDB1 USED : 141 AVAILABLE : 115 TOTAL : 256
ADMIN@MYDB:MYDB2>
ADMIN@MYDB:MYDB2> select * from gV$FAST_START_TRANSACTIONS;
INST_ID USN SLT SEQ STATE UNDOBLOCKSDONE UNDOBLOCKSTOTAL PID CPUTIME PARENTUSN PARENTSLT PARENTSEQ XID PXID RCVSERVERS
--------- --------- --------- --------- ------------------------------------------------ -------------- --------------- --------- --------- --------- --------- --------- ---------------- ---------------- ----------
1 71 112 9176 RECOVERING 5211 159526 120 137 167 27 4681 47007000D8230000 A7001B0049120000 126
1 150 21 11387 RECOVERING 4019 155416 85 137 167 27 4681 960015007B2C0000 A7001B0049120000 1
1 271 25 1662 RECOVERING 2397 148283 89 137 167 27 4681 0F0119007E060000 A7001B0049120000 1
1 42 41 37080 RECOVERED 28 28 10 2A002900D8900000 128
4 rows selected.
-------------
After setting fast_start_parallel_rollback value from 'LOW' to 'FALSE'.
ADMIN@MYDB:MYDB2> alter system set fast_start_parallel_rollback=FALSE scope=both sid=*;
ADMIN@MYDB:MYDB2> select * from GV$FAST_START_TRANSACTIONS;
INST_ID USN SLT SEQ STATE UNDOBLOCKSDONE UNDOBLOCKSTOTAL PID CPUTIME PARENTUSN PARENTSLT PARENTSEQ XID PXID RCVSERVERS
--------- --------- --------- --------- ------------------------------------------------ -------------- --------------- --------- --------- --------- --------- --------- ---------------- ---------------- ----------
1 71 112 9176 RECOVERING 4 153858 1 47007000D8230000 0
1 271 25 1662 RECOVERING 4 145586 1 0F0119007E060000 0
1 150 21 11387 RECOVERING 5 151280 1 960015007B2C0000 0
1 42 41 37080 RECOVERED 28 28 10 2A002900D8900000 128
4 rows selected.
ADMIN@MYDB:MYDB2> @pari
INSTANCE USERNAME OSUSER ACTIVE INACTIVE SES_COUNT PERCENT
-------------------- ------------------------- --------------- --------- --------- --------- -------
MYDB1-MYDB1 ODI MYUSER 13 %5,07
MYDB2-MYDB2 ODI MYUSER 14 11 31 %12,1
INSTANCE Username QC/Slave SID SERIAL OSUSER HASH_VALUE AUDSID Re Ac MACHINE STATUS
-------------------- ------------------------- ---------- ------ ------ --------------- ------------ ------------ -- -- ------------ ----------
MYDB1-MYDB1 ODI QC 1059 18677 MYUSER 3974800174 10212110 TURKCELL\T ACTIVE
MYDB2-MYDB2 ADMIN QC 1061 1393 MYUSER2 3551939252 10221613 TURKCELL\T ACTIVE
INSTANCE PARALLEL PROCESS INFO
-------------------- ----------------------------------------
MYDB2-MYDB2 USED : 31 AVAILABLE : 225 TOTAL : 256
MYDB1-MYDB1 USED : 13 AVAILABLE : 243 TOTAL : 256
ADMIN@MYDB:MYDB2>
-------------
The recovery is not so fast, so I changed the value of the parameter again.
After setting fast_start_parallel_rollback value from 'FALSE' to 'HIGH'.
Despite the huge number of parallel processes overhead, the outcome is remarkable, that you can see it is faster.
ADMIN@MYDB:MYDB2> alter system set fast_start_parallel_rollback=HIGH scope=both sid=*;
ADMIN@MYDB:MYDB2> select * from GV$FAST_START_TRANSACTIONS;
INST_ID USN SLT SEQ STATE UNDOBLOCKSDONE UNDOBLOCKSTOTAL PID CPUTIME PARENTUSN PARENTSLT PARENTSEQ XID PXID RCVSERVERS
--------- --------- --------- --------- ------------------------------------------------ -------------- --------------- --------- --------- --------- --------- --------- ---------------- ---------------- ----------
1 71 112 9176 RECOVERING 1272 16970 80 24 167 27 4681 47007000D8230000 A7001B0049120000 233
1 150 21 11387 RECOVERING 651 20408 81 24 167 27 4681 960015007B2C0000 A7001B0049120000 1
1 271 25 1662 RECOVERING 909 11312 82 24 167 27 4681 0F0119007E060000 A7001B0049120000 1
1 42 41 37080 RECOVERED 28 28 10 2A002900D8900000 128
ADMIN@MYDB:MYDB2> @pari
INSTANCE USERNAME OSUSER ACTIVE INACTIVE SES_COUNT PERCENT
-------------------- ------------------------- --------------- --------- --------- --------- -------
MYDB1-MYDB1 ODI MYUSER 13 %5,07
oracle 243 0 243 %94,92
MYDB2-MYDB2 ODI MYUSER 24 0 30 %11,71
INSTANCE Username QC/Slave SID SERIAL OSUSER HASH_VALUE AUDSID Re Ac MACHINE STATUS
-------------------- ------------------------- ---------- ------ ------ --------------- ------------ ------------ -- -- ------------ ----------
MYDB1-MYDB1 ODI QC 1059 18677 MYUSER 0 10212110 TURKCELL\T ACTIVE
QC 721 1 oracle 0 0 MYDB1.bi ACTIVE
QC 721 1 0 0 MYDB2.bi ACTIVE
MYDB2-MYDB2 ADMIN QC 1061 1393 MYUSER2 3551939252 10221613 TURKCELL\T ACTIVE
INSTANCE PARALLEL PROCESS INFO
-------------------- ----------------------------------------
MYDB1-MYDB1 USED : 256 AVAILABLE : 0 TOTAL : 256
MYDB2-MYDB2 USED : 30 AVAILABLE : 226 TOTAL : 256
ADMIN@MYDB:MYDB2>
No comments:
Post a Comment