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)