이펙티브 오라클 (2009년)
PL/SQL 은 한 번에 한 행씩 또는 여러 행의 데이터를 절차적으로 처리할 수 있다.
대량 처리와 한 행 처리 비교 데모#1
| 대량 처리 | 한 행 처리 | |
| 상대적 수행 속도 | 빠르다(메모리를 더 쓴다) | 느리다 |
| 상대적 작성 난이도 | 어렵다 | 쉽다 |
- 상황에 맞춰서 사용할 경우에, BULK COLLECT 가 더 빠르다.
- 사용 가능 메모리의 한계가 있기 때문에 상황에 따른 BULK COLLECT 크기 제한이 있다.
ETL 작업에 대량 처리를 사용하라 (Extract, Transform, Load) 데모#2
- 루틴이 INSERT SELECT 로 수행될 수 있는지 한번더 검토
- 많은 경우의 절차적 과정이 SQL로 대체 가능
- 테스트 데이터는 USER_TAB_COLUMNS 추천
- 대량 처리는 보다 많은 코드를 필요로 하지만, 처리율의 이점은 상상 초월
- 모든 경우에 적합한 것은 아니다. (대량 작업에 적합)
대량 처리
9iR2 이후
create or replace procedure nrows_at_a_time ( p_array_size in number )
as
type array is table of all_objects%rowtype;
l_data array;
cursor c is select * from all_objects;
begin
open c;
loop
fetch c bulk collect into l_data limit p_array_size;
forall i in 1 .. l_data.count
insert into t2 values l_data(i);
exit when c%notfound;
end loop;
end;
/
9iR2 이전
create or replace procedure nrows_at_a_time ( p_array_size in number )
as
l_owner dbms_sql.varchar2_table;
l_object_name dbms_sql.varchar2_table;
l_subobject_name dbms_sql.varchar2_table;
l_object_id dbms_sql.number_table;
l_data_object_id dbms_sql.number_table;
l_object_type dbms_sql.varchar2_table;
l_created dbms_sql.date_table;
l_last_ddl_time dbms_sql.date_table;
l_timestamp dbms_sql.varchar2_table;
l_status dbms_sql.varchar2_table;
l_temporary dbms_sql.varchar2_table;
l_generated dbms_sql.varchar2_table;
l_secondary dbms_sql.varchar2_table;
cursor c is select * from all_objects;
begin
open c;
loop
fetch c bulk collect into l_owner,
l_object_name,
l_subobject_name,
l_object_id,
l_data_object_id,
l_object_type,
l_created,
l_last_ddl_time,
l_timestamp,
l_status,
l_temporary,
l_generated,
l_secondary
limit p_array_size;
forall i in 1 .. l_owner.count
insert into t2 ( owner,
object_name,
subobject_name,
object_id,
data_object_id,
object_type,
created,
last_ddl_time,
timestamp,
status,
temporary,
generated,
secondary )
values ( l_owner(i),
l_object_name(i),
l_subobject_name(i),
l_object_id(i),
l_data_object_id(i),
l_object_type(i),
l_created(i),
l_last_ddl_time(i),
l_timestamp(i),
l_status(i),
l_temporary(i),
l_generated(i),
l_secondary(i));
exit when c%notfound;
end loop;
end;
/
한 행 처리
9iR2 이후
create or replace procedure row_at_a_time
as
begin
for x in ( select * from all_objects )
loop
insert into t1 values x;
end loop;
end;
/
9iR2 이전
create or replace procedure row_at_a_time
as
begin
for x in ( select * from all_objects )
loop
insert into t1 ( owner, object_name, subobject_name, object_id, data_object_id, object_type,
created, last_ddl_time, timestamp, status, temporary, generated, secondary )
values ( x.owner, x.object_name, x.subobject_name, x.object_id, x.data_object_id, x.object_type,
x.created, x.last_ddl_time, x.timestamp, x.status, x.temporary, x.generated, x.secondary );
end loop;
end;
/
데모
데모#1 - 대량 처리와 한 행 처리 비교
SQL> create table emp(empno number, ename varchar2(30), hiredate date);
테이블이 생성되었습니다.
SQL> insert into emp (empno, ename, hiredate)
select rownum, object_name, created from all_objects where rownum < 11; 2
10 개의 행이 만들어졌습니다.
SQL> exec runstats_pkg.rs_start;
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> begin
for i in 1 .. 5000
loop
for x in ( select ename, empno, hiredate from emp )
loop
null;
end loop;
end loop;
end;
/ 2 3 4 5 6 7 8 9 10
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> exec runstats_pkg.rs_middle;
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> declare
2 l_ename dbms_sql.varchar2_table;
l_empno dbms_sql.number_table;
3 4 l_hiredate dbms_sql.date_table;
begin
5 6 for i in 1 .. 5000
7 loop
8 select ename, empno, hiredate
9 bulk collect into l_ename, l_empno, l_hiredate
10 from emp;
11 end loop;
12 end;
13 /
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> exec runstats_pkg.rs_stop(10000);
Run1 ran in 658 hsecs
Run2 ran in 641 hsecs
run 1 ran in 102.65% of the time
Name Run1 Run2 Diff
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
72,642 79,280 6,638 91.63%
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> truncate table emp;
테이블이 잘렸습니다.
SQL> insert into emp (empno, ename, hiredate)
select rownum, object_name, created from all_objects where rownum < 1001; 2
1000 개의 행이 만들어졌습니다.
SQL> exec runstats_pkg.rs_start;
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> begin
for i in 1 .. 5000
loop
for x in ( select ename, empno, hiredate from emp )
loop
null;
end loop;
end loop;
end;
/ 2 3 4 5 6 7 8 9 10
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> exec runstats_pkg.rs_middle;
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> declare
l_ename dbms_sql.varchar2_table;
2 3 l_empno dbms_sql.number_table;
4 l_hiredate dbms_sql.date_table;
begin
5 6 for i in 1 .. 5000
7 loop
8 select ename, empno, hiredate
9 bulk collect into l_ename, l_empno, l_hiredate
10 from emp;
11 end loop;
12 end;
13 /
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> exec runstats_pkg.rs_stop(10000);
Run1 ran in 1331 hsecs
Run2 ran in 1028 hsecs
run 1 ran in 129.47% of the time
Name Run1 Run2 Diff
STAT...session logical reads 80,035 35,043 -44,992
STAT...consistent gets from ca 80,017 35,021 -44,996
STAT...consistent gets 80,017 35,021 -44,996
STAT...no work - consistent re 70,005 25,005 -45,000
STAT...table scan blocks gotte 70,005 25,005 -45,000
STAT...recursive calls 55,009 5,008 -50,001
LATCH.cache buffers chains 162,691 71,889 -90,802
STAT...table scan rows gotten 14,261,000 5,001,000 -9,260,000
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
170,948 79,462 -91,486 215.13%
PL/SQL 처리가 정상적으로 완료되었습니다.
데모#2 - ETL 작업에 대량 처리를 사용하라
SQL> create table t1 as
select * from all_objects where 1 = 0; 2
테이블이 생성되었습니다.
SQL> create table t2 as
select * from all_objects where 1 = 0;
2
테이블이 생성되었습니다.
SQL> create or replace procedure row_at_a_time
as
begin
for x in ( select * from all_objects where owner = 'PUBLIC' )
loop
insert into t1 values x;
end loop;
end;
/ 2 3 4 5 6 7 8 9
프로시저가 생성되었습니다.
SQL> create or replace procedure nrows_at_a_time ( p_array_size in number )
as
type array is table of all_objects%rowtype;
2 3 4 l_data array;
cursor c is select * from all_objects where owner = 'PUBLIC';
5 6 begin
7 open c;
8 loop
9 fetch c bulk collect into l_data limit p_array_size;
10
11 forall i in 1 .. l_data.count
12 insert into t2 values l_data(i);
13 exit when c%notfound;
14 end loop;
15 end;
16 /
프로시저가 생성되었습니다.
SQL> exec runstats_pkg.rs_start;
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> exec row_at_a_time;
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> exec runstats_pkg.rs_middle;
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> exec nrows_at_a_time(100);
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> exec runstats_pkg.rs_stop(5000);
Run1 ran in 532 hsecs
Run2 ran in 532 hsecs
run 1 ran in 100% of the time
Name Run1 Run2 Diff
STAT...session logical reads 24,808 5,151 -19,657
STAT...redo entries 21,914 2,193 -19,721
STAT...db block gets 23,334 3,541 -19,793
STAT...db block gets from cach 23,334 3,541 -19,793
STAT...recursive calls 21,030 1,158 -19,872
STAT...execute count 20,134 261 -19,873
LATCH.shared pool simulator 20,647 563 -20,084
STAT...db block changes 42,423 3,122 -39,301
LATCH.cache buffers chains 114,206 15,974 -98,232
STAT...undo change vector size 1,378,824 102,008 -1,276,816
STAT...redo size 7,530,696 2,391,912 -5,138,784
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
145,398 25,225 -120,173 576.40%
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> truncate table t1;
테이블이 잘렸습니다.
SQL> truncate table t2;
테이블이 잘렸습니다.
SQL> exec runstats_pkg.rs_start;
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> exec row_at_a_time;
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> exec runstats_pkg.rs_middle;
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> exec nrows_at_a_time(10000);
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> exec runstats_pkg.rs_stop(5000);
Run1 ran in 545 hsecs
Run2 ran in 629 hsecs
run 1 ran in 86.65% of the time
Name Run1 Run2 Diff
STAT...table scan rows gotten 70,403 53,992 -16,411
STAT...redo entries 21,916 1,969 -19,947
STAT...db block gets 23,338 3,319 -20,019
STAT...db block gets from cach 23,338 3,319 -20,019
STAT...execute count 20,132 60 -20,072
STAT...session logical reads 24,812 4,737 -20,075
LATCH.shared pool simulator 20,382 136 -20,246
STAT...recursive calls 21,017 741 -20,276
STAT...db block changes 42,424 2,745 -39,679
LATCH.cache buffers chains 118,515 14,590 -103,925
STAT...undo change vector size 1,378,824 90,940 -1,287,884
STAT...redo size 7,530,556 2,353,052 -5,177,504
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
157,529 21,773 -135,756 723.51%
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> truncate table t1;
테이블이 잘렸습니다.
SQL> truncate table t2;
테이블이 잘렸습니다.
SQL> create index t1_idx1 on t1 (object_name);
인덱스가 생성되었습니다.
SQL> create index t1_idx2 on t1 (owner, object_type, object_name);
인덱스가 생성되었습니다.
SQL> create index t1_idx3 on t1 (object_id);
인덱스가 생성되었습니다.
SQL> create index t2_idx1 on t2 (object_name);
인덱스가 생성되었습니다.
SQL> create index t2_idx2 on t2 (owner, object_type, object_name);
인덱스가 생성되었습니다.
SQL> create index t2_idx3 on t2 (object_id);
인덱스가 생성되었습니다.
SQL> exec runstats_pkg.rs_start;
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> exec row_at_a_time;
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> exec runstats_pkg.rs_middle;
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> exec nrows_at_a_time(100);
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> exec runstats_pkg.rs_stop(5000);
Run1 ran in 514 hsecs
Run2 ran in 518 hsecs
run 1 ran in 99.23% of the time
Name Run1 Run2 Diff
STAT...recursive calls 22,750 2,878 -19,872
STAT...execute count 20,257 384 -19,873
LATCH.shared pool simulator 20,263 198 -20,065
STAT...redo entries 87,386 36,587 -50,799
STAT...session logical reads 157,195 74,961 -82,234
STAT...db block gets 153,265 70,777 -82,488
STAT...db block gets from cach 153,265 70,777 -82,488
STAT...db block changes 172,677 70,947 -101,730
LATCH.cache buffers chains 634,650 292,107 -342,543
STAT...undo change vector size 10,855,320 7,448,760 -3,406,560
STAT...redo size 30,079,376 19,136,516 -10,942,860
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
684,687 329,136 -355,551 208.03%
PL/SQL 처리가 정상적으로 완료되었습니다.
"구루비 데이터베이스 스터디모임" 에서 2009년에 "이펙티브 오라클" 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/3504
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.