-
Dear gurus
We have a pl/sql procedure, which is used to process for datapumping, it processes 20 records per min, ... we would like to improve the performance... we have tuned the code as much as possible to improve the performace with various aspects...
We have an idea, to post 4times same procedure with different arguments simultaneously, so it may improve performance 4 times (theoretically)...
Example:
Procedure1(ID1,Filename1) 20 rec processing the same time
Procedure1(ID2,Filename2) 20 rec processing
Procedure1(ID3,Filename3) 20 rec processing
Procedure1(ID4,Filename4) 20 rec processing
this is something like, the procedure has to be assigned in different worker / processor...
so, we need to have a controller script, which will control the operation of all those procedures, and once this process is completed for , it has to take next argument and processes..
Could someone throw some light on this...
regards
anandharaj
-
Start each of your procedures in the background and wait for them to finish. For example:
Code:
#!/bin/ksh
sqlplus /@mydb @script1.sql > /dev/null 2>&1 &
sqlplus /@mydb @script2.sql > /dev/null 2>&1 &
sqlplus /@mydb @script3.sql > /dev/null 2>&1 &
sqlplus /@mydb @script4.sql > /dev/null 2>&1 &
wait # will wait for all your bg jobs to finish
echo "Done!"
script1-4 will contain your Procedure call. Make sure script1-4 are self contained meaning they spool to different files and quit at the end.
-
Hi Jeff Hunter
Thank you very much for your prompt reply...
I have a question that, how can i pass the arguments of other records once the stage one is completed..., so that the entire record could be processed stage by stage automatically...
i will appreciate you that if you could explain with more details about the same..?
thanks and regards
anandharaj
-
I need more information in order to intelligently answer your question....
-
if u plan to use a dbms_job to automatically execute ur procedure .
try preparing script in the following lines .
- i guess u are executing a pl/sql procedure which accepts same number of arguments with similar datatype .
SQL> create table xyz
( id varchar2(10) ,
filename varchar2(30) ) ;
SQL> insert all arguments as values into this table .
create procedure automate
cursor c1 is select id , filename from xyz where .... ;
begin
for r1 in c1
loop
Procedure1(r1.id,r1.filename ) ;
end loop ;
end ;
----
automattically submitting the job
declare
x binary_integer;
begin
dbms_job.submit(x, 'begin automate; end;',
sysdate , 'SYSDATE + FREQUENCY ' , FALSE);
commit;
end;
DOES THIS ANSWER UR QUESTION ??
CHECK SYNTAX
-
consider earlier post if ur intention is to automate in other way it is the database way not the unix way ...
-
Hi Siva Prakash
Thank you very much... Your update comes very close to my question.. Thanks once again...
and i need a clarification that if i use dbms_job, i need to wait till my first record to complete the job... then it will proceed for next record and so on... am i correct ?
In the unix environment, is there any way to do as below:
-assign 1st record to worker1,
-assign 2nd record to worker2,
-assign 3rd record to worker3,
-assign 4th record to worker4 and
restart the workers..
-assign 5th record to worker1,
-assign 6th record to worker2,
-assign 7th record to worker3,
-assign 8th record to worker4 and.. so on repeat till last record to complete...
if i do like wise, performance will be improved to 4 times...
thanks and regards
and second recordeed, multi-process/worker to process my job simultaneousely, i mean
