ANBOB™

前几天机房迁移前计划先停掉一些dbms_job防止停机时“填乱”，结果有一台服务器在同事的toad以dba role 用户执行dbms_ijob.broken job,执行成功未报错，但发现job broken 状态未发现改变，重启toad一样，我用ssh 登录，sqlplus 执行成功查看是broken 成功，状态也已改变，但在toad中还是未改变。随后我关闭ssh,再次broken 时等待了5分钟没反应,hang住了..

SQL> select job,broken from dba_jobs;

       JOB B
---------- -
         1 N
         4 Y
         3 N
        22 N
      9998 N
        82 N

6 rows selected.

SQL> exec dbms_ijob.broken(22,true);
BEGIN dbms_ijob.broken(22,true); END;

*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
ORA-06512: at "SYS.DBMS_IJOB", line 357
ORA-06512: at line 1

SQL> set linesize 150
SQL> select * from v$locked_object;

    XIDUSN    XIDSLOT     XIDSQN  OBJECT_ID SESSION_ID ORACLE_USERNAME                OS_USER_NAME                   PROCESS      LOCKED_MODE
---------- ---------- ---------- ---------- ---------- ------------------------------ ------------------------------ ------------ -----------
         6         46     306992        237       1777 SYS                            oracle                         17435                  3
         0          0          0        237       1786                                oracle                         17098                  3

SQL> col object_name for a30
SQL> set linesize 150
SQL> select object_name,object_type,owner from dba_objects where object_id=237;

OBJECT_NAME                    OBJECT_TYPE         OWNER
------------------------------ ------------------- ------------------------------
JOB$                           TABLE               SYS

SQL> oradebug setmypid;
Statement processed.
SQL> oradebug unlimit;
Statement processed.
SQL>
SQL> oradebug hanganalyze 3;
Hang Analysis in /oracle/admin/multi/udump/multi_ora_16838.trc

HANG ANALYSIS:
==============
Open chains found:
Chain 1 :  :
    <0/1777/32406/0x5275fca0/13913/SQL*Net message from client>
 -- <0/1477/37455/0x5275e4e8/16043/enq: TX - row lock contention>
Other chains found:
Chain 2 :  :
    <0/1897/17365/0x5276aa90/16119/enq: TX - row lock contention>
Chain 3 :  :
    <0/1900/31123/0x52768af0/16838/No Wait>
Chain 4 :  :
    <0/2004/18088/0x52742a28/30733/Streams AQ: qmn slave idle wait>
Chain 5 :  :
    <0/2167/1/0x526f2170/5834/Streams AQ: waiting for time man>
Chain 6 :  :
    <0/2191/7/0x526e7b68/5541/Streams AQ: qmn coordinator idle>
Extra information that will be dumped at higher levels:
[level  4] :   1 node dumps -- [REMOTE_WT] [LEAF] [LEAF_NW]
[level  5] :   4 node dumps -- [SINGLE_NODE] [SINGLE_NODE_NW] [IGN_DMP]
[level  6] :   2 node dumps -- [NLEAF]
[level 10] : 278 node dumps -- [IGN] 

State of nodes
([nodenum]/cnode/sid/sess_srno/session/ospid/state/start/finish/[adjlist]/predecessor):
...
[1437]/0/1438/33103/0x5131db18/17010/IGN/23/24//none
[1439]/0/1440/51434/0x513205e8/16779/IGN/25/26//none
[1442]/0/1443/43536/0x51324620/16866/IGN/27/28//none
[1458]/0/1459/51806/0x51339ca0/16874/IGN/29/30//none
[1471]/0/1472/45640/0x5134b2e8/16441/IGN/31/32//none
[1473]/0/1474/47872/0x5134ddb8/16425/IGN/33/34//none
[1476]/0/1477/37455/0x51351df0/16043/NLEAF/35/38/[1776]/none
[1775]/0/1776/16155/0x514e1e68/16697/IGN/231/232//none
[1776]/0/1777/32406/0x514e33d0/13913/LEAF/36/37//1476
[1896]/0/1897/17365/0x51583c90/16119/NLEAF/323/324/[1776]/none
[1899]/0/1900/31123/0x51587cc8/16838/SINGLE_NODE_NW/327/328//none

note:
 adjlist     = adjacent node   (Usually represents a  blocker node)
 predecessor = predecessor node (Usually represents a waiter node)

TIP:
OPEN CHAINS: This section reports sessions involved on a wait chain. A wait chains means that one session is blocking one or more other sessions.
OTHER CHAINS: It refers to chains of blockers and waiters related to other sessions identified under “open chains”, but not blocked directly by the process reported on the "open chain".
STATE OF NODES: This section might be considered as the main section of the report. It shows all the sessions connected when the HANGANALYZE trace file was generated. This section essentially describes a dependency graph between nodes(known as an “adjacency list”), where each session is considered a node, and each node may have an “adjacent” node or a “predecessor” node related to it. Depending on the state of the node it may be a blocker, a waiter or a hung node. The “STATE DESCRIPTION” section of this document explains further the meaning of the states.

The following describes the important states to be considered:
NLEAF : These sessions are usually considered as “stuck” sessions. It means that there is another session holding a resource needed by the session in this state. By using the adjlist, you can determine which node is the blocker of this process. When many sessions are found in this state, it is likely the database is experiencing a performance problem rather than a hang problem.

IGN and IGN_DMP : Sessions in this state are usually considered as IDLE sessions, unless they reference a node in the “ adjlist” field. In this case, the node is waiting for another node, so it will be considered as a ‘stuck’ session as well.

LEAF and LEAF_NW: Leaf nodes are considered on top of the wait chain (usually blockers). They are considered “Blockers” when there is another session waiting. This can be easily identified using the “predecesor” field. If there is a node referenced in the ‘prdecessor’ field, the node is considered as “blocker”, otherwise it is considered as a “slow” session waiting for some resource.
The difference between LEAF and LEAF_NW is that LEAF nodes are not waiting for something, while LEAF_NW are not waiting or may be using the CPU.

SQL> select sid,username,serial# from v$session where sid=1777;

       SID USERNAME                          SERIAL#
---------- ------------------------------ ----------
      1777 SYS                                 32432
SQL> alter system kill session '1777,32432' immediate;
System altered.

SQL> select sid,username,serial# from v$session where sid=1777;
no rows selected

SQL> select * from v$locked_object;
no rows selected

SQL> exec dbms_ijob.broken(22,true);
PL/SQL procedure successfully completed.

SQL> select job,broken from dba_jobs;

       JOB B
---------- -
         1 N
         4 Y
         3 N
        22 Y
      9998 N
        82 N

打赏

微信扫一扫，打赏作者吧～