資料庫慢方法論二 ——一個例子

一個例子: 資料庫突然變慢

背景: 一個新應用上線後, 資料庫突然變慢

 

第一步, 調查新應用

 

據開發人員講新應用訪問的都是新建立的表, 表的資料量很小, 沒有複雜的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   

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/81/viewspace-213872/，如需轉載，請註明出處，否則將追究法律責任。