Do-It-Yourself Parallelism
from
今天还用到了这个分rowid的脚本，有才。
Say we have that same process as in the preceding section: the serial, simple procedure. We cannot
afford a rather extensive rewrite of the implementation, but we would like to execute it in
parallel. What can we do? My approach many times has been to use rowid ranges to break the table up
into some number of ranges that don't overlap (yet completely cover the table).
This is very similar to how Oracle performs a parallel query conceptually. If you think of a full
table scan, Oracle processes that by coming up with some method to break the table into many
"small" tables, each of which is processed by a parallel execution server. We are going to do the
same thing using rowid ranges. In early releases, Oracle's parallel implementation actually used
rowid ranges itself.
We'll use a BIG_TABLE of 1,000,000 rows, as this technique works best on big tables with lots of
extents, and the method I use for creating rowid ranges depends on extent boundaries. The more
extents used, the better the data distribution. So, after creating the BIG_TABLE with 1,000,000
rows, we'll create T2 like this:
big_table-ORA10G> create table t2
2 as
3 select object_id id, object_name text, 0 session_id
4 from big_table
5 where 1=0;
Table created.
We are going to use the job queues built into the database to parallel process our procedure. We
will schedule some number of jobs. Each job is our procedure slightly modified to just process the
rows in a given rowid range.
Note In Oracle 10g, you could use the scheduler as well for something so simple. In order to
make the example 9i compatible, we'll use the job queues here.
To efficiently support the job queues, we'll use a parameter table to pass inputs to our jobs:
big_table-ORA10G> create table job_parms
2 ( job number primary key,
3 lo_rid rowid,
4 hi_rid rowid
5 )
6 /
Table created.
This will allow us to just pass the job ID into our procedure, so it can query this table to get
the rowid range it is to process. Now, for our procedure. The code in bold is the new code we'll be
adding:
big_table-ORA10G> create or replace
2 procedure serial( p_job in number )
3 is
4 l_rec job_parms%rowtype;
5 begin
6 select * into l_rec
7 from job_parms
8 where job = p_job;
9
10 for x in ( select object_id id, object_name text
11 from big_table
12 where rowid between l_rec.lo_rid
13 and l_rec.hi_rid )
14 loop
15 -- complex process here
16 insert into t2 (id, text, session_id )
17 values ( x.id, x.text, p_job );
18 end loop;
19
20 delete from job_parms where job = p_job;
21 commit;
22 end;
23 /
Procedure created.
As you can see, it is not a significant change. Most of the added code was simply to get our inputs
and the rowid range to process. The only change to our logic was the addition of the predicate on
lines 12 and 13.
Now let's schedule our job. We'll use a rather complex query using analytics to divide the table.
The innermost query on lines 19 through 26 breaks the data into eight groups in this case. The
first sum on line 22 is computing a running total of the sum of blocks; the second sum on line 23
is the total number of blocks. If we integer divide the running total by the desired "chunk size"
(the total size divided by 8 in this case), we can create groups of files/blocks that cover about
the same amount of data. The query on lines 8 through 28 finds the high and low file numbers and
block numbers by GRP and returns the distinct entries. . It builds the inputs we can then send to
DBMS_ROWID to create the rowids Oracle wants. We take that output and, using DBMS_JOB, submit a job
to process the rowid range:
big_table-ORA10G> declare
2 l_job number;
3 begin
4 for x in (
5 select dbms_rowid.rowid_create
( 1, data_object_id, lo_fno, lo_block, 0 ) min_rid,
6 dbms_rowid.rowid_create
( 1, data_object_id, hi_fno, hi_block, 10000 ) max_rid
7 from (
8 select distinct grp,
9 first_value(relative_fno)
over (partition by grp order by relative_fno, block_id
10 rows between unbounded preceding and unbounded following) lo_fno,
11 first_value(block_id )
over (partition by grp order by relative_fno, block_id
12 rows between unbounded preceding and unbounded following) lo_block,
13 last_value(relative_fno)
over (partition by grp order by relative_fno, block_id
14 rows between unbounded preceding and unbounded following) hi_fno,
15 last_value(block_id+blocks-1)
over (partition by grp order by relative_fno, block_id
16 rows between unbounded preceding and unbounded following) hi_block,
17 sum(blocks) over (partition by grp) sum_blocks
18 from (
19 select relative_fno,
20 block_id,
21 blocks,
22 trunc( (sum(blocks) over (order by relative_fno, block_id)-0.01) /
23 (sum(blocks) over ()/8) ) grp
24 from dba_extents
25 where segment_name = upper('BIG_TABLE')
26 and owner = user order by block_id
27 )
28 ),
29 (select data_object_id
from user_objects where object_name = upper('BIG_TABLE') )
30 )
31 loop
32 dbms_job.submit( l_job, 'serial(JOB);' );
33 insert into job_parms(job, lo_rid, hi_rid)
34 values ( l_job, x.min_rid, x.max_rid );
35 end loop;
36 end;
37 /
PL/SQL procedure successfully completed.
That PL/SQL block would have scheduled up to eight jobs for us (fewer if the table could not be
broken in to eight pieces due to insufficient extents or size). We can see how many jobs were
scheduled and what their inputs are as follows:
big_table-ORA10G> select * from job_parms;
JOB LO_RID HI_RID
---------- ------------------ ------------------
172 AAAT7tAAEAAAAkpAAA AAAT7tAAEAAABQICcQ
173 AAAT7tAAEAAABQJAAA AAAT7tAAEAAABwICcQ
174 AAAT7tAAEAAABwJAAA AAAT7tAAEAAACUICcQ
175 AAAT7tAAEAAACUJAAA AAAT7tAAEAAAC0ICcQ
176 AAAT7tAAEAAAC0JAAA AAAT7tAAEAAADMICcQ
177 AAAT7tAAEAAADaJAAA AAAT7tAAEAAAD6ICcQ
178 AAAT7tAAEAAAD6JAAA AAAT7tAAEAAAEaICcQ
179 AAAT7tAAEAAAEaJAAA AAAT7tAAEAAAF4ICcQ
8 rows selected.
big_table-ORA10G> commit;
Commit complete.
That commit released our jobs for processing. We have JOB_QUEUE_PROCESSES set to 0 in the parameter
file, so all eight started running and shortly finished. The results are as follows:
big_table-ORA10G> select session_id, count(*)
2 from t2
3 group by session_id;
SESSION_ID COUNT(*)
---------- ----------
172 130055
173 130978
174 130925
175 129863
176 106154
177 140772
178 140778
179 90475
8 rows selected.
It's not as evenly distributed as the Oracle built-in parallelism in this case, but it's pretty
good. If you recall, earlier we saw how many rows were processed by each parallel execution server
and, using the built-in parallelism, the row counts were very close to each other (they were off
only by one or two). Here we had a job that processed as few as 90,475 rows and one that processed
as many as 140,778. Most of them processed about 130,000 rows in this case.
Suppose, however, that you do not want to use the rowid processing-perhaps the query is not as
simple as SELECT * FROM T and involves joins and other constructs that make using the rowid
impractical. You can use the primary key of some table instead. For example, say you want to break
that same BIG_TABLE into ten pieces to be processed concurrently by primary key. You can do that
easily using the NTILE built-in analytic function. The process is rather straightforward:
big_table-ORA10G> select nt, min(id), max(id), count(*)
2 from (
3 select id, ntile(10) over (order by id) nt
4 from big_table
5 )
6 group by nt;
NT MIN(ID) MAX(ID) COUNT(*)
---------- ---------- ---------- ----------
1 1 100000 100000
2 100001 200000 100000
3 200001 300000 100000
4 300001 400000 100000
5 400001 500000 100000
6 500001 600000 100000
7 600001 700000 100000
8 700001 800000 100000
9 800001 900000 100000
10 900001 1000000 100000
10 rows selected.
Now you have ten nonoverlapping primary key ranges, all of nice equal size, that you can use to
implement the same DBMS_JOB technique as shown earlier to parallelize your process.