Running Jobs in Parallel using DBMS_SCHEDULER (part 2)

My second example of using DBMS_SCHEDULER to run jobs in parallel involves another common DBA task, rebuilding indices.

Under certain circumstances, deteriorating performance on a database can be traced to fragmented indices. In addition, direct path loads and certain other operations can invalidate indices, thus requiring them to be rebuilt.

In this example, indices that are invalid are identified and jobs submitted to rebuild the index. Not only can the individual rebuild processes run in parallel, but multiple rebuild jobs can be run at the same time by using DBMS_SCHEDULER.

This example does not use a driver table, but rather looks at the Oracle data dictionary tables to identify candidates for rebuild. The changes required to use a driver table containing the indices to rebuild are fairly simple, and I leave that to the reader.

In this example, we create a program that calls a stored procedure, and then individual jobs that call the program.  This allows us to use the arguments of owner and index name to specify each index to be rebuilt.

Below is the driving script, that finds each index needing to be rebuilt, and creates the job to rebuild them.  Typically we will rebuild two indexes at the same time, each with degree of parallelism set to four.


CREATE OR REPLACE
procedure maint_enable_rebuild(num_simul in integer default 2,par_degree in integer default 4) authid current_user
is
owner varchar2(30);
index_name varchar2(30);
type index_record is record
( owner varchar2(30),
index_name varchar2(30));
type index_array_type is varray(10000) of index_record;
index_count integer :=0;
lcounter integer :=0;
sql_cmd varchar2(2000);
index_array index_array_type;
num_running integer :=0;
continue_next varchar2(5) :='FALSE';
exit_now varchar2(5) :='FALSE';
num_ran integer :=0;
num_not_ran integer :=0;
num_short_count integer := num_simul;
begin
begin
maint_drop_rebuild_jobs;
exception
when others then null;
end;
dbms_scheduler.create_program
(program_name=>'REBUILD_INDEX_PROGRAM',
program_type=>'STORED_PROCEDURE',
program_action=>'MAINT_RUN_REBUILD',
number_of_arguments=>3,
enabled=>false,
comments=>'Stored procedure to rebuild unusable indexes asynchronously'
);
dbms_scheduler.define_program_argument (
program_name=>'REBUILD_INDEX_PROGRAM',
argument_name=>'owner',
argument_position=>1,
argument_type=>'varchar2'
);
dbms_scheduler.define_program_argument (
program_name=>'REBUILD_INDEX_PROGRAM',
argument_name=>'index_name',
argument_position=>2,
argument_type=>'varchar2'
);
dbms_scheduler.define_program_argument (
program_name=>'REBUILD_INDEX_PROGRAM',
argument_name=>'par_degree',
argument_position=>3,
argument_type=>'integer',
default_value=>4
);
dbms_scheduler.enable('REBUILD_INDEX_PROGRAM');
dbms_output.enable(100000);
index_array:=index_array_type();
for icursor in
(select owner, index_name from all_indexes where status in ('INVALID','UNUSABLE') and index_type='NORMAL' and owner !='SYS')
--/* testing only */ (select owner, index_name from all_indexes where owner='SCOTT' and table_name like 'E%')
loop
if icursor.owner is not null
then
index_count:=index_count+1;
index_array.extend();
index_array(index_count).owner:=icursor.owner;
index_array(index_count).index_name:=icursor.index_name;
-- dbms_output.put_line(to_char(index_count)||' '||icursor.owner||' '||icursor.index_name);
end if;
end loop;
for lcounter in 1..index_count
-- for lcounter in 1..52
loop
while continue_next = 'FALSE'
loop
if num_short_count <= 0
then
sleep(1);
select count(1) into num_running from all_scheduler_jobs where job_Name like 'REBUILD_UNUSUABLE_INDEXES%' and state='RUNNING';
num_short_count:=num_simul-num_running;
-- dbms_output.put_line(to_char(num_running));
end if;
if num_running <= num_simul
then
if trim(index_array(lcounter).owner) is not null
then
-- dbms_output.put_line(index_array(lcounter).owner||'.'||index_array(lcounter).index_name);
dbms_scheduler.create_job(
job_name=>'REBUILD_UNUSUABLE_INDEXES'||to_char(lcounter),
program_name=>'REBUILD_INDEX_PROGRAM',
comments=>'Unusable index asynch job',
start_date=>sysdate+1000,
enabled=>false,
auto_drop=>false);
dbms_scheduler.set_job_argument_value(
job_name=>'REBUILD_UNUSUABLE_INDEXES'||to_char(lcounter),
argument_position=>1,
argument_value=>index_array(lcounter).owner
);
dbms_scheduler.set_job_argument_value(
job_name=>'REBUILD_UNUSUABLE_INDEXES'||to_char(lcounter),
argument_position=>2,
argument_value=>index_array(lcounter).index_name
);
dbms_scheduler.set_job_argument_value(
job_name=>'REBUILD_UNUSUABLE_INDEXES'||to_char(lcounter),
argument_position=>3,
argument_value=>par_degree
);
dbms_scheduler.enable('REBUILD_UNUSUABLE_INDEXES'||to_char(lcounter));
dbms_scheduler.run_job(job_name=>'REBUILD_UNUSUABLE_INDEXES'||to_char(lcounter), use_current_session=>false);
end if;
num_short_count:=num_short_count-1;
continue_next:='TRUE';
else
sleep(5);
continue_next:='FALSE';
end if;
end loop;
continue_next:='FALSE';
end loop;
while exit_now='FALSE'
loop
select count(1) into num_not_ran from all_scheduler_jobs where job_Name like 'REBUILD_UNUSUABLE_INDEXES%' and run_count=0;
if num_not_ran >0
then
sleep(5);
else
exit_now:='TRUE';
select count(1) into num_ran from all_scheduler_jobs where job_Name like 'REBUILD_UNUSUABLE_INDEXES%' and run_count>0;
dbms_output.put_line('Total Indexes rebuilt: '||to_char(num_ran));
end if;
end loop;
end;
/

Note that in this example, we cannot create a job to run immediately because we need to set the job arguments.  Because of this, the procedure DBMS_SCHEDULER.RUN_JOB is used to execute the job.  It may also be possible to use the DBMS_SCHEDULER.ENABLE procedure to execute them.

At the start of the driving procedure, the procedure below is called to remove any old index rebuild jobs that may exist:


CREATE OR REPLACE
procedure maint_drop_rebuild_jobs  authid current_user
is
begin
for drop_job_cursor in
(select job_name from all_scheduler_jobs where job_name like 'REBUILD_UNUSUABLE_INDEXES%')
loop
begin
dbms_scheduler.drop_job(job_name=>drop_job_cursor.job_name,force=>true);
exception
when others then null;
end;
end loop;
begin
dbms_scheduler.drop_program('REBUILD_INDEX_PROGRAM');
exception
when others then null;
end;
end;
/

Finally, as you review the initial script you will see the stored procedure that is called to rebuild the index, It is a very simple stored procedure:


CREATE OR REPLACE
procedure maint_run_rebuild(owner in varchar2, index_name in varchar2, par_degree in integer default 4) authid current_user is
sql_command varchar2(4000);
begin
sql_command:='alter index '||owner||'.'||index_name||' rebuild online parallel '||to_char(par_degree);
--  dbms_output.put_line(sql_command);
execute immediate sql_command;
end;
/

I definitely recommend keeping the number of indices rebuilt at any one time low until you have determined what kind of load your system can handle.  Needless to say they are highly IO intensive.  An index rebuild will generate a lot of redo. The procedures in this blog do not use the nologging option as we have a physical standby database configured. When a database has a physical standby, the force logging option is set so any nologging options are ignored.

If you have many smaller indices to rebuild, set parallel to 1 or 2, and rebuild more indices at the same time.  If you have fewer, larger, indices rebuild fewer indices simultaneously at a higher degree of parallelism.  If you have many large indices to rebuild, well, just figure out what works best.

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


%d bloggers like this: