Oracle-L: LIBRARY CACHE LOCK !!!! ( SQL Tuning )

From: Reddy, Madhusudana <Madhusudana.Reddy_at_bestbuy.com>
Date: Tue, 25 Feb 2003 14:09:04 -0800
Message-ID: <F001.0055944B.20030225140904@fatcity.com>

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: 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).

Received on Tue Feb 25 2003 - 16:09:04 CST