| performance Tuning [message #612557] |
Tue, 22 April 2014 01:15  |
 |
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 #612562 is a reply to message #612557] |
Tue, 22 April 2014 01:34  |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
Hi,
ramchandru1989 wrote on Tue, 22 April 2014 08:15So 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:15do 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 #612567 is a reply to message #612566] |
Tue, 22 April 2014 02:00 |
John Watson
Messages: 8965
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 |
 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
