fast_start_parallel_rollback引數的一點測試

warehouse發表於2009-11-21

oracle從8i就引入的引數fast_start_parallel_rollback旨在解決由於session或者程式中斷從而縮短回滾大事務所需要的時間問題,不過透過測試發現當fast_start_parallel_rollback=low時觀察不到oracle啟動多個paralle process,當fast_start_parallel_rollback=high時oracle確實啟動了4×cpu_count個parallel process,但是幾乎觀察不到有多個process paralle來恢復事務,也許被恢復的事務還不夠大、所需的時間還不夠長,似乎不是;也許oracle認為使用smon一個程式恢復時間更快?

[@more@]

C:>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on 星期五 11月 20 22:44:17 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.


連線到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select sid from v$mystat where rownum=1;

SID
----------
153
SQL> select sid,serial#,paddr,status from v$session where sid=153;

SID SERIAL# PADDR STATUS
---------- ---------- -------- --------
153 26 6FA4BA1C ACTIVE
SQL> select pid,spid from v$process where addr='6FA4BA1C';

PID SPID
---------- ------------
13 1496
SQL> select count(*) from t;

COUNT(*)
----------
11540

SQL> insert into t select * from t;

已建立11540行。

SQL> insert into t select * from t;

已建立23080行。

SQL> insert into t select * from t;

已建立46160行。

SQL> insert into t select * from t;

已建立92320行。

SQL> insert into t select * from t;

已建立184640行。

SQL> insert into t select * from t;

已建立369280行。

SQL> select xid from v$transaction;

XID
----------------
08002D000E030000
--===========================================
session 2:
SQL> alter system kill session '153,26';

系統已更改。

SQL> select sid,serial#,paddr,status from v$session where sid=153;

SID SERIAL# PADDR STATUS
---------- ---------- -------- --------
153 26 6FA7F358 KILLED

SQL> select pid,spid from v$process where addr='6FA4BA1C';

PID SPID
---------- ------------
13 1496
SQL> desc v$fast_start_transactions
名稱 是否為空? 型別
----------------------------------------- -------- ----------------------------

USN NUMBER
SLT NUMBER
SEQ NUMBER
STATE VARCHAR2(16)
UNDOBLOCKSDONE NUMBER
UNDOBLOCKSTOTAL NUMBER
PID NUMBER
CPUTIME NUMBER
PARENTUSN NUMBER
PARENTSLT NUMBER
PARENTSEQ NUMBER
XID RAW(8)
PXID RAW(8)
RCVSERVERS NUMBER

SQL> desc v$fast_start_servers
名稱 是否為空? 型別
----------------------------------------- -------- ----------------------------

STATE VARCHAR2(11)
UNDOBLOCKSDONE NUMBER
PID NUMBER
XID RAW(8)
SQL> select state,pid,xid,rcvservers from v$fast_start_transactions;

STATE PID XID RCVSERVERS
---------------- ---------- ---------------- ----------
RECOVERED 08002D000E030000 1

SQL> select * from v$fast_start_servers;

未選定行

SQL> show parameter fast

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fast_start_io_target integer 0
fast_start_mttr_target integer 0
fast_start_parallel_rollback string LOW
SQL> show parameter cpu

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cpu_count integer 1
parallel_threads_per_cpu integer 2
SQL>
--在smon的trc檔案中發現了下面的資訊:
--==========================
Windows thread id: 512, image: ORACLE.EXE (SMON)


*** 2009-11-20 22:26:47.203
*** SERVICE NAME:(SYS$BACKGROUND) 2009-11-20 22:26:47.156
*** SESSION ID:(164.1) 2009-11-20 22:26:47.156
Dead transaction 0x0008.02d.0000030e recovered by 1 server(s)
*** 2009-11-20 22:50:48.265
SMON: Parallel transaction recovery tried
--=============================
C:>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on 星期五 11月 20 22:59:38 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.


連線到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select sid from v$mystat where rownum=1;

SID
----------
153

SQL> select sid,serial#,paddr,status from v$session where sid=153;

SID SERIAL# PADDR STATUS
---------- ---------- -------- --------
153 28 6FA4BA1C ACTIVE

SQL> select pid,spid from v$process where addr='6FA4BA1C';

PID SPID
---------- ------------
13 2532

SQL> select count(*) from t;

COUNT(*)
----------
11540

SQL> insert into t select * from t;

已建立11540行。

SQL> insert into t select * from t;

已建立23080行。

SQL> insert into t select * from t;

已建立46160行。

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
test
--==============================
--嘗試殺掉thread
C:>orakill test 2532

Kill of thread id 2532 in instance test successfully signalled.
--==============================
--奇怪的是居然沒有殺掉
SQL> select pid,spid from v$process where addr='6FA4BA1C';

PID SPID
---------- ------------
13 2532

SQL> select sid,serial#,paddr,status from v$session where sid=153;

SID SERIAL# PADDR STATUS
---------- ---------- -------- --------
153 28 6FA4BA1C ACTIVE

SQL> select pid,spid from v$process where addr='6FA4BA1C';

PID SPID
---------- ------------
13 2532
--========================
C:>orakill test 2532

Kill of thread id 2532 in instance test successfully signalled.
--========================
--這次是殺掉了
SQL> select pid,spid from v$process where addr='6FA4BA1C';
select pid,spid from v$process where addr='6FA4BA1C'
*
第 1 行出現錯誤:
ORA-03113: 通訊通道的檔案結束


SQL> exit
從 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options 斷開

C:>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on 星期五 11月 20 23:04:21 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.


連線到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select count(*) from t;

COUNT(*)
----------
11540

SQL>
--重新連線發現事務rollback了,插入而沒有提及的資料不見了,在smon對應
的trace檔案中發現了rollback資訊:
--============================
Dead transaction 0x0006.02a.0000031c recovered by 1 server(s)
*** 2009-11-20 23:03:37.843
SMON: Parallel transaction recovery tried
--============================
--再次嘗試恢復時間長一些的事務看看是否啟動parallel事務恢復:
C:>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on 星期五 11月 20 23:04:21 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.


連線到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select count(*) from t;

COUNT(*)
----------
11540
SQL> select sid from v$mystat where rownum=1;

SID
----------
159

SQL> select sid,serial#,paddr,status from v$session where sid=159;

SID SERIAL# PADDR STATUS
---------- ---------- -------- --------
159 22 6FA4D7CC ACTIVE

SQL> select pid,spid from v$process where addr='6FA4D7CC';

PID SPID
---------- ------------
18 3044

SQL> select count(*) from t;

COUNT(*)
----------
11540

SQL> insert into t select * from t;

已建立11540行。

SQL> insert into t select * from t;

已建立23080行。

SQL> insert into t select * from t;

已建立46160行。

SQL> insert into t select * from t;

已建立92320行。

SQL> insert into t select * from t;

已建立184640行。

SQL> insert into t select * from t;

已建立369280行。

SQL> commit;

提交完成。

SQL> delete from t;

已刪除738560行。

SQL>
--==============================
session 2:
SQL> alter system kill session '159,22';

系統已更改。

SQL> select state,pid,xid,rcvservers from v$fast_start_transactions;

STATE PID XID RCVSERVERS
---------------- ---------- ---------------- ----------
RECOVERING 16 02002F001B030000 1
RECOVERED 0300100012030000 1
RECOVERED 06002A001C030000 1
RECOVERED 08002D000E030000 1

SQL> select * from v$fast_start_servers;

STATE UNDOBLOCKSDONE PID XID
----------- -------------- ---------- ----------------
RECOVERING 1030 16 02002F001B030000

SQL> select pid,spid from v$process where addr='6FA4D7CC';

PID SPID
---------- ------------
18 3044

SQL> select sid,serial#,paddr,status from v$session where sid=159;

SID SERIAL# PADDR STATUS
---------- ---------- -------- --------
159 22 6FA7F358 KILLED

SQL> select * from v$fast_start_servers;

STATE UNDOBLOCKSDONE PID XID
----------- -------------- ---------- ----------------
RECOVERING 3877 16 02002F001B030000

SQL> select state,pid,xid,rcvservers from v$fast_start_transactions;

STATE PID XID RCVSERVERS
---------------- ---------- ---------------- ----------
RECOVERING 16 02002F001B030000 1
RECOVERED 0300100012030000 1
RECOVERED 06002A001C030000 1
RECOVERED 08002D000E030000 1

SQL> select state,pid,xid,rcvservers from v$fast_start_transactions;

STATE PID XID RCVSERVERS
---------------- ---------- ---------------- ----------
RECOVERING 16 02002F001B030000 1
RECOVERED 0300100012030000 1
RECOVERED 06002A001C030000 1
RECOVERED 08002D000E030000 1

SQL> select * from v$fast_start_servers;

STATE UNDOBLOCKSDONE PID XID
----------- -------------- ---------- ----------------
RECOVERING 7549 16 02002F001B030000
--smon的trace資訊:
--======================
Dead transaction 0x0002.02f.0000031b recovered by 1 server(s)
*** 2009-11-20 23:16:14.421
SMON: Parallel transaction recovery tried
--=======================
--修改fast_start_parallel_rollback=high
C:>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on 星期五 11月 20 23:27:30 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.


連線到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select sid from v$mystat where rownum=1;

SID
----------
159

SQL> select sid,serial#,paddr,status from v$session where sid=159;

SID SERIAL# PADDR STATUS
---------- ---------- -------- --------
159 24 6FA4CBEC ACTIVE

SQL> select pid,spid from v$process where addr='6FA4CBEC';

PID SPID
---------- ------------
16 2332

SQL> show parameter fast

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fast_start_io_target integer 0
fast_start_mttr_target integer 0
fast_start_parallel_rollback string LOW
SQL> alter system set fast_start_parallel_rollback=high;

系統已更改。

SQL> show parameter fast

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fast_start_io_target integer 0
fast_start_mttr_target integer 0
fast_start_parallel_rollback string HIGH
SQL> select sid,serial#,paddr,status from v$session where sid=159;

SID SERIAL# PADDR STATUS
---------- ---------- -------- --------
159 24 6FA4CBEC ACTIVE

SQL> select pid,spid from v$process where addr='6FA4CBEC';

PID SPID
---------- ------------
16 2332

SQL> select count(*) from t;

COUNT(*)
----------
738560

SQL> delete from t;

已刪除738560行。

SQL>
--====================
session 2:
SQL> alter system kill session '159,24';

系統已更改。

SQL> select state,pid,xid,rcvservers from v$fast_start_transactions;

STATE PID XID RCVSERVERS
---------------- ---------- ---------------- ----------
RECOVERING 18 0400170019030000 4
RECOVERED 02002F001B030000 1
RECOVERED 0300100012030000 1
RECOVERED 06002A001C030000 1
RECOVERED 08002D000E030000 1

SQL> select * from v$fast_start_servers;

STATE UNDOBLOCKSDONE PID XID
----------- -------------- ---------- ----------------
RECOVERING 1204 18 0400170019030000
RECOVERING 0 19 0400170019030000
RECOVERING 0 20 0400170019030000
RECOVERING 0 21 0400170019030000

SQL> select * from v$fast_start_servers;

STATE UNDOBLOCKSDONE PID XID
----------- -------------- ---------- ----------------
RECOVERING 2908 18 0400170019030000
RECOVERING 0 19 0400170019030000
RECOVERING 0 20 0400170019030000
RECOVERING 0 21 0400170019030000

SQL> select * from v$fast_start_servers;

STATE UNDOBLOCKSDONE PID XID
----------- -------------- ---------- ----------------
RECOVERING 3444 18 0400170019030000
RECOVERING 0 19 0400170019030000
RECOVERING 0 20 0400170019030000
RECOVERING 0 21 0400170019030000

SQL> select pid,spid from v$process where addr='6FA4CBEC';

PID SPID
---------- ------------
16 2332

SQL> select sid,serial#,paddr,status from v$session where sid=159;

SID SERIAL# PADDR STATUS
---------- ---------- -------- --------
159 24 6FA7F358 KILLED

SQL> select program from v$process where program like '%P00%';

PROGRAM
----------------------------------------------------------------
ORACLE.EXE (P000)
ORACLE.EXE (P001)
ORACLE.EXE (P002)
ORACLE.EXE (P003)

SQL> select * from v$fast_start_servers;

STATE UNDOBLOCKSDONE PID XID
----------- -------------- ---------- ----------------
RECOVERING 12358 18 0400170019030000
RECOVERING 0 19 0400170019030000
RECOVERING 0 20 0400170019030000
RECOVERING 0 21 0400170019030000

SQL> select state,pid,xid,rcvservers from v$fast_start_transactions;

STATE PID XID RCVSERVERS
---------------- ---------- ---------------- ----------
RECOVERING 18 0400170019030000 4
RECOVERED 02002F001B030000 1
RECOVERED 0300100012030000 1
RECOVERED 06002A001C030000 1
RECOVERED 08002D000E030000 1

SQL> select pid,program from v$process where program like '%P00%';

PID PROGRAM
---------- ----------------------------------------------------------------
18 ORACLE.EXE (P000)
19 ORACLE.EXE (P001)
20 ORACLE.EXE (P002)
21 ORACLE.EXE (P003)
--恢復完成了,終於看到了oracle啟動了多個praralle process,但是儘管啟動了
多個程式,事實上還算一個process來恢復,其實這樣恢復時間可能會更長...
SQL> select * from v$fast_start_servers;

未選定行

SQL> select state,pid,xid,rcvservers from v$fast_start_transactions;

STATE PID XID RCVSERVERS
---------------- ---------- ---------------- ----------
RECOVERED 02002F001B030000 1
RECOVERED 0300100012030000 1
RECOVERED 0400170019030000 4
RECOVERED 06002A001C030000 1
RECOVERED 08002D000E030000 1

SQL> select pid,program from v$process where program like '%P00%';

PID PROGRAM
---------- ----------------------------------------------------------------
18 ORACLE.EXE (P000)
19 ORACLE.EXE (P001)
20 ORACLE.EXE (P002)
21 ORACLE.EXE (P003)

SQL>
SQL> show parameter fast

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fast_start_io_target integer 0
fast_start_mttr_target integer 0
fast_start_parallel_rollback string HIGH
SQL>
在smon的trace檔案中也看到了4個server(s)的提示資訊:
--=============================
Dead transaction 0x0004.017.00000319 recovered by 4 server(s)
*** 2009-11-20 23:34:11.718
SMON: Parallel transaction recovery tried
--==========================
--嘗試在事務恢復的過程中殺掉process:
session 1:
C:>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on 星期五 11月 20 23:43:03 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.


連線到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select sid from v$mystat where rownum=1;

SID
----------
159

SQL> select sid,serial#,paddr,status from v$session where sid=159;

SID SERIAL# PADDR STATUS
---------- ---------- -------- --------
159 26 6FA4BA1C ACTIVE

SQL> select pid,spid from v$process where addr='6FA4BA1C';

PID SPID
---------- ------------
13 2072

SQL> show parameter fast

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fast_start_io_target integer 0
fast_start_mttr_target integer 0
fast_start_parallel_rollback string HIGH
SQL> select count(*) from t;

COUNT(*)
----------
738560

SQL> delete from t;

已刪除738560行。

SQL>
--============================
session 2:
SQL> alter system kill session '159,26';

系統已更改。

SQL> select pid,program from v$process where program like '%P00%';

PID PROGRAM
---------- ----------------------------------------------------------------
18 ORACLE.EXE (P000)
19 ORACLE.EXE (P001)
20 ORACLE.EXE (P002)
21 ORACLE.EXE (P003)

SQL> select state,pid,xid,rcvservers from v$fast_start_transactions;

STATE PID XID RCVSERVERS
---------------- ---------- ---------------- ----------
RECOVERING 18 0A0011001E030000 4
RECOVERED 02002F001B030000 1
RECOVERED 0300100012030000 1
RECOVERED 0400170019030000 4
RECOVERED 06002A001C030000 1
RECOVERED 08002D000E030000 1

已選擇6行。

SQL> select * from v$fast_start_servers;

STATE UNDOBLOCKSDONE PID XID
----------- -------------- ---------- ----------------
RECOVERING 2451 18 0A0011001E030000
RECOVERING 0 19 0A0011001E030000
RECOVERING 0 20 0A0011001E030000
RECOVERING 0 21 0A0011001E030000
--=========================
--連續多次執行下面語句,保證一定要殺掉thread:2072
C:>orakill test 2072

Kill of thread id 2072 in instance test successfully signalled.

C:>orakill test 2072

Kill of thread id 2072 in instance test successfully signalled.

C:>orakill test 2072

Kill of thread id 2072 in instance test successfully signalled.

C:>orakill test 2072

Kill of thread id 2072 in instance test successfully signalled.

C:>orakill test 2072

Kill of thread id 2072 in instance test successfully signalled.

C:>orakill test 2072

Kill of thread id 2072 in instance test successfully signalled.

C:>
--=========================
SQL> select sid,serial#,paddr,status from v$session where sid=159;

SID SERIAL# PADDR STATUS
---------- ---------- -------- --------
159 26 6FA7F358 KILLED
--kill 程式之後很顯然session對應的程式的地址由6FA4BA1C變成了6FA7F358,
而地址6FA7F358所對應的程式其實並不存在,真真存在的程式是6FA4BA1C,但是6FA4BA1C
不在能為session 159服務了...
SQL> select * from v$fast_start_servers;

STATE UNDOBLOCKSDONE PID XID
----------- -------------- ---------- ----------------
RECOVERING 5533 18 0A0011001E030000
RECOVERING 0 19 0A0011001E030000
RECOVERING 0 20 0A0011001E030000
RECOVERING 0 21 0A0011001E030000

SQL> select state,pid,xid,rcvservers from v$fast_start_transactions;

STATE PID XID RCVSERVERS
---------------- ---------- ---------------- ----------
RECOVERING 18 0A0011001E030000 1
RECOVERED 02002F001B030000 1
RECOVERED 0300100012030000 1
RECOVERED 0400170019030000 4
RECOVERED 06002A001C030000 1
RECOVERED 08002D000E030000 1

已選擇6行。

SQL> select sid,serial#,paddr,status from v$session where sid=159;

SID SERIAL# PADDR STATUS
---------- ---------- -------- --------
159 26 6FA7F358 KILLED

SQL> select pid,spid from v$process where addr='6FA7F358';

未選定行

SQL> select pid,spid from v$process where addr='6FA4BA1C';

PID SPID
---------- ------------
13 2072

SQL> select state,pid,xid,rcvservers from v$fast_start_transactions;

STATE PID XID RCVSERVERS
---------------- ---------- ---------------- ----------
RECOVERING 18 0A0011001E030000 4
RECOVERED 02002F001B030000 1
RECOVERED 0300100012030000 1
RECOVERED 0400170019030000 4
RECOVERED 06002A001C030000 1
RECOVERED 08002D000E030000 1

已選擇6行。

SQL> select * from v$fast_start_servers;

STATE UNDOBLOCKSDONE PID XID
----------- -------------- ---------- ----------------
RECOVERING 14144 18 0A0011001E030000
RECOVERING 0 19 0A0011001E030000
RECOVERING 0 20 0A0011001E030000
RECOVERING 0 21 0A0011001E030000

SQL> select * from v$fast_start_servers;

未選定行

SQL> select state,pid,xid,rcvservers from v$fast_start_transactions;

STATE PID XID RCVSERVERS
---------------- ---------- ---------------- ----------
RECOVERED 02002F001B030000 1
RECOVERED 0300100012030000 1
RECOVERED 0400170019030000 4
RECOVERED 06002A001C030000 1
RECOVERED 08002D000E030000 1
RECOVERED 0A0011001E030000 4

已選擇6行。

SQL> select sid,serial#,paddr,status from v$session where sid=159;

SID SERIAL# PADDR STATUS
---------- ---------- -------- --------
159 26 6FA7F358 KILLED

SQL> select pid,spid from v$process where addr='6FA7F358';

未選定行
--儘管我們kill掉了session也嘗試kill thread:2072,但是事實上
這個thread一直存在,直到我們用exit退出session 1所在的sqlplus程式
SQL> select pid,spid from v$process where addr='6FA4BA1C';

PID SPID
---------- ------------
13 2072
session 1:
SQL> select count(*) from t;
select count(*) from t
*
第 1 行出現錯誤:
ORA-00028: 您的會話己被終止
--==============
session 2:
SQL> select pid,spid from v$process where addr='6FA4BA1C';

PID SPID
---------- ------------
13 2072
session 1:
SQL> exit
從 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options 斷開

C:>
--================
session 2:
SQL> select pid,spid from v$process where addr='6FA4BA1C';

未選定行

SQL>

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/19602/viewspace-1028922/,如需轉載,請註明出處,否則將追究法律責任。

相關文章