session 1:
SQL> select sid from v$mystat where rownum=1;
SID
----------
148
SQL> create or replace procedure pining
is
begin
null;
end;
/
Procedure created
SQL>
SQL> create or replace procedure calling
is
begin
pining;
dbms_lock.sleep(200);
end;
/
SQL>
SQL> call calling();
.................
session 2:
SQL> select sid from v$mystat where rownum=1;
SID
----------
158
SQL> alter procedure pining compile;
hang.................
session 3:
SQL> select sid from v$mystat where rownum=1;
SID
----------
149
SQL> drop procedure pining;
hang.................
session 4:
SQL> select sid, event,wait_class, seconds_in_wait from v$session_wait w where w.WAIT_CLASS <> 'Idle';
SID EVENT WAIT_CLASS SECONDS_IN_WAIT
---------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------
146 SQL*Net message to client Network 0
149 library cache lock Concurrency 3
158 library cache pin Concurrency 6
SQL> oradebug setmypid
Statement processed.
SQL> oradebug hanganalyze 3;
Hang Analysis in /u01/app/oracle/admin/orcl/udump/orcl_ora_16916.trc
SQL> oradebug close_trace;
Statement processed.
session 5:
SQL> oradebug setmypid
Statement processed.
SQL> oradebug unlimit
Statement processed.
SQL> oradebug dump systemstate 266
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/admin/orcl/udump/orcl_ora_17000.trc
SQL> oradebug close_trace;
Statement processed.
SQL>
--查看hang的trace文件:
[root@rhel ~]# cat /u01/app/oracle/admin/orcl/udump/orcl_ora_16916.trc
/u01/app/oracle/admin/orcl/udump/orcl_ora_16916.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /u01/app/oracle/product/10.2.0.1/db_1
System name: Linux
Node name: rhel
Release: 2.6.18-238.el5
Version: #1 SMP Sun Dec 19 14:22:44 EST 2010
Machine: x86_64
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 20
Unix process pid: 16916, image: oracle@rhel (TNS V1-V3)
*** SERVICE NAME:(SYS$USERS) 2017-11-23 15:08:24.320
*** SESSION ID:(146.17) 2017-11-23 15:08:24.320
*** 2017-11-23 15:08:24.320
==============
HANG ANALYSIS:
==============
Open chains found:
--从这里开始以下的session都是被前面的session阻塞
Chain 1 :
:
<0/148/102/0x83a5dcc0/16769/PL/SQL lock timer>
-- <0/158/250/0x83a5e4a8/16912/library cache pin>
-- <0/149/135/0x83a5ec90/16914/library cache lock>
Other chains found:
--下面的session也是被前面所阻塞,被间接阻塞
Chain 2 : :
<0/140/1/0x83a62bd0/2825/Streams AQ: qmn slave idle wait>
Chain 3 : :
<0/146/17/0x83a5f478/16916/No Wait>
Chain 4 : :
<0/147/2/0x83a623e8/2823/Streams AQ: waiting for time man>
Chain 5 : :
<0/151/987/0x83a5ccf0/17003/jobq slave wait>
Chain 6 : :
<0/154/1/0x83a5d4d8/2797/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] : 5 node dumps -- [SINGLE_NODE] [SINGLE_NODE_NW] [IGN_DMP]
[level 6] : 2 node dumps -- [NLEAF]
[level 10] : 12 node dumps -- [IGN]
State of nodes
([nodenum]/cnode/sid/sess_srno/session/ospid/state/start/finish/[adjlist]/predecessor):
[139]/0/140/1/0x83b58c18/2825/SINGLE_NODE/1/2//none
[144]/0/145/12/0x83b5f130/17000/IGN/3/4//none
[145]/0/146/17/0x83b60568/16916/SINGLE_NODE_NW/5/6//none
[146]/0/147/2/0x83b619a0/2823/SINGLE_NODE/7/8//none
[147]/0/148/102/0x83b62dd8/16769/LEAF/9/10//157
[148]/0/149/135/0x83b64210/16914/NLEAF/11/14/[157]/none
[150]/0/151/987/0x83b66a80/17003/SINGLE_NODE/15/16//none
[153]/0/154/1/0x83b6a728/2797/SINGLE_NODE/17/18//none
[157]/0/158/250/0x83b6f808/16912/NLEAF/12/13/[147]/148
[159]/0/160/1/0x83b72078/2789/IGN/19/20//none
[160]/0/161/1/0x83b734b0/2787/IGN/21/22//none
[161]/0/162/1/0x83b748e8/2785/IGN/23/24//none
[162]/0/163/1/0x83b75d20/2783/IGN/25/26//none
[163]/0/164/1/0x83b77158/2781/IGN/27/28//none
[164]/0/165/1/0x83b78590/2779/IGN/29/30//none
[165]/0/166/1/0x83b799c8/2777/IGN/31/32//none
[166]/0/167/1/0x83b7ae00/2775/IGN/33/34//none
[167]/0/168/1/0x83b7c238/2773/IGN/35/36//none
[168]/0/169/1/0x83b7d670/2771/IGN/37/38//none
[169]/0/170/1/0x83b7eaa8/2769/IGN/39/40//none
====================
END OF HANG ANALYSIS
====================
/u01/app/oracle/admin/orcl/udump/orcl_ora_16916.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /u01/app/oracle/product/10.2.0.1/db_1
System name: Linux
Node name: rhel
Release: 2.6.18-238.el5
Version: #1 SMP Sun Dec 19 14:22:44 EST 2010
Machine: x86_64
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 20
Unix process pid: 16916, image: oracle@rhel (TNS V1-V3)
*** 2017-11-23 15:08:44.057
Cannot find symbol
Cannot find symbol
Cannot find symbol
[root@rhel ~]#
orcl_ora_17000.txt
网站栏目：1OG数据库之librarycachelock、pin模拟结合hanganalyze定位及systemdump定位
当前链接：http://cdxtjz.cn/article/giisjo.html
