library cache lock解决一则

这是同事报上来的一个问题。最终发现是library cache lock阻塞会话。

问题的处理并不难,终止持有library cache lock的会话(部分是长时间执行不出来的SQL,另一部分是OS僵死进程)来消除library cache lock

这次在分析问题上走了弯路,在此记录作为经验。

关键字:library cache lock 等待事件

[@more@]

问题描述

前一天下班,同事让我忙看一个问题。他反映做了如下操作后出现问题:

1-在数据库DB1的用户user1下创建了私有同义词syn1,该同义词通过公共 database link db_link1指向数据库DB2的用户user2下的表tab2SQL类似:

create or replace synonym syn1 for user2.tab2@db_link1;

2-误操作,再次创建user1下创建了私有同义词syn1指向原有私有同义词syn1SQL类似:

create or replace synonym syn1 for syn1;

然后出现问题,对该同义词做操作会话似乎假死在那里,一直执行不返回,sql类似:

select * from syn1;

drop synonym syn1;

由于是错误创建,用户希望drop该同义词。

环境:

·OS

AIX testDB 3 5 0055054E4C00

·DB

SQL> select * from v$version;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi

PL/SQL Release 10.2.0.3.0 - Production

CORE 10.2.0.3.0 Production

TNS for IBM/AIX RISC System/6000: Version 10.2.0.3.0 - Productio

NLSRTL Version 10.2.0.3.0 - Production

·第三方软件

PL/SQL DEVELOPER 7

分析过程

用户(DB开发同事)描述了自己的操作,然后出现问题,感觉是产生了某种死循环(同义词指向同义词本身)。我在这里犯了一个错误:以用户的感觉作为出发点,这种先入为主想法使分析过程走了弯路。

在此总结两条个人经验:

DBA处理DB问题的唯一出发点就是客观现象。

DBA处理DB问题时一定要先独立思考,后讨论参考。

用户的思维和DBA不一样,不仅仅指专业程度(一个资深DB开发者比一个DBA rookie更专业的情况也是很多的),而是对待问题的角度,这一点上很难一样。用户的感觉和判断不能作为DBA处理问题的依据,最多只能作为参考。甚至用户所描述的,做过什么操作,然后如何如何都只能作为参考,因为有时他们说的不是关键点甚至是错的(本案就是这样),并且用户自己都不知道。一开始就采信这些将会使DBA的出发角度和关注焦点偏移。

这次以用户的思维作为先入为主的想法,开始分析:

1-获取后台trace文件

因为觉得是产生了某种死循环导致使用这个对象有问题,所以考虑看看到底SQL在内部被解析成什么。于是 set sql_trace=true,执行了select命令(一直没有返回)一段时间后,关闭sql_trace去主机上找生成的trace文件(生成trace的具体操作略)。结果是后台没有生成trace文件。难道sql_trace有问题?运行了另一个能正常返回的SQL,顺利生成后台trace文件,证明sql_trace没问题。这种情况使我产生了疑惑:明明有SQL在执行中,为什么没有产生trace文件?

2-获取sqltext

带着疑惑我查询该会话在执行什么,预期是可以查询出正在执行的select命令,但结果是什么都没执行,sqltext是空的。SQL如下:

SELECT sql_text

FROM v$sqltext a

WHERE a.hash_value = (SELECT sql_hash_value

FROM v$session b

WHERE b.SID = &sid);

3-获取等待事件

这时我终于意识到,其实这个SQL根本就没有开始执行,甚至都没有解析完成,sql_hash_value都是0。这大概可以解释为什么没有生成trace,因为什么都没做嘛!这时的思路终于回到正轨:检查SQL的等待事件。它一定是在等什么才一直没有返回,甚至都没有完成解析。

查询session的等待事件,SQL如下:

select event from v$session v where v.sid=1517 and v.SERIAL#=5179;

结果是存在library cache lock。10gORACLE已将v$session_wait中的等待事件相关信息整合到了v$session中,这样查询起来更方便了。这里证实了前面提到的SQL没有解析完成的说法(对象权限检查是解析阶段的工作)

以下处理library cache lock的方法来自盖国强的《深入解析ORACLE》。感谢eygle,从你的书中我获得了很多帮助和很大提高。

4-获取引起阻塞的session

获得持有该library cache locksessionSQL如下:

SELECT a.SID,

a.SERIAL#,

a.username,

a.osuser,

a.program,

b.addr,

b.kglpnadr,

b.kglpnuse,

b.kglpnses,

b.kglpnhdl,

b.kglpnlck,

b.kglpnmod,

b.kglpnreq

FROM v$session a, x$kglpn b

WHERE a.saddr = b.kglpnuse

AND b.kglpnmod <> 0

AND b.kglpnhdl IN

(SELECT p1raw FROM v$session_wait WHERE event = 'library cache lock');

5-找到原因

可以再通过session信息查询到引起阻塞的具体SQL(查询SQL上文有)。发现有一个执行dropsession成为僵尸进程,他阻塞了之后的所有selectdrop的会话,这些被阻塞的会话有些被人工停止了,其中大部分只是在客户端强行关掉,再主机上的进程依然存在,而且也有一些也成为了僵尸进程。至此分析完成,原因也找到了。

处理过程

处理过程就很简单了,杀!将所有相关session并对应的OS进程团灭。

查询ORACLE session对应的OS进程spidSQL如下:

select p.spid, s.sid, s.SERIAL#

from v$process p, v$session s

where p.ADDR = s.PADDR

and s.SID = &sid

and s.SERIAL# = &serial;

然后drop synonym syn1;done

后记

最后要补充说明一下,用户所说的第2步,就是误操作的一步。这个步骤是不可能实现的。在一个用户下不可能创建两个类型和名字都相同的对象,比如此案中的私有同义词syn1。这一点在最初用户表述时没有第一时间甄别,也是使分析走了弯路的原因之一。在之后的分析中发现,在重现这一问题的过程中,这个误操作是无法执行的,ORACLE会报出ora-01471。本案中用户提供的操作信息完全不是产生问题的原因。

经本案,再次发现自己的不足。经验需要积累,技术细节需要进一步巩固。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/21129591/viewspace-1052408/,如需转载,请注明出处,否则将追究法律责任。

转载于:http://blog.itpub.net/21129591/viewspace-1052408/

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值