2024년 2월 14일 5교시 + 6교시

 

4교시에 했던 내용

HR@ora11g> create table c_table nologging as select * from all_objects order by object_id;

Table created.

nologging: 리두가 생기지 않도록 함

 

인덱스 만들기 1

HR@ora11g> create index c_obj_idx on c_table(object_id);

Index created.

 

인덱스 만들기 2

HR@ora11g> create index c_obj_name_idx on c_table(object_name);
Index created.

 

HR@ora11g> select num_rows, blocks, avg_row_len from user_tables where table_name = 'C_TABLE';

  NUM_ROWS     BLOCKS AVG_ROW_LEN
---------- ---------- -----------

 

지금은 결과가 하나도 안보이지만

 

통계 수집을 하고 나서

HR@ora11g> execute dbms_stats.gather_table_stats('hr','c_table')

PL/SQL procedure successfully completed.

 

위의 문장을 다시 실행하면: 결과가 보인다.

HR@ora11g> select num_rows, blocks, avg_row_len from user_tables where table_name = 'C_TABLE';

  NUM_ROWS     BLOCKS AVG_ROW_LEN
---------- ---------- -----------
     68186       1009          98   --- 한 행의 바이트 수

 

이어서...

 

HR@ora11g> 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

 

- BLEVEL: 뿌리블록에서 잎블록까지 도달하기 위해 필요한 I/O 수

blevel 이 1 이면 = 뿌리블록에서 잎블록까지 I/O 가 1번 (뿌리 > 잎 = 1번 / 뿌리에서 가지 안거치고 바로 잎으로 감)

blevel 이 2 이면 = 뿌리블록에서 잎블록까지 I/O 가 2번 (뿌리 > 가지 > 잎 = 2번)

만약 blevel 값이 매우 높으면 (뿌리 > 가지 > 가지 > ... > 가지 > 잎)

= 인덱스를 재구성해야 한다. (index rebuild)

- LEAF_BLOCKS : 잎블록의 수

- CLUSTERING_FACTOR : 클러스터링 팩터, 군집도

 

잎 블록

row	rowid
1 2 3 4 5 6	# A # A # A # A # A # B

 

rowid 를 가지고 row 가 있는 블록에 액세스할 때:

1번 row 가 있는 블록 A 로 액세스하면 > clustering factor 값 1 (clustering factor는 1이 기본값)

2번 row 가 있는 블록 A 로 또 액세스하면 > 클러스터링 팩터 값은 변하지 않고 그대로

3번 row, 4번 row, 5번 row 에 액세스할 때도 마찬가지...

 

현재 rowid 블록주소와 다음 rowid 블록주소가 같으면 : 클러스터링 팩터 값은 증가하지 않는다.

 

그런데 만약

 

잎 블록

row	rowid
1 2 3 4 5 6	# A # B # A # B # A # B

 

1번 row 가 있는 블록 A 로 액세스 > clustering factor 값은 1 이 되고 (기본값)

1번 row 가 있는 블록과 2번 row 가 있는 블록이 다르다 > 클러스터링 팩터 값 1 증가 (총 2)

2번 row 가 있는 블록과 3번 row 가 있는 블록이 또 다르다 > 클러스터링 팩터는 또 1 증가 (총 3)

... 이런 식으로 6번 row 까지 액세스가 끝나면 > 클러스터링 팩터 값은 총 6

> 전체 row의 수와 클러스터링 팩터 값이 같다 (= 값이 비슷할수록 성능이 매우 나쁘다는 뜻)

버퍼 피닝 발생하지 않음

 

HR@ora11g> select /*+ gather_plan_statistics index(c c_obj_idx) */ count(*) from c_table c where object_id >= 0 and object_name >= ' ';

  COUNT(*)
------------------
     68186

 

HR@ora11g> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

실행계획 보기

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  9fvtbbtgh7qah, 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.02 |    1138 |
|   1 |  SORT AGGREGATE              |           |      1 |     1 |       1 |00:00:00.02 |    1138 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| C_TABLE   |      1 |  68186 |  68186 |00:00:00.03 |    1138 |
|*  3 |    INDEX RANGE SCAN          | C_OBJ_IDX |      1 |  68186 |  68186 |00:00:00.01 |     153 |
------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
-----------------------------------------------------------------------------------------------------------------------------------
   2 - filter("OBJECT_NAME">=' ')
   3 - access("OBJECT_ID">=0)

22 rows selected.

 

1138-153 = 985 = 클러스터링 팩터 값과 똑같다.

따라서 클러스터링 팩터가 좋다 = 버퍼 피닝이 걸리면 I/O 가 좋아질 수 있다.

(특히 범위 스캔을 할 때)

 

그런데 클러스터링 팩터가 나쁘면 : 성능이 매우 나빠진다.

 

HR@ora11g> select /*+ gather_plan_statistics index(c c_obj) */ count(*) from c_table c where object_id >= 0 and object_name >= ' ';

 

  COUNT(*)
----------
     68186

 

HR@ora11g> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));
실행계획 보기

 

 

 

★ 군집도 (클러스터링 팩터 clustering factor)

 

- 특정 컬럼을 기준으로, 서로 다른 행들의 데이터가 같은 블록에 모여 있는 정도

- 클러스터링 팩터가 좋은 컬럼에 인덱스를 사용하면 I/O를 줄일 수 있다.

   (버퍼 피닝이 돌아가기 때문에 래치 점유 시간이 없을 수 있다.)

- 클러스터링 팩터를 좋게 하려면 : 재구성을 하거나 / 파티셔닝을 해야 한다.

(현장에서는 보통 파티션 작업을 많이 한다)

- 클러스터링 팩터 계산하기

   1. 카운트 변수 선언하기

   2. 잎블록을 처음부터 끝까지 스캔하기

   3. 스캔하면서 rowid 로부터 블록 번호를 찾고

   4. 찾은 블록 주소가 바로 직전의 블록 주소와 다를 때마다 카운트 변수 1 증가

   5. 이 작업을 끝까지 수행하면 > 카운트 변수에 있는 값을 클러스터링 팩터 인덱스 통계정보에 저장한다.