问题描述

1、应用连接数据异常缓慢，包括客户端使用plsql连接；
2、数据库主机cpu占用率居高不下，IO写入居高不下。
3、主机日常维护操作响应慢，如man或w；

分析问题

Ø系统及oracle应用为什么响应慢
1、为什么系统连w这么简单的操作都会觉得卡呢？
2、为什么没有任何应用接入的情况下，数据库会有大量的写入操作呢？
Top //查看cpu使用情况，发现iowait%占用了大量的cpu时间；
Iostat –mx 2 100 查看disk使用情况，发现磁盘利用率长时间处于100%状态；将系统响应慢定位在io请求过多导致。（关于iostat的使用参见man）。
Ø什么导致出现如此之多的IO请求呢?
在观察后台的进程，发现有ora_p000...ora_p015. 共16个进程在运行。
我机器物理上2颗CPU，共有8个core （Cat /proc/cpuinfo可以看到机器cpu信息）。 运行Sqlplus “/as sysdba”进入sql命令行查看rollback相关参数，Show parameter rollback 看到FAST_START_PARALLEL_ROLLBACK = LOW，此参数为默认设置为LOW，表明并行运行的回滚进程有2*number of cpu，在我的系统刚好表现为16个进程。与我使用ps –ef | grep ora_p  看到的ora_p000_*0**到ora_p015_***进程对应。
Ø为什么会有如此多的回滚进程出现呢？
经过询问项目组相关人员，发现有人在执行imp导入时，手动终止了。拿到该同事的imp语句一看清楚了，由于导入的数据量较大，又没有逐行提交（commit=y），异常终止后产生大量的回滚动作。
Ø回滚慢操作为什么慢：
View $ORACLE_BASE/admin/$ORACLE_SID/bdump/alter_<ORACLE_SID>.log查看oracle alert日志，发现大量的Checkpoint not completed，表明redo文件组太少，导致LGWR进程在切换到新redo file时，等待旧数据写入(dbwn)数据文件；
解决办法就是增加redo file 组；
Alert database add logfile group 4(‘/u01/app/oracle/oradata/oracl/redo04.log’) size 100M;
Alert database add logfile group 5(‘/u01/app/oracle/oradata/oracl/redo05.log’) size 100M;
Alert database add logfile group 6(‘/u01/app/oracle/oradata/oracl/redo06.log’) size 100M;
根据需要可添加更多的redo文件组。
Select group#,members,status from v$log;发现有inactive出现就可以了。Redo 文件处在active状态说明redo文件还没写入在数据文件中，若此时LGWR switch切换到active文件，将在alert日志中出现Checkpoint未完成告警。
需要说明的是：回滚操作由于要写入redo文件，其本身就是很消耗系统资源的。

结论

当在 Oracle Database 10g 中回滚长期运行的事务时，无论是并行实例恢复会话还是用户执行的回滚语句。您所需做的一切就是查看视图 V$SESSION_LONGOPS 并评估还需要多少时间。
项目中该数据库每月定期要导入大量数据。通过对导入数据期间LGWR switch出现频率的观察，发现LGWR switch切换过于频繁，需要对redo File进行优化，建议设置16个group，每个group member大小为200M。
另外，需要对导入脚本进行优化，
imp dw/cnfj_bts_dw file=call_gaa_551_200906.dmp full=y ignore=y feedback=50000 buffer=10240000 commit=y indexes=n log=’/home/imp200909.log’;
 

附录：

1、停止并行回滚，减少IO请求，快速提升系统响应能力
如果你没时间等待回滚进程完成回滚操作，可根据如下提示进行操作。
最后在google上根据ora_p001, wait for a undo record 的关键字，找到了一些信息，以下信息引起了我的注意：
Oracle工程师首先怀疑是临时表空间空间不足导致，经检查临时表空间没有空间不足的情况，仔细观察日志发现重做日志文件不断切换，分析应该是有较多的事务没有完成提交或者有较多没有提交的事务完成回滚。现在面临的问题是我们没有很多时间去等待所有的事务去完成回滚或提交。解决问题的思路就是如何尽快结束这些事务的回滚或提交。
1) 查看spfile文件中是否有fast_start_parallel_rollback参数的设置，检查结果G网数据库没有设置该参数。如果没有显式设置，则该参数的默认值为low。修改该参数值为false
　　2) 将数据库启动到nomount状态：startup nomount
　　3) 修改改参数值：alter system set fast_start_parallel_rollback = FALSE scope=spfile
　　4) shutdown immediate关闭数据库
　　5) startup启动
　　6) 查看该参数是否生效：show parameter fast_start_parallel_rollback
　　7) 等待一段时间
8) shutdown immediate数据库可以关闭
2、加快回滚速度
提高并行回滚进程的数量，设置为HIGH时回滚进程=4*cpu数。在sql命令行模式下执行
ALTER SYSTEM SET FAST_START_PARALLEL_ROLLBACK = HIGH