태터데스크 관리자

도움말
닫기
적용하기   첫페이지 만들기

태터데스크 메시지

저장하였습니다.

Implici vs. Explicit Cursor - Who Wins?

오라클 2008. 8. 9. 13:56
Implicit Cursor가 Explicit Cursor에 비해 성능이 뛰어나다는 언급을 여러 번 봤을 것이다.

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

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

우선 다음과 같이 필요한 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
Implicit Cursor가 모든 면에서 Explicit Cursor에 비해 현격한 성능 우위를 보이는 것을 알 수 있다.

그 이유가 무엇인지 가장 쉽게 알 수 있는 방법은?
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
차이가 무엇인가?
놀랍게도 일량(Reads)의 차이가 절대적이라는것을 알 수 있다. logical reads가 10배 정도 차이나며 그 차이로 인해 성능의 차이가 왔다.

이 차이는 어디서 온 것인가?
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
Bulk Collection과 함께 Explicit Cursor를 사용한 경우 오히려 성능이 더 뛰어나다. 그 이유는?
-- 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
Bulk Collection을 사용한 경우 한번에 필요한 Row를 Fetch하기 때문에 일량은 현격하게 주는 반면에 많은 양의 메모리(20M)를 사용한다. 그만큼 성능은 개선되었지만 그 대가는 메모리가 되는 셈이다.

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

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

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

Trackbacks 0 : Comments 6
  1. 왕마두 2008.08.10 20:19 Modify/Delete Reply

    매회가 지날수록 배울점이 너무 많은 강의입니다.

  2. 푸름조아 2008.08.20 15:37 Modify/Delete Reply

    좋은글입니다..
    bulk collect에 limit 10 구문을 사용하여 implicit cursor의 기본 fetch사이즈와 같게 한다면 pga를 적게 사용하고도 비슷한 성능을 낼수 있을까요?

  3. 욱짜 2008.08.20 17:21 신고 Modify/Delete Reply

    Internal Fetch Size는 10이 아니라 100입니다
    (이 수치는 버전마다 다를거 같습니다)

    실제로 Test를 해보면 100근처에서 거의 비슷한 성능 및 일량을 보입니다.
    정확하게 100은 아니고 그 근처라고 보면 될 거 같습니다.

  4. oraking 2009.02.04 13:46 Modify/Delete Reply

    Implicit 커서, Explicit 커서의 차이가 아니라 Cursor FOR Loop 문이냐 아니냐의 차이입니다.
    Cursor FOR Loop 문은 커서의 Open, Fetch, Close가 내부적으로 이루어지는 것이 특징이죠.
    아마 아래처럼 Explicit 커서를 사용해도 Array Fetch 효과가 나타날걸요?

    declare
    cursor v_cursor is select * from t1 ;
    begin
    for r in v_cursor loop
    null;
    end loop;
    end;
    /

    오래된 글이지만 우연히 발견하고는 토를 달아봅니다. 제목 때문에 오해가 생길 수 있을 거 같아서요.
    늦게나마 새책 나오신 것을 축하드립니다.

  5. 욱짜 2009.02.04 14:00 신고 Modify/Delete Reply

    정확한 지적이군요.

    Cursor의 선언 방식이 문제가 아니라 Fetch하는 방식의 문제라는 것이 테스트 결과이니까 제목에 문제가 있다고 할 수 있군요.

    책이 나올 때마다 또 이번에는 얼마나 많은 오류가 있을 것인가... 하는 우려가 됩니다. 오류를 발견하시게 되면 이메일을 통해서 언제든지 논의부탁드리겠습니다.

  6. 엽기토끼이요 2019.10.08 00:19 Modify/Delete Reply

    관리자의 승인을 기다리고 있는 댓글입니다

Write a comment