클러스터링 팩터(CLUSTERING FACTOR)

클러스터링 팩터(CLUSTERING FACTOR)
: 인덱스에 저장된 순서와 테이블에 저장된 데이터의 저장 순서가 얼마나 일치하는지를 나타내는 값
: 최대값은 인덱스에 저장된 항목의 개수
: 최소값은 테이블 블록의 개수

 

 

 

클러스터링 팩터가 좋은 경우
: 인덱스의 정렬 순서와 테이블에 저장된 행의 저장 순서가 일치하는 경우

 

 

클러스터링 팩터가 나쁜 경우
: 인덱스의 정렬 순서와 테이블에 저장된 행의 저장 순서가 일치되지 않는 경우

 

 

 

 

ex) 클러스터링 팩터가 좋은 경우, 읽게 되는 블록수 확인

CREATE TABLE CUST_GOOD_CF
AS SELECT * FROM CUSTOMERS
ORDER BY CNAME;


CREATE INDEX CUST_GOOD_CF_IDX ON CUST_GOOD_CF(CNAME);


SELECT /*+ gather_plan_statistics INDEX(C CUST_GOOD_CF_IDX) */ * 
FROM CUST_GOOD_CF C
WHERE CNAME BETWEEN 'A' AND 'B';

 

#[PLAN]

SQL_ID  2p3jwgxymh3g1, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics INDEX(C CUST_GOOD_CF_IDX) */ *        
 FROM CUST_GOOD_CF C        WHERE CNAME BETWEEN 'A' AND 'B'
 
Plan hash value: 2715322676
 
-----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name             | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                  |      1 |        |       |   202 (100)|          |    375 |00:00:00.01 |     259 |    132 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| CUST_GOOD_CF     |      1 |    298 |   592K|   202   (0)| 00:00:01 |    375 |00:00:00.01 |     259 |    132 |
|*  2 |   INDEX RANGE SCAN                  | CUST_GOOD_CF_IDX |      1 |    298 |       |   102   (0)| 00:00:01 |    375 |00:00:00.01 |     131 |    132 |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("CNAME">='A' AND "CNAME"<='B')

#[Plan 설명]

[Id=2], CUST_GOOD_CF_IDX 인덱스에서 375건을 읽음

[Id=1], 150개의 테이블 블록을 읽음 (128=259-131)

 

 

 

 

 

 

ex) 클러스터링 팩터가 나쁜 경우, 읽게 되는 블록수 확인

CREATE TABLE CUST_BAD_CF
AS SELECT * FROM CUSTOMERS
ORDER BY DBMS_RANDOM.VALUE;

CREATE INDEX CUST_BAD_CF_IDX ON CUST_BAD_CF(CNAME);

SELECT /*+ gather_plan_statistics INDEX(C CUST_BAD_CF_IDX) */ * 
FROM CUST_BAD_CF C
WHERE CNAME BETWEEN 'A' AND 'B';

 

[PLAN]

SQL_ID  4bs8714rjjh4c, child number 0
-------------------------------------
     SELECT /*+ gather_plan_statistics INDEX(C CUST_BAD_CF_IDX) */ *    
  FROM CUST_BAD_CF C     WHERE CNAME BETWEEN 'A' AND 'B'
 
Plan hash value: 11224858
 
----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name            | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                 |      1 |        |       |   400 (100)|          |    375 |00:00:00.01 |     506 |    132 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| CUST_BAD_CF     |      1 |    298 |   592K|   400   (0)| 00:00:01 |    375 |00:00:00.01 |     506 |    132 |
|*  2 |   INDEX RANGE SCAN                  | CUST_BAD_CF_IDX |      1 |    298 |       |   102   (0)| 00:00:01 |    375 |00:00:00.01 |     131 |    132 |
----------------------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("CNAME">='A' AND "CNAME"<='B')

#[PLAN 설명]

[Id=2], CUST_BAD_CF_IDX 인덱스에서 375건을 읽음

[Id=1],  375개의 테이블 블록을 읽음(375=506-131)

 

 

 

 

클러스터링 팩터 조회
: 통계를 수집하여 확인

 

 

EXEC DBMS_STATS.GATHER_TABLE_STATS('TUNING', 'CUST_GOOD_CF', CASCADE=>TRUE, ESTIMATE_PERCENT=>100);
EXEC DBMS_STATS.GATHER_TABLE_STATS('TUNING', 'CUST_BAD_CF', CASCADE=>TRUE, ESTIMATE_PERCENT=>100);


SELECT A.TABLE_NAME, A.INDEX_NAME, A.CLUSTERING_FACTOR,
           A.NUM_ROWS, B.BLOCKS
FROM USER_INDEXES A, USER_TABLES B
WHERE A.TABLE_NAME = B.TABLE_NAME
AND A.INDEX_NAME IN ('CUST_GOOD_CF_IDX', 'CUST_BAD_CF_IDX');

 

TABLE_NAME	INDEX_NAME	CLUSTERING_FACTOR	NUM_ROWS	BLOCKS
CUST_BAD_CF	CUST_BAD_CF_IDX	9998	10000	3396
CUST_GOOD_CF	CUST_GOOD_CF_IDX	3334	10000	3396

 

##

클러스터링 팩터가 좋을 수록 테이블 블록 개수에 근접하며, 

클러스터링 팩터가 나쁠 수록 인덱스 내의 행의 개수와 근접한다. 

 

 

클러스터링 팩터를 이용하여 튜닝하는 사례는 거의 없다. 

특정 인덱스의 순서로 테이블을 재구축하여 클러스터링 팩터를 향상시키면, 그 외의 인덱스에 대해서는 오히려 클러스터링 팩터가 나빠지기 때문

 

 

=====================================================================

여태까지 튜닝을 진행하면서 한군데 에서만 클러스터링 팩터를 건드려 튜닝한 경험이 있다.

그 이유는, 스토리지(hdd) 속도가 나오지 않아 디스크 읽는 시간이 너무 오래 걸려 filter 처리 과정에서 너무 많은 소요시간을 잡는 경우 였다.  결국, 스토리지 에서 나오는 속도가 한계가 있어 서비스에서 사용자가 가장 많이 사용하는 화면단의 SQL을 추출하여 특정 테이블을 선정하여, 인덱스 사용빈도를 체크 후 테이블을 재생성하는 과정을 거쳐 성능 향상을 얻게 되었다.  (인덱스 또한 많이 걸려있어 추가로 인덱스를 잡아주기에는 무리)

 

아래는 특정 테이블 중 하나를 작업했던 내용을 적었다. 

 

# 작업 순서

1. 사용자가 가장 많이 사용하는 서비스 화면단 확인

2. 화면단의 SQL 모두 추출

3. 해당 SQL에서 사용되는 테이블 목록 우선순위 선정

4. 테이블에 생성되어 있던 인덱스 COST 확인

5. Column order by 선정

6. 테이블 재생성

7. 통계정보 수집 

8. 클러스터링 팩터 비교

9. AS-IS 테이블 <-> TO-BE 테이블 Rename

10. 서비스단 속도 비교

 

 

## EMR.MRIMESPI 테이블에 생성된 인덱스 COST 확인

OWNER	INDEX_NAME	SUM(COST)
EMR	IX_MRIMESPI_03	6638
EMR	IX_MRIMESPI_01	24
EMR	IX_MRIMESPI_02	24
EMR	PK_MRIMESPI	4

 

## Order by 순서 선정

 >> INSTCD, PID, ORDDD, CRETNO

 

## 테이블 재생성

MRIMESPI_BAK = 기존 테이블

MRIMESPI = 재생성 테이블

OWNER	TABLE_NAME	ROWS	BLOCKS	ANALYZED
EMR	MRIMESPI	10882480	286721	2020-10-04 23:13
EMR	MRIMESPI_BAK	11414085	296980	2020-11-10 19:49

 

## 클러스터링 팩터 확인

INDEX_NAME	기존 CF	재생성 CF	기존 CF%	재생성 CF%	COLUMN_NAME
IX_MRIMESPI_03	6181659	933891	0.04638	0.318	INSTCD ,PID ,ORDDD ,ORDDEPTCD ,SIGNFLAG ,SIGNGENRFLAG ,DISPYN
IX_MRIMESPI_01	5793533	454518	0.04949	0.6534	INSTCD ,PID ,ORDDD ,CRETNO ,SIGNGENRFLAG ,DELDD
IX_MRIMESPI_02	5931408	4600400	0.04834	0.06456	INSTCD, PID, ORDDD, CRETNO , FORMCD, RECDD
PK_MRIMESPI	4251350	6323600	0.06744	0.04696	INSTCD ,SIGNNO
IX_MRIMESPI_04	6276496	715451	0.04568	0.41509	INSTCD ,PID ,ORDDD ,CRETNO ,SIGNGENRFLAG ,DELDDDISPYN