当前位置:Oracle研究中心 > 故障案例 >
时间:2016-05-26 21:44 来源:Oracle研究中心 作者:惜分飞 点击: 次
[oracle@node1 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Thu Feb 16 12:47:08 2012 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options SQL> show sga; Total System Global Area 622149632 bytes Fixed Size 2230912 bytes Variable Size 406848896 bytes Database Buffers 205520896 bytes Redo Buffers 7548928 bytes SQL> create table chf.xifenfei tablespace users 2 as 3 select * from dba_objects; --下面两句多次执行 SQL> insert into chf.xifenfei 2 select * from chf.xifenfei; 73831 rows created. SQL> commit; Commit complete. SQL> select count(*) from chf.xifenfei; COUNT(*) ---------- 18900736 SQL> select bytes/1024/1024 from dba_segments where segment_name='XIFENFEI'; BYTES/1024/1024 --------------- 2103 --删除数据不提交 SQL> delete from chf.xifenfei; 18900736 rows deleted. --直接kill掉ora_dbw进程
SQL> select undoblockstotal "Total", undoblocksdone "Done", undoblockstotal-undoblocksdone 2 "ToDo",decode(cputime,0,'unknown',to_char(sysdate+(((undoblockstotal-undoblocksdone) 3 / (undoblocksdone / cputime)) / 86400),'yyyy-mm-dd hh24:mi:ss')) 4 "Estimated time to complete",to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') 5 from v$fast_start_transactions; Total Done ToDo Estimated time to c TO_CHAR(SYSDATE,'YY ---------- ---------- ---------- ------------------- ------------------- 545624 103020 442604 2012-02-16 13:57:38 2012-02-16 13:47:02 SQL> / Total Done ToDo Estimated time to c TO_CHAR(SYSDATE,'YY ---------- ---------- ---------- ------------------- ------------------- 545624 122614 423010 2012-02-16 13:57:42 2012-02-16 13:47:31 --每秒钟回滚undo数据块数量 SQL> select (122614-103020)/29 from dual; (122614-103020)/29 ------------------ 675.655172 --数据库并发回滚进程数 [oracle@node1 ~]$ ps -ef|grep ora_p0 oracle 24901 1 4 13:44 ? 00:00:15 ora_p000_chf oracle 24903 1 3 13:44 ? 00:00:12 ora_p001_chf oracle 24905 1 3 13:44 ? 00:00:12 ora_p002_chf oracle 24907 1 3 13:44 ? 00:00:12 ora_p003_chf oracle 24909 1 3 13:44 ? 00:00:12 ora_p004_chf oracle 24911 1 3 13:44 ? 00:00:12 ora_p005_chf oracle 24913 1 3 13:44 ? 00:00:12 ora_p006_chf oracle 24915 1 3 13:44 ? 00:00:12 ora_p007_chf oracle 24917 1 3 13:44 ? 00:00:12 ora_p008_chf oracle 24919 1 3 13:44 ? 00:00:12 ora_p009_chf oracle 24921 1 3 13:44 ? 00:00:12 ora_p010_chf oracle 24923 1 3 13:44 ? 00:00:12 ora_p011_chf oracle 24925 1 3 13:44 ? 00:00:12 ora_p012_chf oracle 24927 1 3 13:44 ? 00:00:12 ora_p013_chf oracle 24929 1 3 13:44 ? 00:00:12 ora_p014_chf oracle 24931 1 3 13:44 ? 00:00:12 ora_p015_chf 说明:该机器操作系统是8个CPU 并发数=CPU*2
SQL> alter system set FAST_START_PARALLEL_ROLLBACK =HIGH;
System altered.
SQL> select undoblockstotal "Total", undoblocksdone "Done", undoblockstotal-undoblocksdone
2 "ToDo",decode(cputime,0,'unknown',to_char(sysdate+(((undoblockstotal-undoblocksdone)
3 / (undoblocksdone / cputime)) / 86400),'yyyy-mm-dd hh24:mi:ss'))
4 "Estimated time to complete",to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')
5 from v$fast_start_transactions;
Total Done ToDo Estimated time to c TO_CHAR(SYSDATE,'YY
---------- ---------- ---------- ------------------- -------------------
380434 25351 355083 2012-02-16 14:03:53 2012-02-16 13:49:39
SQL> /
Total Done ToDo Estimated time to c TO_CHAR(SYSDATE,'YY
---------- ---------- ---------- ------------------- -------------------
380434 39592 340842 2012-02-16 14:03:33 2012-02-16 13:50:12
--每秒钟回滚undo数据块数量
SQL> select (39592-25351)/33 from dual;
(39592-25351)/33
----------------
431.545455
--数据库并发回滚进程数
[oracle@node1 ~]$ ps -ef|grep ora_p0
oracle 24901 1 4 13:44 ? 00:00:15 ora_p000_chf
oracle 24903 1 3 13:44 ? 00:00:12 ora_p001_chf
oracle 24905 1 3 13:44 ? 00:00:12 ora_p002_chf
oracle 24907 1 3 13:44 ? 00:00:12 ora_p003_chf
oracle 24909 1 3 13:44 ? 00:00:12 ora_p004_chf
oracle 24911 1 3 13:44 ? 00:00:12 ora_p005_chf
oracle 24913 1 3 13:44 ? 00:00:12 ora_p006_chf
oracle 24915 1 3 13:44 ? 00:00:12 ora_p007_chf
oracle 24917 1 3 13:44 ? 00:00:12 ora_p008_chf
oracle 24919 1 3 13:44 ? 00:00:12 ora_p009_chf
oracle 24921 1 3 13:44 ? 00:00:12 ora_p010_chf
oracle 24923 1 3 13:44 ? 00:00:12 ora_p011_chf
oracle 24925 1 3 13:44 ? 00:00:12 ora_p012_chf
oracle 24927 1 3 13:44 ? 00:00:12 ora_p013_chf
oracle 24929 1 3 13:44 ? 00:00:12 ora_p014_chf
oracle 24931 1 3 13:44 ? 00:00:12 ora_p015_chf
oracle 25072 1 0 13:48 ? 00:00:01 ora_p016_chf
oracle 25074 1 0 13:48 ? 00:00:01 ora_p017_chf
oracle 25076 1 0 13:48 ? 00:00:01 ora_p018_chf
oracle 25078 1 0 13:48 ? 00:00:01 ora_p019_chf
oracle 25080 1 0 13:48 ? 00:00:01 ora_p020_chf
oracle 25082 1 0 13:48 ? 00:00:01 ora_p021_chf
oracle 25084 1 0 13:48 ? 00:00:01 ora_p022_chf
oracle 25086 1 0 13:48 ? 00:00:01 ora_p023_chf
oracle 25088 1 0 13:48 ? 00:00:01 ora_p024_chf
oracle 25090 1 0 13:48 ? 00:00:01 ora_p025_chf
oracle 25092 1 0 13:48 ? 00:00:01 ora_p026_chf
oracle 25094 1 0 13:48 ? 00:00:01 ora_p027_chf
oracle 25096 1 0 13:48 ? 00:00:01 ora_p028_chf
oracle 25098 1 0 13:48 ? 00:00:01 ora_p029_chf
oracle 25100 1 0 13:48 ? 00:00:01 ora_p030_chf
oracle 25102 1 0 13:48 ? 00:00:01 ora_p031_chf
1.说明问题:直接修改FAST_START_PARALLEL_ROLLBACK =HIGH后,
数据库在原来并发进程基础上,又重新启动额外进程
2.修改FAST_START_PARALLEL_ROLLBACK后,以前回滚过的数据块是成功的
v$fast_start_transactions视图重新开始计算
3.并发数=CPU*4
[/shell]
4.FAST_START_PARALLEL_ROLLBACK=FALSE
SQL> alter system set FAST_START_PARALLEL_ROLLBACK=FALSE;
System altered.
--直接修改为FALSE后,观察到数据库的并发等资源都没有释放,重启数据库释放资源继续试验
SQL> startup force;
ORACLE instance started.
Total System Global Area 622149632 bytes
Fixed Size 2230912 bytes
Variable Size 406848896 bytes
Database Buffers 205520896 bytes
Redo Buffers 7548928 bytes
Database mounted.
Database opened.
SQL> select undoblockstotal "Total", undoblocksdone "Done", undoblockstotal-undoblocksdone
2 "ToDo",decode(cputime,0,'unknown',to_char(sysdate+(((undoblockstotal-undoblocksdone)
3 / (undoblocksdone / cputime)) / 86400),'yyyy-mm-dd hh24:mi:ss'))
4 "Estimated time to complete",to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')
5 from v$fast_start_transactions;
Total Done ToDo Estimated time to c TO_CHAR(SYSDATE,'YY
---------- ---------- ---------- ------------------- -------------------
306828 15735 291093 2012-02-16 14:04:34 2012-02-16 13:52:33
SQL> /
Total Done ToDo Estimated time to c TO_CHAR(SYSDATE,'YY
---------- ---------- ---------- ------------------- -------------------
306828 65861 240967 2012-02-16 14:05:15 2012-02-16 13:54:46
--每秒钟回滚undo数据块数量
SQL> select (65861-15735)/133 from dual;
(65861-15735)/133
-----------------
376.887218
--数据库并发回滚进程数
[oracle@node1 ~]$ ps -ef|grep ora_p0
oracle 25252 1 1 13:51 ? 00:00:00 ora_p000_chf
oracle 25254 1 0 13:51 ? 00:00:00 ora_p001_chf
oracle 25256 1 1 13:51 ? 00:00:00 ora_p002_chf
oracle 25258 1 1 13:51 ? 00:00:00 ora_p003_chf
oracle 25260 1 1 13:51 ? 00:00:00 ora_p004_chf
oracle 25262 1 1 13:51 ? 00:00:00 ora_p005_chf
oracle 25264 1 1 13:51 ? 00:00:00 ora_p006_chf
1.数据库重启后,以前的回滚依然生效(v$fast_start_transactions.undoblockstotal变小)
2.FAST_START_PARALLEL_ROLLBACK=false还是有并发,而非官方文档描述(Parallel rollback is disabled)
通过这三种情况下的每秒钟回滚undo数据块数量比较可以知道在LOW状态下最快,HIGH状态下次之,FALSE最慢。其实这个实验没有任何实际说明力,只是想说明几个问题:
1)Oracle大事物回滚,是没有办法取消,但是可以通过FAST_START_PARALLEL_ROLLBACK干预回滚速度
2)数据库的并发效率高于低,取决于系统的资源情况(如果你系统的cpu非常强大,那么可能设置HIGH速度最快)
3)回滚的数据类型,在回滚表中数据时可能设置并发比FALSE快,
但是如果是要回滚串行数据(如:index),那么可能串行方法方式速度更快
4)根据你的系统的使用状况,比如你想让系统的业务受到的影响最小,那么设置FALSE可能是个不错的选择。
FAST_START_PARALLEL_ROLLBACK specifies the degree of parallelism used when recovering terminated transactions. Terminated transactions are transactions that are active before a system failure. If a system fails when there are uncommitted parallel DML or DDL transactions, then you can speed up transaction recovery during startup by using this parameter. Values: 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 Note:If you change the value of this parameter, then transaction recovery will be stopped and restarted with the new implied degree of parallelism.
--------------------------------------ORACLE-DBA----------------------------------------
最权威、专业的Oracle案例资源汇总之案例:Oracle数据库回滚 FAST_START_PARALLEL_ROLLBACK加快回滚恢复
本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/230.html
Powered by Oracle研究中心 OraclePlus Inc.
BY DedeCMS 京ICP备14022373号-5
声明:Oracle研究中心部分文章源自互联网,转载均按原文作者要求转载,如有疑问联系QQ:1026002015,协商未果24小时删除。