| 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 )
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-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: INET: Jared.Still_at_radisys.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 - 18:38:43 CST
 |
 |