Re: library cache pin while trying to recreate a stored proc

From: David Fitzjarrell <oratune_at_yahoo.com>
Date: Wed, 26 Oct 2011 15:54:21 -0700 (PDT)
Message-ID: <1319669661.61476.YahooMailNeo_at_web65401.mail.ac4.yahoo.com>

Edition-based redefinition is installed, even though you're not using it, so Oracle is creating an AE lock for each session connected to the database.  There's no harm in this; they don't lock anything yet they are present and ready to be pressed into service should the need/desire arise.  
There are other 'fish' in the 'database tuning sea' to hunt and possibly eradicate.  You need not spend time on AE locks. David Fitzjarrell

From: dd yakkali <dd.yakkali_at_gmail.com> To: tim_at_evdbt.com
Cc: oracle-l_at_freelists.org
Sent: Wednesday, October 26, 2011 3:26 PM Subject: Re: library cache pin while trying to recreate a stored proc

Tim,
Thanks for the reply. I ran the query and found that there are a lot of them with that id1 and I do not think they are all executing that procedure. Again i am more curious about AE type locks, I read they are Edition based lock types and we are not using that edition based features unless oracle uses them internally

SELECT * FROM GV$LOCK WHERE ID1B769047;
  INST_ID ADDR            KADDR                  SID TY        ID1 ID2      LMODE    REQUEST      CTIME      BLOCK

---------- ---------------- ---------------- ---------- -- ----------

---------- ---------- ---------- ---------- ----------         17 0000000906A747F0 0000000906A74848      1564 AE 42769047          0          4          0        657          2         17 0000000906A74A60 0000000906A74AB8      2502 AE 42769047          0          4          0        58          2         17 0000000906A6DD90 0000000906A6DDE8      2586 AE 42769047          0          4          0    637946          2         17 0000000906A78BD8 0000000906A78C30      2599 AE 42769047          0          4          0    636456          2         17 0000000906A77840 0000000906A77898      2600 AE 42769047          0          4          0    440984          2         17 0000000906A76BF8 0000000906A76C50      2610 AE 42769047          0          4          0    637990          2         12 0000000906A7B9B8 0000000906A7BA10      1908 AE 42769047          0          4          0      1033          2         12 0000000906A75C58 0000000906A75CB0      2219 AE 42769047          0          4          0        609          2         12 0000000906A7D4B8 0000000906A7D510      2258 AE 42769047          0          4          0      5423          2         12 0000000906A82B68 0000000906A82BC0      2268 AE 42769047          0          4          0        31          2         12 0000000906A828F8 0000000906A82950      2335 AE 42769047          0          4          0      8143          2 ...
..

        1 0000000906A7E940 0000000906A7E998      2603 AE
42769047          0          4          0      2515          2
        1 0000000906A7E6B8 0000000906A7E710      2610 AE
42769047          0          4          0    638417          2

452 rows selected.

On Wed, Oct 26, 2011 at 2:27 PM, Tim Gorman <tim_at_evdbt.com> wrote:

> DD,

>

> Procedure CLONE_X is pinned because it is referenced by procedure A, which
> you are currently executing.

>

> To find the sessions blocked by your TOAD session 2513, run the following
> query based on the TYPE and ID1 values shown in your output...

>
>      select * from gv$lock where type = 'AE' and ID1 = 0;
>      select * from gv$lock where type = 'AE' and ID1 = 42769047;
>

> Hope this helps...

>

> -Tim

>

> -----Original Message-----
> *From:* dd yakkali [mailto:dd.yakkali_at_gmail.com]
> *Sent:* Wednesday, October 26, 2011 03:11 PM
> *To:* oracle-l_at_freelists.org
> *Subject:* library cache pin while trying to recreate a stored proc

>

> 11.1.0.7 on RHEL5 stored proc a is begin clone_x; copy_x; do_blah_x; <-
> Currently executing this. .... end; I am trying to recreate clone_x in a
> different session and i am is getting a library cache pin and it does not
> show any blocking session. I am wondering why i am even seeing AE type
> locks? any help in understanding is greatly appreciated. Thanks Deen SELECT
> * FROM GV$LOCK WHERE INST_ID=3 AND SID%13; INST_ID ADDR KADDR SID TY ID1
> ID2 LMODE REQUEST CTIME BLOCK ---------- ---------------- ----------------
> ---------- -- ---------- ---------- ---------- ---------- ----------
> ---------- 3 0000000906A7C5E8 0000000906A7C640 2513 AE 0 1 4 0 2411 2 3
> 0000000906A8A8A8 0000000906A8A900 2513 AE 42769047 0 4 0 6895 2 select
> object_name,owner,object_type from dba_objects where object_idB769047;
> OBJECT_NAME OWNER OBJECT_TYPE
> --------------------------------------------------------------------------------------------------------------------------------
> ------------------------------ ------------------- ORA$BASE SYS EDITION
> SELECT SID, SERIAL#,BLOCKING_SESSION, BLOCKING_INSTANCE, PROGRAM, EVENT FROM
> GV$SESSION WHERE sid%13 AND INST_ID=3; SID SERIAL# BLOCKING_SESSION
> BLOCKING_INSTANCE PROGRAM EVENT ---------- ---------- ----------------
> ----------------- ------------------------------------------------ -------
> 2513 24597 Toad.exe library cache pin --
> http://www.freelists.org/webpage/oracle-l
>
>

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

Received on Wed Oct 26 2011 - 17:54:21 CDT