| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: LIBRARY CACHE LOCK !!!! ( SQL Tuning )
Thanks John !!
In this present case the PL/SQL code ,which was shown below itself is
causing the deadlock ( SELF DEADLOCK !! ,also plz find the deadlock graph
shown below), i mean the session which is holding the Library Cache Lock . I
want your expertise in identifying the problem with the code which i have
shown below !. that would be great help to me !!!
Thanks in advance,
Madhu
-----Original Message-----
Sent: Tuesday, February 25, 2003 9:29 PM
To: Multiple recipients of list ORACLE-L
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 !!!! )
8888888888888888888888888888888888888888888888888888888888888888888888888888
888888888888888888888888888888888888 8888888888888888888888888888888888888888888888888888888888888888888888888888
888888888888888888888888888888888888 8888888888888888888888888888888888888888888888888888888888888888888888888888
888888888888888888888888888888888888 8888888888888888888888888888888888888888888888888888888888888888888888888888
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
8888888888888888888888888888888888888888888888888888888888888888888888888888
888888888888888888888888888888888888 8888888888888888888888888888888888888888888888888888888888888888888888888888
888888888888888888888888888888888888 8888888888888888888888888888888888888888888888888888888888888888888888888888
888888888888888888888888888888888888 8888888888888888888888888888888888888888888888888888888888888888888888888888
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)
, 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-LReceived on Wed Feb 26 2003 - 00:38:46 CST
(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). -- 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).
 |
 |