请问大量的硬解析会产生library cache lock吗？

不了峰

--份awr,及相应时间内的ash报告

os :Linux Rehat5.4  
oracle :10.2.0.5 64bit RAC  双节点


弄不明白，为什么会产生大量的library cache lock ，是由于大量的硬解析造成的？还有频繁调用存储过程引起的，
还是别的什么原因？

通过sql查询 ：
SELECT * FROM v$session_wait WHERE wait_class<>'Idle';
发现library cache lock 事件产生等待的时间很短

谢谢～

注:上传的节点1的awr , 节点2其本没有负载，也没有出现library cache lock ,所以没有上传(由于应用做了实例指定)

awr_rac1.html (445.63 KB, 下载次数: 704)

2013-12-30 14:31 上传

点击文件名下载附件

ash_rac1.html (31.97 KB, 下载次数: 703)

2013-12-30 14:31 上传

点击文件名下载附件

Liu Maclean(刘相兵

library cache lock        5.03        "9029570680","9480598648","301"        0.07        handle address        lock address        100*mode+namespace

Liu Maclean(刘相兵

做一个systemstate dump 266 发上来

不了峰

Liu Maclean(刘相兵 发表于 2013-12-30 14:55
做一个systemstate dump 266 发上来

刘大，我这样子做完的trc文件有1.2G，压缩后1百多兆
SQL> oradebug setmypid
Statement processed.
SQL> oradebug unlimit
Statement processed.
SQL> oradebug dump systemstate 226
Statement processed.
SQL> oradebug tracefile_name
/oracle/app/oracle/admin/gpsrac/udump/gpsrac1_ora_4919.trc


[oracle@dwptdb1 /oracle/app/oracle/admin/gpsrac/udump]
$ ll
total 1306932
-rw-r----- 1 oracle oinstall 1210947942 Dec 30 15:07 gpsrac1_ora_4919.trc
-rw-r--r-- 1 oracle oinstall  126026076 Dec 30 15:10 gpsrac1_ora_4919.trc.zip

不了峰

Liu Maclean(刘相兵 发表于 2013-12-30 14:55
做一个systemstate dump 266 发上来

trc文件很大，是否有上传的必要，还是要做个
alter session set max_dump_file_size=unlimited;
    alter session set events 'immediate trace name systemstate level 10';

不了峰

Liu Maclean(刘相兵 发表于 2013-12-30 14:55
做一个systemstate dump 266 发上来

我知道是哪几个sid 会产生 library cache lock ，是否可以指定oradebug setmypid 来指定某几个sid

Liu Maclean(刘相兵

oradebug setmypid
oradebug unlimit;
oradebug dump systemstate 266;
oradebug tracefile_name

即可

Liu Maclean(刘相兵

1.Troubleshooting Library Cache: Lock, Pin and Load Lock (Doc ID 444560.1)
What is "Library cache lock" ?

This event controls the concurrency between clients of the library cache. It acquires a lock on
the object handle so that either:

* One client can prevent other clients from accessing the same object.

* The client can maintain a dependency for a long time (for example, so that no other client can change the object).

This lock is also obtained to locate an object in the library cache.
Library cache lock will be obtained on database objects referenced during parsing or compiling of
SQL or PL/SQL statements (table, view, procedure, function, package, package body, trigger, index, cluster, synonym).
The lock will be released at the end of the parse or compilation.

Cursors (SQL and PL/SQL areas), pipes and any other transient objects do not use this lock.
Library cache lock is not deadlock sensitive and the operation is synchronous.

Parameters:

* handle address
Address of the object being loaded.

* lock address
Address of the load lock being used. This is not the same thing as a latch or an enqueue, it is a State Object.

* Mode
Indicates the data pieces of the object which need to be loaded.

* Namespace
The name of the object namespace as it is displayed in V$DB_OBJECT_CACHE view

How can Library cache lock be reduced?

In general , high contention on library cache lock is usually a result of an under-sized shared pool or
non-sharing of sql. Some ways of reducing the contention are:

Liu Maclean(刘相兵

21a345078

     for 'library cache lock' count=1 wait_time=0.000235 sec
                handle address=21a345078, lock address=235167478, 100*mode+namespace=12d
                               
                               
                               

SO: 0x1fe159988, type: 53, owner: 0x24e5343e8, flag: INIT/-/-/0x00
      LIBRARY OBJECT LOCK: lock=1fe159988 handle=21a345078 mode=N
      call pin=(nil) session pin=(nil) hpc=0000 hlc=0000
      htl=0x1fe159a08[0x1d27c5f58,0x1d27c5f58] htb=0x1d27c5f58 ssga=0x1d27c5690
      user=24e5343e8 session=24e5343e8 count=0 flags=LRU/[4044] savepoint=0x148e7c0
      LIBRARY OBJECT HANDLE: handle=21a345078 mtx=0x21a3451a8(0) lct=56028761 pct=56028445 cdp=0
      name=FJDXGPS.GK_T_BS_PARAM
      hash=f3d9b11ff88b1d97d201eb5b00b8dc7b timestamp=06-02-2011 01:43:34
      namespace=TABL flags=KGHP/TIM/XLR/[00000020]
      kkkk-dddd-llll=0000-0709-0709 lock=N pin=0 latch#=14 hpc=bfce hlc=bfce
      lwt=0x21a345120[0x21a345120,0x21a345120] ltm=0x21a345130[0x21a345130,0x21a345130]
      pwt=0x21a3450e8[0x21a3450e8,0x21a3450e8] ptm=0x21a3450f8[0x21a3450f8,0x21a3450f8]
      ref=0x21a345150[0x21a345150,0x21a345150] lnd=0x21a345168[0x25238c1f0,0x2524740e8]
        LOCK INSTANCE LOCK: id=LBf3d9b11ff88b1d97
        PIN INSTANCE LOCK: id=NBf3d9b11ff88b1d97 mode=S release=F flags=[00]
        INVALIDATION INSTANCE LOCK: id=IV00004f2b02022c23 mode=S
        LIBRARY OBJECT: object=210f7ef10
        type=TABL flags=EXS/LOC[0005] pflags=[0000] status=VALD load=0
        DATA BLOCKS:
        data#     heap  pointer    status pins change whr
        ----- -------- -------- --------- ---- ------ ---
            0 21a2c6e98 210f7f028 I/-/A/-/-    0 NONE   00
            3 1e8db1c60 1fb34d410 I/-/A/-/-    0 NONE   00
            8 210f7f210 1f491c808 I/-/A/-/-    0 NONE   00
            9 1e8db1b98 1d8e4bd10 I/-/A/-/-    0 NONE   00
           10 1e8db1ad0 1e02b8a28 I/-/A/-/-    0 NONE   00
                 
                 
      SO: 0x222031508, type: 53, owner: 0x24e5343e8, flag: INIT/-/-/0x00
      LIBRARY OBJECT LOCK: lock=222031508 handle=20a663e50 mode=N
      call pin=(nil) session pin=(nil) hpc=0000 hlc=0000
      htl=0x222031588[0x1d27c5bf8,0x2223ce808] htb=0x1d27c5bf8 ssga=0x1d27c5690
      user=24e5343e8 session=24e5343e8 count=1 flags=[0000] savepoint=0x52c11b15
      LIBRARY OBJECT HANDLE: handle=20a663e50 mtx=0x20a663f80(1) lct=1 pct=1 cdp=1
      name=update GK_T_BS_PARAM set  last_time=sysdate where car_id=257228
      hash=195f90cee08c7cd5e5473e741ed86e45 timestamp=12-30-2013 15:04:53
      namespace=CRSR flags=RON/KGHP/TIM/PN0/SML/KST/DBN/MTX/[120100d0]
      kkkk-dddd-llll=0000-0001-0001 lock=N pin=0 latch#=8 hpc=0002 hlc=0002
      lwt=0x20a663ef8[0x20a663ef8,0x20a663ef8] ltm=0x20a663f08[0x20a663f08,0x20a663f08]
      pwt=0x20a663ec0[0x20a663ec0,0x20a663ec0] ptm=0x20a663ed0[0x20a663ed0,0x20a663ed0]
      ref=0x20a663f28[0x20a663f28,0x20a663f28] lnd=0x20a663f40[0x20a663f40,0x20a663f40]
        LIBRARY OBJECT: object=2205236d0
        type=CRSR flags=EXS[0001] pflags=[0000] status=VALD load=0
        CHILDREN: size=16
        child#    table reference   handle
        ------ -------- --------- --------
             0 22f9eec50 1b040d538 21bb09ef0
        DATA BLOCKS:
        data#     heap  pointer    status pins change whr
        ----- -------- -------- --------- ---- ------ ---
            0 20a502a88 2205237e8 I/P/A/-/-    0 NONE   00
                       

      SO: 0x1fe159988, type: 53, owner: 0x24e5343e8, flag: INIT/-/-/0x00
      LIBRARY OBJECT LOCK: lock=1fe159988 handle=21a345078 mode=N
      call pin=(nil) session pin=(nil) hpc=0000 hlc=0000
      htl=0x1fe159a08[0x1d27c5f58,0x1d27c5f58] htb=0x1d27c5f58 ssga=0x1d27c5690
      user=24e5343e8 session=24e5343e8 count=0 flags=LRU/[4044] savepoint=0x148e7c0
      LIBRARY OBJECT HANDLE: handle=21a345078 mtx=0x21a3451a8(0) lct=56028761 pct=56028445 cdp=0
      name=FJDXGPS.GK_T_BS_PARAM
      hash=f3d9b11ff88b1d97d201eb5b00b8dc7b timestamp=06-02-2011 01:43:34
      namespace=TABL flags=KGHP/TIM/XLR/[00000020]
      kkkk-dddd-llll=0000-0709-0709 lock=N pin=0 latch#=14 hpc=bfce hlc=bfce
      lwt=0x21a345120[0x21a345120,0x21a345120] ltm=0x21a345130[0x21a345130,0x21a345130]
      pwt=0x21a3450e8[0x21a3450e8,0x21a3450e8] ptm=0x21a3450f8[0x21a3450f8,0x21a3450f8]
      ref=0x21a345150[0x21a345150,0x21a345150] lnd=0x21a345168[0x25238c1f0,0x2524740e8]
        LOCK INSTANCE LOCK: id=LBf3d9b11ff88b1d97
        PIN INSTANCE LOCK: id=NBf3d9b11ff88b1d97 mode=S release=F flags=[00]
        INVALIDATION INSTANCE LOCK: id=IV00004f2b02022c23 mode=S
        LIBRARY OBJECT: object=210f7ef10
        type=TABL flags=EXS/LOC[0005] pflags=[0000] status=VALD load=0
        DATA BLOCKS:
        data#     heap  pointer    status pins change whr
        ----- -------- -------- --------- ---- ------ ---
            0 21a2c6e98 210f7f028 I/-/A/-/-    0 NONE   00
            3 1e8db1c60 1fb34d410 I/-/A/-/-    0 NONE   00
            8 210f7f210 1f491c808 I/-/A/-/-    0 NONE   00
            9 1e8db1b98 1d8e4bd10 I/-/A/-/-    0 NONE   00
           10 1e8db1ad0 1e02b8a28 I/-/A/-/-    0 NONE   00
      ----------------------------------------                       
                       
                       
      name=update GK_T_BS_PARAM set  last_time=sysdate where car_id=257017
      name=update GK_T_BS_PARAM set  last_time=sysdate where car_id=257010
      name=update GK_T_BS_PARAM set  last_time=sysdate where car_id=257011
      name=update GK_T_BS_PARAM set  last_time=sysdate where car_id=257018
      name=update GK_T_BS_PARAM set  last_time=sysdate where car_id=257009
      name=update GK_T_BS_PARAM set  last_time=sysdate where car_id=257005
      name=update GK_T_BS_PARAM set  last_time=sysdate where car_id=257004
      name=update GK_T_BS_PARAM set  last_time=sysdate where car_id=257006
      name=update GK_T_BS_PARAM set  last_time=sysdate where car_id=257002
      name=update GK_T_BS_PARAM set  last_time=sysdate where car_id=257003
      name=update GK_T_BS_PARAM set  last_time=sysdate where car_id=257045
      name=update GK_T_BS_PARAM set  last_time=sysdate where car_id=257043
      name=update GK_T_BS_PARAM set  last_time=sysdate where car_id=257044
      name=update GK_T_BS_PARAM set  last_time=sysdate where car_id=257041
      name=update GK_T_BS_PARAM set  last_time=sysdate where car_id=257032
      name=update GK_T_BS_PARAM set  last_time=sysdate where car_id=257038
      name=update GK_T_BS_PARAM set  last_time=sysdate where car_id=257037
      name=update GK_T_BS_PARAM set  last_time=sysdate where car_id=257039
      name=update GK_T_BS_PARAM set  last_time=sysdate where car_id=257028
      name=update GK_T_BS_PARAM set  last_time=sysdate where car_id=257031
      name=update GK_T_BS_PARAM set  last_time=sysdate where car_id=257029
      name=update GK_T_BS_PARAM set  last_time=sysdate where car_id=257030
      name=update GK_T_BS_PARAM set  last_time=sysdate where car_id=257027
      name=update GK_T_BS_PARAM set  last_time=sysdate where car_id=257024
      name=update GK_T_BS_PARAM set  last_time=sysdate where car_id=257023
      name=update GK_T_BS_PARAM set  last_time=sysdate where car_id=257020
      name=update GK_T_BS_PARAM set  last_time=sysdate where car_id=257021
      name=update GK_T_BS_PARAM set  last_time=sysdate where car_id=257001
      name=update GK_T_BS_PARAM set  last_time=sysdate where car_id=257000
      name=update GK_T_BS_PARAM set  last_time=sysdate where car_id=256998
      name=FJDXGPS.GK_T_BS_PARAM
      name=update GK_T_BS_PARAM set  last_time=sysdate where car_id=298145
      name=update GK_T_BS_PARAM set  last_time=sysdate where car_id=284920
          
          
          
比较明显的update GK_T_BS_PARAM  这语句没有绑定变量 car_id

不了峰

Liu Maclean(刘相兵 发表于 2013-12-30 17:11
1.Troubleshooting Library Cache: Lock, Pin and Load Lock (Doc ID 444560.1)
What is "Library cache lo ...

谢谢，谢谢～

awr看每秒400多个硬解析。。

到时看应用端能不能改，改完后，再看看是否还会有明显的library cache lock 等待事件的产生

再时我再来更新贴子

非常感谢 ！