6.5 Run-Time 메모리 크기에 따른 Sorting/Hashing 성능분석

• sort-set 크기와 input hashing-set 크기는 고정, work-area 크기에 따른 성능 차이 분석

-- 소트 테스트용 (PAT를 증가하면서 테스트)
set serveroutput on

declare
  i      number := 0;
  v_spat number := 0;
  v_tcnt number;
  v_pval number;
  q_str1 varchar2(1000)

begin
while v_spat < 2500 loop
i := i+1;
    if (i <= 5) then
      v_spat := v_spat + 20;
    else
      v_spat := 100 * (i-4);
    end if;

      execute immediate 'alter system set pga_aggregate_target='||v_spat||'m';

      -- 소트에 대한 상세 분석을 위해 10032 event를 수행한다.
      execute immediate 'alter session set events ' ||''''||'10032 trace name context forever, level 1'||'''';
      q_str1 :='select count(*) from (select * from t1 where rownum /*+ PAT='||v_spat||'Mb   */ <=1600000 order by id)';
      execute immediate q_str1 into v_tcnt;
      execute immediate 'alter session set events ' ||''''||'10032 trace name context off'||'''';
  end loop;
end;
/

-- v$sql_workarea 뷰를 이용한 소트 activity 확인 
-- _PGA_MAX_SIZE를 Default 값인 200 Mb로 유지할 경우에는
--  PAT의 크기를 2000 Mb 이상 증가시켜도 _SMM_MAX_SIZE가 100 Mb이상 사용될 수 없으므로
-- _PGA_MAX_SIZE=400 Mb로 변경함

SQL>@getworkarea 
  ==> Page 265 결과 참조

-- 해쉬 테스트용 (PAT를 증가하면서 테스트)
set serveroutput on

declare
  i      number := 0;
  v_spat number := 0;
  v_spid number;
  v_tcnt number;
  v_pval number;
  q_str1 varchar2(1000);
  q_str2 varchar2(100);
  q_str3 varchar2(100);

begin
while v_spat < 2500 loop
i := i+1;
    if (i <= 5) then
      v_spat := v_spat + 20;
    else
      v_spat := 100 * (i-4);
    end if;

    execute immediate 'alter system set pga_aggregate_target='||v_spat||'m';

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

-- v$sql_workarea 뷰를 이용한 hashing activity 확인 
SQL>@getworkarea 
  ==> Page 267 결과 참조

결과 분석

==> Page 268 그림 참조

• 소트 오퍼레이션

- one-pass mode와 optimal mode 간이 성능 차이는 2.5배 정도
- one-pass mode일 경우 PAT 크기가 증가할 수록 성능이 저하
  + one-pass 수행에 필요한 최소 메모리를 사용할수록 성능상 이점
  + 가능하면 optimal mode로 수행하는 것이 올바름

• 해쉬 오퍼레이션

- one-pass mode와 optimal mode 간이 성능 차이는 1.5배 정도
- one-pass mode로 수행될 경우 PAT 크기가 증가할수록 성능이 향상
  + 가능한 많은 메모리를 할당 받을수록 성능이 향상

10032 Event Trace를 이용한 소트 오퍼레이션 테스트 상세 분석

• 소트의 단계

- Input Phase 비교단계
- Merge Phase 비교단계

• Cost 비교 : Input Phase 단계 > Merge Phase 단계

• one-pass mode일 때 work-area가 클수록 성능이 저하되는 이유는 work-area가 클수록 Input Phase 단계에서 수행되는 비교 횟수의 증가 때문임

• _PGA_MAX_SIZE=200Mb (Default)

• _PGA_MAX_SIZE=400Mb로 변경

표 6-5 10032 Event를 이용한 Sorting Operation 분석