数据库慢该如何着手

作者: Peak Wong 出处:IT专家网　2008-04-29 17:01　   砖    好    评论  条 　进入论坛

阅读提示：数据库的performance是一个长期的监控过程。本文详细介绍了数据库慢的三种情况及其解决对策。

数据库的performance是一个长期的监控过程，不能头疼医头，脚疼医脚。

数据库慢一般有三种情况：

1、逐渐变慢

2、突然变慢

3、不定时变慢

第一种情况：“逐渐变慢”，要建立一个长期的监控机制。比如，写个shell脚本每天的忙时(通常9~10 etc.)定时收集os，network，db的信息， 每个星期出report对收集到的信息进行分析。这些数据的积累，可以决定后期的优化决策，并且可以是DBA说服manager采用自己决策的重要数据。DBA的价值，就在每个星期的report中体现。

第二种情况：“突然变慢”，也是最容易解决的。先从业务的角度看是DB的使用跟以前有何不同，然后做进一步判断。硬件/网络故障通常也会引起DB性能的突然下降。

第一步：查看DB/OS/NETWORK的系统log，排除硬件/网络问题。

第二步：查看数据库的等待事件，根据等待事件来判断可能出问题的环节。如果, 没有等待事件, 可以排除数据库的问题. 如果有等待时间, 根据不同的等待事件, 来找引起这些事件的根源。

比如latch free等跟SQL parse有关系的等待事件，OS的表现是CPU 的占用率高。

db file scattered read等跟SQL disk read有关系的等待时间，OS的表现是iostat可以看到磁盘读写量增加。

第三步: 查看os的信息，CPU/IO/MEMORY等。

a. Cpu 的占用率

CPU占用率与数据库性能不成反比。CPU占用率高，不能说明数据库性能慢。通常情况，一个优化很好，而且业务量确实很大的数据库，CPU的占用率都会高，而且会平均分布在每个进程上。反过来，CPU的占用率都会高也不代表数据库性能就好，要结合数据库的等待事件来判断CPU占用率高是否合理。

如果某个进程的cpu占用高，肯定是这个进程有问题。如果不是oracle的进程，可以让application查看是否程序有死循环等漏洞。如果是oracle的进程，可以根据pid查找oracle数据字典看看这个进程的发起程序，正在执行的sql语句，以及等待事件。然后不同情况使用不同的方法来解决。

b. IO

排除硬件的IO问题，数据库突然变慢，一般来说，都是一个或几个SQL语句引起的。

如果IO很频繁，可以通过优化disk reads高的TOP SQL来解决。当然这也是解决IO问题的最笨也是最有效的办法。

OS以及存储的配置也是影响IO的一个重要的原因。

比如, 最常见的HP-unix下异步IO的问题，如果DBA GROUP没有MLOCK的权限，ORACLE是不使用AIO的。偏偏OS与DB的两方的admin如果配合不够好地话，这个配置就很容易给漏掉了。

c. Memory

第二种情况与memory的关系比较小，只要SGA区配置合理没有变化，一般来说，只要不是Application Memory leak， 不会引起突然变慢的现象。

第三种情况 “不定时变慢”，是最难解决的。现场出现的问题原因也是五花八门千奇百怪，最重要的是，出现慢的现象时，以最快的速度抓取到最多的信息以供分析。先写好抓取数据的shell 脚本，并在现象发生时及时按下回车键。

一个例子：

数据库突然变慢

背景：一个新应用上线后，数据库突然变慢。

第一步，调查新应用

据开发人员讲新应用访问的都是新建立的表，表的数据量很小，没有复杂的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 RES STATE 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等待事件的描述有如下一段话：

“If P3 shows 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 same file# and block#.”

输出结果显示，等待分布在多个不同的对象上，等待原因为“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

【相关文章】

• 提高ORACLE数据库的查询统计速度

• ORACLE数据库变得非常慢解决方案一例

• 详细讲解大型数据库的设计原则与开发技巧