Home » SQL & PL/SQL » SQL & PL/SQL » Bulk insert is very slow (Oracle 9i)
Bulk insert is very slow [message #338446] Tue, 05 August 2008 02:21 Go to next message
vipinsonkar2000
Messages: 38
Registered: May 2008
Member
Hi
My requirment is to load data from X table to a partition table Y using bulk collect. If record already present in Y table or partition key does not map to any partition then rejected count should increase by 1, if it get successfully load into Y table then inserted count will increase by 1.
I used bulk insert with save exception clause and code is also working fine but it is very slow. Even insertion from cursor is also faster then my code.

Version :
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
PL/SQL Release 9.2.0.6.0 - Production

Code:

VARIABLE v_ins_rows NUMBER
VARIABLE v_rej_rows NUMBER

DECLARE
x_Bulk_Error EXCEPTION;
PRAGMA EXCEPTION_INIT(x_Bulk_Error,- 24381);
TYPE Get_Scripts_Cur_typ IS REF CURSOR;
cv_Get_Scripts GET_SCRIPTS_CUR_TYP;
v_Fetch_Rows NUMBER := $Commit_cnt;
TYPE Fill_dt_Tab IS TABLE OF Pas.Pas_sig_Script_tmp.Fill_dt%TYPE INDEX BY BINARY_INTEGER ;
TYPE Store_nbr_Tab IS TABLE OF Pas.Pas_sig_Script_tmp.Store_nbr%TYPE INDEX BY BINARY_INTEGER ;
TYPE rx_nbr_Tab IS TABLE OF Pas.Pas_sig_Script_tmp.rx_nbr%TYPE INDEX BY BINARY_INTEGER ;
TYPE sig_seq_nbr_Tab IS TABLE OF Pas.Pas_sig_cd.sig_seq_nbr%TYPE INDEX BY BINARY_INTEGER ;
TYPE Insert_dt_tMe_Tab IS TABLE OF Pas.Pas_sig_Script_tmp.Insert_dt_tMe%TYPE INDEX BY BINARY_INTEGER ;
Fill_dt_arr FILL_DT_TAB;
Store_nbr_arr STORE_NBR_TAB;
rx_nbr_arr RX_NBR_TAB;
sig_seq_nbr_arr SIG_SEQ_NBR_TAB;
Insert_dt_tMe_arr INSERT_DT_TME_TAB;
BEGIN
:v_rej_rows := 0;

:v_ins_rows := 0;

OPEN cv_Get_Scripts FOR 'SELECT FILL_DT,
STORE_NBR ,
RX_NBR,
SIG_SEQ_NBR,
SCT.INSERT_DT_TME
FROM PAS.PAS_SIG_SCRIPT_TMP SCT, PAS.PAS_SIG_CD SC
WHERE SCT.SIG_CD_DSC = SC.SIG_CD_DSC';

LOOP

LOOP
FETCH cv_Get_Scripts
BULK COLLECT INTO Fill_dt_arr,Store_nbr_arr,rx_nbr_arr,sig_seq_nbr_arr,Insert_dt_tMe_arr
Limit 1000;

BEGIN

BEGIN
FORALL i IN Store_nbr_arr.FIRST .. Store_nbr_arr.LAST Save EXCEPTIONS
INSERT INTO Pas.Pas_sig_Script(
sig_Script_seq_nbr,
Fill_dt,
Store_nbr,
rx_nbr,
sig_seq_nbr,
Insert_dt_tMe,
Cur_Ind)
VALUES(
Pas.sig_Script_seq_nbr.Nextval,
Fill_dt_arr(i),
Store_nbr_arr (i),
rx_nbr_arr (i),
sig_seq_nbr_arr(i),
Insert_dt_tMe_arr(i),
'N');
EXCEPTION
WHEN x_Bulk_Error THEN
FOR i IN 1.. SQL%Bulk_Exceptions.COUNT LOOP
IF ((SQL%Bulk_Exceptions(i).Error_Code = 14400)
OR (SQL%Bulk_Exceptions(i).Error_Code = 1)) THEN
:v_rej_rows := :v_rej_rows + 1;
END IF;
END LOOP;
END;

IF (SQL%FOUND = True) THEN
:v_ins_rows := :v_ins_rows + SQL%ROWCOUNT;
END IF;

COMMIT;

EXIT WHEN cv_Get_Scripts%NOTFOUND;
END LOOP;

CLOSE cv_Get_Scripts;

COMMIT;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;

[Updated on: Tue, 05 August 2008 02:58]

Report message to a moderator

Re: Bulk insert is very slow [message #338452 is a reply to message #338446] Tue, 05 August 2008 02:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As already been requested in your previous topic: please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: Bulk insert is very slow [message #338454 is a reply to message #338452] Tue, 05 August 2008 03:04 Go to previous messageGo to next message
vipinsonkar2000
Messages: 38
Registered: May 2008
Member
Hi Michel
Thanks. Now i have added oracle version too. I need your help. It is very urgent.
Thank you.
Re: Bulk insert is very slow [message #338457 is a reply to message #338454] Tue, 05 August 2008 03:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is still not formatted AND "urgent" is one of the forbidden word.
Read and follow rules.

Regards
Michel

[Updated on: Tue, 05 August 2008 03:12]

Report message to a moderator

Re: Bulk insert is very slow [message #338458 is a reply to message #338454] Tue, 05 August 2008 03:12 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Why Bulk Collect when you can insert data in one single statement??

Regards,
Rajat
Re: Bulk insert is very slow [message #338461 is a reply to message #338446] Tue, 05 August 2008 03:18 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Can you please take trace of your procedure and see where exactly it is taking time. Your code is not formatted at all and it is difficult to understand it..
Re: Bulk insert is very slow [message #338464 is a reply to message #338446] Tue, 05 August 2008 03:24 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
I don't understand why you using procedure for this.And then thinking about it's performance.

Ask yourself why you need procedure??

Their are several methods through which you can insert data in single SQL statement.And believe it SQL is going to be faster then PL/SQL approach as well as simple.

Hint:-
Use Merge or Use (exists or not exists) clause in your sql.

Regards,
Rajat

[Updated on: Tue, 05 August 2008 03:25]

Report message to a moderator

Re: Bulk insert is very slow [message #338468 is a reply to message #338464] Tue, 05 August 2008 03:36 Go to previous messageGo to next message
vipinsonkar2000
Messages: 38
Registered: May 2008
Member
Thanks for reply. I cant use single SQL statment because I have handle the exception for ORA-10000 and ORA-14400.
Re: Bulk insert is very slow [message #338471 is a reply to message #338468] Tue, 05 August 2008 03:48 Go to previous messageGo to next message
vipinsonkar2000
Messages: 38
Registered: May 2008
Member
Hi All
Can any one tell me how one can handle the exception through single SQL statment. I have to inrease the rejected counter for ORA-10000 and ORA-14400. That is why i am using oracle procedure.

Is it clear now.
Re: Bulk insert is very slow [message #338472 is a reply to message #338468] Tue, 05 August 2008 03:51 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
vipinsonkar2000 wrote on Tue, 05 August 2008 03:36

Quote:
Thanks for reply. I cant use single SQL statment because I have handle the exception for ORA-10000 and ORA-14400


You are not handling any thing.You are just getting the count
of rows that you are unable to insert.

And that i can find with a single sql statement.

You are using a cursor that get data from a table and then you try to insert data into another table and exception is raised if data is already there.And you get a counter incremented.

For this task you have written a Stored Procedure. Shocked

Regards,
Rajat

[Updated on: Tue, 05 August 2008 03:52]

Report message to a moderator

Re: Bulk insert is very slow [message #338481 is a reply to message #338472] Tue, 05 August 2008 04:00 Go to previous messageGo to next message
vipinsonkar2000
Messages: 38
Registered: May 2008
Member
Hi
yes I want to increment the counter but only in case ORA-10000 and ORA-14400 not for other ORA- errors. If apart from these two error occurs than my procedue should get failed.
You are suggesting that just insert using single statment? Will you please tell me what will happen ORA-01401 inserted value too large for column erro is there?
Re: Bulk insert is very slow [message #338482 is a reply to message #338472] Tue, 05 August 2008 04:00 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Get rows that are already in table

SELECT 
 COUNT(1) 
FROM <TableInWhichDataHasToBeInserted> a 
WHERE EXISTS(
SELECT 1 FROM <loadingtable> b WHERE b.<key>=a.<key>)


Get rows that are not in table

SELECT 
 COUNT(1) 
FROM <TableInWhichDataHasToBeInserted> a 
WHERE NOT EXISTS(
SELECT 1 FROM <loadingtable> b WHERE b.<key>=a.<key>)




Make a insert statement by using Exists and Not Exists.
Do i need to provide it or you will like to try??

Sorry i didn't see the previous post.

Regards,
Rajat

[Updated on: Tue, 05 August 2008 04:02]

Report message to a moderator

Re: Bulk insert is very slow [message #338485 is a reply to message #338482] Tue, 05 August 2008 04:04 Go to previous messageGo to next message
vipinsonkar2000
Messages: 38
Registered: May 2008
Member
Hi Rajat
Thankls for reply. Are you really serious. See the details of ORA-14400 and ORA-10000 then reply.
I am not a beginner. I also have more then 4 yrs PL/SQL experience. And please dont tell me how to find if record is already there?
Re: Bulk insert is very slow [message #338491 is a reply to message #338482] Tue, 05 August 2008 04:10 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
I already mentioned that i didn't see the previous post.

you have not mentioned that you have 4+ years of experience
in PL/SQL before. Confused

How the hell i Know.

you have not formatted your post.So the question is not clear.
i answered by just having a look at your post and that is the maximum i can get in an unformatted post.


Regards,
Rajat
Re: Bulk insert is very slow [message #338492 is a reply to message #338485] Tue, 05 August 2008 04:11 Go to previous messageGo to next message
vipinsonkar2000
Messages: 38
Registered: May 2008
Member
Hi Rajat
What happend? Now you got my point or not? Please do reply?
Re: Bulk insert is very slow [message #338493 is a reply to message #338492] Tue, 05 August 2008 04:15 Go to previous messageGo to next message
vipinsonkar2000
Messages: 38
Registered: May 2008
Member
Hi Rajat
No problem dear. Mistake was mine as I am able to format the code because it is very hard to understand the error while formating.
Re: Bulk insert is very slow [message #338497 is a reply to message #338493] Tue, 05 August 2008 04:25 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
How many records you are inserting.

Regards,
Rajat
Re: Bulk insert is very slow [message #338499 is a reply to message #338446] Tue, 05 August 2008 04:28 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
And by the way why two loop statements for one cursor??

Edit your post and add [code] tags.

Regards,
Rajat

[Updated on: Tue, 05 August 2008 04:28]

Report message to a moderator

Re: Bulk insert is very slow [message #338500 is a reply to message #338497] Tue, 05 August 2008 04:30 Go to previous messageGo to next message
vipinsonkar2000
Messages: 38
Registered: May 2008
Member
It depends on particular day in production data. But average is 2.0-2.5 million records daily.
Re: Bulk insert is very slow [message #338501 is a reply to message #338446] Tue, 05 August 2008 04:35 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

It depends on particular day in production data. But average is 2.0-2.5 million records daily. 


Why you want to keep guessing the real problem, when Oracle suppiles tool to find the real cause of the issue? Take a trace and see exact issue. It may not be directly to do with code but may be while getting the space allocation for data insert? Also if you are inserting million records then why limiting the set to 1000 for bulk fetch?

Re: Bulk insert is very slow [message #338503 is a reply to message #338501] Tue, 05 August 2008 04:40 Go to previous messageGo to next message
vipinsonkar2000
Messages: 38
Registered: May 2008
Member
Hi
I have tested my code just 100000 rows but it is still slow. Even inserion using cursor is also faster than BULK INSERT.

Re: Bulk insert is very slow [message #338505 is a reply to message #338503] Tue, 05 August 2008 04:42 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Define Fast first??

vipinsonkar2000 if you wan't us to help you then please format your post.

How can we know what you have tried using cursor.
You are limiting your bulk collect to 1000 rows.

try what himang has told you to do??

Regards,
Rajat

[Updated on: Tue, 05 August 2008 04:44]

Report message to a moderator

Re: Bulk insert is very slow [message #338506 is a reply to message #338505] Tue, 05 August 2008 04:47 Go to previous messageGo to next message
vipinsonkar2000
Messages: 38
Registered: May 2008
Member
Can anyone help me to format the code. I am not able to do the formating.
Re: Bulk insert is very slow [message #338507 is a reply to message #338506] Tue, 05 August 2008 04:50 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
edit your message and apply code tags.

preview your message and apply message changes.

Michel already posted you the link.Just read it

Regards,
Rajat
Re: Bulk insert is very slow [message #338509 is a reply to message #338506] Tue, 05 August 2008 04:53 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

vipinsonkar2000 wrote on Tue, 05 August 2008 15:17
Can anyone help me to format the code. I am not able to do the formating.


What problem you have in formatting the code???? Confused Just an idea how to do it.. not completely formatted though...

VARIABLE v_ins_rows NUMBER
VARIABLE v_rej_rows NUMBER

DECLARE
	x_Bulk_Error EXCEPTION;
	PRAGMA EXCEPTION_INIT(x_Bulk_Error,- 24381);
	
	cv_Get_Scripts GET_SCRIPTS_CUR_TYP;
	v_Fetch_Rows NUMBER := $Commit_cnt;
	
	TYPE Get_Scripts_Cur_typ IS REF CURSOR;
	TYPE Fill_dt_Tab IS TABLE OF Pas.Pas_sig_Script_tmp.Fill_dt%TYPE INDEX BY BINARY_INTEGER ;
	TYPE Store_nbr_Tab IS TABLE OF Pas.Pas_sig_Script_tmp.Store_nbr%TYPE INDEX BY BINARY_INTEGER ;
	TYPE rx_nbr_Tab IS TABLE OF Pas.Pas_sig_Script_tmp.rx_nbr%TYPE INDEX BY BINARY_INTEGER ;
	TYPE sig_seq_nbr_Tab IS TABLE OF Pas.Pas_sig_cd.sig_seq_nbr%TYPE INDEX BY BINARY_INTEGER ;
	TYPE Insert_dt_tMe_Tab IS TABLE OF Pas.Pas_sig_Script_tmp.Insert_dt_tMe%TYPE INDEX BY BINARY_INTEGER ;
	
	Fill_dt_arr 		FILL_DT_TAB;
	Store_nbr_arr 		STORE_NBR_TAB;
	rx_nbr_arr 			RX_NBR_TAB;
	sig_seq_nbr_arr 	SIG_SEQ_NBR_TAB;
	Insert_dt_tMe_arr 	INSERT_DT_TME_TAB;
	
BEGIN
	:v_rej_rows := 0;
	
	:v_ins_rows := 0;

	OPEN cv_Get_Scripts FOR 'SELECT FILL_DT,
									 STORE_NBR ,
									 RX_NBR,
									 SIG_SEQ_NBR,
									 SCT.INSERT_DT_TME
							FROM PAS.PAS_SIG_SCRIPT_TMP SCT, PAS.PAS_SIG_CD SC	
							WHERE SCT.SIG_CD_DSC = SC.SIG_CD_DSC';

	LOOP

		LOOP
		FETCH cv_Get_Scripts BULK COLLECT INTO Fill_dt_arr,Store_nbr_arr,rx_nbr_arr,sig_seq_nbr_arr,Insert_dt_tMe_arr Limit 1000;

		BEGIN

			BEGIN
				FORALL i IN Store_nbr_arr.FIRST .. Store_nbr_arr.LAST Save EXCEPTIONS
					INSERT INTO Pas.Pas_sig_Script(
						sig_Script_seq_nbr,
						Fill_dt,
						Store_nbr,
						rx_nbr,
						sig_seq_nbr,
						Insert_dt_tMe,
						Cur_Ind)
					VALUES(
						Pas.sig_Script_seq_nbr.Nextval,
						Fill_dt_arr(i),
						Store_nbr_arr (i),
						rx_nbr_arr (i),
						sig_seq_nbr_arr(i),
						Insert_dt_tMe_arr(i),
						'N');
				EXCEPTION
				WHEN x_Bulk_Error THEN
					FOR i IN 1.. SQL%Bulk_Exceptions.COUNT LOOP
						IF ((SQL%Bulk_Exceptions(i).Error_Code = 14400) OR (SQL%Bulk_Exceptions(i).Error_Code = 1)) THEN
							:v_rej_rows := :v_rej_rows + 1;
						END IF;
					END LOOP;
			END;

		IF (SQL%FOUND = True) THEN
			:v_ins_rows := :v_ins_rows + SQL%ROWCOUNT;
		END IF;

		COMMIT;

			EXIT WHEN cv_Get_Scripts%NOTFOUND;
		END LOOP;

		CLOSE cv_Get_Scripts;

		COMMIT;
	EXCEPTION
	WHEN OTHERS THEN
		RAISE;
	END; 	
/
Re: Bulk insert is very slow [message #338512 is a reply to message #338446] Tue, 05 August 2008 05:00 Go to previous messageGo to next message
vipinsonkar2000
Messages: 38
Registered: May 2008
Member
VARIABLE v_ins_rows NUMBER
VARIABLE v_rej_rows NUMBER

DECLARE
x_Bulk_Error EXCEPTION;
PRAGMA EXCEPTION_INIT(x_Bulk_Error,- 24381);

TYPE Get_Scripts_Cur_typ IS REF CURSOR;
cv_Get_Scripts GET_SCRIPTS_CUR_TYP;
v_Fetch_Rows NUMBER := $Commit_cnt;

TYPE Fill_dt_Tab IS TABLE OF Pas.Pas_sig_Script_tmp.Fill_dt%TYPE INDEX BY BINARY_INTEGER ;
TYPE Store_nbr_Tab IS TABLE OF Pas.Pas_sig_Script_tmp.Store_nbr%TYPE INDEX BY BINARY_INTEGER ;
TYPE rx_nbr_Tab IS TABLE OF Pas.Pas_sig_Script_tmp.rx_nbr%TYPE INDEX BY BINARY_INTEGER ;
TYPE sig_seq_nbr_Tab IS TABLE OF Pas.Pas_sig_cd.sig_seq_nbr%TYPE INDEX BY BINARY_INTEGER ;
TYPE Insert_dt_tMe_Tab IS TABLE OF Pas.Pas_sig_Script_tmp.Insert_dt_tMe%TYPE INDEX BY BINARY_INTEGER ;

Fill_dt_arr FILL_DT_TAB;
Store_nbr_arr STORE_NBR_TAB;
rx_nbr_arr RX_NBR_TAB;
sig_seq_nbr_arr SIG_SEQ_NBR_TAB;
Insert_dt_tMe_arr INSERT_DT_TME_TAB;

BEGIN
:v_rej_rows := 0;
:v_ins_rows := 0;

OPEN cv_Get_Scripts
FOR 'SELECT FILL_DT,
STORE_NBR ,
RX_NBR,
SIG_SEQ_NBR,
SCT.INSERT_DT_TME
FROM PAS.PAS_SIG_SCRIPT_TMP SCT, PAS.PAS_SIG_CD SC
WHERE SCT.SIG_CD_DSC = SC.SIG_CD_DSC';

LOOP

LOOP
FETCH cv_Get_Scripts
BULK COLLECT INTO Fill_dt_arr,Store_nbr_arr,rx_nbr_arr,sig_seq_nbr_arr,Insert_dt_tMe_arr
Limit 1000;

BEGIN

BEGIN
FORALL i IN Store_nbr_arr.FIRST .. Store_nbr_arr.LAST Save EXCEPTIONS
INSERT INTO Pas.Pas_sig_Script
(sig_Script_seq_nbr,
Fill_dt,
Store_nbr,
rx_nbr,
sig_seq_nbr,
Insert_dt_tMe,
Cur_Ind)
VALUES
(Pas.sig_Script_seq_nbr.Nextval,
Fill_dt_arr(i),
Store_nbr_arr (i),
rx_nbr_arr (i),
sig_seq_nbr_arr(i),
Insert_dt_tMe_arr(i),
'N');
EXCEPTION
WHEN x_Bulk_Error THEN
FOR i IN 1.. SQL%Bulk_Exceptions.COUNT LOOP
IF ((SQL%Bulk_Exceptions(i).Error_Code = 14400) OR (SQL%Bulk_Exceptions(i).Error_Code = 1)) THEN
:v_rej_rows := :v_rej_rows + 1;
END IF;
END LOOP;
END;

IF (SQL%FOUND = True) THEN
:v_ins_rows := :v_ins_rows + SQL%ROWCOUNT;
END IF;

COMMIT;

EXIT WHEN cv_Get_Scripts%NOTFOUND;
END LOOP;

CLOSE cv_Get_Scripts;

COMMIT;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
Re: Bulk insert is very slow [message #338516 is a reply to message #338446] Tue, 05 August 2008 05:05 Go to previous messageGo to next message
vipinsonkar2000
Messages: 38
Registered: May 2008
Member
Hi I am again facing the problem in formating. I formated it but again in preview it is like this only.
Re: Bulk insert is very slow [message #338522 is a reply to message #338516] Tue, 05 August 2008 05:12 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Sorry, if you can't even figure out how to put code tags in your post like :


[code]
Your code here
[/code]



and use the preview feature, you will have not much chance of figuring Oracle out.
Re: Bulk insert is very slow [message #338523 is a reply to message #338446] Tue, 05 August 2008 05:12 Go to previous messageGo to next message
SAURABH_MISHRA
Messages: 2
Registered: August 2008
Location: India,Hyderabad
Junior Member
Declare your cursor outside begin...
Fetch rows inside first loop...not inside second loop...
Try to check performance of cursor query...
Try to check limit value increase or decrease it....
Try to check undo and redo latches in stats pack....
Re: Bulk insert is very slow [message #338528 is a reply to message #338516] Tue, 05 August 2008 05:20 Go to previous messageGo to next message
vipinsonkar2000
Messages: 38
Registered: May 2008
Member
VARIABLE v_ins_rows  NUMBER
VARIABLE v_rej_rows  NUMBER

DECLARE
   x_Bulk_Error  EXCEPTION;
   PRAGMA EXCEPTION_INIT(x_Bulk_Error,- 24381);

   TYPE Get_Scripts_Cur_typ IS REF CURSOR;
   cv_Get_Scripts      GET_SCRIPTS_CUR_TYP;

   v_Fetch_Rows       NUMBER := $Commit_cnt;

   TYPE Fill_dt_Tab IS TABLE OF Pas.Pas_sig_Script_tmp.Fill_dt%TYPE INDEX BY BINARY_INTEGER ;
   TYPE Store_nbr_Tab IS TABLE OF Pas.Pas_sig_Script_tmp.Store_nbr%TYPE INDEX BY BINARY_INTEGER ;
   TYPE rx_nbr_Tab IS TABLE OF Pas.Pas_sig_Script_tmp.rx_nbr%TYPE INDEX BY BINARY_INTEGER ;
   TYPE sig_seq_nbr_Tab IS TABLE OF Pas.Pas_sig_cd.sig_seq_nbr%TYPE INDEX BY BINARY_INTEGER ;
   TYPE Insert_dt_tMe_Tab IS TABLE OF Pas.Pas_sig_Script_tmp.Insert_dt_tMe%TYPE INDEX BY BINARY_INTEGER ;
   
   Fill_dt_arr        FILL_DT_TAB;
   Store_nbr_arr      STORE_NBR_TAB;
   rx_nbr_arr         RX_NBR_TAB;
   sig_seq_nbr_arr    SIG_SEQ_NBR_TAB;
   Insert_dt_tMe_arr  INSERT_DT_TME_TAB;
BEGIN
  :v_rej_rows := 0;
  
  :v_ins_rows := 0;
  
  OPEN cv_Get_Scripts 
  FOR 'SELECT FILL_DT,
            STORE_NBR ,
            RX_NBR,
            SIG_SEQ_NBR,
            SCT.INSERT_DT_TME
       FROM PAS.PAS_SIG_SCRIPT_TMP SCT, PAS.PAS_SIG_CD SC
       WHERE SCT.SIG_CD_DSC = SC.SIG_CD_DSC';
  
 
 	    LOOP
            FETCH cv_Get_Scripts 
            BULK COLLECT INTO Fill_dt_arr,Store_nbr_arr,rx_nbr_arr,sig_seq_nbr_arr,Insert_dt_tMe_arr 
            Limit 1000;
         
            BEGIN
                  FORALL i IN Store_nbr_arr.FIRST .. Store_nbr_arr.LAST Save EXCEPTIONS
                  INSERT INTO Pas.Pas_sig_Script (
                                                  sig_Script_seq_nbr,
                                                  Fill_dt,Store_nbr,
                                                  rx_nbr,sig_seq_nbr,
                                                  Insert_dt_tMe,
                                                  Cur_Ind
                                                  )
                                           VALUES(
                                                  Pas.sig_Script_seq_nbr.Nextval,
                                                  Fill_dt_arr(i),
                                                  Store_nbr_arr (i),
                                                  rx_nbr_arr (i),
                                                  sig_seq_nbr_arr(i),
                                                  Insert_dt_tMe_arr(i),
                                                  'N');
    EXCEPTION
      WHEN x_Bulk_Error THEN

        FOR i IN 1.. SQL%Bulk_Exceptions.COUNT LOOP

          IF ((SQL%Bulk_Exceptions(i).Error_Code = 14400) OR (SQL%Bulk_Exceptions(i).Error_Code = 1)) THEN
            :v_rej_rows := :v_rej_rows + 1;
          END IF;

        END LOOP;
      END;
    
    IF (SQL%FOUND = True) THEN
      :v_ins_rows := :v_ins_rows + SQL%ROWCOUNT;
    END IF;
    
    COMMIT;
    
    EXIT WHEN cv_Get_Scripts%NOTFOUND;
  END LOOP;
  
  CLOSE cv_Get_Scripts;
  
  COMMIT;

EXCEPTION
  WHEN OTHERS THEN
  RAISE;
END;

[Updated on: Tue, 05 August 2008 05:26]

Report message to a moderator

Re: Bulk insert is very slow [message #338537 is a reply to message #338528] Tue, 05 August 2008 05:33 Go to previous messageGo to next message
vipinsonkar2000
Messages: 38
Registered: May 2008
Member
Hi All
Now my code is formatted one? or still some problem? Can any one help me out? Why this BULK insert is slower the normal cursor insert.
Thank you.
Re: Bulk insert is very slow [message #338558 is a reply to message #338446] Tue, 05 August 2008 06:15 Go to previous messageGo to next message
SAURABH_MISHRA
Messages: 2
Registered: August 2008
Location: India,Hyderabad
Junior Member
Hi Vipin,

We also use to have such issues while using bulk collect and for-all
1) check explain plan and execution time for below query. if more try to tune this.
SELECT FILL_DT,
STORE_NBR ,
RX_NBR,
SIG_SEQ_NBR,
SCT.INSERT_DT_TME
FROM PAS.PAS_SIG_SCRIPT_TMP SCT, PAS.PAS_SIG_CD SC
WHERE SCT.SIG_CD_DSC = SC.SIG_CD_DSC';

2)Also try to increase limit

FETCH cv_Get_Scripts
BULK COLLECT INTO Fill_dt_arr,Store_nbr_arr,rx_nbr_arr,sig_seq_nbr_arr,Insert_dt_tMe_arr
Limit 1000;

3) check statspack while running this sp for wait events.

Thanks
Saurabh
Re: Bulk insert is very slow [message #338724 is a reply to message #338446] Tue, 05 August 2008 17:27 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
    EXCEPTION
      WHEN x_Bulk_Error THEN

        FOR i IN 1.. SQL%Bulk_Exceptions.COUNT LOOP

          IF ((SQL%Bulk_Exceptions(i).Error_Code = 14400) OR (SQL%Bulk_Exceptions(i).Error_Code = 1)) THEN
            :v_rej_rows := :v_rej_rows + 1;
          END IF;

        END LOOP;
      END;


You are probably getting mroe errors than you reasise.

This exception section doesnt do a RAISE on WHEN OTHERS.

Also, bulk insert is much faster than standard cursor insert.

You need to pinpoint where the time is spent. Is it spent inserting the rows, is it spent handling errors, or is it spent building the and retreiving rows fromt he cursor.

Put in some log messages with timestamps on at appropriate point to identify your bottleneck.

DOes it start fast and get slower, is anything else inserting intot he table you are, how many indexes are being maintained byt he insert.

There is a good tuning post on this forum with general tips.
Re: Bulk insert is very slow [message #338782 is a reply to message #338724] Tue, 05 August 2008 23:08 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
coleing wrote on Wed, 06 August 2008 00:27

This exception section doesnt do a RAISE on WHEN OTHERS.

But since the WHEN OTHERS are not caught, there is no reason/way to reraise. Any other exception than the one caught, will simply fall through.
This is good. Please don't encourage him to add a when others..

[Edit: rescanned original code.. Remove that stupid, unnecessary WHEN OTHERS exception. It only obscures stuff!!!]

[Updated on: Tue, 05 August 2008 23:13]

Report message to a moderator

Previous Topic: Analytic Query
Next Topic: Hetergenous services
Goto Forum:
  


Current Time: Sat Dec 03 03:37:26 CST 2016

Total time taken to generate the page: 0.29536 seconds