请问大量的硬解析会产生library cache lock吗？
本帖最后由 不了峰 于 2013-12-30 14:42 编辑--份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 ,所以没有上传(由于应用做了实例指定)
library cache lock 5.03 "9029570680","9480598648","301" 0.07 handle address lock address 100*mode+namespace 做一个systemstate dump 266 发上来 Liu Maclean(刘相兵 发表于 2013-12-30 14:55 static/image/common/back.gif
做一个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
$ 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 static/image/common/back.gif
做一个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 static/image/common/back.gif
做一个systemstate dump 266 发上来
我知道是哪几个sid 会产生 library cache lock ，是否可以指定oradebug setmypid 来指定某几个sid oradebug setmypid
oradebug unlimit;
oradebug dump systemstate 266;
oradebug tracefile_name
即可 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:
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 htb=0x1d27c5f58 ssga=0x1d27c5690
user=24e5343e8 session=24e5343e8 count=0 flags=LRU/ 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/
kkkk-dddd-llll=0000-0709-0709 lock=N pin=0 latch#=14 hpc=bfce hlc=bfce
lwt=0x21a345120 ltm=0x21a345130
pwt=0x21a3450e8 ptm=0x21a3450f8
ref=0x21a345150 lnd=0x21a345168
LOCK INSTANCE LOCK: id=LBf3d9b11ff88b1d97
PIN INSTANCE LOCK: id=NBf3d9b11ff88b1d97 mode=S release=F flags=
INVALIDATION INSTANCE LOCK: id=IV00004f2b02022c23 mode=S
LIBRARY OBJECT: object=210f7ef10
type=TABL flags=EXS/LOC pflags= 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 htb=0x1d27c5bf8 ssga=0x1d27c5690
user=24e5343e8 session=24e5343e8 count=1 flags= 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/
kkkk-dddd-llll=0000-0001-0001 lock=N pin=0 latch#=8 hpc=0002 hlc=0002
lwt=0x20a663ef8 ltm=0x20a663f08
pwt=0x20a663ec0 ptm=0x20a663ed0
ref=0x20a663f28 lnd=0x20a663f40
LIBRARY OBJECT: object=2205236d0
type=CRSR flags=EXS pflags= 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 htb=0x1d27c5f58 ssga=0x1d27c5690
user=24e5343e8 session=24e5343e8 count=0 flags=LRU/ 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/
kkkk-dddd-llll=0000-0709-0709 lock=N pin=0 latch#=14 hpc=bfce hlc=bfce
lwt=0x21a345120 ltm=0x21a345130
pwt=0x21a3450e8 ptm=0x21a3450f8
ref=0x21a345150 lnd=0x21a345168
LOCK INSTANCE LOCK: id=LBf3d9b11ff88b1d97
PIN INSTANCE LOCK: id=NBf3d9b11ff88b1d97 mode=S release=F flags=
INVALIDATION INSTANCE LOCK: id=IV00004f2b02022c23 mode=S
LIBRARY OBJECT: object=210f7ef10
type=TABL flags=EXS/LOC pflags= 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 static/image/common/back.gif
1.Troubleshooting Library Cache: Lock, Pin and Load Lock (Doc ID 444560.1)
What is "Library cache lo ...
谢谢，谢谢～
awr看每秒400多个硬解析。。
到时看应用端能不能改，改完后，再看看是否还会有明显的library cache lock 等待事件的产生
再时我再来更新贴子
非常感谢 ！
页:
[1]