ITPub博客

首页 > 应用开发 > IT综合 > fast_start_parallel_rollback参数的一点测试

fast_start_parallel_rollback参数的一点测试

原创 IT综合 作者:warehouse 时间:2009-11-21 00:14:04 0 删除 编辑

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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2007-12-07

  • 博文量
    717
  • 访问量
    5098437