RE: library cache pin wait

  • From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxx>
  • To: "guillermo.bort@xxxxxxx" <guillermo.bort@xxxxxxx>, "oracledbaquestions@xxxxxxxxx" <oracledbaquestions@xxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
  • Date: Sat, 4 Oct 2008 22:19:42 -0400

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@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] On Behalf 
Of Bort, Guillermo [guillermo.bort@xxxxxxx]
Sent: Saturday, October 04, 2008 9:43 PM
To: oracledbaquestions@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
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@xxxxxxx
E-mail: ardbagroup@xxxxxxx
E-mail: ardbamain@xxxxxxx

From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Dba DBA
Sent: Friday, October 03, 2008 10:50 PM
To: oracle-l@xxxxxxxxxxxxx
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.

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


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


Other related posts: