資料庫正常運作，突然變慢的解決思路

分類： 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

1

8

2.23

log file sync

45

0.94

db file sequential read

182

2

3.79

SQL*Net break/reset to client

6

17

0.13

os thread startup

28

0.02

db file scattered read

5

0.10

SQL*Net more data from client

39

0.81

control file sequential read

1,142

23.79

SQL*Net message to client

7,743

161.31

SQL*Net more data to client

346

7.21

latch: shared pool

LGWR wait for redo copy

20

0.42

direct path read

89

1.85

rdbms ipc reply

3

0.06

direct path write

12

0.25

SQL*Net message from client

7,739

17,212

2224

161.23

Streams AQ: qmn slave idle wait

54

1,475

27322

1.13

Streams AQ: qmn coordinator idle wait

109

50.46

13536

2.27

virtual circuit status

50

100.00

1,458

29156

1.04

Streams AQ: waiting for time management or cleanup tasks

77

76683

jobq slave wait

59

2931

用戶端連接配接時間都是很長，很可以。我就是問下伺服器管理者，此伺服器的網絡是否和平時網絡一緻。

伺服器管理者和我說這台伺服器是遠端連接配接。遠端連接配接稍有延遲很正常。是以我做出了合了解釋。

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

以下是轉載資料:

　　資料庫慢一般有三種情況

　　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