【中亦安圖】小機上執行Oracle需要注意的程式排程bug(1)

lhrbest發表於2016-04-18

第一章 技術人生系列 · 我和資料中心的故事——第一期

第一期:技術人生系列 · 我和資料中心的故事(第一期)小機上執行Oracle需要注意的程式排程bug

2016-01-11 小y 

 

前 言

 

小y這個名字,是筆者臨時想的一個筆名,其實沒有什麼特殊的含義,就暫且用他來代表我們這些為各個資料中心奉獻自己青春的一群默默無聞的IT人吧!

 

小y今天要和大家分享的是一個疑難雜症的分析過程。如果大家有耐心讀完這個案例,一定會或多或少有些收穫,也就沒浪費小y的一片苦心。

 

具體來說是一個應用間歇性區域性掛起案例的分析過程,報告中將對Oracle資料庫穩定執行的共性風險和隱患作出提醒。

 

1問題描述

據客戶反映,應用會間歇性出現異常,包括insert單條記錄在內的操作長時間無法完成,按照客戶的說法,資料庫內可能有“死鎖”現象,希望能夠找到問題發生的根因,提出解決方案,以避免問題再次發生。

20151223日,問題再次發生,客戶再次聯絡到小y,y透過遠端方式進行了資訊收集和故障診斷,最終定位了問題的根本原因。

環境介紹:

作業系統 HPUX IA64 B.11.31

資料庫 ORACLE 10.2.0.5,單例項

 

2分析過程

>>> 2.1 異常時刻資料庫出現異常等待

 

wpsC336.tmp 

可以看到:

 

2個會話在等待行鎖(拿不到事務鎖,需要一直等待),另外有一個會話在等待“undo segment extension”。

 

>>>> 2.2 梳理異常等待之間的關係

 

1)分析行鎖等待的阻塞者

 

wpsC337.tmp

 

可以看到:

 

SID 285/290的兩個會話都是被SID=315的會話阻塞了,他們在等待行鎖,時間已經超過60000秒。

2)檢視阻塞者SID=315在做什麼

 

wpsC338.tmp 

 

可以看到:

 

SID=315的會話阻塞了其他兩個會話,他本身也處於一個資源的等待上,在等待“undo segment extension”,已經等了70384秒了!該等待事件沒有阻塞者。

3)SID=315在執行的SQL語句

INSERT INTO TABLE_NAME(COL1,COL2,COL3,COL4,COL5,COL6,COL7)VALUES(:1,:2,:3,:4,:5,:6,:7)

 

>>>> 2.3 什麼是“undo segment extension”等待事件

“undo segment extension”即等待回滾段擴充套件完成。當執行增刪改等操作時,資料庫需要回滾段來儲存前映象,當回滾段空間不足時,則需要擴充套件。

 

具體來說,undo segment的擴充套件或者回收(extend / shrink)都是前臺程式透過通知SMON後臺程式來完成的。

 

發出下列命令檢視undo的使用情況,UNDO表有空有1個檔案27750M,其中目前活動的值有8M,未過undo retention1892M,過了undo retention的有40M未發現異常。

 

wpsC348.tmp 

 

>>>> 2.4 收集資料庫hanganalyze和systemstate資訊

 

發出下列命令異常時候的hanganalyzesystemstate資訊

SQL> oradebug setmypid

Statement processed.

SQL> oradebug hanganalyze 3

Hang Analysis in /oracle/admin/xxdb/udump/xxdb_ora_14136.trc

SQL>

SQL> oradebug dump systemstate 266

Statement processed.

SQL> oradebug tracefile_name

/oracle/admin/xxdb/udump/xxdb_ora_14136.trc

 

>>>> 2.5 問題在收集資訊後自動解決

 

在發出上述命令收集相關資訊後,再次檢查,發現資料庫異常等待居然已經自動解決掉了。

從原理和經驗分析,這是因為oradebug 在收集systemstate dump時,其中會呼叫dbx等OS命令去做程式堆疊的列印,此時會將進行wakeup(喚醒)。

如下所示

 

wpsC349.tmp 

 

>>>> 2.6 獲得SID:315會話的活動會話歷史

 

wpsC35A.tmp 

可以看到,確實一直在等待undo segment extension

 

>>>> 2.7 分析systemstate dump定位問題根因

 

其中SID=315的會話在等待”undo segment extension”,該會話對應PROCESS 19 SSD當中的資訊如下:

PROCESS 19:

----------------------------------------

SO: c00000003949b948, type: 2, owner: 0000000000000000, flag: INIT/-/-/0x00

(process) Oracle pid=19, calls cur/top: c0000000397209b0/c0000000397209b0, flag: (0) -

int error: 0, call error: 0, sess error: 0, txn error 0

(post info) last post received: 0 0 121

last post received-location: kcbzww

last process to post me: c000000039496148 1 22

last post sent: 0 0 121

last post sent-location: kcbzww

last process posted by me: c000000039496148 1 22

(latch info) wait_event=0 bits=0

Process Group: DEFAULT, pseudo proc: c000000039529928

O/S info: user: oracle, term: UNKNOWN, ospid: 11880

OSD pid info: Unix process pid: 11880, image: oracle@ap-machine-

*** 2015-12-22 10:34:53.431

Short stack dump:

ksdxfstk()+48<-ksdxcb()+1200<-sspuser()+368<-<-_pw_wait()+48<-pw_wait()+128<-sskgpwwait()+384<-skgpwwait()+208<-ksliwat()+1728<-kslwaitns_timed()+112<-kskthbwt()+400<-kslwait()+640<-ktugur()+4416<-ktuchg()+1280<-ktbchg2()+704<-kdiins0()+267536<-kdiinsp()+320<-kauxsin()+2960<-insidx()+1744<-insrow()+1440<-insdrv()+960<-inscovexe()+1408<-insExecStmtExecIniEngine()+176<-insexe()+1040<-opiexe()+13776<-kpoal8()+3808<-opiodr()+2144<-ttcpip()+1680<-opitsk()+2368<-opiino()+1664<-opiodr()+2144<-opidrv()+1248<-sou2o()+240<-opimai_real()+496<-main()+240<-main_opd_entry()+80

----------------------------------------

SO: c0000000396d80d8, type: 4, owner: c00000003949b948, flag: INIT/-/-/0x00

(session) sid: 315 trans: c0000000355b2a28, creator: c00000003949b948, flag: (100041) USR/- BSY/-/-/-/-/-

DID: 0001-0013-00000027, short-term DID: 0000-0000-00000000

txn branch: 0000000000000000

oct: 2, prv: 0, sql: c0000000384bad50, psql: c0000000384bad50, user: 33/XXDB

service name: xxdb

O/S info: user: , term: , ospid: 1234, machine: rvwapp2-1

program:

waiting for 'db file sequential read' wait_time=0, seconds since wait started=0

file#=c, block#=2f359, blocks=1

blocking sess=0x0000000000000000 seq=42271

Dumping Session Wait History

for 'undo segment extension' count=1 wait_time=2 min 45 sec

segment#=6, =0, =0

for 'buffer busy waits' count=1 wait_time=0.000009 sec

file#=2, block#=59, class#=1b

for 'SQL*Net message from client' count=1 wait_time=0.000087 sec

driver id=28444553, #bytes=1, =0

for 'SQL*Net message to client' count=1 wait_time=0.000001 sec

driver id=28444553, #bytes=1, =0

for 'SQL*Net message from client' count=1 wait_time=0.000086 sec

driver id=28444553, #bytes=1, =0

for 'SQL*Net message to client' count=1 wait_time=0.000001 sec

driver id=28444553, #bytes=1, =0

for 'SQL*Net message from client' count=1 wait_time=0.000086 sec

driver id=28444553, #bytes=1, =0

for 'SQL*Net message to client' count=1 wait_time=0.000001 sec

driver id=28444553, #bytes=1, =0

for 'SQL*Net message from client' count=1 wait_time=0.000087 sec

driver id=28444553, #bytes=1, =0

for 'SQL*Net message to client' count=1 wait_time=0.000001 sec

driver id=28444553, #bytes=1, =0

Sampled Session History of session 315 serial 4544

---------------------------------------------------

The sampled session history is constructed by sampling

the target session every 1 second. The sampling process

captures at each sample if the session is in a non-idle wait,

an idle wait, or not in a wait. If the session is in a

non-idle wait then one interval is shown for all the samples

the session was in the same non-idle wait. If the

session is in an idle wait or not in a wait for

consecutive samples then one interval is shown for all

the consecutive samples. Though we display these consecutive

samples in a single interval the session may NOT be continuously

idle or not in a wait (the sampling process does not know).

The history is displayed in reverse chronological order.

sample interval: 1 sec, max history 120 sec

---------------------------------------------------

[120 samples, 10:32:52 - 10:34:53]

waited for 'undo segment extension', seq_num: 42270

p1: 'segment#'=0x6

p2: ''=0x0

p3: ''=0x0

time_waited: >= 120 sec (still in wait)

---------------------------------------------------

Sampled Session History Summary:

longest_non_idle_wait: 'undo segment extension'

[120 samples, 10:32:52 - 10:34:53]

time_waited: >= 120 sec (still in wait)

可以看到:

程式在被ORADEBUG間接喚醒後,不再等待undo segment extension,而是做 db file sequential read

從前面的分析得知,“undo segment extension”即等待回滾段擴充套件完成。當執行增刪改等操作時,資料庫需要回滾段來儲存前映象,當回滾段空間不足時,則需要擴充套件。具體來說,undo segment的擴充套件或者回收(extend / shrink)都是前臺程式透過通知SMON後臺程式來完成的。CallStack堆疊的呼叫中,最後停在“pw_wait”的呼叫上,即說明在等待SMON返回擴充套件UNDO SEGMENT成功與否的訊息。

因此,我們需要檢視SMON程式的狀態。

 

檢查SMON的資訊如下:

PROCESS 8:

----------------------------------------

SO: c000000039496148, type: 2, owner: 0000000000000000, flag: INIT/-/-/0x00

(process) Oracle pid=8, calls cur/top: c00000003971e868/c00000003971e868, flag: (16) SYSTEM

int error: 0, call error: 0, sess error: 0, txn error 0

(post info) last post received: 0 0 121

last post received-location: kcbzww

last process to post me: c0000000394a0948 211 0

last post sent: 0 0 24

last post sent-location: ksasnd

last process posted by me: c000000039495148 1 6

(latch info) wait_event=0 bits=0

Process Group: DEFAULT, pseudo proc: c000000039529928

O/S info: user: oracle, term: UNKNOWN, ospid: 10072

OSD pid info: Unix process pid: 10072, image: oracle@ap-machine- (SMON)

Short stack dump:

ksdxfstk()+48<-ksdxcb()+1200<-sspuser()+368<-<-_pw_wait()+48<-pw_wait()+352<-sskgpwwait()+384<-skgpwwait()+208<-ksliwat()+1728<-kslwaitns_timed()+112<-kskthbwt()+400<-kslwait()+640<-ktmmon()+1168<-ktmSmonMain()+64<-ksbrdp()+2368<-opirip()+1184<-opidrv()+1184<-sou2o()+240<-opimai_real()+336<-main()+240<-main_opd_entry()+80

----------------------------------------

----------------------------------------

SO: c0000000396ead68, type: 4, owner: c000000039496148, flag: INIT/-/-/0x00

(session) sid: 329 trans: 0000000000000000, creator: c000000039496148, flag: (100051) USR/- BSY/-/-/-/-/-

DID: 0001-0008-00000002, short-term DID: 0000-0000-00000000

txn branch: 0000000000000000

oct: 0, prv: 0, sql: 0000000000000000, psql: 0000000000000000, user: 0/SYS

service name: SYS$BACKGROUND

waiting for 'smon timer' wait_time=0, seconds since wait started=3109

sleep time=12c, failed=0, =0

blocking sess=0x0000000000000000 seq=7382

Dumping Session Wait History

for 'smon timer' count=1 wait_time=4 min 53 sec

sleep time=12c, failed=0, =0

for 'smon timer' count=1 wait_time=4 min 53 sec

sleep time=12c, failed=0, =0

for 'smon timer' count=1 wait_time=4 min 11 sec

sleep time=12c, failed=0, =0

for 'smon timer' count=1 wait_time=4 min 53 sec

sleep time=12c, failed=0, =0

for 'smon timer' count=1 wait_time=4 min 53 sec

sleep time=12c, failed=0, =0

for 'smon timer' count=1 wait_time=4 min 53 sec

sleep time=12c, failed=0, =0

for 'smon timer' count=1 wait_time=4 min 53 sec

sleep time=12c, failed=0, =0

for 'smon timer' count=1 wait_time=4 min 53 sec

sleep time=12c, failed=0, =0

for 'smon timer' count=1 wait_time=4 min 53 sec

sleep time=12c, failed=0, =0

for 'smon timer' count=1 wait_time=4 min 53 sec

sleep time=12c, failed=0, =0

Sampled Session History of session 329 serial 1

 

可以看到,SMON程式在等“SMON TIMER”,即空閒等待。

 

這說明SMON沒有因為阻塞在異常的等待上,導致無法騰出時間來處理前臺程式發過來的undo segment擴充套件請求。

 

>>>> 2.8 定位問題的根本原因

 

綜上所有現象:

1) 前臺程式SID 315smon傳送一條訊息,請求對回滾段進行擴充套件,並在“undo segment extension“事件上等待,程式callStack停在pw_wait,post/wait,等待訊息的返回;

2) SMON程式在等待”SMON TIMER”,即空閒等待。此時SMON可能已經完成了UNDO SEGMENT的擴充套件請求並返回了前臺程式;也可能沒有收到前臺程式傳送過來的請求;

 

3) ORADEBUGSID 315收集資訊並間接喚醒了該前臺程式後,前臺進行可以繼續往下工作,這說明,步驟2)的訊息已經返回給前臺程式,只是由於作業系統排程進位制的問題,未能即使將前臺程式排程到CPU上,拿到UNDO擴充套件成功的訊息。

 

結上所述,我們可以判定:造成該故障的根本原因是作業系統排程機制的問題。

環境介紹:

作業系統 HPUX IA64 B.11.31

資料庫 ORACLE 10.2.0.5,單例項

 

因此,我們在ORACLE官方網站BUG中以“Hpux pw_wait“做關鍵字索HPUX已知的排程缺陷,可知命中作業系統缺陷。

The problem is a defect in the pw_wait() O/S system call.
On HP-UX 11.31, this is caused by a problem with the HP-UX scheduler patch PHKL_37456:

PHKL_37456 scheduler cumulative patch

HPUX11.31上,當安裝了PHKL_37456這個排程補丁後,作業系統呼叫pw_wait存在缺陷,導致程式無法從post/wait中被喚醒,解決方案是Solution: on 11.31 install PHKL_38397 or later equivalent.

 

以下是官網原文

 

Processes Hang Waiting on 'cursor: pin S wait on X' (and other Wait Events) on HP-UX 11.23 and 11.31 Itanium Systems (Doc ID 580273.1)

In this Document

Symptoms

Changes

Cause

Solution

 

APPLIES TO:

Oracle Database - Enterprise Edition - Version 9.2.0.1 and later
HP-UX Itanium
***Checked for relevance on 23-JUL-2013***

SYMPTOMS

Process hangs indefinitely waiting on one of the following wait events:

· 

cursor: pin S wait on X (10g wait)

· 

· 

kksfbc child completion

· 

· 

SGA: allocation forcing component growth

· 

There is no blocker process.
The variety in wait events is explained by the fact that the underlying issue is with the pw_wait() HP-UX system call.
Therefore, the above list is not exhaustive, use the pstack output below to match the problem in all cases.
The problem is commonly seen on Parallel Execution Slave processes but can affect any Oracle process.
A command to generate a systemstate, processstate, or errorstack dump e.g. via ALTER SESSION or oradebug command frees the hanging process.

An example of the top of the call stack of an affected process, obtained using pstack:

0: c0000000003e2ff0 : pw_wait() + 0x30 (/usr/lib/hpux64/libc.so.1)
1: 4000000002e98a20 : pw_wait() + 0x80
(/oracle/DwhProd/orabin/oradb/product/10.2.0/bin/oracle)
2: 4000000002e492f0 : sskgpwwait() + 0x170 (/oracle/DwhProd/orabin/oradb/pr
oduct/10.2.0/bin/oracle)
3: 4000000002e3af80 : skgpwwait() + 0xa0 (/oracle/DwhP
rod/orabin/oradb/product/10.2.0/bin/oracle)
4: 4000000002d6ffd0 : ksliwat() + 0x670
(/oracle/DwhProd/orabin/oradb/product/10.2.0/bin/oracle)
5: 4000000002d4a1b0 : kslwaitns_timed() + 0x70
(/oracle/DwhProd/orabin/oradb/product/10.2.0/bin/oracle)
6: 4000000002d4b9b0 : kskthbwt() + 0x190 (/oracle/DwhProd/orabin/oradb/p
roduct/10.2.0/bin/oracle)
7: 4000000002e35220 : kslwait() + 0xe0 (/oracle/DwhPr
od/orabin/oradb/product/10.2.0/bin/oracle)
...

First few calls of the call stacks will differ depending on the particular wait event.

Output of the tusc system call tracer tool will be similar to the following:

( Attached to process 6144 ("ora_j032_SATAVA02") [64-bit] )
15:30:27 [6144]{631499} #1 pw_wait(0x9ffffffffffe1700) ... [sleeping]
User time: 0.00
Interrupt time: 0.00
Elapsed time: 66.3

CHANGES

The following HP Patch Bundle for 11.31 may have been recently applied
Mar 2008: FEATURE11i,B.11.31.0803.318b.

Note: it has been brought to our attention that the problem may also occur on HP-UX 11.23 systems.

CAUSE

The problem is a defect in the pw_wait() O/S system call.
On HP-UX 11.31, this is caused by a problem with the HP-UX scheduler patch PHKL_37456:

PHKL_37456 scheduler cumulative patch

Additional info may be sought in HP Change Request QXCR1000811756.

Please note that the wait events shown above 'cursor: pin S wait on X' and 'kksfbc child completion' can occur for a number of other legitimate reasons, so presence of these waits alone does not indicate that the problem described in this article has been encountered. You will need to verify in detail the other symptoms described here when investigating the issue you are facing.

The problem is also reported to occur on HP-UX 11.23. We have no information at this time on specific PHKL patch levels are susceptible to it, only that it is fixed starting with PHKL_37809.

SOLUTION

A number of options are available to work around or resolve this problem:

· 

Workaround: oradebug setospid+unlimit to free the hanging process:

· 

1. identify the Unix process id of the hanging process

2. use oradebug on it as follows (the unlimit command is sufficient to stop the hang)

SQL> oradebug setospid
SQL> oradebug unlimit

3. alternatively, if there are many processes hanging on 'cursor: pin S wait on X', or time is short, a systemstate dump (at minimum level 1 to minimize the amount of trace that will be written) will be the quickest way to go over all hanging processes and wake them up:

SQL> oradebug setmypid
SQL> oradebug unlimit
SQL> oradebug dump systemstate 1

· 

Solution: on 11.31 install PHKL_38397 or later equivalent.

· 

· 

Solution: on 11.23 install PHKL_37809 or later equivalent.

· 

Note: the patches whose names begin with PHKL_ are HP-UX patches. Please check with HP-UX support on the latest applicable patch numbers as such patches are regularly updated and superceded.

 

3原因總結和建議

>>>> 3.1 原因總結:應用程式中的下列INSERT語句

 

INSERT INTO TABLE_NAME(COL1,COL2,COL3,COL4,COL5,COL6,COL7)VALUES(:1,:2,:3,:4,:5,:6,:7)

之所以執行不下去,不是因為死鎖,而是因為該會話即SID=315的會話,需要執行DML,因此需要UNDO SEGMENT即回滾段來儲存前映象,但發現回滾段空間不足,需要通知SMON後臺程式來完成擴充套件的請求,但是長時間沒有獲得回滾段擴充套件成功與否的返回訊息。前臺程式和SMON之間透過POST/WAIT進位制來通訊。

由於作業系統HPUX排程的缺陷,具體來說是pw_wait系統呼叫(post/wait)存在缺陷,當SMON程式完成回滾段擴充套件後,訊息返回時,前臺程式SID=315並沒有能被及時排程到CPU上繼續處理後續工作,即體現出來就是等待在“undo segment extension“事件上等待,當我們人為使用ORADEBUGSID 315收集資訊並間接喚醒了該前臺程式後,前臺進行可以繼續往下工作,這更加印證了SMON已經將訊息已經返回給前臺程式,只是由於作業系統排程進位制的問題,未能即使將前臺程式排程到CPU上,拿到UNDO擴充套件成功的訊息

造成該故障的根本原因是作業系統排程機制的問題,該問題命中作業系統HPUX上的已知缺陷。

The problem is a defect in the pw_wait() O/S system call.
On HP-UX 11.31, this is caused by a problem with the HP-UX scheduler patch PHKL_37456:

PHKL_37456 scheduler cumulative patch

HPUX11.31上,當安裝了PHKL_37456這個排程補丁後,作業系統呼叫pw_wait存在缺陷,導致程式無法從post/wait中被喚醒,解決方案是Solution: on 11.31 install PHKL_38397 or later equivalent.

 

>>>> 3.2 建議

 

Solution: on 11.31 install PHKL_38397 or later equivalent.

請系統管理員為作業系統安裝PHKL_38397補丁。

 

About Me

....................................................................................................................................................

本文來自於微信公眾號轉載文章,若有侵權,請聯絡小麥苗及時刪除

ITPUB BLOG:http://blog.itpub.net/26736162

QQ:642808185 若加QQ請註明您所正在讀的文章標題

【版權所有,文章允許轉載,但須以連結方式註明源地址,否則追究法律責任】

....................................................................................................................................................


來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/26736162/viewspace-2083690/,如需轉載,請註明出處,否則將追究法律責任。

相關文章