Home » SQL & PL/SQL » SQL & PL/SQL » Delete statement is taking long time. Please suggest. (oracle 11g )
| Delete statement is taking long time. Please suggest. [message #616212] |
Fri, 13 June 2014 08:42  |
 |
somnathsikdar
Messages: 1
Registered: June 2014
Location: Kolkata
|
Junior Member |
|
|
Hi all,
Please find the below script. I have written the script. At the end of the script a delete statement is taking 45 minute to delete 21613600 records from a table. Can you please suggest how can I improve the performance.
------------------------------------------------------------------------------------------------------------------------------------- -
------------------------------------------------------------------------------------------------------------------------------------- -
-- Script Name : cleanSKU.sql
-- Purpose :
-------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------- -
WHENEVER OSERROR EXIT FAILURE rollback
WHENEVER SQLERROR EXIT SQL.SQLCODE rollback
SET TIMING ON
SET VERIFY OFF
SET FEEDBACK OFF
SET ESCAPE ON
SET SERVEROUTPUT ON SIZE 999999
PROMPT ***
PROMPT *** Running cleansku.sql
PROMPT ***
SELECT 'Current environment is User= '||user||' DB= '||name||
' Date= '|| SYSDATE
FROM V$DATABASE;
SET FEEDBACK ON
SET HEADING ON
ALTER SESSION ENABLE PARALLEL QUERY;
ALTER SESSION ENABLE PARALLEL DDL;
ALTER SESSION ENABLE PARALLEL DML;
--------------------------------------------------------------
--Declare the variables, exceptions used throughout the script
--------------------------------------------------------------
variable rc NUMBER;
DECLARE
l_n_tab_row NUMBER;
TYPE l_cu_my_cur is REF CURSOR;
l_cv_ExprdSku l_cu_my_cur;
l_n_limit PLS_INTEGER := 10000;
l_n_count PLS_INTEGER := 0;
l_n_update_count PLS_INTEGER := 0;
l_n_error_count PLS_INTEGER := 0;
l_n_record_count PLS_INTEGER := 0;
BULKERRORS EXCEPTION;
TYPE TYPE1 IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
v1 TYPE1;
l_d_process_date DATE;
PRAGMA EXCEPTION_INIT(BULKERRORS,-24381);
--------------------------------------------------------------
--CREATE temporary table to hold records
--------------------------------------------------------------
BEGIN
:rc:=0;
l_n_tab_row :=0;
SELECT to_date(MAX(param_value),'YYYYMMDD') - 30 INTO l_d_process_date
FROM stsc.udt_parameter
WHERE param_name='PROCESS_DATE';
DBMS_OUTPUT.PUT_LINE('================================================================================');
SELECT count(*) INTO l_n_tab_row FROM ALL_TABLES WHERE TABLE_NAME='TMP_EXPIREDSKUEXTRACT_&&1._&&2' ;
IF l_n_tab_row > 0 THEN
EXECUTE IMMEDIATE 'TRUNCATE TABLE TMP_EXPIREDSKUEXTRACT_&&1._&&2';
EXECUTE IMMEDIATE 'DROP TABLE TMP_EXPIREDSKUEXTRACT_&&1._&&2 PURGE';
END IF;
DBMS_OUTPUT.PUT_LINE('Creating Temporary Table started at '|| to_char(sysdate,'hh24:MI:SS'));
EXECUTE IMMEDIATE 'CREATE TABLE TMP_EXPIREDSKUEXTRACT_&&1._&&2 parallel (degree &&3) nologging AS
Select /*+ parallel(spp,&&3) parallel(sft,&&3) parallel(usku,&&3) */
spp.item,
spp.loc,
sft.rowid sftrid,
usku.rowid uskurid,
ssp.rowid ssprid,
CASE WHEN sft.minss <> 0 OR sft.maxss <> 999999999 OR sft.sscov <> 0 OR nvl(sft.sstemplate,''XX'') <> '' ''
THEN ''Y'' ELSE ''N''
END updflg_sftyrec,
CASE WHEN nvl(usku.disc_profile_id,''XX'') <> '' '' OR
usku.eol_exp_date <> to_date(''01/01/1970'',''mm/dd/yyyy'') OR
usku.Processed_EOL_IND <> 0 OR
usku.Processed_EOL_date <> to_date(''01/01/1970'',''mm/dd/yyyy'') OR
usku.disc_profile_manual_ind <> 0 OR
usku.manual_exp_date <> to_date(''01/01/1970'',''mm/dd/yyyy'') OR
usku.sspres_qty <> 0 OR
usku.sspres_eff_date <> to_date(''01/01/1970'',''mm/dd/yyyy'') OR
usku.sspres_discont_date <> to_date(''01/01/1970'',''mm/dd/yyyy'') OR
usku.sspres_manual_ind <> 0
THEN ''Y'' ELSE ''N''
END updflg_uskurec
From stsc.skuplanningparam spp, stsc.skusafetystockparam sft, stsc.udt_sku usku, stsc.sspresentation ssp
Where spp.expdate < To_date('''||to_char(l_d_process_date,'dd-mon-yy')||''',''dd-mon-yy'')
and spp.expdate > to_date(''01/01/1970'',''mm/dd/yyyy'')
and spp.loc like ''&&1._&&2%''
and usku.legacy_replen_flag = 0
and spp.item = sft.item(+) and spp.loc = sft.loc
and spp.item = usku.item(+) and spp.loc = usku.loc(+)
and spp.item = ssp.item(+) and spp.loc = ssp.loc
order by spp.item,spp.loc'; -- This order by used to reduce multiple read/write same base table block
EXECUTE IMMEDIATE 'SELECT /*+ full(tmp) parallel(tmp, &&3) */ COUNT(*) FROM TMP_EXPIREDSKUEXTRACT_&&1._&&2 tmp' INTO l_n_count;
DBMS_OUTPUT.PUT_LINE('Temp Table Created with '||To_char(l_n_count)||' '||'Records @ '|| to_char(sysdate,'hh24:MI:SS'));
---------------------------------------------------------------------------------------------
--Ref Cursor to fetch records from temp table and Updating stsc.skusafetystockparam table
---------------------------------------------------------------------------------------------
OPEN l_cv_ExprdSku FOR 'SELECT /*+ FULL(tmp) PARALLEL(tmp, &&3) */
tmp.sftrid
FROM TMP_EXPIREDSKUEXTRACT_&&1._&&2 tmp
WHERE updflg_sftyrec =''Y''';
LOOP
FETCH l_cv_ExprdSku
BULK COLLECT INTO v1 LIMIT l_n_limit;
l_n_record_count :=v1.COUNT;
EXIT WHEN l_n_record_count=0;
BEGIN
FORALL i IN v1.first..v1.last SAVE EXCEPTIONS
EXECUTE IMMEDIATE 'UPDATE stsc.skusafetystockparam
SET minss = 0, maxss = 999999999, sscov = 0,sstemplate = '' ''
WHERE ROWID = :1'
using v1(i);
l_n_update_count:=l_n_update_count+SQL%ROWCOUNT;
EXCEPTION
WHEN BULKERRORS
THEN
:rc:=-1;
l_n_error_count:=SQL%BULK_EXCEPTIONS.COUNT();
FOR i IN 1 .. l_n_error_count
LOOP
DBMS_OUTPUT.PUT_LINE('SQLCODE:'||SQL%BULK_EXCEPTIONS(i).
ERROR_CODE||'-SQLERRM:'||SQLERRM(-1 *
SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
END LOOP;
RAISE_APPLICATION_ERROR(-20042,
'Error in updating stsc.udt_sku using temp table1');
END;
Commit;
END LOOP;
CLOSE l_cv_ExprdSku;
v1.DELETE;
DBMS_OUTPUT.PUT_LINE(chr(13)||'Number of rows Updated in skusafetystockparam Table = '||l_n_update_count||' @ '|| to_char(sysdate,'hh24:MI:SS') );
l_n_update_count :=0;
---------------------------------------------------------------------------------------------
--Ref Cursor to fetch records from temp table and Updating stsc.udt_sku table
---------------------------------------------------------------------------------------------
OPEN l_cv_ExprdSku FOR 'SELECT /*+ FULL(tmp) PARALLEL(tmp, &&3) */
tmp.uskurid
FROM TMP_EXPIREDSKUEXTRACT_&&1._&&2 tmp
WHERE updflg_uskurec =''Y''';
LOOP
FETCH l_cv_ExprdSku
BULK COLLECT INTO v1 LIMIT l_n_limit;
l_n_record_count:=v1.COUNT;
EXIT WHEN l_n_record_count=0;
BEGIN
FORALL i IN v1.first..v1.last SAVE EXCEPTIONS
EXECUTE IMMEDIATE 'UPDATE stsc.udt_sku usku
SET Disc_Profile_ID = '' '',
EOL_EXP_DATE = to_date(''01/01/1970'',''mm/dd/yyyy''),
Processed_EOL_IND = 0,
Processed_EOL_date = to_date(''01/01/1970'',''mm/dd/yyyy''),
DISC_Profile_Manual_Ind = 0,
Manual_exp_date = to_date(''01/01/1970'',''mm/dd/yyyy''),
sspres_qty = 0,
sspres_eff_date = to_date(''01/01/1970'',''mm/dd/yyyy''),
sspres_discont_date = to_date(''01/01/1970'',''mm/dd/yyyy''),
sspres_manual_ind = 0
WHERE ROWID = :1'
using v1(i);
l_n_update_count:=l_n_update_count+SQL%ROWCOUNT;
EXCEPTION
WHEN BULKERRORS
THEN
:rc:=-1;
l_n_error_count:=SQL%BULK_EXCEPTIONS.COUNT();
FOR i IN 1 .. l_n_error_count
LOOP
DBMS_OUTPUT.PUT_LINE('SQLCODE:'||SQL%BULK_EXCEPTIONS(i).
ERROR_CODE||'-SQLERRM:'||SQLERRM(-1 *
SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
END LOOP;
RAISE_APPLICATION_ERROR(-20042,
'Error in updating stsc.udt_sku using temp table1');
END;
Commit;
END LOOP;
CLOSE l_cv_ExprdSku;
v1.DELETE;
DBMS_OUTPUT.PUT_LINE('Number of rows Updated in udt_sku = '||l_n_update_count||' @ '|| to_char(sysdate,'hh24:MI:SS') );
l_n_update_count :=0;
---------------------------------------------------------------------------------------------
--Ref Cursor to fetch records from temp table and Deleting records from stsc.udt_sku
---------------------------------------------------------------------------------------------
OPEN l_cv_ExprdSku FOR 'SELECT /*+ FULL(tmp) PARALLEL(tmp, &&3) */
tmp.ssprid
FROM TMP_EXPIREDSKUEXTRACT_&&1._&&2 tmp
WHERE tmp.ssprid is not null';
LOOP
FETCH l_cv_ExprdSku
BULK COLLECT INTO v1 LIMIT l_n_limit;
l_n_record_count :=v1.COUNT;
EXIT WHEN l_n_record_count=0;
BEGIN
FORALL i IN v1.first..v1.last SAVE EXCEPTIONS
EXECUTE IMMEDIATE 'DELETE FROM stsc.sspresentation
WHERE ROWID = :1'
using v1(i);
l_n_update_count :=l_n_update_count+SQL%ROWCOUNT;
EXCEPTION
WHEN BULKERRORS
THEN
:rc:=-1;
l_n_error_count:=SQL%BULK_EXCEPTIONS.COUNT();
FOR i IN 1 .. l_n_error_count
LOOP
DBMS_OUTPUT.PUT_LINE('SQLCODE:'||SQL%BULK_EXCEPTIONS(i).
ERROR_CODE||'-SQLERRM:'||SQLERRM(-1 *
SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
END LOOP;
RAISE_APPLICATION_ERROR(-20042,
'Error in updating stsc.udt_sku using temp table1');
END;
Commit;
END LOOP;
CLOSE l_cv_ExprdSku;
v1.DELETE;
DBMS_OUTPUT.PUT_LINE('Number of rows Deleted from sspresentation Table = '||l_n_update_count||' @ '|| to_char(sysdate,'hh24:MI:SS') );
Commit;
DBMS_OUTPUT.PUT_LINE('================================================================================');
EXCEPTION
WHEN OTHERS THEN
:rc:=-1 ;
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('Error in PL/SQL Block');
DBMS_OUTPUT.PUT_LINE('SQLCODE: ' || SQLCODE);
DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || substr(SQLERRM, 1, 200));
DBMS_OUTPUT.PUT_LINE('cleanSQL @:'||TO_CHAR(sysdate, 'DD-MM-YYYY HH24:MI:SS'));
raise_application_error (-20001,'PL/SQL Error : '||SQLCODE||substr(SQLERRM, 1, 200));
END;
/
UNDEF 1 2 3
EXIT :rc;
------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------- -----
Please find the execution result of the above script as:
06:50:20: Process Started grsExecuteSqlScript.pl 06-11-2014 06:50:20
06:50:20: Process Id: 20450668
06:50:20: JOBNAME: JWUSN31U
06:50:20: COUNTRY: US
06:50:20: BANNER: WMT
06:50:20: SCRIPTNAME: cleanSku.sql
06:50:20: SCRIPTFLAG: S
06:50:20: ARGSTRING: 8
06:50:20: SQL ARGSTRING: 8
06:50:20: USERNAME: STSC
06:50:20: Beeping ENABLED for this run.
06:50:20: Connection string being built using a specific userid: STSC
06:50:24: Executing sqlplus -l STSC/*****@grsprd1_service @/u/apps/grs/scripts/cleanSku.sql US WMT 8
07:39:01: ***********************SQLPLUS OUTPUT***********************
SQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 11 06:50:24 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
***
*** Running cleansku.sql
***
'CURRENTENVIRONMENTISUSER='||USER||'DB='||NAME||'DATE='||SYSDATE
--------------------------------------------------------------------------------
Current environment is User= STSC DB= GRSPRD1 Date= 11-JUN-14
Elapsed: 00:00:00.00
Session altered.
Elapsed: 00:00:00.01
Session altered.
Elapsed: 00:00:00.00
Session altered.
Elapsed: 00:00:00.00
================================================================================
Creating Temporary Table started at 06:50:24
Temp Table Created with 57089536 Records @ 06:55:03
Number of rows Updated in skusafetystockparam Table = 2440350 @ 06:56:35
Number of rows Updated in udt_sku = 21647 @ 06:56:42
Number of rows Deleted from sspresentation Table = 21613600 @ 07:39:01
================================================================================
PL/SQL procedure successfully completed.
Elapsed: 00:48:36.69
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
07:39:01: ***********************SQLPLUS OUTPUT***********************
07:39:01: Return code: 0
07:39:01: Completed with return code of 0
Can anybody help to tune this delete statement?
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Apr 26 23:50:11 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
