Oracle Hang Analysis

Author: rainnyzhong

Date:2010-1-15

1． 症狀描述：

FALB12從EXCEL IMPORT DATA到DB，預計事務會執行1個多小時，在開始操作後40分鐘左右，ORACLE掛死，任何使用者都不可以再登陸了。

2． 分析

(1) 下面是掛死時OS的資源狀況：

09:37:54 up 73 days, 23:31, 1 user, load average: 0.11, 0.24, 0.18

194 processes: 191 sleeping, 3 running, 0 zombie, 0 stopped

CPU states: cpu user nice system irq softirq iowait idle

total 6.7% 0.0% 0.3% 0.3% 0.0% 1.9% 90.4%

Mem: 2186616k av, 2168780k used, 17836k free, 0k shrd, 154716k buff

1633584k actv, 498176k in_d, 64108k in_c

Swap: 2040244k av, 18624k used, 2021620k free 2283068k cached

從top命令的輸出中，我們可以看出，MEMORY使用幾乎已經達到100%。

(2) 下面是ORACLE報警日誌的內容：

Thu Jan 14 16:22:44 2010

Errors in file /opt/oracle9i/admin/ORA16G/udump/ora16g_ora_29758.trc:

ORA-00604: error occurred at recursive SQL level 4

ORA-04021: timeout occurred while waiting to lock object

ORA-00604: error occurred at recursive SQL level 4

ORA-04021: timeout occurred while waiting to lock object

ORA-00604: error occurred at recursive SQL level 3

ORA-04021: timeout occurred while waiting to lock object

Thu Jan 14 16:26:06 2010

可以看出，在解析遞迴SQL時出問題了，在等待某種鎖時延時了。

(3) 下面是跟蹤檔案ora16g_ora_29758.trc的部分內容

*** 2010-01-14 16:22:44.412

=====================

PARSE ERROR #8:len=56 dep=2 uid=0 oct=3 lid=0 tim=1233845082434255 err=604

select value$ from props$ where name = 'GLOBAL_DB_NAME'

*** 2010-01-14 16:22:44.412

ksedmp: internal or fatal error

ORA-00604: error occurred at recursive SQL level 4

ORA-04021: timeout occurred while waiting to lock object

ORA-00604: error occurred at recursive SQL level 4

ORA-04021: timeout occurred while waiting to lock object

ORA-00604: error occurred at recursive SQL level 3

ORA-04021: timeout occurred while waiting to lock object

Current SQL statement for this session:

select value$ from props$ where name = 'GLOBAL_DB_NAME'

……………

SO: 0x5e6ec29c, type: 4, owner: 0x5e61d48c, flag: INIT/-/-/0x00

(session) sid: 132 trans: (nil), creator: 0x5e61d48c, flag: (c0000041) USR/- BSY/-/-/-/-/-

DID: 0001-0015-00000004, short-term DID: 0000-0000-00000000

txn branch: (nil)

oct: 0, prv: 0, sql: (nil), psql: (nil), user: 86/FALB12

O/S info: user: , term: , ospid: 1234, machine: ecv09

program:

last wait for 'library cache pin' blocking sess=0x(nil) seq=7 wait_time=2929670 seconds since wait started=906

handle address=5e24edf4, pin address=5b0f5364, 100*mode+namespace=c8

Dumping Session Wait History

for 'library cache pin' count=1 wait_time=2929670

handle address=5e24edf4, pin address=5b0f5364, 100*mode+namespace=c8

for 'library cache pin' count=1 wait_time=2929670

handle address=5e24edf4, pin address=5b0f5364, 100*mode+namespace=c8

從跟蹤檔案可以看出，在解析ORACLE遞迴SQL時報錯。何為recursive SQL？

當使用者釋出SQL語句時，ORACLE需要對SQL語句進行語法與語義檢查，這時，ORACLE會發布一些內部的SQL語句，對這條SQL所訪問的物件進行各種檢查，如檢查訪問的表存不存在，使用者有沒有許可權對該表進行訪問等，這種ORACLE內部的SQL稱之為遞迴SQL。遞迴SQL也是分LEVEL的。如，如果是由LEVEL 1遞迴SQL再引起的recursive SQL，則此條recursive SQL的LEVEL就是2，由此類推。

從跟蹤檔案我們可以看出，ORACLE在等待library cache pin。我們知道，ORACLE解析使用者的SQL和PLSQL程式碼是在共享池(share pool)中進行的，庫快取是共享池的一個元件(library cache,簡稱LC)，還有一個快取叫做字典快取(dictionary cache,簡稱DC)，ORACLE每次解析SQL語句時，都要獲取一塊LC記憶體地址並把它鎖住(獨佔，即把記憶體釘住，稱之為pin)，此例中，很顯然，ORACLE沒辦法再釘住任何一塊LC的記憶體地址了，所以一直在等。當新的使用者登陸時，ORACLE要釋出內部SQL對該使用者進行許可權驗證等，這些遞迴SQL也需要獲取LC記憶體來進行解析，由於LC已經沒有任何空閒的地址了，所以，這些使用者驗證的SQL也沒辦法執行，所以導致新的使用者沒辦法登入ORCLE(SYS使用者也沒辦法登入)，整個資料庫出現掛死的現象。

3. 診斷

初步診斷為ORACLE記憶體嚴重不足。IMPORT DATA時由於事務很長，沒辦法釋放資源(表鎖，記憶體鎖等)，導致ORACLE掛死。

4． 建議

將IMPORT DATA放到一個比較大記憶體的ORACLE伺服器上執行，且在做IMPORT DATA時，不要做太多其它的操作。

[@more@]