乐山网站建设

专注高端品牌网站建设行业优质供应商！认准小谭建站！
028-86922220135-1821-9792
  • 首页
  • 网站建设
    网站设计包含服务 网站制作流程 有效沟通方式 售后服务
  • 微信开发
  • 案例展示
    乐山小谭建站公司作品案例展示
  • 新闻资讯
  • 解决方案
  • 小谭建站
  • 联系我们

    • 小机上运行ORACLE需要注意的进程调度BUG

    前 言

    创新互联是创新、创意、研发型一体的综合型网站建设公司，自成立以来公司不断探索创新，始终坚持为客户提供满意周到的服务，在本地打下了良好的口碑，在过去的10年时间我们累计服务了上千家以及全国政企客户，如石凉亭等企业单位，完善的项目管理流程，严格把控项目进度与质量监控加上过硬的技术实力获得客户的一致夸奖。

    小y这个名字，是笔者临时想的一个笔名，其实没有什么特殊的含义，就暂且用他来代表我们这些为各个数据中心奉献自己青春的一群默默无闻的IT人吧！

    小y今天要和大家分享的是一个疑难杂症的分析过程。如果大家有耐心读完这个案例，一定会或多或少有些收获，也就没浪费小y的一片苦心。

    具体来说是一个应用间歇性局部挂起案例的分析过程，报告中将对Oracle数据库稳定运行的共性风险和隐患作出提醒。

    1问题描述

    据客户反映，应用会间歇性出现异常，包括insert单条记录在内的操作长时间无法完成，按照客户的说法，数据库内可能有“死锁”现象，希望能够找到问题发生的根因，提出解决方案，以避免问题再次发生。

    2015年12月23日，问题再次发生，客户再次联系到小y，小y通过远程方式进行了信息收集和故障诊断，最终定位了问题的根本原因。

    环境介绍：

    操作系统 HPUX IA64 B.11.31

    数据库 ORACLE 10.2.0.5,单实例

    2分析过程

    >>> 2.1 异常时刻数据库出现异常等待

    小机上运行ORACLE需要注意的进程调度BUG

    可以看到：

    有2个会话在等待行锁（拿不到事务锁，需要一直等待），另外有一个会话在等待“undo segment extension”。


    >>>> 2.2 梳理异常等待之间的关系

    1）分析行锁等待的阻塞者

    小机上运行ORACLE需要注意的进程调度BUG

    可以看到：

    SID 285/290的两个会话都是被SID=315的会话阻塞了，他们在等待行锁，时间已经超过60000秒。

    2）查看阻塞者SID=315在做什么

    小机上运行ORACLE需要注意的进程调度BUG

    可以看到：

    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 retention的1892M，过了undo retention的有40M未发现异常。


    小机上运行ORACLE需要注意的进程调度BUG

    >>>> 2.4 收集数据库hanganalyze和systemstate信息

    发出下列命令异常时候的hanganalyze和systemstate信息

    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(唤醒)。

    如下所示


    小机上运行ORACLE需要注意的进程调度BUG

    >>>> 2.6 获得SID：315会话的活动会话历史

    小机上运行ORACLE需要注意的进程调度BUG

    可以看到，确实一直在等待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, p_w_picpath: 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, p_w_picpath: 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 315向smon发送一条消息，请求对回滚段进行扩展，并在“undo segment extension“事件上等待，进程callStack停在pw_wait，即post/wait,等待消息的返回；

    2） SMON进程在等待”SMON TIMER”,即空闲等待。此时SMON可能已经完成了UNDO SEGMENT的扩展请求并返回了前台进程；也可能没有收到前台进程发送过来的请求；


    3） ORADEBUG对SID 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“事件上等待，当我们人为使用ORADEBUG对SID 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补丁。


    分享名称：小机上运行ORACLE需要注意的进程调度BUG
    链接分享：http://lszwz.com/article/iipedc.html

    其他资讯

    售后响应及时

    7×24小时客服热线

    数据备份

    更安全、更高效、更稳定

    价格公道精准

    项目经理精准报价不弄虚作假

    合作无风险

    重合同讲信誉，无效全额退款

    联系我们

    TEL

    135-1821-9792

    028-86922220

    地址：乐山市太升南路288号锦天国际

    快捷导航

    Shortcut

    二维码

    QR CODE

    • 微信公众号

    • 手机端网站

    乐山小谭建站工作室是一家专注从事于高品质视觉体验及互联网设计开发，乐山网站建设，乐山网站设计，乐山网页设计，乐山网站制作，品牌网站建设，营销网站建设，集团网站建设，企业网站建设，外贸网站建设，响应式网站建设，小程序开发，微信开发，企业形象设计，企业宣传视频等服务，小谭建站位于乐山市龙岗区大运软件小镇，小谭建站拥有经验丰富的高级网站建设工程师和一流的网页高端设计人员，具备各种规模与类型网站建设的雄厚实力，在网站建设领域树立了自己独特的设计风格。
    友情链接 交换友情链接
    三台网站建设   宁波红锐电子   广东网站设计   成都广告招牌制作   广告媒介投放   标志设计   网站解决方案   高端网站定制   简阳电信服务器托管   商城网站建设公司   

    Copyright © 2022 青羊区小谭信息技术咨询服务工作室 乐山建站工作室 All Rights Reserved   蜀ICP备2021004003号-25

    Copyright © 2022 青羊区小谭信息技术咨询服务工作室 乐山建站工作室 

    蜀ICP备2021004003号-25