分享
 
 
 

library cache lock 的解决案例

王朝other·作者佚名  2006-01-09
窄屏简体版  字體: |||超大  

下午,业务人员报告,执行任何和zzss03201281cs_no表有关的操作都会hang住,包括desc zzss03201281cs_no,也会hang在那里

第一感觉是锁了,于是,我看看锁

SQL> select * from v$lock where block=1;

no rows selected

SQL>

SQL> select * from gv$lock where block=1;

no rows selected

SQL>

再看看等待事件:

SQL> col event for a30

SQL> l

1* select event,p1,p2,sid from v$session_wait where event='library cache lock'

SQL> /

EVENT P1 P2 SID

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

library cache lock 1.3835E+19 1.3835E+19 32

SQL> /

EVENT P1 P2 SID

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

library cache lock 1.3835E+19 1.3835E+19 32

SQL> /

EVENT P1 P2 SID

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

library cache lock 1.3835E+19 1.3835E+19 32

。。。

奇怪,怎么这么多 library cache lock ?

SQL> show user

USER is "SYS"

SQL> exec dbms_system.set_ev(32,27506,10046,12,'');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.10

SQL> l

1 SELECT d.VALUE

2 || '/'

3 || LOWER (RTRIM (i.INSTANCE, CHR (0)))

4 || '_ora_'

5 || p.spid

6 || '.trc' trace_file_name

7 FROM (SELECT p.spid

8 FROM v$mystat m, v$session s, v$process p

9 WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,

10 (SELECT t.INSTANCE

11 FROM v$thread t, v$parameter v

12 WHERE v.NAME = 'thread'

13 AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,

14 (SELECT VALUE

15 FROM v$parameter

16* WHERE NAME = 'user_dump_dest') d

SQL> /

TRACE_FILE_NAME

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

/ora9i/app/oracle/admin/csmisc/udump/csmisc2_ora_2708.trc

Elapsed: 00:00:00.10

SQL>

SQL> select xidusn, object_id, session_id, locked_mode from v$locked_object;

XIDUSN OBJECT_ID SESSION_ID LOCKED_MODE

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

14 35202 31 3

15 18 30 3

SQL> col object_name format a30

SQL> select owner,object_name,status from dba_objects where object_id=35202;

OWNER

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

OBJECT_NAME

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

STATUS

-------

SYS

PLAN_TABLE

VALID

SQL>

这个对象显然不是我们关注的。

SQL> l

/ 1* select owner,object_name,status from dba_objects where object_id=18

SQL>

OWNER OBJECT_NAME STATUS

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

SYS OBJ$ VALID

就是这个对象搞得,估计是开发人员异常退出一些进程

SQL> c/18/30

1* select serial#,username,command,lockwait,status,schemaname,osuser,machine,terminal,program,module from v$session where sid=30

SQL> /

SERIAL# USERNAME COMMAND LOCKWAIT STATUS

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

SCHEMANAME OSUSER

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

MACHINE

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

TERMINAL PROGRAM

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

MODULE

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

17921 PUBUSER 0 ACTIVE

PUBUSER report16

cs_dc02

SERIAL# USERNAME COMMAND LOCKWAIT STATUS

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

SCHEMANAME OSUSER

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

MACHINE

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

TERMINAL PROGRAM

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

MODULE

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

sqlplus@cs_dc02 (TNS V1-V3)

SQL*Plus

SQL> select b.username username, b.terminal terminal,b.program program,b.spid

2 from v$session a, v$process b

where a.PADDR=b.ADDR and a.sid ='&sid';

3 Enter value for sid: 30

old 3: where a.PADDR=b.ADDR and a.sid ='&sid'

new 3: where a.PADDR=b.ADDR and a.sid ='30'

USERNAME TERMINAL

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

PROGRAM SPID

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

ora9i UNKNOWN

oracle@cs_dc02 (TNS V1-V3) 835

很显然,是由于report16用户执行了某些DDL操作,然后,异常退出,造成系统的锁(估计和bug有关,有待考证)

SQL> host

ora9i@cs_dc02:/ora9i/app/oracle/product/920/rdbms/admin > ps -ef | grep 835

ora9i 4619 4617 1 14:48:18 pts/te 0:00 grep 835

ora9i 835 1 0 Jan 5 ? 0:01 oraclecsmisc2 (LOCAL=NO)

ora9i@cs_dc02:/ora9i/app/oracle/product/920/rdbms/admin > kill 835

ora9i@cs_dc02:/ora9i/app/oracle/product/920/rdbms/admin > exit

SQL> select xidusn, object_id, session_id, locked_mode from v$locked_object;

XIDUSN OBJECT_ID SESSION_ID LOCKED_MODE

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

14 35202 31 3

SQL>

kill掉这个进程后,问题解决了。(遗憾的是,忘了看看这个家伙执行的sql了,呵呵)

SQL> desc zzss03201281cs_no

ERROR:

ORA-04043: object zzss03201281cs_no does not exist

SQL> desc zzss03201281cs_no

ERROR:

ORA-04043: object zzss03201281cs_no does not exist

SQL>

SQL> exec dbms_system.set_ev(32,27506,0,0,'');

PL/SQL procedure successfully completed.

SQL>

查看trace文件,:

果然大量的wait:

WAIT #1: nam='library cache lock' ela= 316 p1=-4611686013647472824 p2=-4611686013691747544 p3=1301

WAIT #1: nam='library cache lock' ela= 326 p1=-4611686013647472824 p2=-4611686013691747544 p3=1301

WAIT #1: nam='library cache lock' ela= 398 p1=-4611686013647483736 p2=-4611686013691747816 p3=1301

WAIT #1: nam='library cache lock' ela= 552 p1=-4611686013647483736 p2=-4611686013691747816 p3=1301

WAIT #1: nam='library cache lock' ela= 330 p1=-4611686013649700264 p2=-4611686013691715248 p3=1301

WAIT #1: nam='library cache lock' ela= 141 p1=-4611686013649700264 p2=-4611686013691715248 p3=1301

WAIT #1: nam='library cache lock' ela= 223 p1=-4611686013647485472 p2=-4611686013691762016 p3=1301

WAIT #1: nam='library cache lock' ela= 93 p1=-4611686013647485472 p2=-4611686013691762016 p3=1301

WAIT #1: nam='library cache lock' ela= 223 p1=-4611686013595934816 p2=-4611686013642107320 p3=1301

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
推荐阅读
 
 
 
>>返回首頁<<
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有