Implici vs. Explicit Cursor - Who Wins?

Implicit Cursor가 Explicit Cursor에 비해 성능이 뛰어나다는 언급을 여러 번 봤을 것이다.

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

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

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

view plaincopy to clipboardprint?

1. @ukja102   
2.   
3. drop table t1 purge;   
4. create table t1(c1 int, c2 char(10));   
5. insert into t1   
6. select level, 'dummy'  
7. from dual   
8. connect by level <= 200000;   
9. commit;   
10. select count(*) from t1;  

@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를 사용하는 경우의 성능을 비교해 보자.

view plaincopy to clipboardprint?

1. -- Implicit Cursor를 사용하는 경우   
2. @ukja102   
3. @mysid   
4. @mon_on &v_sid   
5.   
6. begin  
7.   for r in (select * from t1) loop   
8.     null;   
9.   end loop;   
10. end;   
11. /   
12.   
13. @mon_off   
14. spool result1.txt   
15. @mon_show   
16. spool off  
17.   
18.   
19. @ukja102   
20. @mysid   
21. @mon_on &v_sid   
22.   
23. -- Explicit Cursor를 사용하는 경우   
24. declare  
25.   cursor v_cursor is  
26.     select * from t1   
27.   ;   
28.      
29.   v_rec v_cursor%rowtype;   
30. begin  
31.   open v_cursor;   
32.   loop   
33.     fetch v_cursor into v_rec;   
34.     exit when v_cursor%notfound;   
35.   end loop;   
36.   close v_cursor;   
37. end;   
38. /   
39.   
40. @mon_off   
41. spool result2.txt   
42. @mon_show   
43. spool off  

-- 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 정보를 비교한다. 이 뷰를 이용하면 별도의 코드를 통해 시간을 측정하지 않아도 된다.

view plaincopy to clipboardprint?

1. -- Implicit Cursor를 사용한 경우   
2. STAT_NAME                                      VALUE1       VALUE2         DIFF   
3. ---------------------------------------- ------------ ------------ ------------   
4. DB time                                        59,773    1,777,125    1,717,352   
5. sql execute elapsed time                       40,140    1,721,534    1,681,394   
6. DB CPU                                         51,929    1,683,972    1,632,043   
7. parse time elapsed                             42,324      256,573      214,249   
8.   
9. -- Explicit Cursor를 사용한 경우   
10. STAT_NAME                                      VALUE1       VALUE2         DIFF   
11. ---------------------------------------- ------------ ------------ ------------   
12. DB time                                        29,622    6,051,808    6,022,186   
13. sql execute elapsed time                       25,827    6,044,618    6,018,791   
14. DB CPU                                         29,331    6,034,029    6,004,698   
15. PL/SQL execution elapsed time                      60      558,753      558,693   
16. parse time elapsed                              1,509      131,440      129,931  

-- 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 뷰를 통해 본 차이는 다음과 같다.

view plaincopy to clipboardprint?

1. -- Implicit Cursor인 경우                                                                                  
2. NAME                                           VALUE1       VALUE2         DIFF   
3. ---------------------------------------- ------------ ------------ ------------   
4. table scan rows gotten                             62      914,002      913,940   
5. session pga memory max                      1,826,388    2,154,068      327,680   
6. session uga memory max                      1,282,300    1,544,264      261,964   
7. session pga memory                          1,826,388    1,957,460      131,072   
8. session logical reads                             275        3,249        2,974   
9.   
10. -- Explicit Cursor인 경우   
11. NAME                                           VALUE1       VALUE2         DIFF   
12. ---------------------------------------- ------------ ------------ ------------   
13. table scan rows gotten                             62   69,366,045   69,365,983   
14. session pga memory max                      1,498,708    1,891,924      393,216   
15. session pga memory                          1,498,708    1,891,924      393,216   
16. session uga memory max                      1,151,372    1,413,336      261,964   
17. session logical reads                              72      200,261      200,189  

-- 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을 사용하는 것이다. 아래와 같이...

view plaincopy to clipboardprint?

1. @ukja102   
2. @mysid   
3. @mon_on &v_sid   
4.   
5. declare  
6.   cursor v_cursor is  
7.     select * from t1   
8.   ;   
9.      
10.   type c1tab is table of t1.c1%type;   
11.   type c2tab is table of t2.c2%type;   
12.      
13.   c1t c1tab;   
14.   c2t c2tab;   
15.      
16. begin  
17.   open v_cursor;   
18.   fetch v_cursor bulk collect into c1t, c2t; -- Do it bulk!!!   
19.   close v_cursor;   
20. end;   
21. /   
22.   
23. @mon_off   
24. spool result3.txt   
25. @mon_show   
26. spool off  

@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

결과는 다음과 같다.

view plaincopy to clipboardprint?

1. -- Implicit Cursor를 사용한 경우   
2. STAT_NAME                                      VALUE1       VALUE2         DIFF   
3. ---------------------------------------- ------------ ------------ ------------   
4. DB time                                        59,773    1,777,125    1,717,352   
5. sql execute elapsed time                       40,140    1,721,534    1,681,394   
6. DB CPU                                         51,929    1,683,972    1,632,043   
7. parse time elapsed                             42,324      256,573      214,249   
8.   
9. -- Explicit Cursor + Bulk Collection을 사용한 경우   
10. STAT_NAME                                      VALUE1       VALUE2         DIFF   
11. ---------------------------------------- ------------ ------------ ------------   
12. DB time                                        28,024    1,503,542    1,475,518   
13. DB CPU                                         18,620    1,489,167    1,470,547   
14. sql execute elapsed time                       24,547    1,493,775    1,469,228   
15. PL/SQL execution elapsed time                      59        5,512        5,453   
16. parse time elapsed                              1,302        4,793        3,491  

-- 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를 사용한 경우 오히려 성능이 더 뛰어나다. 그 이유는?

view plaincopy to clipboardprint?

1. -- Implicit Cursor인 경우                                                                                  
2. NAME                                           VALUE1       VALUE2         DIFF   
3. ---------------------------------------- ------------ ------------ ------------   
4. table scan rows gotten                             62      914,002      913,940   
5. session pga memory max                      1,826,388    2,154,068      327,680   
6. session uga memory max                      1,282,300    1,544,264      261,964   
7. session pga memory                          1,826,388    1,957,460      131,072   
8. session logical reads                             275        3,249        2,974   
9.   
10. -- Explicit Cursor + Bulk Collection인 경우   
11. NAME                                           VALUE1       VALUE2         DIFF   
12. ---------------------------------------- ------------ ------------ ------------   
13. session pga memory max                      1,498,708   21,618,260   20,119,552   
14. session uga memory max                      1,151,372    1,478,800      327,428   
15. table scan rows gotten                             62      200,062      200,000  

-- 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]한번으로 결과가 나오게끔 하라

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

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

<script language=javascript src="http://wiki.ex-em.com/hilite/Scripts/shCore.js">

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-동일조건변경허락 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

'Advanced Oracle' 카테고리의 다른 글

Deterministic Function의 비밀  (10)	2008/08/18
LOB 데이터와 Cache 속성 - 두번째 이야기  (1)	2008/08/13
Implici vs. Explicit Cursor - Who Wins?  (3)	2008/08/09
Calculationg Clustering Factor Manually  (4)	2008/08/07
10704 Event - Troubleshooting Enqueue Problem  (0)	2008/08/04

 

 

출처 : http://ukja.tistory.com/156