View Source

||Clustering Factor 란?||
|{panel:bgColor=#FFFFCE}
Clustering Factor 란 Index 의 Table 에 대한 정렬 정도(orderedness)이다.
Clustering Factor 는 Index 를 경유한 Table Lookup 의 비용을 예측하는데 사용된다.
{panel}|
|{code}# 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, ...
{code}|

||Clustering Factor 측정은?||
|{panel:bgColor=#FFFFCE}
Clustering Factor 는 Index 를 Scan 하는 동안 방문(access)하게 되는 Table 의 Data Block 의 개수이다.
{panel}|
|두가지 전제 필요
* 첫째, 단 하나의 Table Data Block 만을 Memory 에 Cache 할 수 있다.
* 둘째, Clustering Factor 는 Physical Reads, 즉 Cache 에 존재하지 않아 Disk 에서 읽어야 할 경우에만 증가한다.|
||Clustering Factor 계산 데모#1 (Clustering Factor : 1)||
|!fig0.PNG|align=center, vspace=4!|
| * \{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||
|!fig1.PNG|align=center, vspace=4!|
| * \{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||
|!fig2.PNG|align=center, vspace=4!|
| * 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 수|
|!fig3.PNG|align=center, vspace=4!|
| * 이 경우 비록 반대지만, 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||
|!fig4.PNG|align=center, vspace=4!|
| * 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||
|{panel:bgColor=#FFFFCE}1) Index Scan 의 Cost 에 큰 영향을 준다.{panel}|
|{code} Cost = Blevel +
Leaf Blocks * Index Selectivity +
Clustering Factor * Table Selectivity +
Adjusted CPU Count{code}
{code:sql}
-- 예제
-- 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)와 거의 일치
{code}
{code:Sql}
-- 좋은 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) |
--------------------------------------------------------------------------------
{code}
|
|{panel:bgColor=#FFFFCE}2) Clustering Factor 는 Index Lookup 을 통해 Data 를 읽는 일량을 결정한다.{panel}|
|{note:title=질문}T_CLSF_I1 를 경유하는 경우, T_CLSF_I2 를 경유하는 경우, 그리고 TABLE FULL SCAN 하는 경우 단순히 Cost 의 차이가 아닌 실제 성능 차이가 존재하는가? 존재 한다면 어느 정도나?{note}|
|{code:sql}
-- 예제
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 |
-----------------------------------------------------------------------------
{code}|
|!fig5.PNG|align=center, vspace=4!|
|{code}
* 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){code}|
|{code}
@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
{code}|
|{code}
-- 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 에서 밀려나고, 다시 읽고...)
{code}|
|{code:sql}
-- 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;
{code}|


||Manual Clustering Factor||
| * Clustering Factor 는 통계 정보 수집시 계산됨
* DBA_INDEXES.CLUSTERING_FACTOR 를 통해 확인 가능
* 그런데, Manual 하게 Clustering Factor 계산 : [cf.sql|http://sites.google.com/site/ukja/sql-scripts-1/c/cf]
# Index Key 에 해당하는 Column 들에 대해 정렬된 순서로 Table 을 읽으면서
# ROWID 에 해당하는 DBA(Data Block Address)를 구하고
# 이전 ROWID 의 DBA 와 현재 ROWID 의 DBA 가 바뀔 때마다 값을 1 증가시킨다.
* 혹은 SYS_OP_COUNTCHG 함수 사용
{code:sql}
-- 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
;
{code}|