Re: DBA_HIST_ACTIVE_SESS_HISTORY library cache pin waits

From: Riyaj Shamsudeen <riyaj.shamsudeen_at_gmail.com>
Date: Wed, 26 Feb 2014 16:26:53 -0800
Message-ID: <CAA2Dszwd4EnLAi-GNxM46-iGAXTmaCci_Srwkh7EWMa+o2hNZQ_at_mail.gmail.com>

Library cache pins are held in share mode (kglpnmod=2) during package execution and pins are requested in exclusive mode pglpnreq=3) for package (re)creation. So, unless I missed a new feature recently, you can't create a package while the package is still being executed in another session.

Here is small test case:

Copy the contents to a file , say pkg1.sql -----cut---
create or replace package pkg1 as
  procedure proc1( p_sleep in number);
end;
/

create or replace package body pkg1 as
  procedure proc1 (p_sleep in number) is     begin
      dbms_lock.sleep(p_sleep);
    end;
end;
/

--- cut----
My test case uses sqlplus.

1. From session #1, create the above package: _at_pkg1.sql
2. From session #2, execute the package as exec pkg1.proc1(300); -- this should sleep for 5 minutes.
3. revert to session #1 and try recreating the package _at_pkg1.sql. This should hang ( ctrl+c after a minute, logoff, and retry.it should hang.)
4. Open a new session, and execute the following to see the library pin contention. Require sys access (I know, I know, I need to update the script to use 11g new view).

select ses.sid, ses.serial#, ses.username, ses.program, ses.module, ses.machine, pin.* from x$kglpn pin, v$session ses where kglpnhdl in
(
select kglpnhdl from x$kglpn
where kglpnreq >0
)
and pin.kglpnses=ses.saddr
/

I am really not sure, how the OP was able to recreate the package while the package is in use? I tested this in 11.2.0.4 (RAC & exadata if that make any difference). Is the problem more deeper than single package issue  (meaning is there a dependency issue we are missing?)

Matt
  What version of RDBMS and platform? Are you sure the original package was created immediately?

Cheers

Riyaj Shamsudeen
Principal DBA,
Ora!nternals - http://www.orainternals.com - Specialists in Performance, RAC and EBS
Blog: http://orainternals.wordpress.com/ Oracle ACE Director and OakTable member <http://www.oaktable.com/>

Co-author of the books: Expert Oracle
Practices<http://tinyurl.com/book-expert-oracle-practices/> , Pro Oracle SQL, <http://tinyurl.com/ahpvms8> <http://tinyurl.com/ahpvms8>Expert
RAC Practices 12c. <http://tinyurl.com/expert-rac-12c> Expert PL/SQL practices <http://tinyurl.com/book-expert-plsql-practices>

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

Received on Thu Feb 27 2014 - 01:26:53 CET