■ CLUSTERING FACTOR
1) 정의: 특정 컬럼을 기준으로 한 인덱스의 클러스터링 정도를 나타내는 지표. 서로 다른 행들의 데이터가 같은 블록에 모여 있는 정도를 나타난다.
2) 좋은 CLUSTERING_FACTO
- 해당 컬럼에 인덱스를 사용할 때 I/O를 최소화할 수 있다. Range Scan 등의 작업에서 성능 향상이 기대됨. (해당 컬럼에 대한 인덱스를 사용할 때 성능이 향상되는 효과를 기대할 수 있다.)
- Buffer Pinning이 수행되기 때문에 Latch를 점유하는 시간이 감소할 수 있다. 따라서 블록에 대한 접근이 더 효율적으로 이루어질 수 있다.
3) 예시 상황
- 파티션 분리: 데이터가 특정 범위 또는 순서에 따라 파티션으로 나뉘어 있거나, ORDER BY에서 사용되는 정렬에 적합한 컬럼에 인덱스를 적용한 경우 CLUSTERING_FACTOR가 좋을 수 있다.
□ CLUSTERING FACTOR 계산
1) 변수 선언: count 변수 선언
2) leaf 블록 스캔: Leaf 블록을 처음부터 끝까지 스캔하면서 각 행의 RowID로부터 해당 블록의 번호를 찾는다.
3) 블록 주소 비교: 찾은 블록 주소가 바로 직전의 블록 주소와 다를 때마다 `count` 변수를 1 증가시킨다.
4) CLUSTERING_FACTOR 계산: Leaf 블록의 끝까지 위 작업을 수행하면 `count` 변수에 있는 값을 CLUSTERING_FACTOR로 간주하고 해당 인덱스의 통계 정보에 저장한다.
#) CLUSTERING_FACTOR 좋은 경우
- buffer pinning 돌아가는 경우
leaf
------------------
| id | rowid |
| 1 | A |
| 2 | A |
| 3 | A |
| 4 | A |
| 5 | A |
| 6 | A |
A
---------
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
B
---------
| 6 |
#2) CLUSTERING_FACTOR 좋지 않은 경우
- buffer pinning 돌아가지 않을 때
leaf
------------------
| id | rowid |
------------------
| 1 | A |
| 2 | B |
| 3 | A |
| 4 | B |
| 5 | A |
| 6 | B |
------------------
A
---------
| 1 |
| 3 |
| 5 |
B
---------
| 2 |
| 4 |
| 6 |
[실습 해보기]
<hr sess>
#) 기본적으로 redo 생성이 된다. nologging 을 붙이면 redo 가 생성되지 않는다.
create table hr.c_table
nologging
as
select * from all_objects
order by object_id;
#) index 생성
create index hr.c_obj_idx on c_table(object_id);
create index c_obj_name_idx on c_table(object_name);
#) 생성한 c_table의 기본적인 통계정보 확인
=> 보이지 않음
select num_rows, blocks, avg_row_len
from user_tables
where table_name = 'C_TABLE';
NUM_ROWS BLOCKS AVG_ROW_LEN
---------- ---------- -----------
#) hr 스키마의 c_table 테이블에 대한 통계를 수집
execute dbms_stats.gather_table_stats('hr','c_table')
#) 생성한 c_table의 기본적인 통계정보 다시 확인
select num_rows, blocks, avg_row_len
from user_tables
where table_name = 'C_TABLE';
NUM_ROWS BLOCKS AVG_ROW_LEN
---------- ---------- -----------
68178 1009 98
#) 인덱스 정보 확인
select index_name, blevel, leaf_blocks, clustering_factor
from user_indexes
where table_name = 'C_TABLE';
INDEX_NAME ★BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
------------------------------ ---------- ----------- -----------------
C_OBJ_IDX 1 152 985
C_OBJ_NAME_IDX 2 352 61813
#) 설명
- index_name: 인덱스의 이름
- blevel: 인덱스의 레벨 (B-tree 인덱스의 높이)
- leaf_blocks: 리프 블록의 수 (인덱스의 끝 부분에 위치한 블록)
- clustering_factor: 클러스터링 팩터 (인덱스의 클러스터링 정도를 나타내는 값)
#) 힌트 사용: /*+ gather_plan_statistics index(c c_obj_idx)*/
select /*+ gather_plan_statistics index(c c_obj_idx)*/ count(*)
from c_table c
where object_id >= 0
and object_name >= ' ';
COUNT(*)
----------
68178
#) 실행계획 확인
select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 2tc3v10cbk00j, child number 0
-------------------------------------
select /*+ gather_plan_statistics index(c c_obj_idx)*/ count(*) from
c_table c where object_id >= 0 and object_name >= ' '
Plan hash value: 1213134236
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 1138 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 1138 |
|* 2 | TABLE ACCESS BY INDEX ROWID| C_TABLE | 1 | 68178 | 68178 |00:00:00.02 | 1138 |
|* 3 | INDEX RANGE SCAN | C_OBJ_IDX | 1 | 68178 | 68178 |00:00:00.01 | 153 |
----------------------------------------------------------------------------------------------------
-- 실제 row 수보다 buffers 수가 적은 이유? buffer pinning 발생 / latch 점유율이 적다.
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_NAME">=' ')
3 - access("OBJECT_ID">=0)
#) 힌트 사용: /*+ gather_plan_statistics index(c c_obj_name_idx)*/
select /*+ gather_plan_statistics index(c c_obj_name_idx)*/ count(*)
from c_table c
where object_id >= 0
and object_name >= ' ';
COUNT(*)
----------
68178
#) 실행계획 확인
select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID 78hphzt12rd5j, child number 0
-------------------------------------
select /*+ gather_plan_statistics index(c c_obj_name_idx)*/ count(*)
from c_table c where object_id >= 0 and object_name >= ' '
Plan hash value: 586128974
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.03 | 62167 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.03 | 62167 |
|* 2 | TABLE ACCESS BY INDEX ROWID| C_TABLE | 1 | 68178 | 68178 |00:00:00.04 | 62167 |
|* 3 | INDEX RANGE SCAN | C_OBJ_NAME_IDX | 1 | 68178 | 68178 |00:00:00.01 | 354 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID">=0)
3 - access("OBJECT_NAME">=' ')
#) 옵티마이저에서 선택하는 방법
select index_name, blevel, leaf_blocks, clustering_factor
from user_indexes
where table_name = 'C_TABLE';
INDEX_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
------------------------------ ---------- ----------- -----------------
C_OBJ_IDX 1 152 985 ★ 작은 것을 선택
C_OBJ_NAME_IDX 2 352 61813
'Data Base > SQL 튜닝' 카테고리의 다른 글
B-Tree 구조, Bitmap 구조 인덱스, session_cached_cursors (0) | 2024.02.17 |
---|---|
row chaining, row migration (1) | 2024.02.17 |
Serial direct read (1) | 2024.02.17 |
v$sql_plan (0) | 2024.02.14 |
auto trace _ set autotrace on, set autotrace on explain, set autotrace on statistics, set autotrace traceonly, set autotrace traceonly explain, set autotrace traceonly statistics (1) | 2024.02.14 |