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 사용량에 따라 수행시간이 선형적으로 증가하는 현상 발생