library cache lock解决一则

最新推荐文章于 2023-03-16 16:51:06 发布

congbu2215

最新推荐文章于 2023-03-16 16:51:06 发布

阅读量149

点赞数

文章标签：运维数据库

这是同事报上来的一个问题。最终发现是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下的表tab2。SQL类似：

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

2-误操作，再次创建user1下创建了私有同义词syn1指向原有私有同义词syn1。SQL类似：

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。10g中ORACLE已将v$session_wait中的等待事件相关信息整合到了v$session中，这样查询起来更方便了。这里证实了前面提到的SQL没有解析完成的说法（对象权限检查是解析阶段的工作）。

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

4-获取引起阻塞的session

获得持有该library cache lock的session，SQL如下：

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上文有）。发现有一个执行drop的session成为僵尸进程，他阻塞了之后的所有select和drop的会话，这些被阻塞的会话有些被人工停止了，其中大部分只是在客户端强行关掉，再主机上的进程依然存在，而且也有一些也成为了僵尸进程。至此分析完成，原因也找到了。

处理过程

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

查询ORACLE session对应的OS进程spid的SQL如下：

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/