數(shù)據(jù)庫慢該如何著手?

數(shù)據(jù)庫的performance是一個長期的監(jiān)控過程，不能頭疼醫(yī)頭，腳疼醫(yī)腳。

?　數(shù)據(jù)庫慢一般有三種情況

　　1、逐漸變慢

　　2、突然變慢

　　3、不定時變慢

　　第一種情況 “逐漸變慢”，要建立一個長期的監(jiān)控機制。比如，寫個shell腳本每天的忙時(通常9~10 etc.)定時收集os,network,db的信息，每個星期出report對收集到的信息進(jìn)行分析。這些數(shù)據(jù)的積累，可以決定后期的優(yōu)化決策，并且可以是DBA說服manager采用自己決策的重要數(shù)據(jù)。DBA的價值，就在每個星期的report中體現(xiàn)。

　　第二種情況 “突然變慢”，也是最容易解?的。先從業(yè)務(wù)的角度看是DB的使用跟以前有何不同，然后做進(jìn)一步判斷。硬件/網(wǎng)絡(luò)故障通常也會引起DB性能的突然下降。

　　第一步: 察看DB/OS/NETWORK的系統(tǒng)log, 排除硬件/網(wǎng)絡(luò)問題

　　第二步： 察看數(shù)據(jù)庫的等待事件，根據(jù)等待事件來判斷可能出問題的環(huán)節(jié)。如果, 沒有等待事件, 可以排除數(shù)據(jù)庫的問題. 如果有等待時間, 根據(jù)不同的等待事件, 來找引起這些事件的根源.

　　比如latch free等跟SQL parse有關(guān)系的等待事件，OS的表現(xiàn)是CPU 的占用率高

　　db file scattered read等跟SQL disk read有關(guān)系的等待時間， OS的表現(xiàn)是iostat可以看到磁盤讀寫量增加

　　第三步: 察看os的信息, CPU/IO/MEMORY等.

　　a. Cpu 的占用率

　　CPU占用率與數(shù)據(jù)庫性能不成反比. CPU占用率高, 不能說明數(shù)據(jù)庫性能慢. 通常情況, 一個優(yōu)化很好, 而且業(yè)務(wù)量確實很大的數(shù)據(jù)庫, CPU的占用率都會高, 而且會平均分布在每個進(jìn)程上. 反過來, CPU的占用率都會高也不代表數(shù)據(jù)庫性能就好, 要結(jié)合數(shù)據(jù)庫的等待事件來判斷CPU占用率高是否合理.

　　如果某個進(jìn)程的cpu占用高, 肯定是這個進(jìn)程有問題. 如果,不是oracle的進(jìn)程, 可以讓application察看是否程序有死循環(huán)等漏洞. 如果,是oracle的進(jìn)程, 可以根據(jù)pid查找oracle數(shù)據(jù)字典看看這個進(jìn)程的發(fā)起程序, 正在執(zhí)行的sql語句, 以及等待事件. 然后, 不同情況使用不同的方法來解決.

　　b. IO

　　排除硬件的IO問題, 數(shù)據(jù)庫突然變慢, 一般來說, 都是一個或幾個SQL語句引起的.

　　如果IO很頻繁, 可以通過優(yōu)化disk reads高的TOP SQL來解決. 當(dāng)然這也是解決IO問題的最笨也是最有效的辦法.

　　OS以及存儲的配置也是影響IO的一個重要的原因.

　　比如, 最常見的HP-unix下異步IO的問題, 如果DBA GROUP沒有MLOCK的權(quán)限, ORACLE是不使用AIO的. 偏偏OS與DB的兩方的admin如果配合不夠好地話, 這個配置就很容易給漏掉了.

　　c. Memory

　　第二種情況與memory的關(guān)系比較小, 只要SGA區(qū)配置合理沒有變化, 一般來說, 只要不是Application Memory leak, 不會引起突然變慢的現(xiàn)象.

　　第三種情況 “不定時變慢”, 是最難解決的. 現(xiàn)場出現(xiàn)的問題原因也是五花八門千百怪, 最重要的是, 出現(xiàn)慢的現(xiàn)象時, 以最快的速度抓取到最多的信息以供分析. 先寫好抓取數(shù)據(jù)的shell 腳本, 并在現(xiàn)象發(fā)生時及時按下回車鍵

　　一個例子

　　數(shù)據(jù)庫突然變慢

　　背景: 一個新應(yīng)用上線后, 數(shù)據(jù)庫突然變慢

　　第一步, 調(diào)查新應(yīng)用

　　據(jù)開發(fā)人員講新應(yīng)用訪問的都是新建立的表, 表的數(shù)據(jù)量很小, 沒有復(fù)雜的SQL查詢.

　　查詢 v$sqlarea 分別按照disk_reads / buffer_gets / executions 排序, TOP SQL 中沒有新應(yīng)用的SQL. 排除新應(yīng)用數(shù)據(jù)庫訪問照成的性能問題.

　　第二步, 察看數(shù)據(jù)庫log/ OS log

　　數(shù)據(jù)庫log中可以看到大量的ORA-7445錯誤, 以及大量的dump文件. 分析dump文件(時間久了,沒有dump文件可參考, 具體細(xì)節(jié)沒法描述下來. ), 發(fā)現(xiàn)是新應(yīng)用通過dblink訪問remote DB時生成的dump文件, 應(yīng)用開發(fā)人說沒法修改, Oracle也沒有相應(yīng)的patch解決.

　　OS log中沒有錯誤信息

　　第三步, 察看statspack report

　　從wait events中看到,Top event是“buffer busy waits” “db file parallel write” 等于IO相關(guān)的等待事件.

　　從buffer busy waits 的統(tǒng)計信息來看, 是等待data block.

　　還有些physical reads等信息與從前比沒有太多的異常.

　　Tablespace 的IO reads/writes也沒有異常, 但是wait明顯增加.

　　初步確定是IO問題.

　　第四步, 察看OS的信息

　　1. top 命令(輸出為實驗室數(shù)據(jù)，僅作格式參考)

　　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

　　當(dāng)時現(xiàn)場數(shù)據(jù)顯示：iowait 值與以前相比大很多， 沒有異常進(jìn)程

　　2. sar ?d (輸出為實驗室數(shù)據(jù)，僅作格式參考)

　　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

　　當(dāng)時現(xiàn)場數(shù)據(jù)顯示，放數(shù)據(jù)文件的設(shè)備 avwait, avque, blks/s值偏大

　　第五步, 察看數(shù)據(jù)庫的等待事件

　　一個大業(yè)務(wù)量的數(shù)據(jù)庫如果性能不好的話, 一般來說都會有大量的等待事件, 上百個等待事件很常見, 我通常會按照EVENT進(jìn)行g(shù)roup.

　　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;

　　輸出結(jié)果顯示最多的等待事件是buffer busy waits。

　　進(jìn)一步分析，找出等待的原因

　　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對應(yīng)為等待的原因)

　　按照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#.”

　　輸出結(jié)果顯示，等待分布在多個不同的對象上，等待原因為 “waiting for a block read to complete”，進(jìn)一步分析為IO的問題。

　　如果，buffer busy waits等待集中在某個對象上，說明有hot block, 通過重新rebuild這個對象增加freelist來解決,RAC環(huán)境增加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可以推出，有數(shù)據(jù)庫以外的IO訪問磁盤。

　　察看磁盤配置，該VG只存放了數(shù)據(jù)庫數(shù)據(jù)文件和數(shù)據(jù)庫系統(tǒng)文件。排除數(shù)據(jù)文件，產(chǎn)生IO的是數(shù)據(jù)庫系統(tǒng)文件。

　　數(shù)據(jù)庫系統(tǒng)文件一般來說不會產(chǎn)生IO, 有IO讀寫的地方只有l(wèi)og和dump文件。

　　結(jié)論：ora-7445產(chǎn)生的大量core dump文件堵塞IO

　　解決辦法：

　　1，消除ora-7445. (應(yīng)用不改的情況下，無法解決)

　　2, 把dump目錄指向別的VG

　　3, 讓oracle盡量少的去寫core dump文件

　　background_core_dump = partial

　　shadow_core_dump = partial