Home » RDBMS Server » Performance Tuning » Issue with bulk collect over a db link (oracle,10.2.0.3,HP-UX)
icon5.gif  Issue with bulk collect over a db link [message #544795] Thu, 23 February 2012 22:23 Go to next message
vishith13
Messages: 1
Registered: February 2012
Location: hyderabad
Junior Member

Hi experts can somebody suggest any tips for improving the speed of my following pl/sql

create or replace PROCEDURE CDR_PROC_ARCHIVE_ORDER_EXTRACT
IS

/*
Criteria to be followed to Order Archival

* Order Status should be 'Cancelled' or 'Complete'
* Order Closed date should be 6 months before
*/

-- main Cursor to spool the Orders to be archived based on criteria
CURSOR GET_ORD_DET IS
(
SELECT ORD.ROW_WID ORDER_ID      
FROM SRMW.W_ORDER_D@ARCHIVAL_TO_CRMSPA2 ORD
WHERE  
  (
  (ORD.STATUS_CD ='Complete' 
  --AND (ORD.STATUS_DT<add_months(trunc(sysdate,'MM'),GET_ENTITY('Orders'))
	--or (ORD.STATUS_DT is null AND ORD.ORDER_COMPL_DATE<add_months(trunc(sysdate,'MM'),GET_ENTITY('Orders'))))
  AND (trunc(ORD.STATUS_DT)<'01-AUG-2009'
   or (ORD.STATUS_DT is null AND trunc(ORD.ORDER_COMPL_DATE)<'01-AUG-2009'))
  )
  OR  
  (ORD.STATUS_CD ='Cancelled'
  --AND (ORD.X_CANCEL_DT<add_months(trunc(sysdate,'MM'),GET_ENTITY('Orders'))
  --or (ord.x_cancel_dt is null AND ORD.ORDER_COMPL_DATE<add_months(trunc(sysdate,'MM'),GET_ENTITY('Orders')))
  AND (trunc(ORD.X_CANCEL_DT)<'01-AUG-2009'
   or (ORD.X_CANCEL_DT is null AND trunc(ORD.ORDER_COMPL_DATE)<'01-AUG-2009'))
  )
  )
  --AND ORD.ROW_WID='246723'
 );
	  
	  
    type ORDER_ID is table of varchar(20);
    l_data ORDER_ID;

    dml_errors EXCEPTION;
    PRAGMA exception_init(dml_errors, -24381);
	
    l_errors number;
    l_errno    number;
    l_msg    varchar2(4000);
    --l_idx    number;
	
	
begin
    open GET_ORD_DET;
    loop
    fetch GET_ORD_DET bulk collect into l_data limit 2000;
    begin
    for i in 1 .. l_data.count --SAVE EXCEPTIONS
    loop
    --SAVE EXCEPTIONS
     INSERT INTO OBI_ARCH.W_ORDER_F
    (SELECT ORF.*
     FROM SRMW.W_ORDER_F@ARCHIVAL_TO_CRMSPA2 ORF
     WHERE  ORF.ORDER_WID=l_data(i)
    );  
    end loop;
	COMMIT;
        exception
            when DML_ERRORS then
                l_errors := sql%bulk_exceptions.count;
                for i in 1 .. l_errors
                loop
                    l_errno := sql%bulk_exceptions(i).error_code;
                    l_msg   := sqlerrm(-l_errno);
                    --l_idx   := sql%bulk_exceptions(i).error_index;
                    insert into H_ARCHIVAL_ERROR_HANDLING 
		values
                    (l_data(i),
   'Order Archival',
  'Error while inserting into W_ORDER_F tbl',
  l_msg,
  'CDR_PKG_ARCHIVE_ORDER',
  'CDR_PROC_ARCHIVE_ORDER',null,
  --REC_ORD_DET.SSA_NAME,
  TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
   end loop;
   COMMIT;
   end;
-----------------------------------------------------------------
   begin
    for i in 1 .. l_data.count --SAVE EXCEPTIONS
    loop
    --SAVE EXCEPTIONS
     INSERT INTO OBI_ARCH.W_ORDER_D
    (SELECT ORD.*
FROM SRMW.W_ORDER_D@ARCHIVAL_TO_CRMSPA2 ORD
WHERE ORD.ROW_WID=l_data(i)
    );  
    end loop;
	COMMIT;
        exception
            when DML_ERRORS then
                l_errors := sql%bulk_exceptions.count;
                for i in 1 .. l_errors
                loop
                    l_errno := sql%bulk_exceptions(i).error_code;
                    l_msg   := sqlerrm(-l_errno);
                    --l_idx   := sql%bulk_exceptions(i).error_index;
                    insert into H_ARCHIVAL_ERROR_HANDLING 
		values
                    (l_data(i),
   'Order Archival',
  'Error while inserting into W_ORDER_D tbl',
  l_msg,
  'CDR_PKG_ARCHIVE_ORDER',
  'CDR_PROC_ARCHIVE_ORDER',null,
  --REC_ORD_DET.SSA_NAME,
  TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
   end loop;
   COMMIT;
   end;
 ----------------------------------------------------------------
   begin
    for i in 1 .. l_data.count --SAVE EXCEPTIONS
    loop
    --SAVE EXCEPTIONS
     INSERT INTO OBI_ARCH.W_ORDERITEM_F
    (SELECT ITEM.*
FROM SRMW.W_ORDERITEM_F@ARCHIVAL_TO_CRMSPA2 ITEM
WHERE ITEM.ORDER_WID=l_data(i)
    );  
    end loop;
	COMMIT;
        exception
            when DML_ERRORS then
                l_errors := sql%bulk_exceptions.count;
                for i in 1 .. l_errors
                loop
                    l_errno := sql%bulk_exceptions(i).error_code;
                    l_msg   := sqlerrm(-l_errno);
                    --l_idx   := sql%bulk_exceptions(i).error_index;
                    insert into H_ARCHIVAL_ERROR_HANDLING 
		values
                    (l_data(i),
   'Order Archival',
  'Error while inserting into W_ORDERITEM_F tbl',
  l_msg,
  'CDR_PKG_ARCHIVE_ORDER',
  'CDR_PROC_ARCHIVE_ORDER',null,
  --REC_ORD_DET.SSA_NAME,
  TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
   end loop;
   COMMIT;
   end;
-----------------------------------------------------------------
   begin
    for i in 1 .. l_data.count --SAVE EXCEPTIONS
    loop
    --SAVE EXCEPTIONS
     INSERT INTO OBI_ARCH.WC_ORDERITEM_XA_F
    (SELECT  XA.*
FROM SRMW.WC_ORDERITEM_XA_F@ARCHIVAL_TO_CRMSPA2 XA
WHERE XA.ORDER_WID=l_data(i)
    );  
    end loop;
	COMMIT;
        exception
            when DML_ERRORS then
                l_errors := sql%bulk_exceptions.count;
                for i in 1 .. l_errors
                loop
                    l_errno := sql%bulk_exceptions(i).error_code;
                    l_msg   := sqlerrm(-l_errno);
                    --l_idx   := sql%bulk_exceptions(i).error_index;
                    insert into H_ARCHIVAL_ERROR_HANDLING 
		values
                    (l_data(i),
   'Order Archival',
  'Error while inserting into WC_ORDERITEM_XA_F tbl',
  l_msg,
  'CDR_PKG_ARCHIVE_ORDER',
  'CDR_PROC_ARCHIVE_ORDER',null,
  --REC_ORD_DET.SSA_NAME,
  TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
   end loop;
   COMMIT;
   end;
-----------------------------------------------------------------
   begin
    for i in 1 .. l_data.count --SAVE EXCEPTIONS
    loop
    --SAVE EXCEPTIONS
     INSERT INTO OBI_ARCH.WC_ORDERITEM_XA_D
    (SELECT  XAD.*
FROM SRMW.WC_ORDERITEM_XA_D@ARCHIVAL_TO_CRMSPA2 XAD,
     SRMW.W_ORDERITEM_F@ARCHIVAL_TO_CRMSPA2 ITM
WHERE XAD.ORDER_ITEM_ID=ITM.INTEGRATION_ID
AND ITM.ORDER_WID=l_data(i)
    );  
    end loop;
	COMMIT;
        exception
            when DML_ERRORS then
                l_errors := sql%bulk_exceptions.count;
                for i in 1 .. l_errors
                loop
                    l_errno := sql%bulk_exceptions(i).error_code;
                    l_msg   := sqlerrm(-l_errno);
                    --l_idx   := sql%bulk_exceptions(i).error_index;
                    insert into H_ARCHIVAL_ERROR_HANDLING 
		values
                    (l_data(i),
   'Order Archival',
  'Error while inserting into WC_ORDERITEM_XA_D tbl',
  l_msg,
  'CDR_PKG_ARCHIVE_ORDER',
  'CDR_PROC_ARCHIVE_ORDER',null,
  --REC_ORD_DET.SSA_NAME,
  TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
   end loop;
   COMMIT;
   end;
   
   --------------------------------------------------------------
   begin
    for i in 1 .. l_data.count --SAVE EXCEPTIONS
    loop
    --SAVE EXCEPTIONS
     INSERT INTO OBI_ARCH.W_ACTIVITY_F
    (SELECT EVT.*
FROM SRMW.W_ACTIVITY_F@ARCHIVAL_TO_CRMSPA2 EVT,
	 SRMW.W_ORDERITEM_F@ARCHIVAL_TO_CRMSPA2 ITM
WHERE EVT.ORDER_ITEM_ID=ITM.INTEGRATION_ID
AND ITM.ORDER_WID=l_data(i)
    );  
    end loop;
	COMMIT;
        exception
            when DML_ERRORS then
                l_errors := sql%bulk_exceptions.count;
                for i in 1 .. l_errors
                loop
                    l_errno := sql%bulk_exceptions(i).error_code;
                    l_msg   := sqlerrm(-l_errno);
                    --l_idx   := sql%bulk_exceptions(i).error_index;
                    insert into H_ARCHIVAL_ERROR_HANDLING 
		values
                    (l_data(i),
   'Order Archival',
  'Error while inserting into W_ACTIVITY_F tbl',
  l_msg,
  'CDR_PKG_ARCHIVE_ORDER',
  'CDR_PROC_ARCHIVE_ORDER',null,
  --REC_ORD_DET.SSA_NAME,
  TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
   end loop;
   COMMIT;
   end;   
   
   
exit when GET_ORD_DET%notfound;
		
		
    end loop;
    close GET_ORD_DET;
end;


/*
 TRUNCATE TABLE OBI_ARCH.W_ORDER_F;
 TRUNCATE TABLE OBI_ARCH.W_ORDER_D;
 TRUNCATE TABLE OBI_ARCH.W_ORDERITEM_F;
 TRUNCATE TABLE OBI_ARCH.WC_ORDERITEM_XA_F;
 TRUNCATE TABLE OBI_ARCH.WC_ORDERITEM_XA_D;
 TRUNCATE TABLE OBI_ARCH.W_ACTIVITY_F;
 */


[added [code] tags, jw]

[Updated on: Fri, 24 February 2012 02:56] by Moderator

Report message to a moderator

Re: Issue with bulk collect over a db link [message #544811 is a reply to message #544795] Thu, 23 February 2012 23:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is unreadable.
Repost in a formatted way:

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

And explain with words what the procedure intends to do.

Regards
Michel
Re: Issue with bulk collect over a db link [message #544820 is a reply to message #544795] Fri, 24 February 2012 02:09 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I've added the code tags that male your post a bit easier to read - do you see how the formatting is now preserved? Please do it yourself in future.

THere are better programmers than me on this forum, but I can see some fundamental errors. Such as this:
  AND (trunc(ORD.STATUS_DT)<'01-AUG-2009'

First, you should never apply a function to the column in a predicate because that will make use of an index impossible.
Second, you should never compare a character string such as '01-AUG-2009' to a date.
This should probably be re-written as
AND ORD.STATUS_DT < to_date('01-AUG-2009','DD-MON-YYYY')
Without changes such as this, your code is likely to be slow and unreliable. I would start by correcting all such uses, and removing all those lines that have been disabled with --, they clutter up the code.

Rgds, John.

[Updated on: Fri, 24 February 2012 02:12]

Report message to a moderator

Re: Issue with bulk collect over a db link [message #544829 is a reply to message #544820] Fri, 24 February 2012 03:20 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Using the forall statement might help.
Re: Issue with bulk collect over a db link [message #544864 is a reply to message #544829] Fri, 24 February 2012 06:03 Go to previous message
vishith
Messages: 1
Registered: August 2011
Location: INDIA
Junior Member
First of all thanks to all for replying, Thank you so much John for showing me how to put my code in a readable format.
Your tips were useful. After making the changes u suggested the proc was running fine .

I did not use forall statement as i had dblink in my code


Previous Topic: Function Based Index is not being used
Next Topic: Explain plan got changed suddenly
Goto Forum:
  


Current Time: Thu Mar 28 13:12:29 CDT 2024