Home > tips > fast_start_parallel_rollback recovery effect

fast_start_parallel_rollback recovery effect


I had a chance to see the effect of recovery while changing the fast_start_parallel_rollback parameter values.

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:

  • FALSE Parallel rollback is disabled
  • LOW Limits the maximum degree of parallelism to 2 * CPU_COUNT
  • HIGH Limits 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>
Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Don Charisma

because anything is possible with Charisma

Carol no Mundo!

Aventuras de uma intercambista a trabalho pelo mundo!

Blog do PHP

Oracle DBA for rookies!

nimaidba

Welcome to the world of Oracle with me....

Tech

News and reviews from the world of gadgets, gear, apps and the web

WordPress.com

WordPress.com is the best place for your personal blog or business site.

Gurcan Orhan's ODI and DWH Blog

Some words about Oracle Data Integrator and Data Warehousing.

%d bloggers like this: