library cache lock和library cache pin

最新推荐文章于 2019-07-20 15:03:01 发布

congdiku5262

最新推荐文章于 2019-07-20 15:03:01 发布

阅读量126

点赞数

什么是library cache lock
当客户端访问library cache中的对象时，需要获取该对象的handle lock，用于控制对library cache object的并发访问；
当解析或编译SQL、PL/SQL语句时需要获取相应object的library cache lock，sql执行时不会占用该lock；
当sql使用绑定变量或cursor_sharing=similar时，解析完毕后会进行绑定变量替换，此阶段依旧会占有library cache lock，这是因为bind peeking可能导致parse tree生成的执行计划产生变动，所以必须一直持有library cache lock直到sql执行为止；
Cursors(SQL、PL/SQL area)，pipe以及其他静态对象不需要获取；

什么是library cache pin
也是用于对library cache的并发访问，当客户端需要修改或检查object时，在获取lock后必须获取pin权限，即将相应的heap加载到内存中；
数据库对象在library cache中以两种方式存在：handle 和 object，只有当object存在于library cache时才可获取到pin；
Library cache pin表明其他会话正在以不兼容模式hold pin

两者关系
若要访问一个object，必须先lock object handle而后pin object heap；lock用于控制进程之间的并发，pin则管理协调cache的一致性；
Sql进行硬解析时需要用到library cache lock以防止其他进程访问或修改相关object；
当对object执行DDL时，引用该object的sql需要获取library cache pin，DDL会导致library cache中的object失效，进程必须将其从数据字典中重新加载以获取最新的object信息。

查看相应视图
Library cache pin
获取正在被等待的object信息
SELECT kglnaown "Owner", kglnaobj "Object"
FROM x$kglob
WHERE kglhdadr='&P1RAW'

获取阻塞会话信息
SELECT s.sid, kglpnmod "Mode", kglpnreq "Req"
    FROM x$kglpn p, v$session s
   WHERE p.kglpnuse=s.saddr
     AND kglpnhdl='&P1RAW'

Library cache lock
获取正在被等待的object handle
Select kgllkhdl Handle, kgllkreq Request, kglnaobj Object
From x$kgllk
Where kgllkses in (select saddr from v$session where event =’library cache lock’)
And kgllkreq > 0
HANDLE REQUEST OBJECT
-------- ---------- ------------------------------------------------------------
62d064dc 2 EMPLOYEES

获取blocker的信息
select kgllkses saddr,kgllkhdl handle,kgllkmod mod,kglnaobj object
from x$kgllk lock_a
where kgllkmod > 0
and exists (select lock_b.kgllkhdl from x$kgllk lock_b
where kgllkses in (select saddr from v$session where event =’library cache lock’)
and lock_a.kgllkhdl = lock_b.kgllkhdl
and kgllkreq > 0);

SADDR HANDLE MOD
-------- -------- ----------
OBJECT
------------------------------------------------------------
572eac94 62d064dc 3
EMPLOYEES

select sid,username,terminal,program from v$session where saddr = '572eac94'

dbms_shared_pool.purge应用
procedure purge (name varchar2, flag char DEFAULT 'P', heaps number DEFAULT 1);

Explanation: Purge the named object or particular heap(s) of the object.
Input arguments:
name: The name of the object to purge.
        There are two kinds of objects:
         PL/SQL objects, triggers, sequences, types and Java objects which are specified by name,
         SQL cursor objects which are specified by a twopart number. The value for this identifier
         is the concatenation of the 'address' and 'hash_value' columns from the v$sqlarea view.

flag: This is an optional parameter. If the parameter is not specified,
        the package assumes that the first parameter is the name of a
        package/procedure/function and will resolve the name. Otherwise,
        the parameter is a character string indicating what kind of object
        to purge the name identifies. The string is case insensitive.
        The possible values and the kinds of objects they indicate are
        given in the following table:

        Value Kind of Object to keep
        ----- ----------------------
            P package/procedure/function
            Q sequence
            R trigger
            T type
           JS java source
           JC java class
           JR java resource
           JD java shared data
            C cursor

heaps: heaps to purge. e.g if heap 0 and heap 6 are to be purged.
         1<<0 | 1<<6 => hex 0x41 => decimal 65. so specify heaps=>65.
         Default is 1 i.e heap 0 which means the whole object will be purged.

从10204开始，oracle推出此API用于清除library cache中的特定object;
比如当某个表因为统计信息缺失而产生一个bad sql并严重影响数据库性能，此时需立即对表进行统计信息收集，倘若此表只被该bad sql引用，则可调用grant select on table to dba将其invalid，待sql下次运行时重新解析；若此表被多个sql引用，grant则可能会导致严重的library cache pin和硬解析，此时可调用该API只将该bad sql清除出library cache，
Select address,hash_value from v$sqlarea where sql_text like ‘*****’;
Exec dbms_shared_pool.purge(‘address,hash_value’,’C’);
http://space.itpub.net/15480802/viewspace-701997

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

转载于:http://blog.itpub.net/15480802/viewspace-721526/