資料庫正常執行，但是速度慢，應該從哪方面入手

       今天同事說資料庫執行正常，就是速度慢，之前也沒弄過這樣工作。突然之間感覺到很茫然，不知道如何從方面入手，想想了，嘗試做一下操作：

       我按照我的平時思路，先登陸到oracle資料庫上:

1、檢視資料庫版本（select * from v$version）

2、檢視作業系統的程式（top，ps -ef）

3、檢視作業系統的IO情況（sar 1 10）

4、製作awr報表，檢視報告

在報告中 ：

Event	Waits	%Time -outs	Total Wait Time (s)	Avg wait (ms)	Waits /txn
control file parallel write	500	0.00	11	23	10.42
log file parallel write	107	0.00	1	8	2.23
log file sync	45	0.00	0	8	0.94
db file sequential read	182	0.00	0	2	3.79
SQL*Net break/reset to client	6	0.00	0	17	0.13
os thread startup	1	0.00	0	28	0.02
db file scattered read	5	0.00	0	2	0.10
SQL*Net more data from client	39	0.00	0	0	0.81
control file sequential read	1,142	0.00	0	0	23.79
SQL*Net message to client	7,743	0.00	0	0	161.31
SQL*Net more data to client	346	0.00	0	0	7.21
latch: shared pool	1	0.00	0	1	0.02
LGWR wait for redo copy	20	0.00	0	0	0.42
direct path read	89	0.00	0	0	1.85
rdbms ipc reply	3	0.00	0	0	0.06
direct path write	12	0.00	0	0	0.25
SQL*Net message from client	7,739	0.00	17,212	2224	161.23
Streams AQ: qmn slave idle wait	54	0.00	1,475	27322	1.13
Streams AQ: qmn coordinator idle wait	109	50.46	1,475	13536	2.27
virtual circuit status	50	100.00	1,458	29156	1.04
Streams AQ: waiting for time management or cleanup tasks	1	100.00	77	76683	0.02
jobq slave wait	20	100.00	59	2931	0.42

客戶端連線時間都是很長，很可以。我就是問下伺服器管理員，此伺服器的網路是否和平時網路一致。

伺服器管理員和我說這臺伺服器是遠端連線。遠端連線稍有延遲很正常。所以我做出了合理解釋。

       透過這個問題解決，感覺自己在這方面很欠缺，所以在網上找了些資料。

以下是轉載資料:

　　資料庫慢一般有三種情況

　　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 指令碼, 並在現象發生時及時按下Enter鍵

　　一個例子

　　資料庫突然變慢

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

　　第一步, 調查新應用

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