Manual Parallelism – Rowid Based for Partitioned / Sub Partitioned Tables

Currently, I am working on an optimization project where few Scheduler Jobs are Scheduled and Run every night at 9:00 pm using dbms_scheduler. These jobs are then stopped every day morning 9:00 a:m, so that, these do not impact the OLTP sessions. Customer has implemented VPD and each of these job is scheduled from multiple users, so that, the records are processed based on the policy function and the access predicates for that user. The flaw with this logic is that the total rows to be processed across each of the users are not uniform, which means, if a job for all the users takes 45-50 minutes, one user processing takes almost 3-4 hours.

For optimizing these, the first thought came to the mind was to distribute the rows to be processed uniformly across each of the jobs and then to apply vpd predicates at runtime. This required splitting of the main processing cursor into multiple chunks. Therefore, the table involved in the main cursor query was taken out with an intention to use ROWID Manual Parallelism, introduced by Thomas Kyte. This would help us distribute the rows uniformly across each of the jobs with an efficient use of available resources. I demonstrated a case study on one of my previous blog, where I used this to purge data from a table. The link is as under :

I have come across many pl/sql procedures that processes data and take certain amount of time that can be brought down. Moreover, whenever these procedures are scheduled or run, the CPU Utilization is very less. I remember a discussion with a customer, wherein, they mentioned that one of my “Annual Interest Calculation” job is running very slow and the CPU is 90 to 95% IDLE. This is an expected behaviour as a job, when run as a single process, will be scheduled on only one Processor and will not take the advantage of available resources. Therefore, splitting the job into multiple chunks should help in reducing the process completion time by way of better resource utilization.

As an example, a pl/sql block, as mentioned below, can be optimized and run in parallel. If a single job process 10000 Rows and takes 30 minutes, running it into 10 parallel stream should bring it down to 3-5 minutes.

create procedure test_job as
cursor main is
select column1, column2, column3
from	test
where x=1
and   y=2;
begin
  for i in main
   loop
     some processing;
   end loop;
end;


create procedure test_job(job_id number, low_rid rowid, hi_rid rowid) as
cursor main is
select column1, column2, column3
from	test
where x=1
and   y=2
and   rowid between low_rid and hi_rid;
begin
  for i in main
   loop
     some processing;
   end loop;
end;

This change may require minor changes in the procedure which is worth implementing.

Back to the Original case study. As mentioned earlier, for splitting the rows into multiple chunks, I executed the Query on dba_extents and dba_objects, to get the Start and End ROWID based on the number of chunks. This Query usually takes 2-3 minutes, but was taking almost 45-50 minutes and had to be cancelled. I checked the plan of this Query and it was doing a Merge Join Cartesian. Further digging into the issue, I could notice that the table which was to be splitted was a partitioned and subpartitioned table. This was for the first time I faced this issue and therefore had to modify the logic of the Query to accomodate Partitioned and Sub-Partitioned Objects.

I created a pl/sql block to check whether the table is a Non-Partitioned or Partitioned or Sub-Partitioned and based on this, a loop is executed. Below is the code that accomplished this task for me. An explanation on this is as under :

  1. Input Values are Table_Owner, Process_name, Table_name.
  2. L_CHKS, in my case, is a Global Variable declared in Package specification and defaults to the number of chunks to be created.
  3. For a Partitioned or Sub-Partitioned Objects, the number of chunks will be more than L_CHKS, therefore, at the end of the loop, these are updated again using mod function.
  procedure manual_parallelism(t_owner varchar2, p_name varchar2, t_name varchar2) as
   /* l_chks is dynamic and should be based on available CPU's and is standard for all the jobs */
   /* This value can be changed at later stage if the hardware is upgraded */
   l_part	varchar2(3);
   l_subpart	varchar2(1);
   part_name	varchar2(30);
   l_dataobjectid number;
   cursor partitioned(t_owner varchar2, t_name varchar2) is
   select partition_name from dba_tab_partitions
   where table_owner = t_owner
   and   table_name = t_name
   order by partition_position desc;

   cursor sub_partitioned(t_owner varchar2, t_name varchar2, part_name varchar2) is
   select subpartition_name from dba_tab_subpartitions s
   where s.table_owner = t_owner
   and   s.table_name = t_name
   and   s.partition_name = part_name;
  begin
   /* Assumption in this case is that at any given point in time l_chks Jobs can be easily scheduled */
     select partitioned into l_part from dba_tables where owner=t_owner and table_name=t_name;
     if l_part='YES' then
       begin
	   select 'Y' into l_subpart from dba_tab_subpartitions 
	   where	table_owner=t_owner 
	   and		table_name=t_name
	   and		rownum=1;
       exception when no_data_found then
	   l_subpart:='N';
       end;
       if l_subpart='Y' then /* if Sub Partitioned */
	   for i in partitioned(t_owner, t_name)
	   loop
	     for j in sub_partitioned(t_owner, t_name, i.partition_name)
	     loop
	     insert into ast.process_status(process_name, job_id, s_rowid, e_rowid)
	     select p_name, grp,
  		   dbms_rowid.rowid_create( 1, data_object_id, lo_fno, lo_block, 0 ) min_rid,
		   dbms_rowid.rowid_create( 1, data_object_id, hi_fno, hi_block, 10000 ) max_rid
	     from (
		   select distinct grp,
		   first_value(relative_fno) over (partition by grp order by relative_fno, 
		   block_id rows between unbounded preceding and unbounded following) lo_fno,
		   first_value(block_id) over (partition by grp order by relative_fno, 
		   block_id rows between unbounded preceding and unbounded following) lo_block,
		   last_value(relative_fno) over (partition by grp order by relative_fno, 
		   block_id rows between unbounded preceding and unbounded following) hi_fno,
		   last_value(block_id+blocks-1) over (partition by grp order by relative_fno, 
		   block_id rows between unbounded preceding and unbounded following) hi_block,
		   sum(blocks) over (partition by grp) sum_blocks
		   from (
		   select	relative_fno,
				block_id,
				blocks,
				trunc( (sum(blocks) over (order by relative_fno, block_id)-0.01) /
				(sum(blocks) over ()/l_chks) ) grp
		   from		dba_extents
		   where	segment_name = t_name
		   and		owner = t_owner
		   and		partition_name=j.subpartition_name
		   order by block_id)
		   ),
	     (select data_object_id from dba_objects 
	     where	object_name = t_name 
	     and	subobject_name=j.subpartition_name 
	     and	owner=t_owner);
	     end loop; /* End loop of Sub Partitioned cursor */
	   end loop; /* End loop of Partitioned Cursor */
	   commit;
       else /* it means only Partitioned no Sub Partitions */
	   for i in partitioned(t_owner, t_name)
	   loop
	     insert into ast.process_status(process_name, job_id, s_rowid, e_rowid)
	     select p_name, grp,
		   dbms_rowid.rowid_create( 1, data_object_id, lo_fno, lo_block, 0 ) min_rid,
		   dbms_rowid.rowid_create( 1, data_object_id, hi_fno, hi_block, 10000 ) max_rid
	     from (
		   select distinct grp,
		   first_value(relative_fno) over (partition by grp order by relative_fno, 
		   block_id rows between unbounded preceding and unbounded following) lo_fno,
		   first_value(block_id) over (partition by grp order by relative_fno, 
		   block_id rows between unbounded preceding and unbounded following) lo_block,
		   last_value(relative_fno) over (partition by grp order by relative_fno, 
		   block_id rows between unbounded preceding and unbounded following) hi_fno,
		   last_value(block_id+blocks-1) over (partition by grp order by relative_fno, 
		   block_id rows between unbounded preceding and unbounded following) hi_block,
		   sum(blocks) over (partition by grp) sum_blocks
		   from (
		   select	relative_fno,
				block_id,
				blocks,
				trunc( (sum(blocks) over (order by relative_fno, block_id)-0.01) /
				(sum(blocks) over ()/l_chks) ) grp
		   from		dba_extents
		   where	segment_name = t_name
		   and		owner = t_owner
		   and		partition_name=i.partition_name
		   order by block_id)
		   ),
	     (select data_object_id from dba_objects 
	     where	object_name = t_name 
	     and	subobject_name=i.partition_name 
	     and	owner=t_owner);
	   end loop; /* End loop of Partitioned Cursor */
	   commit;
       end if; /* End if of l_Subpart='Y' */
     else /* Insert Statement for Non-Partitioned Object */ 
  	   insert into process_status(process_name, job_id, s_rowid, e_rowid)
	   select p_name, grp, 
		   dbms_rowid.rowid_create(1,data_object_id, lo_fno, lo_block,0) min_rid,
		   dbms_rowid.rowid_create(1,data_object_id, hi_fno, hi_block,0) max_rid
	   from (
	   select 	distinct grp,
			first_value(relative_fno) over (partition by grp order by relative_fno, 
			block_id rows between unbounded preceding and unbounded following) lo_fno,
			first_value(block_id) over (partition by grp order by relative_fno, 
			block_id rows between unbounded preceding and unbounded following) lo_block,
			last_value(relative_fno) over (partition by grp order by relative_fno, 
			block_id rows between unbounded preceding and unbounded following) hi_fno,
			last_value(block_id) over (partition by grp order by relative_fno, block_id 
			rows between unbounded preceding and unbounded following) hi_block,
			sum(blocks) over (partition by grp) sum_blocks from (
	   select 	relative_fno, block_id, blocks, 
			sum(blocks) over (order by relative_fno, block_id) cum_blocks,
			sum(blocks) over () tot_blocks,
			trunc((sum(blocks) over (order by relative_fno, block_id)-0.01) /
			(sum(blocks) over ()/l_chks)) grp
	   from		dba_extents
	   where 	segment_name=t_name
	   and		owner=t_owner
	   order by relative_fno, block_id)),
	   (select data_object_id from dba_objects where owner=t_owner and object_name=t_name);
	   commit;
     end if; /* END IF of L_PART='Y' */
     update ast.process_status set job_id=mod(rownum,l_chks) where process_name=p_name;
     commit;
  end manual_parallelism;

You can define the value of L_CHKS in the procedure and the procedure will split the table into multiple chunks with minimum group as 0 and maximum group as LCHKS-1. Once these chunks are created, additional intelligence can be built to schedule these L_CHKS jobs and spawn the new jobs based on the completion status of the running job. The Inputs to the Scheduled procedure will be the the Job_ID, which is GRP in our case, Starting Rowid, Ending Rowid, Process_Name. In my case, I scheduled 80 jobs at a time and wrote a logic at the end of the scheduled procedure wherein the process immediately starts the next job with the same job_id, which means, if a process with job_id 1 completes, it will spawn another job with job_id as 1 and so on. This will ensure that at any given point of time, there are 80 jobs running and no manual intervention is required.

Once this pl/sql parallelism was implemented, the total completion time of the process was around 30-35 minutes. Further, ROWID parallelism also ensured that there was no Block Level Contention between each of the jobs thus improving the performance.

Oracle 11g provides a package to accomplish this very easily using dbms_parallel_execute and developers may leverage the benefit of this readymade package to split a long running pl/sql block in parallel, thus improving the performance.

Advertisements

About Vivek Sharma
I am an Oracle Professional from Mumbai (India). I blog on the issues that I feel is Interesting for my readers. Some of these are my real life examples, which I hope, you would find interesting. Comments are always a welcome. The Technical Observations & Views here are my own and not necessarily those of Oracle or its affiliates. These are purely based on my understandings, learnings and resolutions of various customer issues.

2 Responses to Manual Parallelism – Rowid Based for Partitioned / Sub Partitioned Tables

  1. Gary Pennington says:

    The calculation for the non partitioned objects hi_block incorrect.

    It should, like the others, be:

    last_value(block_id+blocks-1)

    If you have been using this on un-partitioned tables, the rowid ranges have been missing some rows.

    I.e. you have been processing upto the the first block in the last extent, not the last block in the last extent.

    GP>

    Like

    • Vivek says:

      Hi GP,

      The number of Chunks has to be equal to or less than the number of extents in the table. I had encountered this problem if I specify more chunks then the number of extents.

      Regards
      Vivek

      Like

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