Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: LIBRARY CACHE LOCK !!!! ( SQL Tuning )

RE: LIBRARY CACHE LOCK !!!! ( SQL Tuning )

From: John Clarke <jclarke_at_centroidsys.com>
Date: Tue, 25 Feb 2003 19:29:00 -0800
Message-ID: <F001.00559802.20030225192900@fatcity.com>


To see who's holding a library cache lock on your object, you could run the following query:

Select s.sid,kglpnmod "mode", kglpnreq "req"
>From x$kglpn p, v$session s

Where p.kglpnuse = s.sddr
And kglpnhdl=
(select p1raw
from v$session_wait
where sid=&your_sid)
/

(as long as you know your sid)

I use this whenever there are lots of library cache pin, library cache lock, or library cache load lock waits in v$session_wait (in my environment, usually b/c developers are compiling code at inappropriate times).

-----Original Message-----
Madhusudana
Sent: Tuesday, February 25, 2003 8:49 PM To: Multiple recipients of list ORACLE-L

Thanks Jared !!!!

Here is the Graph i can see in the trace file : ( SELF DEADLOCK !!!! )

888888888888888888888888888888888888888888888888888888888888888888888888
8888
888888888888888888888888888888888888
888888888888888888888888888888888888888888888888888888888888888888888888
8888
888888888888888888888888888888888888
888888888888888888888888888888888888888888888888888888888888888888888888
8888
888888888888888888888888888888888888
888888888888888888888888888888888888888888888888888888888888888888888888
8888
888888888888888888888888888888888888


A deadlock among DDL and parse locks is detected. This deadlock is usually due to user errors in the design of an application or from issuing a set of concurrent statements which can cause a deadlock. This should not be reported to Oracle Support. The following information may aid in finding the errors which cause the deadlock:
ORA-04020: deadlock detected while trying to lock object MDO.MDO_BSE_TEMP_RETEK_PRICE


  object   waiting  waiting       blocking blocking
  handle   session     lock mode   session     lock mode
-------- -------- -------- ---- -------- -------- ---- c00000004f641168 c000000031a6df18 c000000033dd66f8 X c000000031a6df18
c000000033cef0a0 S
888888888888888888888888888888888888888888888888888888888888888888888888
8888
888888888888888888888888888888888888
888888888888888888888888888888888888888888888888888888888888888888888888
8888
888888888888888888888888888888888888
888888888888888888888888888888888888888888888888888888888888888888888888
8888
888888888888888888888888888888888888
888888888888888888888888888888888888888888888888888888888888888888888888
8888
888888888888888888888888888888888888


Thanks again
Madhu

-----Original Message-----
Sent: Tuesday, February 25, 2003 6:36 PM To: ORACLE-L_at_fatcity.com
Cc: Madhusudana.Reddy_at_bestbuy.com
Importance: High

If you are getting ORA-60 deadlock errors, how about posting the deadlock graph from the trace file?

Also read Doc # 62365.1 on MetaLink.

Jared

"Reddy, Madhusudana" <Madhusudana.Reddy_at_bestbuy.com> Sent by: root_at_fatcity.com
 02/25/2003 02:09 PM
 Please respond to ORACLE-L  

        To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>

        cc: 
        Subject:        LIBRARY CACHE LOCK !!!! ( SQL Tuning )


Hello All,

I have a PL/SQL code which will run once a week, and every time this job will stuck doing nothing .. and end up waiting on " LIBRARY CACHE LOCK " .Most of the time this job results in a deadlock . As I know I am not a SQL
tuning expert ,once again I am seeking your suggestions and help in resolving the issue !!

Another interesting thing is , after restarting the job ( after killing for
the first time ) it will go through fine. I am suspecting the way it is coded. Any inputs ???

Thanks
Madhu










SET SERVEROUTPUT ON
SET LINESIZE 255
SET TAB OFF VARIABLE g_return_code NUMBER;

DECLARE
   CURSOR c_incoming_rows IS

      SELECT product_id
           , store_id
           , clearance_price
           , effective_date
           , out_of_stock_date
           , reset_date
           , flag
      FROM   mdo_pre_temp_retek_price;

   v_existing_count          NUMBER;
   e_invalid_row_count       EXCEPTION;

BEGIN
   DBMS_OUTPUT.ENABLE(1000000);
   :g_return_code := 1;

   FOR v_row IN c_incoming_rows LOOP

      BEGIN

            ELSE
               UPDATE mdo_bse_temp_retek_price
               SET    clearance_price   =
TO_NUMBER(v_row.clearance_price)
/
100.0
                    , effective_date    =
TO_DATE(v_row.effective_date,'YYYYMMDD')
                    , out_of_stock_date =
TO_DATE(v_row.out_of_stock_date,'YYYYMMDD')
                    , reset_date        =
TO_DATE(v_row.reset_date,'YYYYMMDD')
                    , flag              = v_row.flag
               WHERE  product_id        = LTRIM(v_row.product_id,'0')
               AND    store_id          = LTRIM(v_row.store_id,'0');

            END IF;

         -- if we have neither 0 nor 1 records, something is terribly 
wrong
         ELSE
            :g_return_code := 4;
            RAISE e_invalid_row_count;

         END IF;

      EXCEPTION
         WHEN OTHERS THEN
            :g_return_code := 5;
            DBMS_OUTPUT.PUT_LINE(SQLERRM);
            DBMS_OUTPUT.PUT_LINE('Record ignored for store ' ||
v_row.store_id || ' sku ' ||
                  v_row.product_id || '.');
      END;

   END LOOP;
   :g_return_code := 0;

END;
/
EXIT :g_return_code










-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Reddy, Madhusudana
  INET: Madhusudana.Reddy_at_bestbuy.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).





-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Reddy, Madhusudana
  INET: Madhusudana.Reddy_at_bestbuy.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: John Clarke
  INET: jclarke_at_centroidsys.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Tue Feb 25 2003 - 21:29:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US