Vishal desai’s Oracle Blog

June 1, 2016

Load Balancing maintenance tasks on RAC nodes using dbms_scheduler

Filed under: Tools — vishaldesai @ 2:04 am

In shared environment, with instance caging turned on, I had to perform maintenance during downtime window to reorganize few tables that are populated by AQ. I wanted to load balance sub tasks, use all available resources and complete task quickly. Here is little snippet of code I wrote to load balance tasks across multiple nodes in RAC cluster.

Create job table and populate job table with sub tasks as shown below. I didn’t had huge variation in table sizes so order was  not that important. But if there is huge outlier table in list, put them first on the list otherwise you may end up with one job at the end that starts towards the end and run for long time.

drop table job_table purge;
create table job_table( task_no number primary key, task varchar2(4000),status char(1)) tablespace users;
--status null - work to do, I - in progress, S - Success, F - Failure

insert into job_table values(1   , 'begin execute immediate ''alter table  schema_name.table_name1  move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=1   ; commit; end;','');
insert into job_table values(2   , 'begin execute immediate ''alter table  schema_name.table_name2  move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=2   ; commit; end;','');
insert into job_table values(3   , 'begin execute immediate ''alter table  schema_name.table_name3  move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=3   ; commit; end;','');
insert into job_table values(4   , 'begin execute immediate ''alter table  schema_name.table_name4  move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=4   ; commit; end;','');
insert into job_table values(5   , 'begin execute immediate ''alter table  schema_name.table_name5  move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=5   ; commit; end;','');
insert into job_table values(6   , 'begin execute immediate ''alter table  schema_name.table_name6  move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=6   ; commit; end;','');
insert into job_table values(7   , 'begin execute immediate ''alter table  schema_name.table_name7  move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=7   ; commit; end;','');
insert into job_table values(8   , 'begin execute immediate ''alter table  schema_name.table_name8  move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=8   ; commit; end;','');
insert into job_table values(9   , 'begin execute immediate ''alter table  schema_name.table_name9  move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=9   ; commit; end;','');
insert into job_table values(10  , 'begin execute immediate ''alter table  schema_name.table_name10 move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=10  ; commit; end;','');
insert into job_table values(11  , 'begin execute immediate ''alter table  schema_name.table_name11 move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=11  ; commit; end;','');
insert into job_table values(12  , 'begin execute immediate ''alter table  schema_name.table_name12 move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=12  ; commit; end;','');
insert into job_table values(13  , 'begin execute immediate ''alter table  schema_name.table_name13 move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=13  ; commit; end;','');
insert into job_table values(14  , 'begin execute immediate ''alter table  schema_name.table_name14 move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=14  ; commit; end;','');
insert into job_table values(15  , 'begin execute immediate ''alter table  schema_name.table_name15 move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=15  ; commit; end;','');
insert into job_table values(16  , 'begin execute immediate ''alter table  schema_name.table_name16 move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=16  ; commit; end;','');
insert into job_table values(17  , 'begin execute immediate ''alter table  schema_name.table_name17 move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=17  ; commit; end;','');
insert into job_table values(18  , 'begin execute immediate ''alter table  schema_name.table_name18 move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=18  ; commit; end;','');
insert into job_table values(19  , 'begin execute immediate ''alter table  schema_name.table_name19 move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=19  ; commit; end;','');
insert into job_table values(20  , 'begin execute immediate ''alter table  schema_name.table_name20 move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=20  ; commit; end;','');
insert into job_table values(21  , 'begin execute immediate ''alter table  schema_name.table_name21 move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=21  ; commit; end;','');
insert into job_table values(22  , 'begin execute immediate ''alter table  schema_name.table_name22 move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=22  ; commit; end;','');
insert into job_table values(23  , 'begin execute immediate ''alter table  schema_name.table_name23 move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=23  ; commit; end;','');
insert into job_table values(24  , 'begin execute immediate ''alter table  schema_name.table_name24 move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=24  ; commit; end;','');

commit;

 

Change number of instances in your environment, number of sub tasks that you want to run simultaneously on each node and sleep interval and execute pl/sql block.

DECLARE
   --how many instances you want to use
   v_instances            NUMBER := 3;
   --jobs on each instance. Each job is a task from job_Table
   v_jobs                 NUMBER := 2;

   v_available_instance   NUMBER := NULL;
   v_available_jobs       NUMBER := NULL;
   v_job_name             varchar2(100);
   c                      number;

   CURSOR c1
   IS
      SELECT *
        FROM job_table
      ;
BEGIN
   FOR v1 IN c1
   LOOP
      --start find available instance

         DECLARE
            CURSOR c2
            IS
               SELECT running_instance, jobs running_jobs
                 FROM (SELECT a.running_instance, a.jobs - b.jobs jobs
                         FROM (    SELECT ROWNUM running_instance, v_jobs jobs
                                     FROM DUAL
                               CONNECT BY LEVEL <= v_instances) a,
                              (  SELECT running_instance,
                                        COUNT (running_instance) jobs
                                   FROM DBA_SCHEDULER_RUNNING_JOBS
                                  WHERE job_name LIKE '%MYJOB%'
                               GROUP BY running_instance) b
                        WHERE a.running_instance = b.running_instance(+))
                WHERE jobs IS NULL OR jobs > 0;

            v_running_instance   number;
            v_running_jobs       number;
         BEGIN
            OPEN c2;
            LOOP
               FETCH c2 INTO v_running_instance, v_running_jobs;

               
                  v_available_instance := v_running_instance;
                  v_available_jobs := v_running_jobs;
                  
                        v_job_name := DBMS_SCHEDULER.generate_job_name ('MYJOB_');
                          DBMS_SCHEDULER.create_job (
                             job_name              => v_job_name,
                             job_type              => 'PLSQL_BLOCK',
                             job_action            => v1.task,
                             comments              =>    'Instance '
                                                      || v_available_instance
                                                      || ' task no '
                                                      || v1.task_no,
                             number_of_arguments   => 0,
                             start_date            => SYSTIMESTAMP,
                             repeat_interval       => NULL,
                             end_date              => NULL,
                             enabled               => FALSE,
                             auto_drop             => TRUE );
                             
                          DBMS_SCHEDULER.set_attribute (
                            name      => v_job_name,
                            attribute => 'instance_id',
                            value     => v_available_instance);   

                         DBMS_SCHEDULER.enable (name => v_job_name);
                      
                          
                          UPDATE job_table
                             SET status = 'I'
                           WHERE task_no = v1.task_no;

                          COMMIT;
                          dbms_lock.sleep(2);
                
                --EXIT WHEN c2%NOTFOUND;
                EXIT;
            END LOOP;

            CLOSE C2;
         END;

        WHILE 1 > 0 loop
            dbms_lock.sleep(5);
            SELECT count(1) into c
                 FROM (SELECT a.running_instance, a.jobs - b.jobs jobs
                         FROM (    SELECT ROWNUM running_instance, v_jobs jobs
                                     FROM DUAL
                               CONNECT BY LEVEL <= v_instances) a,
                              (  SELECT running_instance,
                                        COUNT (running_instance) jobs
                                   FROM DBA_SCHEDULER_RUNNING_JOBS
                                  WHERE job_name LIKE '%MYJOB%'
                               GROUP BY running_instance) b
                        WHERE a.running_instance = b.running_instance(+))
                WHERE jobs IS NULL OR jobs > 0;
                if c > 0 then
                exit;
                end if;
        end loop;
            
        
   END LOOP;
END;
/

 

Now sit back relax and use your favorite monitoring tool and output from following SQL statements until task is complete.

select * from DBA_SCHEDULER_JOB_RUN_DETAILS where job_name like '%MYJOB%' order by actual_start_date desc;

select * from DBA_SCHEDULER_RUNNING_JOBS where job_name like '%MYJOB%';

select * from dba_scheduler_jobs where job_name like '%MYJOB%';
select * from job_table order by status;
select status,count(1) from job_table group by status;

exec dbms_scheduler.stop_job('MYJOB_11014');
exec dbms_scheduler.drop_job('MYJOB_10997');

 SELECT running_instance, jobs running_jobs
                 FROM (SELECT a.running_instance, a.jobs - b.jobs jobs
                         FROM (    SELECT ROWNUM running_instance, 1 jobs
                                     FROM DUAL
                               CONNECT BY LEVEL <= 3) a,
                              (  SELECT running_instance,
                                        COUNT (running_instance) jobs
                                   FROM DBA_SCHEDULER_RUNNING_JOBS
                                  WHERE job_name LIKE '%MYJOB%'
                               GROUP BY running_instance) b
                        WHERE a.running_instance = b.running_instance(+))
                WHERE jobs IS NULL OR jobs > 0;

I used similar approach for other long running tasks such as index rebuild, gathering statistics on tables and indexes. Other post tasks such as convert table , indexes degree back to 1 etc that does not take long time, were ran from regular sqlplus script.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: