fast_start_parallel_rollback与v$fast_start_servers和v$fast_start_transactions

oracle并行事务回滚
1,目的：提升smon在instance recovery的回滚速度
2,根据回滚的数据块大小,oracle smon自动决定启用多个个并行度进行回滚

/******相关参数****/
fast_start_parallel_rollback
 1,取值有3种：false,low,high
 2,各值含义：false  ---禁用并行回滚功能
             low    ---并行回滚最大为2*cpu_count
             high   ---并行回滚最大为4*cpu_count
 3,此参数适用于system crash之前未执行完毕的并行dml或并行ddl
 4,决定并行回滚的并行度  
 5,此参数应用前滚完成之后回滚阶段即对于undo block操作
 
 
v$fast_start_servers
  1,查看实行并行事务恢复的恢复子进程的工作状态
  2,子进程状态：idle,recovering
    已应用的undo block个数
    事务id
    子进程的进程id  
   
v$fast_start_transactions
  1,查看恢复的进度
  2,几个重要的列:
           usn  --恢复事务的undo segment number
           state ---恢复事务的状态：即正在恢复，恢复完成，未恢复
           pid   ---恢复事务的进程id
           udnoblocksdone --已应用的undo block个数
           undoblockstotal --总共的undo block个数
           xid  ---事务id
           parentusn --并行dml事务的父事务的undo segment number    
          
          
/***********测试工作*****************/            

------------会话1--------------------
--1,在一个会话大量insert一个表,不提交

-----------会话2-------------------
--2,在另一会话shutdown abort

--3,关库并startup mount

--4,alter database open

-----------会话3------------------
--5,另启一会话,查询v$fast_start_transactions及v$fast_start_servers视图在变化

---刚开始没有是因为oracle rollforward在应用redo,而此操作与undo有关
SQL> select * from v$fast_start_servers;

no rows selected

SQL> select * from v$fast_start_transactions;

no rows selected

SQL> /

no rows selected

SQL> /

no rows selected

SQL> /

no rows selected

SQL> /

no rows selected

SQL> /

no rows selected

----这下有数据了
SQL> select * from v$fast_start_servers;

STATE                  UNDOBLOCKSDONE        PID XID
---------------------- -------------- ---------- ----------------
RECOVERING                       1243         21 06001400F5030000

---并行恢复事务进度
SQL> select * from v$fast_start_transactions;

       USN        SLT        SEQ STATE                            UNDOBLOCKSDONE

---------- ---------- ---------- -------------------------------- --------------

UNDOBLOCKSTOTAL        PID    CPUTIME  PARENTUSN  PARENTSLT  PARENTSEQ
--------------- ---------- ---------- ---------- ---------- ----------
XID              PXID             RCVSERVERS
---------------- ---------------- ----------
         6         20       1013 RECOVERING                                 2072

           6137         21         10          0          0          0
06001400F5030000 0000000000000000          1

SQL>

--定位上述执行并行恢复的进程
SQL> select addr,pid,spid,program from v$process where pid=21;
 
ADDR                    PID SPID                     PROGRAM
---------------- ---------- ------------------------ ----------------------------------------------------------------
000007FF624D7BC8         21 1132                     ORACLE.EXE (SHAD)

---禁用共享服务器模式
SQL> alter system set dispatchers='';
 
System altered
 
SQL> show parameter dispa
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dispatchers                          string     
max_dispatchers                      integer    

---重启后发现shad字样的进程还在,说明此进程和是否共享或专有服务器模式无关
SQL> select addr,pid,spid,program from v$process where program like 'ORACLE.EXE (SHAD)';
 
ADDR                    PID SPID                     PROGRAM
---------------- ---------- ------------------------ ----------------------------------------------------------------
000007FF624D3A08         17 3260                     ORACLE.EXE (SHAD)
000007FF624DBD88         25 7864                     ORACLE.EXE (SHAD)
000007FF624DEED8         28 5216                     ORACLE.EXE (SHAD)

/****经分析，shad进程即用户会话，每连接一个会话，产生一个服务器进程*******/
SQL> select addr,pid,spid,program from v$process where program like 'ORACLE.EXE (SHAD)';
 
ADDR                    PID SPID                     PROGRAM
---------------- ---------- ------------------------ ----------------------------------------------------------------
000007FF624D3A08         17 3260                     ORACLE.EXE (SHAD)
000007FF624DBD88         25 7864                     ORACLE.EXE (SHAD)
000007FF624DEED8         28 5216                     ORACLE.EXE (SHAD)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-758205/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-758205/