一个例子:数据库突然变慢
背景:一个新应用上线后,数据库突然变慢
第一步,调查新应用
据开发人员讲新应用访问的都是新建立的表,表的数据量很小,没有复杂的SQL查询.
查询v$sqlarea分别按照disk_reads / buffer_gets / executions排序, TOP SQL中没有新应用的SQL.排除新应用数据库访问照成的性能问题.
第二步,察看数据库log/ OS log
数据库log中可以看到大量的ORA-7445错误,以及大量的dump文件.分析dump文件(时间久了,没有dump文件可参考,具体细节没法描述下来. ),发现是新应用通过dblink访问remote DB时生成的dump文件,应用开发人说没法修改, Oracle也没有相应的patch解决.
OS log中没有错误信息
第三步,察看statspack report
从wait events中看到,Top event是“buffer busy waits” “db file parallel write”等于IO相关的等待事件.
从buffer busy waits的统计信息来看,是等待data block.
还有些physical reads等信息与从前比没有太多的异常.
Tablespace的IO reads/writes也没有异常,但是wait明显增加.
初步确定是IO问题.
第四步,察看OS的信息
1. top命令（输出为实验室数据，仅作格式参考）
load averages: 0.05, 0.10, 0.09 10:18:32
307 processes: 304 sleeping, 1 zombie, 1 stopped, 1 on cpu
CPU states: 96.0% idle, 0.3% user, 2.6% kernel, 1.1%iowait, 0.0% swap
Memory: 4096M real, 2660M free, 1396M swap in use, 3013M swap free
PID USERNAME THR PRI NICE SIZE RESSTATE TIME CPU COMMAND
11928 a21562 1 0 0 3008K 2496K cpu/1 0:02 1.12% top
14965 mpgj76 4 59 0 10M 3696K sleep 3:09 0.18% view_server
当时现场数据显示：iowait值与以前相比大很多，没有异常进程
2. sar –d（输出为实验室数据，仅作格式参考）
SunOS sc19 5.7 Generic_106541-42 sun4u 03/20/08
00:00:00 device %busy avque r+w/s blks/s avwait avserv
sd410 17 0.4 50 1628 0.1 7.1
sd410,a 0 0.0 0 0 0.0 0.0
sd410,b 0 0.0 0 0 0.0 0.0
sd410,c 0 0.0 0 0 0.0 0.0
sd410,g 17 0.4 50 1628 0.1 7.1
当时现场数据显示，放数据文件的设备avwait, avque, blks/s值偏大
第五步,察看数据库的等待事件
一个大业务量的数据库如果性能不好的话,一般来说都会有大量的等待事件,上百个等待事件很常见,我通常会按照EVENT进行group.
Select count(*), event from v$session_wait where event not in ('smon timer','pmon timer','rdbms ipc message','SQL*Net message from client') group by event order by 1 desc;
输出结果显示最多的等待事件是buffer busy waits。
进一步分析，找出等待的原因
Select count(*), p1, p2, p3 from v$session_wait where event = ‘buffer busy waits’ group by p1,p2,p3;
在buffer busy waits等待事件中
P1 = file#
P2 = block#
P3 = id (此id对应为等待的原因)
按照p1,p2,p3 group是为了明确buffer busy waits的等待集中在哪些对象上。
Metalink对buffer busy waits等待事件的描述有如下一段话：
“IfP3shows that the "buffer busy wait" is waiting for a block read to complete then the blocking session is likely to be waiting on an IO wait (eg: "db file sequential read" or "db file scattered read") for the samefile#andblock#.”
输出结果显示，等待分布在多个不同的对象上，等待原因为“waiting for a block read to complete”，进一步分析为IO的问题。
如果，buffer busy waits等待集中在某个对象上，说明有hot block,通过重新rebuild这个对象增加freelist来解决,RAC环境增加freelist group.
通过以下SQL可以找到具体的object.
Select owner, segment_name, segment_type from dba_extents where file_id=P1 and P2 between block_id and block_id+blocks;
P1,P2是上面v$session_wait查出的具体的值
第六步,明确原因，找出解决步骤
分析：
1。磁盘的IO流量增加
2。磁盘的IO等待增加
3。DB的IO流量没有增加
4。DB的IO等待增加
由1，2，3，4可以推出，有数据库以外的IO访问磁盘。
察看磁盘配置，该VG只存放了数据库数据文件和数据库系统文件。排除数据文件，产生IO的是数据库系统文件。
数据库系统文件一般来说不会产生IO,有IO读写的地方只有log和dump文件。
结论：ora-7445产生的大量core dump文件堵塞IO
解决办法：
1，消除ora-7445. (应用不改的情况下，无法解决)
2,把dump目录指向别的VG
3,让oracle尽量少的去写core dump文件
background_core_dump = partial
shadow_core_dump = partial
