태터데스크 관리자

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

태터데스크 메시지

저장하였습니다.

[퀴즈] 성능 튜닝의 절대 원칙 - 오라클에서 가장 가벼운 작업은? - 해답편

오라클 2010.07.20 13:06
오라클 성능 문제에 관한 원조 중의 원조(구루?)로 평가받는 Carry Milsap은 성능을 개선시키는 절대적인 원칙 하나를 언급했습니다.
  • 어떤 작업의 성능을 개선시키는 최고의 방법은 그 작업을 하지 않는 것이다.
정말 명언 중의 명언입니다.

아래에 간단한 예가 있습니다.

1. 더 이상 튜닝할 것이 없는 완벽히 최적화된 문장입니다. 약 3.4초가 걸렸습니다.

TPACK@ukja1106> declare
  2  	v_value number;
  3  begin
  4  	for idx in 1 .. 100000 loop
  5  		select trunc(idx) into v_value from dual;
  6  	end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.42
과연 그럴까요? 위에서 말한 절대 원칙을 적용하면? 불필요한 SELECT ... FROM DUAL 을 없앤다면? (작업을 하지 않는 것)
TPACK@ukja1106> declare
  2  	v_value number;
  3  begin
  4  	for idx in 1 .. 100000 loop
  5  		v_value := trunc(idx);
  6  	end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.07
불과 0.07초만에 수행이 끝납니다.

2. DECODE 함수는 SQL 문장내에서만 수행 가능합니다. 따라서 CASE ... 구문으로 변환하면 같은 효과를 누릴 수 있습니다.


TPACK@ukja1106> -- decode
TPACK@ukja1106> declare
  2  	v_value varchar2(1);
  3  begin
  4  	for idx in 1 .. 100000 loop
  5  		select decode(mod(idx,2),0,'A','B') into v_value from dual;
  6  	end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.59

TPACK@ukja1106> -- case
TPACK@ukja1106> declare
  2  	v_value varchar2(1);
  3  begin
  4  	for idx in 1 .. 100000 loop
  5  		v_value := case mod(idx,2) when 0 then 'A' else 'B' end;
  6  	end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.15
3. Oracle 11g부터는 Sequence의 값도 SELECT ... FROM DUAL을 통하지 않고 얻을 수 있게 되었습니다. 하지만 성능에 주는 영향은 거의 미미하군요.
TPACK@ukja1106> -- SELECT... FROM DUAL을 통해서 얻기
TPACK@ukja1106> declare
  2  	v_value number;
  3  begin
  4  	for idx in 1 .. 100000 loop
  5  		select s1.nextval into v_value from dual;
  6  	end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.73

TPACK@ukja1106> -- 직접 얻기
TPACK@ukja1106> declare
  2  	v_value number;
  3  begin
  4  	for idx in 1 .. 100000 loop
  5  		v_value := s1.nextval;
  6  	end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.64
음... 왜 그럴까요? 그에 대한 답은 SQL*Trace를 통해 얻을 수 있습니다. SQL*Trace를 수행해보면 { v_value := s1.nextval } 작업은 { Select S1.NEXTVAL from dual } 로 변환되는 것을 알 수 있습니다. 따라서 위의 두 작업은 사실은 동일한 일을 수행하고 있는 것입니다.

4. 이제 가벼운 문제 하나 나갑니다.

WAS 같은 시스템에서 오라클의 세션이 살아 있는지 죽었는지 확인하기 위해 특정 Query를 수행해봅니다. 이 작업을 수행하기 위한 최고의 방법은 무엇일까요? 즉 어떤 Query를 수행하면 될까요?


제가 생각한 정답은 이것입니다.
begin 
    null;
end;
/
되도록 아무런 일도 하지 않게 하는 것.

참고로 아무리 Logical Reads가 적은 Query를 구사해도 { select 1 from dual } 보다는 비효율적입니다. { select 1 from dual }은 Buffer Cache를 액세스하지 않기 때문입니다.

TPACK@ukja1106> explain plan for select 1 from dual;

Explained.

---------------------------------
| Id  | Operation        | Name |
---------------------------------
|   0 | SELECT STATEMENT |      |
|   1 |  FAST DUAL       |      |
---------------------------------
단, {select * from dual } 과 갈은 쿼리를 수행하면 Buffer Cache에서 Buffer를 읽게 됩니다.
TPACK@ukja1106> explain plan for select * from dual;

Explained.

----------------------------------
| Id  | Operation         | Name |
----------------------------------
|   0 | SELECT STATEMENT  |      |
|   1 |  TABLE ACCESS FULL| DUAL |
----------------------------------
저작자 표시
신고
Trackback 0 : Comments 7
  1. 열심남 2010.07.16 10:58 신고 Modify/Delete Reply

    좋은 포스트입니다. ^^: RSS로 구독중인데 ukja님 블로그 full feed로 제공해주심 매우 감사할듯합니다. ^^:

  2. 열심남 2010.07.16 11:02 신고 Modify/Delete Reply

    저도 문제에 답변 응모를 해야죠.. 저희 was에서도 위에 나온것처러 select * from dual; 이 문장을 많이 씁니다. 해서 예전에 dual 테이블 자체를 organization index 로 만들어서 적용했던 적이 있습니다. ukja님은 더 좋은 해결책을 제시해주실듯 ^^: 기대됩니다.

  3. onsider 2010.07.16 19:32 신고 Modify/Delete Reply

    음... 제가 생각하기로는 가장 가벼운 Query는"Select * from dual"인고,
    이것을 더 가볍게 하기 위해 Procedure로 만들고 호출하는게 더 나은 방법 아닌지요 ?

  4. 박용석 2010.07.19 15:36 신고 Modify/Delete Reply

    select 1 from waschk ; 그리고 waschk 는 1블럭 짜리 IOT로 구성 합니다.

  5. 2010.07.20 14:13 Modify/Delete Reply

    비밀댓글입니다

    • 욱짜 2010.07.20 16:15 신고 Modify/Delete

      Oracle 10g부터는 dual 접속시 Logical Reads가 발생하지 않도록 보완되었습니다. (FAST DUAL)

      단 DUMMY 컬럼을 액세스할 때는 다시 이전의 DUAL 테이블 액세스로 바뀌게 됩니다. 즉 { select sysdate from dual } 같은 문장은 X$DUAL를 사용한 것과 동일한 효과가 있고, { select * from dual }과 같은 문장은 Logical Reads를 일으키게 됩니다.

  6. 김시연 2010.08.03 14:00 신고 Modify/Delete Reply

    select 1 from dual;방식보다 begin null; end; 방식이 O/S cpu 사용률 관점에서 98%개선되는군요.
    그리고 오라클 down시에 Error Code(3113,3114)도 리턴되므로 좋은 방법이 되겠네요~!


    $cat case1.sh
    sqlplus scott/tiger << EOF
    declare v1 number;
    begin
    for i in 1..100000 loop
    select 1 into v1 from dual;
    end loop;
    end;
    /
    exit
    EOF

    << $time case1.sh output >>
    real 0m4.017s
    user 0m0.017s
    sys 0m0.010s

    << 10046 trace output >>

    call count cpu elapsed
    ------- ------ -------- ----------
    Parse 1 0.00 0.00
    Execute 1 6.26 7.50
    Fetch 0 0.00 0.00
    ------- ------ -------- ----------
    total 2 6.26 7.50

    $cat case2.sh
    sqlplus scott/tiger << EOF
    begin
    for i in 1..100000 loop
    begin null; end;
    end loop;
    end;
    /
    exit
    EOF
    << $time case1.sh output >>
    real 0m0.091s
    user 0m0.020s
    sys 0m0.014s

    << 10046 trace output >>
    call count cpu elapsed
    ------- ------ -------- ----------
    Parse 1 0.00 0.00
    Execute 1 0.00 0.00
    Fetch 0 0.00 0.00
    ------- ------ -------- ----------
    total 2 0.00 0.00

Write a comment

티스토리 툴바