B-Tree 인덱스를 사용하여 단일 테이블을 액세스하는 비용을 계산할 때 옵티마이저가 사용하는 계산식에 알아보자.
인덱스 높이
Sequence-based 의 인덱스인 경우 맨 우측 노드에 추가적인 계층을 양산한다는 공방이 이어지고 있다. 하지만, 이런 일은 실제 발생하지 않는다. 리프 블록이 분할될 때 트리 구조에 변화가 필요하면 그 영향은 위쪽으로 향하며 절대 아래쪽으로 진행하지 않는다. 다시 말하지만, 균형(balanced) B-tree의 모든 리프 블록은 루트로부터 같은 거리를 유지한다.
create table t1 nologging
as
select
trunc(dbms_random.value(0,25)) n1, rpad('x',40) ind_pad, trunc(dbms_random.value(0,20)) n2, lpad(rownum,10,'0') small_vc, rpad('x',200) padding
fromall_obj
ectswhererownum <= 10000 ;
create index t1_i1 on t1(n1, ind_pad, n2)
nologging pctfree 91
*코드 설명* 처음 생성될 브랜치에 PCTFREE가 적용되지 않고, IND_PAD가 모든 로웨 같은 값을 가지므로 전체 통계와 분산도에는 영향을 미치지 않으면서 오라클이 각 브랜치 블록에 적은 수의 로우만을 갖도록 하였음이로 인해 인덱스 높이를 증가시켜 blevel 2로 만들었다.
인덱스에서의 PCTFREE
인덱스에서의 PCTFREE는 단지 인덱스를 생성 및 재생성 할 대, 또는 병합(coalesced)하는 작업에만 관련 있고, 그것은 리프 블록에만 적용된다. 테이블에 대한 PCTFREE 스토리지 파라미터는 오라클에게 해당 블록에 더 이상의 새로운 로우 삽입을 멈춰야 하는 시점을 알려주며, 그렇게 약간의 공간을 확보해 두고 각 블록 안에 있는 로우를 갱신 할 때 사용한다. 그러나 인덱스에 저장된 엔트리는 절대 수정되지 않는다. 인덱스 엔트리를 변경하면 이 엔트리는(대개는) 인덱스 내 다른 어딘가로 옮겨지며, 업데이트할 때 실제로는 Delete & Insert 방식으로 이루어진다. 따라서 인덱스에 대해서는 업데이트를 위한 공간을 예약해 두는 게 아니라 새로운 로우 삽입을 위한 공간을 예약하는 것이다.
TABLE_NAME BLOCKS NUM_ROWS
-------------------- ---------- ----------
T1 371 10000
NUM_ROWS DISTINCT_KEYS BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY
---------- ------------- ---------- ----------- ----------------- ----------------------- -----------------------
10000 500 2 1111 9745 2 19
COLUMN_NAM NUM_NULLS NUM_DISTINCT DENSITY
---------- ---------- ------------ ----------
IND_PAD 0 1 1 N1 0 25 .04 N2 0 20 .05
select small_vc
fromt1
wheren1 = 2
and ind_pad = rpad('x',40)
and n2 = 3 ;
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------
| 0 |SELECTSTATEMENT | | 20 | 1160 | 25 |
| 1 | TABLE ACCESSBYINDEX ROWID| T1 | 20 | 1160 | 25 |
|* 2 | INDEX RANGE SCAN | T1_I1 | 20 | | 5 |
---------------------------------------------------------------------
N1=(constant)(Target : 25개당 1개 로우 = 로우의 4% = 0.04 * 로우 수)
Ind_pad=(constant)(Target : 모든 로우 = 로우의 100% = 1*로우 수)
N2 = (constant)( target : 20개당 1개 로우 = 로우의 5% = 0.05 * 로우 수)
선택도 (X and Y and Z) =
선택도((X and Y) and Z) =
선택도(X and Y) * 선택도(Z) = 선택도(X) * 선택도(Y) * 선택도(Z)
=> 0.04 * 1 * 0.05 = 0.002 ( 이는 3개의 조건절로 쿼리했을 때 전체 인덱스 엔트리 중에서 0.2% 만큼을 방문하게 될 것임을 의미)
select
small_vc
from
t1
where
n1 = 2
and ind_pad = rpad('x',40)
and n2 = 3
and small_vc = '0000000001'
- 위에 처럼 조건절을 하나 더 추가(and small_vc = '0000000001' 추가)할 경우를 보면, 옵티마이저가 현재 생성해 둔 인덱스를 경유해 테이블을 방문하기로 했다면 테이블을 액세스하기 전까지는 마지막에 추가된 이 조건을 체크할 수 없다.
- 이 조건은 테이블을 방문해야 할 데이터 비율에는 미치지 않으며, 최종적으로 턴하는 데이터 비율에만 영향을 미친다.
- 이 사례에서는 테이블에 대한 조건절 모두 인덱스 액세스 단계에서 체크가 가능하므로 유효 테이블 선택도 역시 0.04 * 1* 0.05 = 0.002이다.
인덱스와 클러스터링 팩터
좋은 인덱스는 낮은 클러스터링 팩터를 갖고, 안 좋은 인덱스는 높은 클러스터링 팩터를 갖는다는 상식이 매우 일반화 되었다. 이런 설명은 클러스터링 팩터가 갖는 의미적인 측면에서 분명히 어느정도 진실성이 있다. 하지만,오라클에 대해 얘기할 때 '낮은', '높은', '작은', '큰'과 같은 말, 그리고 '0에 가까운'과 같은 표현에 항상 반감이 생긴다. 10,000이란 수치는 클러스터링 팩터가 과연 낮은 것인가 높은 것인가? 테이블 10,000개 블록을 가졌다면 낮은 것이고, 100개 블록만을 가졌다면 높은 것이다. 그래서 user_tables와 user_indexes를 조인하는 두 개의 작은 스크립트(그리고 파티션 테이블 등을 위한 또 다른 스크립트)를 작성해서 중요한 수치를 비교하기를 원할 수 있다.
브랜치 레벨 (bleve) = 2
유효 인덱스 선택도 = 0.002
리프 블록 수 (leaf_blocks)=1,111
유효 테이블 선택도 = 0.002
클러스터링 팩터(clustering_factor) = 9745
테이블 총 로우수 = 10000
2+ceiling(1, 111 * 0.002) + ceiling(9745 * 0.002)
= 2+ceiling(2.222) + ceiling(19.49)
= 2 + 3 + 20
= 5+20 = 25
*B-tree 인덱스를 사용하는 일반적인 환경은 다음과 같다.
인덱스 재생성
인덱스를 재생성했을 때, 종종 인덱스의 리프 블록 수가 감소하거나 아주 가끔 브랜치 레벨이 감소할 수는 있지만 클러스터링 팩터에는 영향을 주지 않는다. 인덱스를 재생성 했을 때 인덱스 크기가 감소하는 효과 때문에 옵티마이저가 그 인덱스를 더 선호하게 하는 결과를 가져올 수 있다. 그런데 그 효과가 긍정적일 수 있지만 부정적일 수도 있다. 긍정적 측면에서 보면, 인덱스 재생성이 쿼리 시점에 그 인덱스가 캐싱되어 있을 가능성을 크게 하는 이점을 가져 올 수 있다. 반대로 인덱스 크기가 감소해서 생기는 부정적 효과로는, DML 작업시 경합에 의한 불이익을 당할 가능성이 커진다는 점과 인덱스의 균형 상태를 유지하기 위한 '시도'때문에 LEAF BLOCKS SPLITS 증가와 REDO 증가를 불러올 수 있다는 점을 들 수 있다. 모순되는 얘기 같지만, 정기적으로 인덱스를 재생성해 주어야 그 인덱스는 더 이상 인덱스 재생성을 필로 하지 않는다. 옵티마이저가 제대로 작동하길를 원한다면 인덱스가 빈틈없이 압축되도록 만들려고 노력하는 것보다 CLUSTERING_FACTOR가 실제의 데이터 흩어짐 정도를 제대로 표현하는지 여부를 확인하고 그것에 대한 어떤 조치를 취하는 것이 더 중요하다.
select
small_vc
from
t1
where
n1 = 2
and ind_pad = rpad('x',40)
and n2 between 1 and 3
;
----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------
| 0 | SELECT STATEMENT | | 82 | 4756 | 37 |
|* 1 | TABLE ACCESS FULL| T1 | 82 | 4756 | 37 |
----------------------------------------------------------
select
/*+ index(t1) */
small_vc
from
t1
where
n1 = 2
and ind_pad = rpad('x',40)
and n2 between 1 and 3
;
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 82 | 4756 | 93 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 82 | 4756 | 93 |
|* 2 | INDEX RANGE SCAN | T1_I1 | 82 | | 12 |
---------------------------------------------------------------------
8i와 9i 실행계획의 변경
8i는 카디널리티 구할 때 소수점 이하를 항상 정수 값으로 올려 버리지만(ceiling) 9i와 10g 는 가장 가까운 점수 갓으로 올리거나 내린다(round함수 사용)
alter session set "_optimizer_skip_scan_enabled"=false;
select
/*+ index(t1) */
small_vc
from
t1
where
n1 between 1 and 3
and ind_pad = rpad('x',40)
and n2 = 2
;
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 82 | 4756 | 264 (0)| 00:00:04 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 82 | 4756 | 264 (0)| 00:00:04 |
|* 2 | INDEX RANGE SCAN | T1_I1 | 82 | | 184 (0)| 00:00:03 |
-------------------------------------------------------------------------------------
확장된 실행계획 정보
9i는 explain plan 문에서 사용하는 plan_table에 아주 중요한 두 개의 컬럼을 도입했다.
Filter_predicates와 access_predicates가 그것이고, 이는 where 절을 구성하는 조건절들을 옵티마이저가 어디에 어떻게 사용하려는지에 대한 정확한 정보를 제공한다.
인덱스가 효과적으로 사용되지 않는 경우, 실행계획에서 인덱스 라인은 문제점을 아주 분명하게 부각시켜 줄 것이다. Acess_predicates 컬럼은 인덱스의 스캔 범위를 결정하는 데 사용되는 조건절 목록을 보여 줄 것이다. 반면 filter_predicates 컬럼은 리프 블록에 도달한 후에 필터 조건으로 상요될 수 있는 조건절 목록(다시 말해, 유효 인덱스 선택도 계산에 사용될 수 없는 컬럼들)을 나열할 것이다.
alter session set "_optimizer_skip_scan_enabled"=false;
select
/*+ index(t1) */
small_vc
from
t1
where n2 = 2
order by n1
;
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 500 | 8500 | 1601 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 500 | 8500 | 1601 |
|* 2 | INDEX FULL SCAN | T1_I1 | 500 | | 1113 |
---------------------------------------------------------------------
Cost =
2+(1*1111) + (0.05 * 9745) =
2+1111 + 487.25 = --round() or ceil() ?
1113 + 488 = 1601 - ceil 적용
select
/*+ index(t1) */
n2
from
t1
where n1 between 6 and 9
order by n1
;
----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------
| 0 | SELECT STATEMENT | | 2050 | 12300 | 230 |
|* 1 | INDEX RANGE SCAN| T1_I1 | 2050 | 12300 | 230 |
----------------------------------------------------------
작은 테이블에 생성한 인덱스
작은 테이블에 생성한 인덱스와 통계 정보를 다루는 전략은 매우 중요한 문제이다. 일부 작은 인덱스들은 주기적인 재성성이 필요한 것처럼 보이게 할 수 있기 때문이다. Blevel이 1에서 2로 전환되기 직전 상태에 놓인 인덱스에 통계 정보를 계속해서 수집하다 보면 하나의 추가적인 로우 삽입으로 인해 어느 순간 blevel이 1에서 2로 바뀔수 있다. 데이터 사이즈 또는 인덱스 블록 수에는 그다지 큰 변화가 없지만 비용 계산 결과가 갑자기 높아지는 원인이 된다.
이런 비정상적인 상태에 놓인 인덱스들이 있을 때 선택할 수 있는 첫 번째 옵션은, 통게 정보 수집을 멈추는 것이다. 두 번째, 통계 정보를 수집한 후에 수작업으로 blevel을 설정해 주는 것이다. 세번째는, 통계 정보를 수집하기 전에 매번 인덱스를 재생성하는 것이다.