6.4 mode(optimal,one pass,multi pass)에 따른 Sorting,Hashing 성능분석

이 문서는 구루비에서 작성하였습니다.
이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
출처 : http://wiki.gurubee.net/pages/viewpage.action?pageId=13664300&
구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.

6.4 mode(optimal/one pass/multi pass)에 따른 Sorting/Hashing 성능분석

소트와 해쉬 오퍼레이션은 3가지 모드로 수행

optimal mode : 모든 오퍼레이션이 메모리에서 수행됨
one-pass mode : 전체 result set이 한번 disk에 기록됨
multi-pass mode : 전체 result set이 여러번 disk에 기록됨

오라클 9i부터 V$SESSTAT과 V$SYSSTAT 뷰에 4가지 성능지표 추가

workarea memory allocated : 세션 또는 시스템에 할당된 전체 PGA 크기
workarea executions - optimal : optimal mode로 수행된 횟수
workarea executions - onepass : one-pass mode로 수행된 횟수
workarea executions - multipass : multi-pass mode로 수행된 횟수

수행 모드 분석 Script

select name profile, cnt, decode(total, 0, 0, round(cnt*100/total)) percentage
from (select name, value cnt, (sum(value) over ()) total
      from   v$sysstat
      where  name like 'workarea exec%')

수행 모드에 따른 소트와 해쉬 오퍼레이션의 성능차이 분석
건수를 증가하면서 수행하는 이유는 optimal, one-pass, multi-pass mode를 구현하기 위함.


-- 소트 테스트용 (1만 건씩 증가하면서 수행)
declare
  i      number := 0;
  v_spat number := 0;
  v_tcnt number;
  v_pval number;
  q_str1 varchar2(1000);

begin
i := 90000;
  while i < 300000 loop
    i := i+10000;
    i := i+10000;
    q_str1 :='select count(*) from (select * from t1 where rownum <= '||i||' order by id)';
    execute immediate q_str1 into v_tcnt;
  end loop;
end;
/

-- v$sql_workarea 뷰를 이용한 소트 activity 확인 
SQL>@getworkarea
  ==> Page 261 수행 결과 참조


-- 해쉬 테스트용 (1만 건씩 증가하면서 수행)
declare
  i      number := 0;
  v_spat number := 0;
  v_tcnt number;
  v_pval number;
  q_str1 varchar2(1000);

begin
i := 190000;

  while i < 400000 loop
    i := i+10000;

    q_str1 :='select count(*) from (select /*+ ordered use_hash(t1 t2) */ t1.* from (select * from t1 where rownum<'||i||' ) t1, (select * from t1 where rownum<=10000) t2 where t1.id=t2.id)';
    execute immediate q_str1 into v_tcnt;
  end loop;
end;
/

-- v$sql_workarea 뷰를 이용한 해쉬 activity 확인 
SQL>@getworkarea 
  ==> Page 262 수행 결과 참조

결과 분석

==> Page 263 이미지 참조

소트/해쉬 오퍼레이션 모두 optimal mode와 one-pass mode 간의 수행시간의 차이가 발생하고 있으며, 소트의 경우에 더 많은 수행 속도 차이가 발생
소트/해쉬 오퍼레이션 모두 one-pass 모두로 수행되더라도 temp 사용량에 따라 수행시간이 선형적으로 증가하는 현상 발생

문서정보

이 문서는 구루비에서 작성하였습니다.
이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
출처 : http://wiki.gurubee.net/pages/viewpage.action?pageId=13664300&
구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.

6.4 mode(optimal,one pass,multi pass)에 따른 Sorting,Hashing 성능분석

6.4 mode(optimal/one pass/multi pass)에 따른 Sorting/Hashing 성능분석

결과 분석

문서정보

Labels