Home » RDBMS Server » Performance Tuning » performance Tuning (Oracle 10g)
performance Tuning [message #612557] Tue, 22 April 2014 01:15 Go to next message
ramchandru1989
Messages: 4
Registered: April 2014
Location: CHENNAI
Junior Member
Hi All,
Need a Help.
I am having a procedure ,it will fetches 15Lakhs records from staging tables and it will insert into target tables and it's taking 60 Minutes.

I need to insert the same 15Lakhs records in 10 Mins.
So i am running the same procedure for 6 times in parallel using dbms_scheduler(25K records in each batch).

All 6 batches has to complete in 10 minutes of time right ?but its taking 540 minutes. please help to resolve this.

do we need to set any parameter for running procedure in parallel.

Thanks In advance .
Re: performance Tuning [message #612559 is a reply to message #612557] Tue, 22 April 2014 01:20 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum. Please read our OraFAQ Forum Guide and please read How to use [code] tags and make your code easier to read

What is a lakh?
Re: performance Tuning [message #612562 is a reply to message #612557] Tue, 22 April 2014 01:34 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
Hi,
ramchandru1989 wrote on Tue, 22 April 2014 08:15
So i am running the same procedure for 6 times in parallel using dbms_scheduler(25K records in each batch).

All 6 batches has to complete in 10 minutes of time right ?but its taking 540 minutes. please help to resolve this.

It depends on resources and mutual dependency.
When one man digs a hole in 60 minutes, will 60 men dig the same hole in 1 minute?
ramchandru1989 wrote on Tue, 22 April 2014 08:15
do we need to set any parameter for running procedure in parallel.

For start, you can assure that they are not exclusively locking the same resource.
I suspect that you are digging the same hole (processing the same data) multiple times, but it is impossible to prove it without any clue in code.
Re: performance Tuning [message #612566 is a reply to message #612562] Tue, 22 April 2014 01:55 Go to previous messageGo to next message
ramchandru1989
Messages: 4
Registered: April 2014
Location: CHENNAI
Junior Member
@ john: 1 lakh is equal to 100 thousands .15 lakh is equal to 150,000 thousands.
Re: performance Tuning [message #612567 is a reply to message #612566] Tue, 22 April 2014 02:00 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Quote:
@ john: 1 lakh is equal to 100 thousands .15 lakh is equal to 150,000 thousands.
You arithmetic is wrong. If 1 lakh is 100,000, then 15 lakhs is 1,500,000. Please remember to use SI units in future, it will avoid this confusion.

However, to insert 1,500,000 rows should take only a few seconds. You need to post the code of the procedure that you are running: no-one can tune code that they have not seen. Please remember to enclose the code with [code] tags.
Re: performance Tuning [message #613102 is a reply to message #612557] Tue, 29 April 2014 19:20 Go to previous message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
I am going to hazard a guess here that when you say you have a procedure that inserts 1.5 million rows by copying from a staging table and inserting into a target table, you are doing it one row at a time. As Tom Kyte calls it, slow-by-slow insert. To guess further I suspect your code looks like the typical slow code

cursor c1 is ...
...
begin
open cursor
loop
   fetch...
   insert...
end loop;

or maybe the even worse

   fetch
   if sql%notfound then exit; end if;
   ...
   insert...
   commit;
   ...

if you are inserting one row at a time then it might take 60 minutes.

try writing one SQL insert statement to load all the data at one time. Then you can insert it all in under a minute.

Good luck, Kevin
Previous Topic: ODI Performance issue with flat file
Next Topic: After updating memory_target, performance is degraded
Goto Forum:
  


Current Time: Thu Mar 28 20:42:35 CDT 2024