RE: library cache pin wait

From: Bobak, Mark <Mark.Bobak_at_proquest.com>
Date: Sat, 4 Oct 2008 22:19:42 -0400
Message-ID: <667C10D184B2674A82068E06A78382B527A370A1@AAPQMAILBX01V.proque.st>

When the library cache pin wait happens, run this script to see who's blocking you:

select decode(lob.kglobtyp, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',

                      4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
                      7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
                      11, 'PACKAGE BODY', 12, 'TRIGGER',
                      13, 'TYPE', 14, 'TYPE BODY',
                      19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB',
                      22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',
                      28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE',
                      32, 'INDEXTYPE', 33, 'OPERATOR',
                      34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',
                      40, 'LOB PARTITION', 41, 'LOB SUBPARTITION',
                      42, 'MATERIALIZED VIEW',
                      43, 'DIMENSION',
                      44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN',
                      48, 'CONSUMER GROUP',
                      51, 'SUBSCRIPTION', 52, 'LOCATION',
                      55, 'XML SCHEMA', 56, 'JAVA DATA',
                      57, 'SECURITY PROFILE', 59, 'RULE',
                      62, 'EVALUATION CONTEXT',
                     'UNDEFINED') object_type,
       lob.KGLNAOBJ object_name,
       pn.KGLpnMOD lock_mode_held,
       pn.KGLpnREQ lock_mode_requested,
       ses.sid,
       ses.serial#,
       ses.username
  FROM
       x$kglpn pn,
       v$session ses,
       x$kglob lob,
       v$session_wait vsw

  WHERE
   pn.KGLpnUSE = ses.saddr and
   pn.KGLpnHDL = lob.KGLHDADR
   and lob.kglhdadr = vsw.p1raw
   and vsw.event = 'library cache pin'
order by lock_mode_held desc
/

Hope that helps,

-Mark

---

From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] On Behalf Of Bort, Guillermo [guillermo.bort_at_eds.com] Sent: Saturday, October 04, 2008 9:43 PM To: oracledbaquestions_at_gmail.com; oracle-l_at_freelists.org Subject: RE: library cache pin wait

Does this happen if you run the same procedures again? Does any procedure contain any java code or call to any java program? If I recall admin workshop II correctly there is a way to explicitly pin an object to the LC, are you certain the code does not do this?

Does this happen only the first time you run the procedures or package after compilation? Have you run utlrp after compilation (in case there are any objects invalidated because of the recompilation).

Regards.

Guillermo Alan Bort
EDS - ITO DBA Main Group
Arias 1851
Ciudad Autónoma de Buenos Aires (C1429DXC) Argentina

Tel: +54 11 4704 3132
Tel: +54 11 4704 3000

E-mail: guillermo.bort_at_eds.com
E-mail: ardbagroup_at_eds.com
E-mail: ardbamain_at_eds.com

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Dba DBA Sent: Friday, October 03, 2008 10:50 PM
To: oracle-l_at_freelists.org
Subject: library cache pin wait

I recompiled a package. It compiled successfully.

The package has several procedures. Each has 1 large insert select. The all select from the same table, but insert to different tables.

This has happened to me before.

1. I run the package with 1 procedure from 1 session.
2. i try to run the same package with a different procedure in another sessions.

The second procedure waits with a library cache wait until the first sessions completes and exits. (I am nohupping this, so I dont know if the exit is required, it is in the script).

any ideas?

the code is NOT being recompiled. When I look at dba_blockers, I don't see any blocking lockers. i am looking at the wait on the second session. I know the package is not running by. 1. I have dbms_application_info calls inside the package and I do not see these in v$session 2. I check v$sess_io and no IO is incrementing for this session.

--

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

--

http://www.freelists.org/webpage/oracle-l Received on Sat Oct 04 2008 - 21:19:42 CDT