Library Cache Lock/Pin Issue

From: BN <bnsarma_at_gmail.com>
Date: Tue, 20 Mar 2007 15:14:53 -0400
Message-ID: <61292a9d0703201214p60c361eai14d59da5148221e4@mail.gmail.com>

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

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Mar 20 2007 - 14:14:53 CDT