"DBMS PARALLEL EXECUTE 패키지"의 두 판 사이의 차이
DB CAFE
(새 문서: == DBMS PARALLEL EXECUTE 패키지 == === .create_chunks_by_rowid 프로시져 === <source lang=sql> declare l_sql varchar2(500); l_chunk_sql varchar2(500); l_task_nm varc...) |
|||
59번째 줄: | 59번째 줄: | ||
/ | / | ||
</source> | </source> | ||
+ | |||
+ | |||
+ | [[category:oracle]] |
2024년 7월 23일 (화) 18:19 기준 최신판
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
1 DBMS PARALLEL EXECUTE 패키지[편집]
1.1 .create_chunks_by_rowid 프로시져[편집]
declare
l_sql varchar2(500);
l_chunk_sql varchar2(500);
l_task_nm varchar2(50):='k_clearance_1';
l_try number:=0;
l_status number;
l_count number:=0;
begin
select count(1)
into l_count
from user_parallel_execute_tasks
where task_name=l_task_nm;
--create task if not exist
if l_count = 0 then
--create task
dbms_parallel_execute.create_task(l_task_nm);
end if;
--create chunk
dbms_parallel_execute.create_chunks_by_rowid(
task_name=>l_task_nm,
table_owner=>'RXTEAM',
table_name=>'RPM_STAGE_CLEARANCE_ID_FIX',
by_row=>TRUE,
chunk_size=>10000
);
l_sql := 'update XXXXXX krsc
set krsc.lgcy_extr_tmst = sysdate
where exists (select 1
from rxteam.rpm_stage_clearance_id_fix rscf
where rscf.rpm_stage_clearance_id=krsc.rpm_stage_clearance_id
and rscf.rowid between :start_id and :end_id)';
--run task
dbms_parallel_execute.run_task(task_name=>l_task_nm,
sql_stmt=>l_sql,
language_flag=>DBMS_SQL.NATIVE,
parallel_level=>40
);
l_try := 0;
l_status := DBMS_PARALLEL_EXECUTE.task_status(l_task_nm);
while (l_try < 2 and l_status != DBMS_PARALLEL_EXECUTE.FINISHED)
loop
l_try := l_try + 1;
DBMS_PARALLEL_EXECUTE.resume_task(l_task_nm);
l_status := DBMS_PARALLEL_EXECUTE.task_status(l_task_nm);
end loop;
--dbms_parallel_execute.drop_task(l_task_nm)
end;
/