이펙티브 오라클 (2009년)
대량 처리 0 0 44,792

by 구루비스터디 대량처리 BULK COLLECT ETL PLSQL [2018.05.26]


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

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입