꿈꾸는 개발자, DBA 커뮤니티 구루비

Clustering Factor 란?

Clustering Factor 란 Index 의 Table 에 대한 정렬 정도(orderedness)이다.
Clustering Factor 는 Index 를 경유한 Table Lookup 의 비용을 예측하는데 사용된다.


# Good Clustering Factor
  Index = 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, ...
  Table = 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, ...

# Bad Clustering Factor
  Index = 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, ...
  Table = 3, 8, 7, 1, 4, 5, 10, 2, 6, 9, ...

# Good Clustering Factor ?
  Index = 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, ...
  Table = 10, 9, 8, 7, 6, 5, 4, 3, 2, 1, ...

Clustering Factor 측정은?

Clustering Factor 는 Index 를 Scan 하는 동안 방문(access)하게 되는 Table 의 Data Block 의 개수이다.

두가지 전제 필요

첫째, 단 하나의 Table Data Block 만을 Memory 에 Cache 할 수 있다.
둘째, Clustering Factor 는 Physical Reads, 즉 Cache 에 존재하지 않아 Disk 에서 읽어야 할 경우에만 증가한다.

Clustering Factor 계산 데모#1 (Clustering Factor : 1)

{Index Key = 1, Table Row = 1} 를 읽을때 Clustering Factor 1 증가 (Physical Reads 발생, 해당 Data Block 이 Memory 에 Cache 됨)
{Index Key = 2, Table Row = 2} 를 읽을때 Clustering Factor 0 증가 (해당 Data Block 이 이미 Memory 에 Cache 됨)
{Index Key = 3, Table Row = 3} 를 읽을때 Clustering Factor 0 증가 (해당 Data Block 이 이미 Memory 에 Cache 됨)

Clustering Factor 계산 데모#2 (Clustering Factor : 3)

{Index Key = 1, Table Row = 1} 를 읽을때 Clustering Factor 1 증가 (Physical Reads 발생, 해당 Data Block 이 Memory 에 Cache 됨)
{Index Key = 2, Table Row = 2} 를 읽을때 Clustering Factor 1 증가 (Physical Reads 발생, 새로운 Data Block 이 Memory 에 Cache 됨)
{Index Key = 3, Table Row = 3} 를 읽을때 Clustering Factor 1 증가 (Physical Reads 발생, 새로운 Data Block 이 Memory 에 Cache 됨)

Good Clustering Factor

Index 를 경유해서 { 1, 2, .., 12 }를 읽는 동안 실제 Data Block 에 대한 Physical Reads 는 네번 발생한다.
따라서 Clustering Factor 는 4
Index 와 Table 이 완벽하게 정렬 된 경우(Clustering Factor 가 가장 좋은 경우) Physical Reads 는 Table 의 Data Block 수 만큼 발생
즉, Clustering Factor 의 최소값은 Table 의 Data Block 수

이 경우 비록 반대지만, Index 와 Table 의 순서가 완벽하게 정렬 되어 있으므로, Clustering Factor 는 좋다.
Sequence Key 값을 사용하는 Index 는 Index 순서와 Table 순서가 거의 완벽하게 일치하므로 Clustering Factor 가 매우 양호 할것임
Date Type 의 Key 값을 사용하는 Index 는 시간 순으로 Table 에 Insert 가 이뤄진다고 가정할때, Clustering Factor 가 매우 양호 할것임

유의사항

FLM 을 사용하는경우 FREELISTS 속성이 Clustering Factor 에 영향을 줌
FREELISTS 값이 1 이상이고, 동시에 여러개의 Process 가 Insert 를 수행하는 경우
ASSM 을 사용하는경우 Free Block 이 다차원적으로 사용되기 때문에 Clustering Factor 가 높아지는 경향이 있음

Bad Clustering Factor

Index 를 경유해서 { 1, 2, .., 12 }를 읽는 동안 실제 Data Block 에 대한 Physical Reads 는 열두번 발생한다.
따라서 Clustering Factor 는 12
Index 와 Table 이 완벽하게 정렬 안된 경우(Clustering Factor 가 가장 나쁜 경우) Physical Reads 는 Table 의 Row 수 만큼 발생
즉, Clustering Factor 의 최대값은 Table 의 Row 수

Index Key 값이 Random 하게 할당되는 경우, Clustering Factor 는 불량 함
Reverse Index 의 경우 Index 값이 Random 하게 흩어지므로 Clustering Factor가 불량할 것이다.
Composite Index 의 경우, Key 의 조합 방식에 따라 Clustering Factor 값의 변화가 생긴다.
대부분의 Index Key 는 완벽한 정렬(Sequence Key)과 완벽한 랜덤의 사이에 존재한다.

Clustering Factor and Performance

1) Index Scan 의 Cost 에 큰 영향을 준다.


Cost = Blevel +
        Leaf Blocks * Index Selectivity +
        Clustering Factor * Table Selectivity +
        Adjusted CPU Count


-- 예제
-- T_CLSF_I1 : 최적 Clustering Factor 값
-- T_CLSF_I2 : 최악 Clustering Factor 값

-- create objects
drop table t_clsf purge;

create table t_clsf(c1 int, c22 int);

create index t_clsf_i1 on t_clsf(c1);
create index t_clsf_i2 on t_clsf(c2);

-- c : same order as table, c2 : random
insert into t_clsf
select rownum, lvl
  from
    (select level lvl
       from dual connect by level <= 10000
      order by dbms_random.random)
;

-- gather stats
exec dbms_stats.gather_table_stats(user, 't_clsf', method_opt=>'for all columns size 1', cascade=>'true');

-- Table 통계
TABLE_NAME                     NUM_ROWS    BLOCKS     SAMPLE_SIZE
------------------------- ------------- --------- ---------------
T_CLSF                            10000        20           10000

-- Column 통계 (NDV 와 DENSITY 가 완벽히 일치)
COLUMN_NAME                NUM_DISTINCT NUM_NULLS         DENSITY
------------------------- ------------- --------- ---------------
C1                                10000         0           .0001
C2                                10000         0           .0001

-- Clustering Factor (완전 다르다)
TABLE_NAME                     BLEVEL  LEAF_BLOCKS  CLUSTERING_FACTOR
------------------------- ----------- ------------ ------------------
T_CLSF_I1                           1           19                 18
T_CLSF_I2                           1           32               9444

-- T_CLSF_I1 의 Clustering Factor(18)가 Table Block 수(20) 보다 작은 것은
--   1) Sampling
--   2) Table Block 중 데이터가 없는 Block 존재
-- T_CLSF_I2 의 Clustering Factor(9444)는 Table Row 수(10000)와 거의 일치


-- 좋은 Clustering Factor
explain plan for
select /*+ good clsf */ *
  from t_clsf
 where c1 between 1 and 100;

--------------------------------------------------------------------------------
| 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) |
--------------------------------------------------------------------------------

-- 나쁜 Clustering Factor (Selectivity 가 1% 인데 TABLE ACCESS FULL ?)
explain plan for
select /*+ bad clsf */ *
  from t_clsf
 where c2 between 1 and 100;

--------------------------------------------------------------------------------
| Id  | Operation                    | Name      |  Rows | Bytes | Cost (%CPU) |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |   100 |   700 |   22    (0) |
|*  1 |  TABLE ACCESS FULL           | T_CLSF    |   100 |   700 |   22    (0) |
--------------------------------------------------------------------------------

-- 억지 (TABLE ACCESS FULL 이 낫네 ㅋ)
explain plan for
 select /*+ bad clsf index(t_clsf) */ *
   from t_clsf
  where c2 between 1 and 100;

--------------------------------------------------------------------------------
| 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) |
--------------------------------------------------------------------------------

2) Clustering Factor 는 Index Lookup 을 통해 Data 를 읽는 일량을 결정한다.

질문

T_CLSF_I1 를 경유하는 경우, T_CLSF_I2 를 경유하는 경우, 그리고 TABLE FULL SCAN 하는 경우 단순히 Cost 의 차이가 아닌 실제 성능 차이가 존재하는가? 존재 한다면 어느 정도나?


-- 예제
alter session set statistics_level = all;

select /*+ index(t_clsf) */ *
  from t_clsf
 where c1 between 1 and 100
union all
select /*+ full(t_clsf) */ *
  from t_clsf
 where c1 between 1 and 100
union all
select /*+ full(t_clsf) */ *
  from t_clsf
 where c2 between 1 and 100
;

select * from table(dbms_xplan.display_cursor(null,null,'iostats cost last'));

alter session set statistics_level = typical;

-----------------------------------------------------------------------------
| Id  | Operation                    | Name      | Cost (%CPU) |    Buffers |
--------------------------------------------------------------------------------
|   1 | UNION-ALL                    |           |             |        151 |
|   2 |  TABLE ACCESS BY INDEX ROWID | T_CLSF    |   3     (0) |         17 |
|*  3 |   INDEX RANGE SCAN           | T_CLSF_I1 |   2     (0) |          9 |
|*  4 |  TABLE ACCESS FULL           | T_CLSF    |  22     (0) |         30 |
|   5 |  TABLE ACCESS BY INDEX ROWID | T_CLSF    |  97     (0) |        104 |
|*  6 |   INDEX RANGE SCAN           | T_CLSF_I2 |   2     (0) |          8 |
-----------------------------------------------------------------------------


 * Clustering Factor 가 불량 해지면 Physical Reads 뿐 아니라 Logical Reads(Buffers)도 증가 한다?
  * Case A : Clustering Factor(1), Table Data Block Logical Reads(1)
  * Case B : Clustering Factor(3), Table Data Block Logical Reads(3)
 * Buffer Pinning 메카니즘
  * 한번의 Fetch 에서 두 번 연속 방문하는 Block 에 대해서 Pinning을 수행
  * Pinned Block 은 cache buffers chains latch 를 패스 하고, Memory Access 를 이용해 직접 읽음 (buffer is pinned count)
 * 케이스별 Table Data Block Logical Reads 의 세부 내용은...
  * Case A : session logical reads(1), buffer is pinned count(2)
  * Case B : session logical reads(3), buffer is pinned count(0)


@mysid
@mon_on &v_sid

select /*+ index(t_clsf) */ *
  from t_clsf
 where c1 between 1 and 100;

@mon_off
@mon_show

@mon_on &v_sid

select /*+ index(t_clsf) */ *
  from t_clsf
 where c2 between 1 and 100;

@mon_off
@mon_show


-- T_CLSF_I1 Index 를 경유하는 경우
NAME					         DIFF
---------------------------------------- ------------
redo size					3,096
bytes sent via SQL*Net to client		2,708
sorts (rows)					2,304
undo change vector size				2,244
bytes received via SQL*Net from client		1,609
buffer is pinned count				  184
table fetch by rowid				  100
session logical reads				   50
db block changes				   31
consistent gets from cache			   29
consistent gets					   29
buffer is not pinned count			   23

-- T_CLSF_I2 Index 를 경유하는 경우
redo size					3,036
bytes sent via SQL*Net to client		2,708
sorts (rows)					2,304
undo change vector size				2,244
bytes received via SQL*Net from client		1,609
session logical reads				  136
consistent gets from cache			  117
consistent gets					  117
buffer is not pinned count			  111
no work - consistent read gets			  103
table fetch by rowid				  100
buffer is pinned count				   96

-- Clustering Factor 가 불량한 경우 Buffer Pinning 의 발생률이 떨어지고, 이로 인해 Logical Reads 가 증가함
-- 넓은 범위의 Data 를 Index 를 경유해서 읽을 경우, Clustering Factor 가 Physical Reads 의 발생 빈도에 큰 영향을 미침 (Data Block 이 Cache 에서 밀려나고, 다시 읽고...)


-- t_clsf.c2 의 Clustering Factor 개선 하기 (대신 t_clsf.c1 는 나빠짐)
create table t_clsf2
as
select *
  from t_clsf
 order by c2;

drop table t_clsf;

alter t_clsf2 rename to t_clsf;

Manual Clustering Factor

Clustering Factor 는 통계 정보 수집시 계산됨
DBA_INDEXES.CLUSTERING_FACTOR 를 통해 확인 가능
그런데, Manual 하게 Clustering Factor 계산 : cf.sql
- Index Key 에 해당하는 Column 들에 대해 정렬된 순서로 Table 을 읽으면서
- ROWID 에 해당하는 DBA(Data Block Address)를 구하고
- 이전 ROWID 의 DBA 와 현재 ROWID 의 DBA 가 바뀔 때마다 값을 1 증가시킨다.
혹은 SYS_OP_COUNTCHG 함수 사용


-- sys_op_countchg
select /*+ index(t1 t1(c1)) */
       sys_op_countchg(substrb(rowid, 1, 15), 1) as cf
  from t1
 where c1 is not null
;

select /*+ index(t1 t1(c2)) */
       sys_op_countchg(substrb(rowid, 1, 15), 1) as cf
  from t1
 where c2 is not null
;