Q. 순차적으로 실행하도록 설계된 배치 어플리케이션의 병렬 처리 방법
| {code:none | borderStyle=solid} Create procedure process_data As Begin For x in ( select * from some_table ) Perform complex process on X Update some other table, or insert the record somewhere else End loop end {code} |
| 테스트 | |
|---|---|
| {code:sql | borderStyle=solid} |
-- 테이블 생성
BIG_TABLE@THREE >create table t1
2 as
3 select object_id id, object_name text
4 from all_objects;
Table created.
BIG_TABLE@THREE >begin
dbms_stats.set_table_stats
( user, 'T1', numrows=>10000000,numblks=>100000 );
end;
/ 2 3 4 5
PL/SQL procedure successfully completed.
BIG_TABLE@THREE >create table t2
2 as
3 select t1.*, 0 session_id
4 from t1
5 where 1=0;
Table created.
-- 파이프라인 함수 변환값을 위한 오브젝트 타입
BIG_TABLE@THREE >CREATE OR REPLACE TYPE t2_type
2 AS OBJECT (
3 id number,
4 text varchar2(30),
5 session_id number
6 )
7 /
Type created.
BIG_TABLE@THREE >create or replace type t2_tab_type
2 as table of t2_type
3 /
Type created.
-- 파이프라인 함수 생성 : 실패
BIG_TABLE@THREE >create or replace
function parallel_pipelined( l_cursor in sys_refcursor )
2 3 return t2_tab_type
4 pipelined
5 parallel_enable ( partition l_cursor by any )
is
l_session_id number;
6 7 8 l_rec t1%rowtype;
9 begin
10 select sid into l_session_id
11 from v$mystat
12 where rownum =1;
13 loop
14 fetch l_cursor into l_rec;
15 exit when l_cursor%notfound;
16 -- complex process here
17 pipe row(t2_type(l_rec.id,l_rec.text,l_session_id));
18 end loop;
19 close l_cursor;
20 return;
21 end;
22 /
Warning: Function created with compilation errors.
|
h1. Do-It-Yourself 병렬
* 11gR2 이상의 내장패키지인 DBMS_PARALLEL EXECUTE를 사용해서 데이터를 여러 작은 단위로 분할해 처리함으로써 SQL 또는 PL/SQL문을 병렬로 실행할 수 있다.
|| 테스트||
| {code:sql|borderStyle=solid}
BIG_TABLE@THREE >select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
-- rowid 조건 추가하여 프로시저 생성
BIG_TABLE@THREE >create or replace
2 procedure serial( p_lo_rid in rowid, p_hi_rid in rowid )
3 is
4 begin
5 for x in ( select object_id id, object_name text
6 from big_table
7 where rowid between p_lo_rid
8 and p_hi_rid )
9 loop
10 -- complex process here
11 insert into t2 (id, text, session_id )
12 values ( x.id, x.text, sys_context( 'userenv', 'sessionid' ) );
end loop;
end;
/
Procedure created.
-- create_chunks_by_rowid 프로시저를 사용하여 rowid범위로 테이블을 나누어 수행한다.
-- 10000블록이하의 청크 단우로 나누고, rowid범위를 생성하기 위해 블록의 개수를 카운트한다.
BIG_TABLE@THREE >begin
2 dbms_parallel_execute.create_task('PROCESS BIG TABLE');
3 dbms_parallel_execute.create_chunks_by_rowid
4 ( task_name => 'PROCESS BIG TABLE',
5 table_owner => user,
6 table_name => 'BIG_TABLE',
7 by_row => false,
8 chunk_size => 10000 );
9 end;
10 /
PL/SQL procedure successfully completed.
-- 청크 정보와 수를 확인 ( 총 213개 )
BIG_TABLE@THREE >select *
from (
select chunk_id, status, start_rowid, end_rowid
from dba_parallel_execute_chunks
where task_name = 'PROCESS BIG TABLE'
order by chunk_id
)
where rownum <= 5
/
CHUNK_ID STATUS START_ROWID END_ROWID
---------- -------------------- ------------------ ------------------
1 UNASSIGNED AAAJd3AAEAAAASAAAA AAAJd3AAEAAAASHCcP
2 UNASSIGNED AAAJd3AAEAAAASIAAA AAAJd3AAEAAAASPCcP
3 UNASSIGNED AAAJd3AAEAAAASQAAA AAAJd3AAEAAAASXCcP
4 UNASSIGNED AAAJd3AAEAAAASYAAA AAAJd3AAEAAAASfCcP
5 UNASSIGNED AAAJd3AAEAAAASgAAA AAAJd3AAEAAAASnCcP
-- 태스크 실행 : 4개의 JOB이 생성
BIG_TABLE@THREE >begin
dbms_parallel_execute.run_task
( task_name => 'PROCESS BIG TABLE' ,
sql_stmt => 'begin serial( :start_id, :end_id ); end;',
language_flag => DBMS_SQL.NATIVE,
parallel_level => 4 );
end;
/
-- 실행 결과 : STATUS 갱신 ( PROCESSED/PROCESSED_WITH_ERROR )
BIG_TABLE@THREE >select *
from (
select chunk_id, status, start_rowid, end_rowid
from dba_parallel_execute_chunks
where task_name = 'PROCESS BIG TABLE'
order by chunk_id
)
where rownum <= 5
/
CHUNK_ID STATUS START_ROWID END_ROWID
---------- -------------------- ------------------ ------------------
1 PROCESSED AAAJd3AAEAAAASAAAA AAAJd3AAEAAAASHCcP
2 PROCESSED AAAJd3AAEAAAASIAAA AAAJd3AAEAAAASPCcP
3 PROCESSED AAAJd3AAEAAAASQAAA AAAJd3AAEAAAASXCcP
4 PROCESSED AAAJd3AAEAAAASYAAA AAAJd3AAEAAAASfCcP
5 PROCESSED AAAJd3AAEAAAASgAAA AAAJd3AAEAAAASnCcP
-- 병렬 서버의 처리량 비교
BIG_TABLE@THREE >select session_id, count(*)
2 from t2
3 group by session_id
4 order by session_id;
SESSION_ID COUNT(*)
---------- ----------
1003051 4463786
1003052 4181209
1003053 4105086
1003054 4389247
|
| 테스트 | |
|---|---|
| {code:sql | borderStyle=solid} |
BIG_TABLE@THREE >drop table t2;
Table dropped.
BIG_TABLE@THREE >create table t2
as
select object_id id, object_name text, 0 session_id
from big_table
where 1=0; 2 3 4 5
Table created.
– JOB에 입력값을 전달할 파라미터 테이블, ROWID범위를 구하기 위해 조회
BIG_TABLE@THREE >create table job_parms
( job number primary key,
lo_rid rowid,
hi_rid rowid
)
/ 2 3 4 5 6
Table created.
--
BIG_TABLE@THREE >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.
-- 스케줄링, 분석함수를 사용하여 테이블 분할, DBMS_JOB을 사용해서 해당 ROWID 범위의 데이터를 처리하기 위한 JOB을 SUMMIT
BIG_TABLE@THREE >declare
2 l_job number;
3 begin
4 for x in (
5 select dbms_rowid.rowid_create
6 ( 1, data_object_id, lo_fno, lo_block, 0 ) min_rid,
7 dbms_rowid.rowid_create
8 ( 1, data_object_id, hi_fno, hi_block, 10000 ) max_rid
9 from (
10 select distinct grp,
11 first_value(relative_fno)
12 over (partition by grp order by relative_fno, block_id
13 rows between unbounded preceding and unbounded following) lo_fno,
14 first_value(block_id )
15 over (partition by grp order by relative_fno, block_id
16 rows between unbounded preceding and unbounded following) lo_block,
17 last_value(relative_fno)
18 over (partition by grp order by relative_fno, block_id
rows between unbounded preceding and unbounded following) hi_fno,
19 20 last_value(block_id+blocks-1)
21 over (partition by grp order by relative_fno, block_id
22 rows between unbounded preceding and unbounded following) hi_block,
23 sum(blocks) over (partition by grp) sum_blocks
24 from (
25 select relative_fno,
26 block_id,
27 blocks,
28 trunc( (sum(blocks) over (order by relative_fno, block_id)-0.01) /
29 (sum(blocks) over ()/8) ) grp
30 from dba_extents
31 where segment_name = upper('BIG_TABLE')
32 and owner = user order by block_id
33 )
34 ),
35 (select data_object_id
36 from user_objects where object_name = upper('BIG_TABLE') )
37 )
38 loop
39 dbms_job.submit( l_job, 'serial(JOB);' );
40 insert into job_parms(job, lo_rid, hi_rid)
41 values ( l_job, x.min_rid, x.max_rid );
42 end loop;
43 end;
44 /
PL/SQL procedure successfully completed.
– JOB스케줄 확인
BIG_TABLE@THREE >select * from job_parms;
JOB LO_RID HI_RID
|
-- 병렬 서버 작업량 조회
!1.JPG!
* 내장 분석 함수 NTILE를 사용해서 중복되지 않는 열개의 기본 키 범위를 생성하는 방법
| {code:sql|borderStyle=solid}
BIG_TABLE@THREE >select nt, min(id), max(id), count(*)
from (
select id, ntile(10) over (order by id) nt
from big_table
)
group by nt
order by nt;
NT MIN(ID) MAX(ID) COUNT(*)
---------- ---------- ---------- ----------
1 1 856967 1713933
2 856967 1713933 1713933
3 1713934 2570900 1713933
4 2570900 3427866 1713933
5 3427867 4284833 1713933
6 4284833 5141799 1713933
7 5141800 5998766 1713933
8 5998766 6855732 1713933
9 6855733 8286068 1713932
10 8286069 10000000 1713932
10 rows selected.
|