1OG数据库之library cache lock、pin模拟结合hanganalyze定位及systemdump定位
发表于:2024-09-24 作者:千家信息网编辑
千家信息网最后更新 2024年09月24日,session 1:SQL> select sid from v$mystat where rownum=1;SID----------148SQL> create or replace proced
千家信息网最后更新 2024年09月24日1OG数据库之library cache lock、pin模拟结合hanganalyze定位及systemdump定位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
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
阻塞
定位
文件
面的
数据
数据库
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
生命周期和个别软件开发模型
手件服务器
软件开发高性价比电脑
软件开发管理复杂性
速达服务器无法启动
志愿数据库设计
ncdb数据库
杭州蚁修网络技术有限公司
java软件开发笔记本
东城区管理网络技术服务怎么样
软件开发项目的风险纬度
数据库量数值范围内
重庆电信dns服务器是什么
服务器编程可以开什么公司
wet服务器硬件配置
做单片机软件开发压力大吗
微服务无法查询数据库的原因
数据库文件怎么导出sql
长沙风信子网络技术
重庆安全隐患数据库
软件开发员招聘广告样稿
淘宝店铺网络安全问题
徐州网络安全审计系统咨询品牌
汕头软件开发哪家强
iar软件开发流程
无锡信息软件开发商家
sql数据库停用方法
九阴绝学服务器
广西公安网络安全专业测试
服务器QQ机器人对接命令