태터데스크 관리자

도움말
닫기
적용하기   첫페이지 만들기

태터데스크 메시지

저장하였습니다.

Explain Plan과 Clustering Factor

오라클 2008.04.22 15:35
Clustering Factor(이하 CF)는 Index Scan과 Table Scan간의 선택에 있어서 가장 중요한 비용 요소 중 하나이다. 보통 Index Scan을 통해 데이터 추출의 비용(Cost)은 다음 공식을 따른다.

Index Scan Cost =
  BLevel +
  Ceiling(Leaf Blocks * Index Selectivity) +
  Ceiling(Clusteing Factor * Table Selectivity)


보통 Blevel은 1~3 사이의 적은 값이고, Index의 Leaf Block 수는 대부분의 경우 Table Block수보다 작다. 따라서 Clustering Factor가 주는 영향은 매우 클 수 밖에 없다.

여기서 한가지 조금은 작위적인 퀴즈를 만들어 본다. Explain Plan에서 실행 계획과 예상 비용만을 보고 CF가 좋은지 안좋은지 알 수 있을까?

정답은 어느 정도 가능하다는 것이다. 아래 테스트 결과를 보자.

UKJA@ukja10>                                                                   
UKJA@ukja10> -- create objects
UKJA@ukja10> drop table t_clsf purge;

Table dropped.

Elapsed: 00:00:00.09
UKJA@ukja10>
UKJA@ukja10> create table t_clsf(c1 int, c2 int);

Table created.

Elapsed: 00:00:00.01
UKJA@ukja10>
UKJA@ukja10> create index t_clsf_i1 on t_clsf(c1);

Index created.

Elapsed: 00:00:00.01
UKJA@ukja10> create index t_clsf_i2 on t_clsf(c2);

Index created.

Elapsed: 00:00:00.00
UKJA@ukja10> -- 데이터 양은 동일하지만 CF만 차이가 나는 데이터를 만든다
UKJA@ukja10> -- c1 : same order as table, c2 : random
UKJA@ukja10> insert into t_clsf
  2  select rownum, lvl
  3  from
  4    (select level lvl
  5    from dual connect by level <= 10000
  6    order by dbms_random.random)
  7  ;

10000 rows created.

Elapsed: 00:00:00.70
UKJA@ukja10>
UKJA@ukja10> commit;

Commit complete.

Elapsed: 00:00:00.00
UKJA@ukja10>
UKJA@ukja10> -- gather stats
UKJA@ukja10> exec dbms_stats.gather_table_stats(user, 't_clsf', -
>   method_opt=>'for all columns size 1', -
>   cascade=>true);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.34
UKJA@ukja10>
UKJA@ukja10> select
  2           table_name, index_name, blevel, leaf_blocks,
  3           distinct_keys, clustering_factor, sample_size,
  4           to_char(last_analyzed,'yyyy/mm/dd hh24:mi:ss') as last_anal
  5  from
  6           dba_ind_statistics
  7  where
  8           index_name = upper('&I_NAME') or
  9           table_name = upper('&I_NAME')
 10  ;
old   8:     index_name = upper('&I_NAME') or
new   8:     index_name = upper('t_clsf') or
old   9:     table_name = upper('&I_NAME')
new   9:     table_name = upper('t_clsf')

TABLE_NAME           INDEX_NAME               CLUSTERING_FACTOR
-------------------- --------------------     ----------
T_CLSF               T_CLSF_I1                   18  <-- Good CF
T_CLSF               T_CLSF_I2                 9436  <-- Ouch. Bad CF
                                                                               

Elapsed: 00:00:00.01
UKJA@ukja10>
UKJA@ukja10> -- compare the cost
UKJA@ukja10> explain plan for
  2  select /*+ good clsf index(t_clsf) */ *
  3  from t_clsf
  4  where c1 between 1 and 100;

Explained.

Elapsed: 00:00:00.00
UKJA@ukja10>
UKJA@ukja10> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT                                                              
------------------------------------------------------------------------------
Plan hash value: 4073175222
------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |   100 |   700 |     3   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID| T_CLSF    |   100 |   700 |     3   (0)|
|*  2 |   INDEX RANGE SCAN          | T_CLSF_I1 |   100 |       |     2   (0)|
------------------------------------------------------------------------------
<-- CF가 좋은 경우 Index에서 Table로의 Lookup에서 비용이 "1"(3-2)만큼만 증가한다.                                                                          
Predicate Information (identified by operation id):
---------------------------------------------------                            
   2 - access("C1">=1 AND "C1"<=100)

14 rows selected.

Elapsed: 00:00:00.03
UKJA@ukja10>
UKJA@ukja10> explain plan for
  2  select /*+ bad clsf index(t_clsf) */ *
  3  from t_clsf
  4  where c2 between 1 and 100;

Explained.

Elapsed: 00:00:00.01
UKJA@ukja10>
UKJA@ukja10> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT                                                              
------------------------------------------------------------------------------
Plan hash value: 3421580928
------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |   100 |   700 |    97   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID| T_CLSF    |   100 |   700 |    97   (0)|
|*  2 |   INDEX RANGE SCAN          | T_CLSF_I2 |   100 |       |     2   (0)|
------------------------------------------------------------------------------
<-- CF가 안좋은 경우 Index에서 Table로의 Lookup에서 비용이 "95"(97-2)이나 증가한다.                                                                              
Predicate Information (identified by operation id):
---------------------------------------------------                            
   2 - access("C2">=1 AND "C2"<=100)

14 rows selected.

Elapsed: 00:00:00.03
UKJA@ukja10>
UKJA@ukja10> spool off

위의 예를 보면 CF가 불량한 경우 Index Range Scan에서 Table Access By Index Rowid로 넘어가는 과정에서 큰 비용차가 발생하는 것을 알 수 있다. 이런 경우 불량한 CF를 의심해볼 수 있고, 통계 정보를 통해서 이를 확인할 수 있을 것이다. 운이 좋으면 왜 우량한 Index를 두고도 Oracle이 굳이 Table Full Scan을 할려고 하는지 원인을 찾을 수도 있을 것이다.

한 가지 주의할 것은 이런 현상이 항상 CF에 의해서만 발생하는 것은 아니라는 것이다. Table에서 Filtering을 수행하는 과정에서 비용이 증가하는 경우도 있기 때문이다.

CF에 대한 기본적인 설명은 Exem Wiki를 참조한다.


신고
Trackback 0 : Comment 0

Write a comment

티스토리 툴바