Executing SQL's in parallel

UNIX for Beginners Q & A
1

Hi Folks,

I have requirement to pull a bunch of SQL's from a table in DB and execute them in parallel and update the status of each query as and when they complete. Can you please help me with ideas on how this can be achieved?

 
create table list_of_sql
(
id number,
full_sql varchar2(255),
status varchar2(50),
create_dt timestamp,
update_dt timestamp
)

id - auto-generated number
full_sql - will have the sql to be executed
status - P : Pending (when the sql is added but not picked up for execution yet)
A : Active (Once the query is picked up for execution and is running)
C : Completed (Once the query execution has completed)

It will be easy to do this sequentially but the requirement is to run the sql's in parallel and at a time there will be no more than 10 sql;s in the table.

Thanks in advance.

2

Please show your own work and any scripts / programs you have written so far to accomplish your task; and any error messages you got.

1 Like
3

It's also important to realize that parallel can be a performance loss in some situations, especially if any of these queries are disk-intensive.

1 Like
4

I am trying to design this and looking for recommendations. As of now i'm thinking to write a script which will take the id as input and execute it. And a wrapper around it to call the script and pass the parameters.

When i try to get a list of ids' into a variable it does not work. Any suggestions on how we can get the list of values into a variable/array in unix which can be used later to use in a loop?

dual_value=`sqlplus -s ${DB_CONN_STR} <<-EOF
       	set serveroutput on
	set heading off
       	set feedback off
       	WHENEVER SQLERROR EXIT SQL.SQLCODE;
        
	select id from list_of_sql where status = 'P';
  
	EOF`