실제로도 그렇다.
하지만 왜 그런가?

간단한 테스트로 많은 것을 알 수 있다.

우선 다음과 같이 필요한 Object를 생성한다.

@ukja102

drop table t1 purge;
create table t1(c1 int, c2 char(10));
insert into t1
select level, 'dummy'
from dual
connect by level <= 200000;
commit;
select count(*) from t1;

Implicit Cursor를 사용하는 경우와 Exlicit Cursor를 사용하는 경우의 성능을 비교해 보자.

-- Implicit Cursor를 사용하는 경우
@ukja102
@mysid
@mon_on &v_sid

begin
  for r in (select * from t1) loop
    null;
  end loop;
end;
/

@mon_off
spool result1.txt
@mon_show
spool off


@ukja102
@mysid
@mon_on &v_sid

-- Explicit Cursor를 사용하는 경우
declare
  cursor v_cursor is
    select * from t1
  ;
  
  v_rec v_cursor%rowtype;
begin
  open v_cursor;
  loop
    fetch v_cursor into v_rec;
    exit when v_cursor%notfound;
  end loop;
  close v_cursor;
end;
/

@mon_off
spool result2.txt
@mon_show
spool off

성능을 어떻게 비교할 것인가?

Oracle은 성능을 비교하기 위한 많은 뷰들을 제공한다. 이들을 잘 활용해야 한다.

우선 v$sess_time_model 뷰를 통해 Time 정보를 비교한다. 이 뷰를 이용하면 별도의 코드를 통해 시간을 측정하지 않아도 된다.

-- Implicit Cursor를 사용한 경우
STAT_NAME                                      VALUE1       VALUE2         DIFF
---------------------------------------- ------------ ------------ ------------
DB time                                        59,773    1,777,125    1,717,352
sql execute elapsed time                       40,140    1,721,534    1,681,394
DB CPU                                         51,929    1,683,972    1,632,043
parse time elapsed                             42,324      256,573      214,249

-- Explicit Cursor를 사용한 경우
STAT_NAME                                      VALUE1       VALUE2         DIFF
---------------------------------------- ------------ ------------ ------------
DB time                                        29,622    6,051,808    6,022,186
sql execute elapsed time                       25,827    6,044,618    6,018,791
DB CPU                                         29,331    6,034,029    6,004,698
PL/SQL execution elapsed time                      60      558,753      558,693
parse time elapsed                              1,509      131,440      129,931

그 이유가 무엇인지 가장 쉽게 알 수 있는 방법은?
Statistics을 봐야 한다. v$sesstat 뷰를 통해 본 차이는 다음과 같다.

-- Implicit Cursor인 경우                                                                               
NAME                                           VALUE1       VALUE2         DIFF
---------------------------------------- ------------ ------------ ------------
table scan rows gotten                             62      914,002      913,940
session pga memory max                      1,826,388    2,154,068      327,680
session uga memory max                      1,282,300    1,544,264      261,964
session pga memory                          1,826,388    1,957,460      131,072
session logical reads                             275        3,249        2,974

-- Explicit Cursor인 경우
NAME                                           VALUE1       VALUE2         DIFF
---------------------------------------- ------------ ------------ ------------
table scan rows gotten                             62   69,366,045   69,365,983
session pga memory max                      1,498,708    1,891,924      393,216
session pga memory                          1,498,708    1,891,924      393,216
session uga memory max                      1,151,372    1,413,336      261,964
session logical reads                              72      200,261      200,189

이 차이는 어디서 온 것인가?
Fetch Array Size에서 온 것이다. 한번에 많은 로우를 Fetch하면 Block을 방문해야할 횟수가 줄어들며 그만큼 Logical Reads가 줄어든다. Implicit Cursor를 사용하는 경우에 Oracle은 내부적으로 10개를 한번에 Fetch한다. 반면에 Explicit Cursor를 사용하는 경우에는 한번에 한 개의 Row만 Fetch한다. 그 결과로 Logical Reads가 대략 10배의 차이가 나게 된다. 그 만큼 성능이 느린 것이다.

Explicit Cursor를 Implicit Cursor보다 빠르게 하는 유일한 방법은 Bulk Collection을 사용하는 것이다. 아래와 같이...

@ukja102
@mysid
@mon_on &v_sid

declare
  cursor v_cursor is
    select * from t1
  ;
  
  type c1tab is table of t1.c1%type;
  type c2tab is table of t2.c2%type;
  
  c1t c1tab;
  c2t c2tab;
  
begin
  open v_cursor;
  fetch v_cursor bulk collect into c1t, c2t; -- Do it bulk!!!
  close v_cursor;
end;
/

@mon_off
spool result3.txt
@mon_show
spool off

-- Implicit Cursor를 사용한 경우
STAT_NAME                                      VALUE1       VALUE2         DIFF
---------------------------------------- ------------ ------------ ------------
DB time                                        59,773    1,777,125    1,717,352
sql execute elapsed time                       40,140    1,721,534    1,681,394
DB CPU                                         51,929    1,683,972    1,632,043
parse time elapsed                             42,324      256,573      214,249

-- Explicit Cursor + Bulk Collection을 사용한 경우
STAT_NAME                                      VALUE1       VALUE2         DIFF
---------------------------------------- ------------ ------------ ------------
DB time                                        28,024    1,503,542    1,475,518
DB CPU                                         18,620    1,489,167    1,470,547
sql execute elapsed time                       24,547    1,493,775    1,469,228
PL/SQL execution elapsed time                      59        5,512        5,453
parse time elapsed                              1,302        4,793        3,491

-- Implicit Cursor인 경우                                                                               
NAME                                           VALUE1       VALUE2         DIFF
---------------------------------------- ------------ ------------ ------------
table scan rows gotten                             62      914,002      913,940
session pga memory max                      1,826,388    2,154,068      327,680
session uga memory max                      1,282,300    1,544,264      261,964
session pga memory                          1,826,388    1,957,460      131,072
session logical reads                             275        3,249        2,974

-- Explicit Cursor + Bulk Collection인 경우
NAME                                           VALUE1       VALUE2         DIFF
---------------------------------------- ------------ ------------ ------------
session pga memory max                      1,498,708   21,618,260   20,119,552
session uga memory max                      1,151,372    1,478,800      327,428
table scan rows gotten                             62      200,062      200,000

위의 테스트 결과는 많은 것을 말해 준다.

• 왜 성능 차이가 나는지는 알아야 하며, 또 알 수 있다.
• 성능의 개선에는 대가가 따르며, 그 대가가 무엇인지도 알 수 있다.
• 성능을 측정하는 다양한 뷰를 잊지 말라. 단순히 시간이나 일량만 보지 말라.
• 자동화하라. Toad나 Orange같은 툴을 사용하지 말고 SQL*Plus를 이용해 [Enter]한번으로 결과가 나오게끔 하라

테스트에 사용한 스크립트는 아래에서 볼 수 있다.

• moninit.sql
• mon_on.sql
• mon_off.sql
• mon_show.sql