본문 바로가기
Oracle

오라클 클러스터링 팩터 테스트 - Clustering_Factor

by goooood 2019. 10. 24.

며칠 전에 인덱스가 생성되기 전에 클러스터링 팩터를 추정하기 위해 얼마 전에 쓴 스크립트에 대한 약간의 메모를 게시했습니다. 그 당시 제한 사항 중 하나는 table_cached_blocks 환경 설정을 계획하려는 경우를 처리하지 못한다는 점을 지적했지만 며칠 후에는 다른 버전의 코드를 작성하기로 결정했습니다. 새로운 기능 – 그리고 내가 창피하게 발견 한 방법입니다.

table_cached_blocks 환경 설정과 clustering_factor에 미치는 영향에 대해 게시 한 많은 메모를 검토 한 결과 필자가 작성한 내용이 항상 두 가지 해석에 열려 있음을 깨달았습니다. 그리고 올바른 것. match_recognize () 메커니즘을 사용하여 적절한 계산으로 간주되는 것을 수행하기 위해 간단한 SQL 문을 작성했기 때문에이 발견을했습니다. 올바른 결과를 생성하는 몇 가지 샘플 데이터로 쿼리를 테스트 한 후 Stew Ashton (내 "go-to"사람이 match_recognize () 질문)에게 전자 메일로 전송하여 코드가 느리기 때문에 코드에서 상태 검사를 수행 할 것인지 묻습니다. 더 나은 작성 방법이 있는지 궁금했습니다.

그의 대답은 대략 다음과 같습니다.

"저는 귀하와 Richard Foote가 clustering_factor 및 table_cached_blocks에 대해 작성한 메모를 읽었으며, 이는 귀하의 설명이 말한대로하지 않습니다."

그런 다음 그는 내가 쓴 내용에서 유추 한 내용을 설명했습니다. 제가 글을 쓸 때 생각했던 것보다 더 의미가있었습니다. 또한 해석을 구현하기 위해 몇 가지 코드를 제공 했으므로 코드를 잘못 해석 한 코드를 잘못 예측할 수있는 몇 가지 데이터 모델을 설계했습니다. 그의 코드는 정답을 줬지만 그렇지 않았습니다.

따라서 table_cached_blocks에 대한 토론 값으로 16을 사용하는 해석의 차이점은 다음과 같습니다.

잘못된 해석 : 인덱스 항목을 살펴볼 때 본 마지막 16 개 rowid (인덱스가 가리키는 테이블의 행에 대한 rowid)를 기억하십시오. 현재 rowid에 기억 된 16 개 rowid 중 하나의 block id와 일치하지 않는 block id 구성 요소가있는 경우 clustering_factor에 대한 카운터를 증가시킵니다.
이 알고리즘의 단순성은 16 개의 항목으로 구성된 "원형"배열을 수정하고 새 항목을 읽을 때마다 가장 오래된 항목을 덮어 쓰면서 원을 계속 걸어 갈 수 있음을 의미합니다. 그것을 구현하기위한 간단한 (대규모 CPU 집약적 match_recognize () 전략이 있기 때문에 잘못된 아이디어라는 것은 유감입니다. 적절한 gather_index_stats () 동안 내부 라이브러리 메커니즘을 사용하는 경우 매우 효율적일 수 있습니다.


올바른 해석 : 각각 "행 번호"가있는 16 개의 블록 ID에 대한 배열을 설정합니다. 색인을 순서대로 따라 가면서 각 항목에 행 번호를 지정하십시오. 현재 항목에서 블록 ID를 추출하고 배열을 통해 일치하는 블록 ID를 검색하십시오. 일치하는 항목을 찾으면 현재 행 번호로 항목을 업데이트하십시오 (따라서 블록 ID를 최근에 본 횟수를 기억할 수 있음). 일치하는 항목을 찾지 못하면 행 번호가 가장 작은 (즉, 과거까지의 거리가 가장 긴) 항목을 현재 블록 ID 및 행 번호로 바꾸고 clustering_factor의 카운터를 늘리십시오.


Stew Ashton이 보낸 첫 번째 코드 조각은 하드 코드 된 조각과 내장 SQL을 포함하여 익명으로 정의한 테스트 테이블과 인덱스를 사용하는 익명의 PL / SQL 블록이었습니다. 동적 SQL을 사용하여 테스트 할 테이블 및 인덱스 정의에 대한 쿼리를 구성하는 일반 함수 후자는 아래에 게시 한 코드 (허가)입니다

 

create or replace function predict_clustering_factor(
/*
Function to predict the clustering factor of an index,
taking into account the intended value of
the TABLE_CACHED_BLOCKS parameter of DBMS_STATS.SET_TABLE_PREFS.

입력은 테이블 이름, 열 이름 목록입니다.
그리고 TABLE_CACHED_BLOCKS의 의도 된 값.

이 함수는 마지막 N 개 항목이 아닌 마지막 N 개의 블록 ID를 수집합니다.
더 이상 공간이 없으면 클러스터링 계수가 증가합니다.
가장 최근에 사용한 블록 ID를 현재 블록 ID로 바꿉니다.

Note: here a "block id" is a rowid with the row_number portion set to 0.
It is effectively a "truncated" rowid.
*/
  p_table_name in varchar2,
  p_column_list in varchar2,
  p_table_cached_blocks in number
) return number authid current_user is

  rc sys_refcursor;
  type tt_rids is table of rowid;
  lt_rids tt_rids;
  
  type t_block_list is record(
    rid rowid,
    last_hit number
  );

  type tt_block_list is table of t_block_list;
  lt_block_list tt_block_list := new tt_block_list();

  l_rid rowid;
  l_clustering_factor number := 0;
  b_block_found boolean;
  l_rn number := 0;
  l_oldest_hit number;
  i_oldest_hit binary_integer := 0;
  
  function truncated_rid(p_rid in rowid) return rowid is
    rowid_type number;
    object_number NUMBER;
    relative_fno NUMBER;
    block_number NUMBER;
    row_number NUMBER;
    rid rowid;

  begin

    DBMS_ROWID.ROWID_INFO (
      p_rid,
      rowid_type,
      object_number,
      relative_fno,
      block_number,
      row_number
    );

    rid := DBMS_ROWID.ROWID_CREATE (
      rowid_type,
      object_number,
      relative_fno,
      block_number,
      0
    );

    return rid;

  end truncated_rid;
  
begin
  if p_table_cached_blocks != trunc(p_table_cached_blocks)
  or p_table_cached_blocks not between 1 and 255 then
    raise_application_error(
      -20001, 
      'input parameter p_table_cached_blocks must be an integer between 1 and 255'
    );
  end if;

  open rc for 'select rowid from '||p_table_name||' order by '||p_column_list||', rowid';
  loop
    fetch rc bulk collect into lt_rids limit 1000;

    for irid in 1..lt_rids.count loop
      l_rn := l_rn + 1;
      l_rid := truncated_rid(lt_rids(irid));
      b_block_found := false;
      l_oldest_hit := l_rn;

      if l_rn = 1 then
        l_clustering_factor := l_clustering_factor + 1;
        lt_block_list.extend;
        lt_block_list(1).rid := l_rid;
        lt_block_list(1).last_hit := l_rn;

      else

        for i in 1..lt_block_list.count loop
          if l_oldest_hit > lt_block_list(i).last_hit then
            l_oldest_hit := lt_block_list(i).last_hit;
            i_oldest_hit := i;
          end if;
          if lt_block_list(i).rid = l_rid then
            b_block_found := true;
            lt_block_list(i).last_hit := l_rn;
            exit;
          end if;
        end loop;

        if not b_block_found then
          l_clustering_factor := l_clustering_factor + 1;
          if lt_block_list.count < p_table_cached_blocks then
            lt_block_list.extend;
            lt_block_list(lt_block_list.count).rid := l_rid;
            lt_block_list(lt_block_list.count).last_hit := l_rn; 
          else         
            lt_block_list(i_oldest_hit).rid := l_rid;
            lt_block_list(i_oldest_hit).last_hit := l_rn;
          end if;
        end if;

      end if;

    end loop;
    exit when rc%notfound;
  end loop;

  close rc;
  return l_clustering_factor;

exception when others then
  if rc%isopen then
    close rc;
  end if;
  raise;

end predict_clustering_factor;
/

After executing the above to create the function, here’s an example of usage:

rem
rem     Script:         clustering_factor_est_2.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Oct 2019
rem
rem     Last tested
rem             19.3.0.0
rem             12.2.0.1
rem

create table t1
as
with generator as (
        select
                rownum id
        from dual
        connect by
                level <= 1e4 -- > comment to avoid WordPress format issue
)
select
        rownum                                  id,
        cast(rownum as varchar2(10))            v1,
        trunc(dbms_random.value(0,10000))       rand,
        rpad('x',100,'x')                       padding
from
        generator       v1,
        generator       v2
where
        rownum <= 1e6 -- > comment to avoid WordPress format issue
/

-- -------------------------------------------------------------------

SQL> execute dbms_output.put_line('Predicted cf for t1(rand, id): ' || predict_clustering_factor('t1','rand, id',16))
Predicted cf for t1(rand, id): 997218
Elapsed: 00:00:07.54

SQL> execute dbms_output.put_line('Predicted cf for t1(rand, id): ' || predict_clustering_factor('t1','rand, id',255))
Predicted cf for t1(rand, id): 985607
Elapsed: 00:00:50.61

 

“table_cached_blocks”매개 변수의 설정이 클수록 clustering_factor를 예측하는 데 더 많은 시간이 걸리며, 예제에서는 모두 CPU 시간이었습니다. 

이전 히스토리를 보유한 어레이를 검색해야한다는 점을 고려할 때 놀라운 것은 아닙니다. 

이 예제에서 테이블 t1은 1,000,000 개의 행을 보유하며 고유 한 값의 수와 분산은 코드가 캐시 된 블록 ID를 거의 찾지 못하도록 배열됩니다. 

본질적으로 혼동을 일으키지 않는 인덱스의 일종입니다. 옵티 마이저를 사용해야하며 옵티마이 저가 필요할 때이를 사용하도록하고 부적합한 경우에는이를 무시하기 위해 특별한주의가 필요하지 않습니다.

마지막으로 cut-n-paste는 두 예측의 정확성을 보여줍니다.

 

SQL> create index t1_i on t1(rand, id);
Elapsed: 00:00:02.96

SQL> execute dbms_stats.set_table_prefs(null,'t1','table_cached_blocks',16)
Elapsed: 00:00:00.01

SQL> execute dbms_stats.gather_index_stats(null,'t1_i')
Elapsed: 00:00:09.55

SQL> select clustering_factor from user_indexes where index_name = 'T1_I';

CLUSTERING_FACTOR
-----------------
           997218

Elapsed: 00:00:00.11

SQL> execute dbms_stats.set_table_prefs(null,'t1','table_cached_blocks',255)
Elapsed: 00:00:00.01

SQL> execute dbms_stats.gather_index_stats(null,'t1_i')
Elapsed: 00:00:07.80

SQL> select clustering_factor from user_indexes where index_name = 'T1_I';

CLUSTERING_FACTOR
-----------------
           985607

Elapsed: 00:00:00.00

 

둘 다 완벽하게 일치하지만 table_cached_blocks = 255를 설정 한 경우에 대한 클러스터링 요소를 예측하는 것보다 인덱스를 작성하고 통계를 수집하는 것이 훨씬 빠르다는 것을 알 수 있습니다.

 

댓글