Library Cache Lock/Pin Issue
- From: BN <bnsarma@xxxxxxxxx>
- To: oracle_L_list <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 20 Mar 2007 15:14:53 -0400
Greetings
Oracle 8.1.7.4 and Sun Solaris 8
App hangs with Library Cache Lock/Pin issue:
I have Identified the Blocker and as soon as I kill the process(2) - Some
times 2 blockers, the Problem clears,
Sahred_pool free memory at the time of the problem is around 70MB.
I have been pulling all the hard Parsing SQL and giving it DEV Team to use
BIND Variables,
Could this be the reason why the App hangs?
Every time the Blocker belongs to a particualr Pro*C Program, they have a
view in this with around 94 tables.
$egrep -i " handle=cc70eb10" bn_hard_parse_4.txt |sort -k6
LIBRARY OBJECT HANDLE: handle=cc70eb10
LIBRARY OBJECT HANDLE: handle=cc70eb10
LIBRARY OBJECT LOCK: lock=d0119cf8 handle=cc70eb10 mode=N
LIBRARY OBJECT LOCK: lock=d959da94 handle=cc70eb10 mode=N
LIBRARY OBJECT PIN: pin=c39cf134 handle=cc70eb10 mode=X lock=d0119cf8
LIBRARY OBJECT PIN: pin=bf22f688 handle=cc70eb10 request=S
lock=d959da94
egrep -i " LIBRARY OBJECT HANDLE: handle=" XYZ_ora_12345.trc |sort -k4|wc -l
70730
egrep -i " LIBRARY OBJECT HANDLE: handle=" XYZ_ora_12345.trc |sort
-k4|uniq|wc -l
5560
XYZ_ora_12345.trc => system state dump
How can I find out why and what is Blocker waiting for? I couldn't pull the
sql for the blocker
I looked at v$sqlarea with no luck.
I want to resolve this issue.
Any suggestions will be highly appreciated.
--
Regards & Thanks
BN
Other related posts:
