by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

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




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)
    PAT _SMM_MAX_SIZE Active Time(sec) Mode Number of Input Phase Comparisons Number of Merge Phase Comparisons
    20Mb 1Mb 100.5 multi-pass 3409185 6727752
    40Mb 2Mb 65.0 multi-pass 4526192 6933471
    60Mb 3Mb 58.9 one-pass 6125554 6593925
    80Mb 4Mb 56.8 one-pass 6433029 6908936
    100Mb 5Mb 56.7 one-pass 7742685 6926471
    1000Mb 50Mb 68.0 one-pass 20473966 4234772
    1100Mb 55Mb 64.7 one-pass 20473966 4234772
    1700Mb 85Mb 69.5 one-pass 24387574 2959958
    1800Mb 90Mb 74.8 one-pass 27558132 1949805
    1900Mb 95Mb 71.8 one-pass 27558132 1949805
    2000Mb 100Mb 71.8 one-pass 27558132 1949805
  • _PGA_MAX_SIZE=400Mb로 변경
    PAT _SMM_MAX_SIZE Active Time(sec) Mode Number of Input Phase Comparisons Number of Merge Phase Comparisons
    2100Mb 105Mb 72.7 one-pass 27558132 1949805
    2200Mb 110Mb 20.1 optimal 27934764 0

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

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.