Oracle数据库ora-4025 Error的处理

mahan

一、问题分析

最近业务上报了一个错误，4025：
ORA-04025: maximum allowed library objectlock allocated for sql1

我们看下4025是一个什么错误：
$ oerrora 4025
04025, 00000, "maximum allowed library object lock allocated for %s%s%s%s%s"
// *Cause: Too many active locks for the object has been allocated. This error
//         can be a result of too many cursors kept open per session.
// *Action: Close cursors, reduce session_cached_cursors value.
通过上面的解释 我们可以看到 分配给这个对象上的active locks 太多了。
由于每个session打开的游标数太多超过了library object lock allocated的上限（通常active lock的上限值是65536），故产生了4025的报错。


二、问题解决方案

2.1 解决方案
关闭游标或者减少session_cached_cursors的值。
那为什么减少session_cached_cursors的值可以缓解4025问题的发生？
因为客户端执行sql时，相关的cursor会缓存在session_cached_cursors中。当客户端关闭游标时，有部分cursor会缓存在数据库的session_cached_cursors中，不被数据库清理，故减少session_cached_cursors的值会缓解4025问题的发生。
2.2 应急处理
那真的出现4025错误时，我们如何应急处理呐？
一种办法是应用程序关闭，这种肯定不太符合业务场景。
另一种方式就是数据库kill 相关的session。
可是我们如何才能准确的定位到相关的session id?
如果通过查询gv$session视图，通过执行的sql_id或者prev_sql_id是无法定位到执行的session_id的，因为此时事务已经执行完成了，会话状态处于sleep状态中了，但是没有释放游标，此时我们可以通过以下sql定位：
with sq as
        (select/*+ materialize */sid,serial#, program, saddr, event
            ,to_char(p1, 'xxxxxxxxxxxxxxx') p1, p1text
            ,to_char(p2, 'xxxxxxxxxxxxxxx') p2, p2text
            ,to_char(p3, 'xxxxxxxxxxxxxxx') p3, p3text
         from v$session
        )
      selectdistinct'alter system kill session ''' ||s.sid||','||s.serial#||''' immediate;'
        from v$libcache_locks l, x$kglob o, sq s
       where1= 1
         and o.kglnahsh in (&SQL HASH_VALUE)   --  这里是SQL HASH_VALUE,通过执行的sql找到对应的sql_hash_value即可
         and l.object_handle = o.kglhdadr
         and l.holding_user_session = s.saddr;

如果是rac环境，在多个节点上执行该sql即可查出相关节点上的session。