Running SQL's in parallel 1

[vrrajeeb]

Hi,

I have a list of SQLs in a file. I need to open 10 Oracle sessions that read from this file in such a way that each session doesn't read or execute a SQL that's being handled by another session.

Initially I tried by allocating a set number of SQL's to each of the sessions. This worked fine, but some of the sessions "finished" their job early and were sitting idle, which I want to avoid.

And when I give access to all SQLs to all sessions, more than 1 session was trying to run the same SQL and erring out ?

Any smart ideas ?

Thanx

-Maria

 

[MikeLacey]

Maria,

Write a shell or Perl script that reads the file a launches a background job which runs the SQL's.

As you're starting a process for each SQL in the file there's no chance that two or more processes will be executing the same SQL.

If you're concerned about starting too many SQL's at once - As you come to launch another job, check how many are still working and, if the number's over 10 then go to sleep for a second and then look again.

Mike

"Deliver me from the bane of civilised life; teddy bear envy."

Want to get great answers to your Tek-Tips questions? Have a look at faq219-2884

 

[vrrajeeb]

Thanx Mike,

How does running in background help ? and how do we ensure that 2 or more processes won't execute the same SQL ?

I had created another file from the SQL list file with an extra column as status. Whenever a SQL is processed by a session, it would update this column as "IN PROCESS", when finished, it is marked as "DONE", so that other sessions ignore the SQL's with status ""IN PROCESS" or "DONE" ...

This approach worked, but between the sessions updating the status, there were many sessions trying to run the same SQL ...

Thanx

-Maria

 

[MikeLacey]

Hi Maria,

I think that your problem is that you have more than one script/program using your SQL list file.

If you have a SINGLE script that reads through the file and launches SQL jobs as required you will not have that problem of having to update the SQL list file to show the status of a job.

In english the logic of such a script might look like this.

# install signal handler to monitor when background 
# jobs finish. This gets called whenever a background 
# process completes.
signal CHILD {
[tab]jobs_running = jobs_running - 1
}

# main part of script, launches SQL jobs
for each line in the SQL list file
[tab]job_started = false
[tab]while job_started = false
[tab][tab]if jobs_running < 10 {
[tab][tab][tab]read SQL
[tab][tab][tab]launch SQL (in background)
[tab][tab][tab]jobs_running = jobs_running + 1
[tab][tab][tab]job_started = true
[tab][tab]} else {
[tab][tab][tab]# too many running jobs, so we sleep for a while
[tab][tab][tab]sleep 5
[tab][tab]
[tab]endwhile
endfor

How you accomplish the above will depend upon what your script is written in. To do the "jobs_running" check you will need to have something to catch the event when a background process completes (a signal handler) This is easier in some scripting languages than others. In Perl it's easy for instance.

Mike

"Deliver me from the bane of civilised life; teddy bear envy."

Want to get great answers to your Tek-Tips questions? Have a look at faq219-2884

 

[MikeLacey]

Hmmm there should be a } between the sleep and endwhile lines - the indentation is correct though and, hopefully, makes the meaning clear

Mike

"Deliver me from the bane of civilised life; teddy bear envy."

Want to get great answers to your Tek-Tips questions? Have a look at faq219-2884

 

[MikeLacey]

An (untested) Perl script for you that takes the SQL list file from the end of itself (after the __END__ line)

The sub routine that actually runs the SQL is just a stub as this is dependant upon which database you're running.

If the version of Perl on your machine complains about 'use strict' and my' you're probably using version 4, just remove the strict line and all occurennces of 'my'.

use strict;

my $jobs_running = 0;
my $max_concurrent_jobs = 10;

# first, install a signal handler to keep track of
# how many SQL jobs are running
sub REAPER {
my $waitedpid = 0;
	$waitedpid = wait;
	$jobs_running--;
	$SIG{CHLD} = \&REAPER;
}
$SIG{CHLD} = \&REAPER;

sub LaunchSQL{
	my $SQL = shift; # get SQL from parameter
	# and then run it
	return;
}

my $job_started;
my $SQL;
while(<DATA>){
	chomp($SQL = $_); # save the SQL statement that will be run
	$job_started = 0; # false
	while(!$job_started){
		$job_started = 0;
		if( $jobs_running < $max_concurrent_jobs ){
			if(fork()){
				# this is still the controlling process
				$jobs_running++;
				$job_started = 1; # true
			} else {
				# this is the background job
				LaunchSQL($SQL);
				exit;
			}
		} else {
			# too many running jobs, so we sleep for a while
			sleep 5
		}
	}
}

__END__
SELECT * FROM A_TABLE;
UPDATE ANOTHER_TABLE SET a_column=1;
DELETE YET_ANOTHER_TABLE WHERE a_columns < 3

Mike

"Deliver me from the bane of civilised life; teddy bear envy."

Want to get great answers to your Tek-Tips questions? Have a look at faq219-2884

 

[vrrajeeb]

Thanx a lot Mike for your posts ... I really appreciate it ...

With help from you first post, I could write a shell to do what I wanted to ... The logic is as follows :

1) I have a master directory which holds all SQLs

2) I then have 10 (temporary) subdirectories under this directory

3) There is just one process that loops through the master directory and and moves the first 10 SQLs to the subdirectories, (one per subdirectory)

4) There is a shell script also that gets copied to all of these subdirectories which execute the SQL's copied into these subdirectories

5)After each SQL execution from within the subdirectory, the .SQL is renamed (moved) as .sql.done

6) The process that loops through the master directory checks for files in the subdirectories and if it doesn't find a .SQL in a subdirectory (becuase it was renamed as .sql.done after the previous execution), moves the next SQL file from the master directory to that subdirectory and so on.

I am pretty sure your's will look elegant ... and I shall certainly give it a try ...

Thanx again!

-Maria

 

[MikeLacey]

you're welcome Maria

Mike

"Deliver me from the bane of civilised life; teddy bear envy."

Want to get great answers to your Tek-Tips questions? Have a look at faq219-2884