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

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

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

　　1、逐漸變慢

　　2、突然變慢

　　3、不定時(shí)變慢

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

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

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

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

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

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

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

　　a. Cpu 的占用率

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

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

　　b. IO

　　排除硬件的IO問(wèn)題, 數(shù)據(jù)庫(kù)突然變慢, 一般來(lái)說(shuō), 都是一個(gè)或幾個(gè)SQL語(yǔ)句引起的.

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

　　OS以及存儲(chǔ)的配置也是影響IO的一個(gè)重要的原因.

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

　　c. Memory

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

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

　　一個(gè)例子

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

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

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

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

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

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

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

　　OS log中沒(méi)有錯(cuò)誤信息

　　第三步, 察看statspack report

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

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

　　還有些physical reads等信息與從前比沒(méi)有太多的異常.

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

　　初步確定是IO問(wèn)題.

　　第四步, 察看OS的信息

　　1. top 命令(輸出為實(shí)驗(yàn)室數(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)時(shí)現(xiàn)場(chǎng)數(shù)據(jù)顯示：iowait 值與以前相比大很多， 沒(méi)有異常進(jìn)程

　　2. sar ?d (輸出為實(shí)驗(yàn)室數(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)時(shí)現(xiàn)場(chǎng)數(shù)據(jù)顯示，放數(shù)據(jù)文件的設(shè)備 avwait, avque, blks/s值偏大

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

　　一個(gè)大業(yè)務(wù)量的數(shù)據(jù)庫(kù)如果性能不好的話(huà), 一般來(lái)說(shuō)都會(huì)有大量的等待事件, 上百個(gè)等待事件很常見(jiàn), 我通常會(huì)按照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對(duì)應(yīng)為等待的原因)

　　按照p1,p2,p3 group是為了明確buffer busy waits的等待集中在哪些對(duì)象上。

　　Metalink對(duì)buffer busy waits等待事件的描述有如下一段話(huà)：

　　“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é)果顯示，等待分布在多個(gè)不同的對(duì)象上，等待原因?yàn)?“waiting for a block read to complete”，進(jìn)一步分析為IO的問(wèn)題。

　　如果，buffer busy waits等待集中在某個(gè)對(duì)象上，說(shuō)明有hot block, 通過(guò)重新rebuild這個(gè)對(duì)象增加freelist來(lái)解決,RAC環(huán)境增加freelist group.

　　通過(guò)以下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、磁盤(pán)的IO流量增加

　　2、磁盤(pán)的IO等待增加

　　3、DB的IO流量沒(méi)有增加

　　4、DB的IO等待增加

　　由1，2，3，4可以推出，有數(shù)據(jù)庫(kù)以外的IO訪(fǎng)問(wèn)磁盤(pán)。

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

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

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

　　解決辦法：

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

　　2, 把dump目錄指向別的VG

　　3, 讓oracle盡量少的去寫(xiě)core dump文件

　　background_core_dump = partial

　　shadow_core_dump = partial