| 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, ... |
|
||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}|
|
Cost = Blevel +
Leaf Blocks * Index Selectivity +
Clustering Factor * Table Selectivity + Adjusted CPU Count
{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)와 거의 일치
-- 좋은 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) |
--------------------------------------------------------------------------------
| {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 |
| INDEX RANGE SCAN | T_CLSF_I1 | 2 (0) | 9 |
| TABLE ACCESS FULL | T_CLSF | 22 (0) | 30 |
| 5 | TABLE ACCESS BY INDEX ROWID | T_CLSF | 97 (0) | 104 |
| INDEX RANGE SCAN | T_CLSF_I2 | 2 (0) | 8 |
|
|!fig5.PNG|align=center, vspace=4!|
|
| {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
|
|
– T_CLSF_I1 Index 를 경유하는 경우
NAME DIFF
– 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: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;
|
| Manual Clustering Factor | |
|---|---|
| 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
;
|