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
c000000033cef0a0 S
--------------------------------------------------------
---------- DUMP OF WAITING AND BLOCKING LOCKS ----------
--------------------------------------------------------
------------- WAITING LOCK -------------
----------------------------------------
SO: c000000033dd66f8, type: 33, owner: c00000003393b710, flag: INIT/-/-/0x00
LIBRARY OBJECT LOCK: lock=c000000033dd66f8 handle=c00000004f641168 request=X
call pin=0 session pin=0
user=c000000031a6df18 session=c000000031a6df18 count=0 flags=[00]
savepoint=408
LIBRARY OBJECT HANDLE: handle=c00000004f641168
name=MDO.MDO_BSE_TEMP_RETEK_PRICE
hash=74b60038 timestamp=01-09-2002 18:05:20
namespace=TABL/PRCD/TYPE flags=TIM/SML/[02000000]
kkkk-dddd-llll=0000-055d-075d lock=S pin=S latch=1
lwt=c00000004f641198[c000000033dd6718,c000000033dd6718]
ltm=c00000004f6411a8[c00000004f6411a8,c00000004f6411a8]
pwt=c00000004f6411c8[c00000004f6411c8,c00000004f6411c8]
ptm=c00000004f641258[c00000004f641258,c00000004f641258]
ref=c00000004f641178[c00000004e451f50,c000000035020518]
LIBRARY OBJECT: object=c00000004f6476a0
type=TABL flags=EXS/LOC[0005] pflags= [00] status=VALD load=0
DATA BLOCKS:
data# heap pointer status pins change
----- -------- -------- ------ ---- ------
0 c00000004f6410a8 c00000004f647790 I/P/A 0 NONE
2 c0000000483699e0 c000000038dfdcb8 I/P/A 1 NONE
3 c000000048369a88 c000000047ed33f0 I/-/A 0 NONE
4 c00000004f640c98 c0000000354158b8 I/-/A 0 NONE
6 c00000004f640d40 0 -/P/- 0 NONE
8 c00000004f640bd0 c00000003b9a80f0 I/-/A 0 NONE
9 c000000048369b50 0 I/P/- 0 NONE
10 c00000004f640de8 c00000003bf98e90 I/-/A 0 NONE
------------- BLOCKING LOCK ------------
----------------------------------------
SO: c000000033cef0a0, type: 33, owner: c000000033d58720, flag: INIT/-/-/0x00
LIBRARY OBJECT LOCK: lock=c000000033cef0a0 handle=c00000004f641168 mode=S
call pin=c000000033cec8b8 session pin=0
user=c000000031a6df18 session=c000000031a6df18 count=5 flags=PNC/[04]
savepoint=241
LIBRARY OBJECT HANDLE: handle=c00000004f641168
name=MDO.MDO_BSE_TEMP_RETEK_PRICE
hash=74b60038 timestamp=01-09-2002 18:05:20
namespace=TABL/PRCD/TYPE flags=TIM/SML/[02000000]
kkkk-dddd-llll=0000-055d-075d lock=S pin=S latch=1
lwt=c00000004f641198[c000000033dd6718,c000000033dd6718]
ltm=c00000004f6411a8[c00000004f6411a8,c00000004f6411a8]
pwt=c00000004f6411c8[c00000004f6411c8,c00000004f6411c8]
ptm=c00000004f641258[c00000004f641258,c00000004f641258]
ref=c00000004f641178[c00000004e451f50,c000000035020518]
LIBRARY OBJECT: object=c00000004f6476a0
type=TABL flags=EXS/LOC[0005] pflags= [00] status=VALD load=0
DATA BLOCKS:
data# heap pointer status pins change
----- -------- -------- ------ ---- ------
0 c00000004f6410a8 c00000004f647790 I/P/A 0 NONE
2 c0000000483699e0 c000000038dfdcb8 I/P/A 1 NONE
3 c000000048369a88 c000000047ed33f0 I/-/A 0 NONE
4 c00000004f640c98 c0000000354158b8 I/-/A 0 NONE
6 c00000004f640d40 0 -/P/- 0 NONE
8 c00000004f640bd0 c00000003b9a80f0 I/-/A 0 NONE
9 c000000048369b50 0 I/P/- 0 NONE
10 c00000004f640de8 c00000003bf98e90 I/-/A 0 NONE
--------------------------------------------------------
8888888888888888888888888888888888888888888888888888888888888888888888888888
888888888888888888888888888888888888
8888888888888888888888888888888888888888888888888888888888888888888888888888
888888888888888888888888888888888888
8888888888888888888888888888888888888888888888888888888888888888888888888888
888888888888888888888888888888888888
8888888888888888888888888888888888888888888888888888888888888888888888888888
888888888888888888888888888888888888
Thanks again
Madhu
-----Original Message-----
Sent: Tuesday, February 25, 2003 6:36 PM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
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" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
02/25/2003 02:09 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
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
-- test for existence of existing records
SELECT COUNT(*)
INTO v_existing_count
FROM mdo_bse_temp_retek_price
WHERE product_id = LTRIM(v_row.product_id,'0')
AND store_id = LTRIM(v_row.store_id,'0');
-- if record does not already exist then insert (unless it's a
delete)
IF (v_existing_count = 0 AND v_row.flag != 'D') THEN
:g_return_code := 2;
INSERT INTO mdo_bse_temp_retek_price (
product_id
, store_id
, clearance_price
, effective_date
, out_of_stock_date
, reset_date
, flag )
VALUES (
LTRIM(v_row.product_id,'0')
, LTRIM(v_row.store_id,'0')
, TO_NUMBER(v_row.clearance_price) / 100.0
, TO_DATE(v_row.effective_date,'YYYYMMDD')
, TO_DATE(v_row.out_of_stock_date,'YYYYMMDD')
, TO_DATE(v_row.reset_date,'YYYYMMDD')
, v_row.flag
);
-- if record already exists then update or delete as needed
ELSIF (v_existing_count = 1) THEN
:g_return_code := 3;
-- check for delete command
IF (v_row.flag = 'D') THEN
DELETE
FROM mdo_bse_temp_retek_price
WHERE product_id = LTRIM(v_row.product_id,'0')
AND store_id = LTRIM(v_row.store_id,'0');
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
