7. 인덱스 스캔 효율

조회 수 6971 추천 수 0 2011.03.10 18:15:26
휘휘 *.10.12.4

 

07. 인덱스 스캔효율

 

■ I/O 튜닝의 핵심은 다음 2 가지이다.

1. Sequential Access의 선택도(selectivity)를 높인다.

(n개의 블록을 읽었을 때 각 블록에 평균적으로 답이 되는 튜플이 많도록 한다.)

2. Random Access 발생량을 줄인다.

(블록의 절대적인 읽는 수를 줄인다.)

 

* 즉, 블록의 수를 적게 읽고 읽은 블록 내에 튜플이 많이 존재해야한다?(당연한 애기)

그럼 답이 되는 튜플이 1개이건 n개이건 블록을 적게 읽도록 하고, 답이 되는 튜플을 같은 블록에 모아 저장하여야 한다. 질의가 어떻게 들어올지 알면 당연하지만 글쎄...

 

* Jonathan Lewis의 유효인덱스 선택도/유효 테이블 선택도 정의 : 답을 얻기 위해 방문 예상되는 인덱스/테이블의 비율로 유효 테이블 선택도가 높으면 안 좋다. 교재의 선택도는 Lewis의 정의가 약간 다르다)

(참고) 두리누리 CBO study 4장

http://www.bysql.net/?mid=w201001&entry=4.%20%EB%8B%A8%EC%88%9C%20B-tree%20%EC%95%A1%EC%84%B8%EC%8A%A4

 

■ Sequential Access의 선택도(selectivity)를 높이는 방법

 

(1) 비교 연산자 종류와 컬럼 순서에 따른 인덱스 레코드의 군집성

인덱스 레코드의 성질

- 인덱스 레코드는 '같은 값을 갖는' 레코드들이 항상 서로 군집해 있음('=' 비교가 전제)

 

<p.133 그림 1-37 참조> 

예 1

모두 = 조건일 경우

where col1 = 1 and col2 = 'A' and col3 = '나' and col4 = 'a'

인덱스 구성 컬럼이 모두 '=' 조건으로 비교될 때는 조건을 만족하는 레코드들이 모두 연속되게 모여 있다.(5~7번까지 모여있음)

예 2

마지막 조건이 범위조건일 경우

where col1 = 1 and col2 = 'A' and col3 = '나' and col4 >= 'a';

선행 컬럼은 모두 '='이고 맨 마지막 컬럼만 범위검색 조건(부등호, between, like)일 때도 조건을 만족하는 레코드가 모두 모여 있다(5~10번까지 모여있음)

예 3

마지막에서 두 번째가 범위조건일 경우

where col1=1 and col2 = 'A' and col3 between '가' and '다' and col4 = 'a'

중간 컬럼이 범위검색 조건일 때는 col1부터 col3까지 세 조건만을 만족하는 인덱스 레코드는 서로 모여 있지만(2~12번) col4 조건까지 만족하는 레코드는 흩어지게 된다(2,3 * 5,6,7 * 11번)

예 4

마지막에서 세 번째가 범위조건일 경우

where col1=1 and col2 <='B' and col3 = '나' and col4 between 'a' and 'b'

두 번째 컬럼 col2가 범위검색 조건인 경우는 col1부터 col2가지 두 조건만을 만족하는 인덱스 레코드는 서로 모이지만(2~16번) col3과 col4 조건까지 만족하는 레코드는 흩어지게 된다.(5,6,7,8,9 * 14,15번)

예 5

첫 번째가 범위조건일 경우

where col1 between 1 and 2 and col2 ='A' and col3 = '나' and col4='a';

첫 번째 나타나는 범위검색 조건까지만 만족하는 인덱스 레코드는 모두 연속되게 모여 있지만, 그 이하 조건까지 만족하는 레코드는 비교 연산자 종류에 상관없이 흩어진다.

 

(2) 인덱스 선행 컬럼이 등치(=) 조건이 아닐 때 발생하는 비효율

- 인덱스 선행 컬럼이 조건에 누락되거나 between, 부등호, like 같은 범위검색 조건이

   사용되면 인덱스를 스캔하는 단계에서 비효율이 발생.

(질의)

select 해당층, 평당가, 입력일, 해당동, 매물구분, 연사용일수, 중개업소코드

from 매물아파트매매

where 아파트시세코드='A0101450900056'  and 평형 = '59' and 평형타입 = 'A' and 인터넷매물 between '1' and '2'

order by 입력일 desc

 

예 1

인덱스 => 아파트시세코드 + 평형 + 평형타입 + 인터넷매물

- 인덱스매물이 between 조건이지만 선행 컬럼들이 모두 '=' 조건이기 때문에 전혀 비효율 없이 조건을 만족하는 2건을 빠르게 찾았다.

<p.135 그림 1-38 참조>

- 인덱스 선행 컬럼이 모두 '=' 조건일 때 필요한 범위만 스캔하고 멈출 수 있는 것은, 조건을 만족하는 레코드가 모두 한데 모여 있기 때문.

예 2

비효율적

인덱스 => 인터넷매물 + 아파트시세코드 + 평형 + 평형타입

- 인덱스 선두 컬럼 인터넷매물에 between 연산자를 사용하면 나머지 조건을 만족하는 레코드들이 인터넷 매물 값 범위로 뿔뿔이 흩어져 있다. 조건을 만족하지 않는 레코드까지 스캔하고서 버려야 하는 비효율.

<p.136 그림 1-39 참조>

 

(3) BETWEEN 조건을 IN-List로 바꾸었을 때 인덱스 스캔 효율

- 선행컬럼이 범위검색이면 어떻게 해결하는가?

=> 범위검색 컬럼이 선행컬럼일 경우 IN-List를 사용하면 효과가 있다.

 

예 1

IN-LIST

(예 1) 인덱스 => 인터넷매물 + 아파트시세코드 + 평형 + 평형타입

select 해당층, 평당가, 입력일, 해당동, 매물구분, 연사용일수, 중개업소코드

from 매물아파트매매

where 아파트시세코드='A0101450900056'

             and 평형 = '59' and 평형타입 = 'A' and 인터넷매물 in ('1', '2')

order by 입력일 desc

 

- 수직적 탐색이 두 번 발생 <p.137 그림 1-40>

- 실행계획은 INLIST ITERATOR가 사용된다.

예 2

RBO의 경우

- RBO에서는 이 경우 union all로 바꾸어 실행하고 실행계획은 CONCATENATION을 사용한다.

    <p.138> 질의예 => 효율적이지는 않다.

예 3

NL 방식 사용

- IN-List 항목 개수가 늘거나 줄 수 있다면 NL 방식의 조인이나 서브쿼리로 구현

   (IN-List 값들을 코드 테이블로 관리할 경우)

 

select /*+ ordered use_nl(b) */ b.해당층, b.평당가, b.입력일, b.해당동, b.매물구분, b.연사용일수, b.중개업소코드

from 통합코드 a, 매물아파트매매 b

where A.코드부분=‘CD064' -- 인터넷매물구분

          and a.코드 between '1' and '2' and b.인터넷매물=a.코드

          and b.아파트시세코드='A0101450900056'

          and b.평형 = '59' and b.평형타입 = 'A'

order by 입력일 desc

 

▣ between 조건을 IN-List 조건으로 바꿀 때 주의사항

- IN-List 개수가 많지 않아야 한다.

   (필요없는 범위를 스캔하는 비효율은 사라지지만 인덱스 수직 탐색이 여러 번 발생)

- 인덱스 스캔 과정에서 선택되는 레코드들이 서로 멀리 떨어져 있을 때만 유리

 

(4) Index Skip Scan을 이용한 비효율 해소

인덱스 선두 컬럼이 누락됐을 뿐만 아니라 부등호, between, like 같은 범위검색 조건일 때도

Index Skip Scan이 유용하게 사용될 수 있다.

 

(실험데이터 생성)

- 월별로 10만 개 판매데이터 입력,  'A'가 10만개, 'B'가 110만개

 

create table 월별고객별판매집계

as

select rownum 고객번호, '2008' || lpad(ceil(rownum/100000), 2, '0') 판매월,

           decode(mod(rownum, 12), 1, 'A', 'B') 판매구분, round(dbms_random.value(1000,100000), -2) 판매금액

from dual

connect by level <= 1200000 ;

 

예 1

(예 1) 선행조건 =, 후행조건 between

create index 월별고객별판매집계_IDX1 on 월별고객별판매집계(판매구분, 판매월);

 

select count(*)

from 월별고객별판매집계 t

where 판매구분 = 'A'  and 판매월 between '200801' and '200812';

 

Rows Row Source Operation

------- ---------------------------------------------------

0  STATEMENT

1  SORT AGGREGATE (cr=281 pr=269 pw=0 time=95791 us)

100000  INDEX RANGE SCAN 월별고객별판매집계_IDX1 (cr=281 pr=269 pw=0 time=...)

 

=> IDX1 인덱스를 스캔하면서 281개 블록 I/O 발생 

예 2

(예 2) 선행조건이 between, 후행조건 =

create index 월별고객별판매집계_IDX2 on 월별고객별판매집계(판매월, 판매구분);

 

select /*+ index(t 월별고객별판매집계_IDX2) */ count(*)

from 월별고객별판매집계 t

where 판매구분 = 'A'  and 판매월 between '200801' and '200812';

 

Rows Row Source Operation

------- ---------------------------------------------------

0 STATEMENT

1 SORT AGGREGATE (cr=3090 pr=3100 pw=0 time=981731 us)

100000 INDEX RANGE SCAN 월별고객별판매집계_IDX2 (cr=3090 pr=3100 pw=0 time=...)

 

=> IDX2 인덱스를 스캔하면서 3,090개 블록 I/O가 발생

예 3

(예 3) 선행조건 IN-LIST, 후행조건 =

select /*+ index(t 월별고객별판매집계_IDX2) */ count(*)

from 월별고객별판매집계 t

where 판매구분 = 'A' and 판매월 in ( '200801', '200802', '200803', '200804', '200805', '200806'

, '200807', '200808', '200809', '200810', '200811', '200812' ) ;

 

Rows Row Source Operation

------- ---------------------------------------------------

0 STATEMENT

1 SORT AGGREGATE (cr=314 pr=2 pw=0 time=115212 us)

100000 INLIST ITERATOR (cr=314 pr=2 pw=0 time=800042 us)

100000 INDEX RANGE SCAN 월별고객별판매집계_IDX2 (cr=314 pr=2 pw=0 time=...)

 

=> between 조건을 IN-List로 변환, 블록 I/O 개수가 314개로 감소

      인덱스 브랜치 블록을 10번 반복 탐색하는 비효율이 있긴 하지만 리프 블록을

       스캔할 때의 비효율을 제거, 1/10 수준으로 성능 개선

예 4

(예 4) 선행조건 between, 후행조건 =, index skip scan 사용

select /*+ INDEX_SS(t 월별고객별판매집계_IDX2) */ count(*)

from 월별고객별판매집계 t

where 판매구분 = 'A' and 판매월 between '200801' and '200812' ;

 

Rows Row Source Operation

------- ---------------------------------------------------

0 STATEMENT

1 SORT AGGREGATE (cr=300 pr=0 pw=0 time=120292 us)

100000 INDEX SKIP SCAN 월별고객별판매집계_IDX2 (cr=300 pr=0 pw=0 time=...)

 

=> 큰 비효율 없이 단 300 블록만 읽었다.

 

(전체 비교)

 

예 1

예 2

예 3

예 4

블록 I/O

281

3090

314

300

(실험결과) 첨부파일 1-13.hwp 참조 1-13.hwp

 

 

(5) 범위검색 조건을 남용할 때 발생하는 비효율

like 조건의 경우 : like도 between일 때와 기본원리는 같고, 다만 스캔 범위에 약간의 차이가 생긴다.

 

(문제 설명)

- 회사, 지역, 상품명을 입력하여 '가입상품' 테이블에서 데이터를 조회하는 프로그램.

   인덱스 : 회사 + 지역 + 상품명

1. 회사는 반드시 입력

2. 지역은 입력하지 않을 수도 있다.

3. 상품명은 단어 중 일부만 입력하고도 조회 가능

 

예 1

(예제 1) 회사, 지역, 상품명 모두 입력, 스캔 범위  <p.144 그림 1-42>

SELECT ....

FROM 가입상품

WHERE 회사 = :com AND 지역 = :reg AND 상품명 LIKE :prod || '%'

예 2

(예제 2) 회사, 상품명만 입력, 스캔 범위  <p.144 그림 1-42> - 스캔범위가 넓음

SELECT ....

FROM 가입상품

WHERE 회사 = :com AND 상품명 LIKE :prod || '%'

예 3

(예제 3) 두 질의를 하나의 질의로 작성

SELECT ....

FROM 가입상품

WHERE 회사 = :com AND 지역 LIKE :reg || '%' AND 상품명 LIKE :prod || '%'

 

- 회사, 지역, 상품명이 모두 입력했을 때와 지역을 입력하지 않았을 때의 스캔 범위

    <p.145 그림 1-43>

- 지역을 입력한 경우 인덱스 스캔 범위가 늘어난다.

   즉, - 인덱스 컬럼에 범위검색 조건을 남용하면 첫 번째 범위검색 조건에 의해 스캔 범위가 대부분 결정.

   그 다음을 따르는 조건부터는 스캔 범위를 줄이는 데에 크게 기여하지 못한다.

▣ 튜닝사례

- 범위조건 질의를 의도적으로 범위 값을 변화시켜 여러 개의 질의로 나누어 실행.

질의

- 운행 중인 택시들로부터 10초마다 송신돼 온 위치정보를 데이터베이스에 저장

- 고객으로부터 콜(Call)이 오면 반경 1Km 이내의 가장 가까운 택시에게 신호를 보내는 시스템

- 인덱스 : gis데이터_x01 : gis_위도 + gis_경도 + gis_시작일자 + gis_종료일자 + gis_지역코드 + gis_위치명

 

select *

from gis데이터

where gis_위도 betwwen :승객위도 - 1 and :승객위도 + 1

           and gis_경도 between :승객경도 - 1 and :승객경도 + 1

           and sysdate between gis_시작일자 and gis_종료일자

(문제점)

- 조건절이 모두 between 범위검색 조건

- 인덱스 스캔 범위는 인덱스 선두 컬럼인 'gis_위도' 컬럼에 대한 between 조건에 의해 결정,

   gis데이터 테이블에는 당일치만 보관, 당일 영업 시작시에는 빠르지만 밤 시간에는

   고객의 특정 위치 기준으로 위도상 좌우 1km 이내에 평균 100만 개 레코드.

- 인덱스 스캔 원리상 현재 데이터 모델로는 과도한 인덱스 스캔 범위를 줄일 방법은 없다.

(튜닝)

- 1km 이내를 한 번에 조회하지 말고 50m, 200m, 1km 순으로 나눠서 질의

- 가장 가까운 데 위치한 하나의 택시를 찾는게 목적, 세 구간으로 나누어 질의하면

   대부분 첫 번째 질의 에서 찾게 된다. 인덱스 스캔량을 1/20로 줄일 수 있다.

- 50m, 200m, 1km 이내의 가장 가까운 택시를 찾을 때 위의 질의의 1을 0.05, 0.2, 1 로 바꾸어 질의,

   추가로 최종 건수 한 건만을 우편번호와 조인하도록 질의 변경.

  (6) 같은 컬럼에 두 개의 범위검색 조건 사용 시 주의 사항

      같은 컬럼에 두 개의 범위검색조건을 사용할 경우 문제

 

(예제) 도서 조회용 프로그램 개발, '오라클'을 키워드로 입력하면, '오라클'로 시작하는 모든 도서가 조회, 한 화면에 10개씩 출력 => 실험결과파일(1-14.hwp) 1-14.hwp

select *

from (

      select rownum rnum, 도서번호, 도서명, 가격, 저자, 출판사, isbn

      from (

             select 도서번호, 도서명, 가격, 저자, 출판사, isbn

             from 도서

             where 도서명 like :book_nm || '%'

       order by 도서명

       )

       where rownum <= 100

)

where rnum >= 91  --> 10 페이지만 출력   

 

(예제) 도서명 컬럼에 인덱스가 있다면 첫 번째 rownum 조건에 해당하는 레코드만 읽고 멈춘다.

   count(stopkey)오퍼레이션 적용, 사용자들이 앞쪽 일부 레코드만 주로 볼 경우 효과적

- 뒤쪽 어느 페이지로 이동하더라도 빠르게 조회되도록 구현해야 한다면?

- 아래는 첫번째 페이지를 출력하고 나서 '다음' 버튼을 누를 경우 예

select *

from (

    select /*+ index(도서 도시명_idx) */ rowid rid, 도서번호, 도서명, 가격, 저자, 출판사, isbn

    from 도서

    where 도서명 like :book_nm || '%'

        and 도서명 = :last_book_nm -- 이전 페이지에서 출력된 마지막 도서명

        and rowid > :last_rid -- 이전 페이지에서 출력된 마지막 도서의 rowid

    union all

    select /*+ index(도서 도시명_idx) */   rowid rid, 도서번호, 도서명, 가격, 저자, 출판사, isbn

    from 도서

    where 도서명 like :book_nm || '%'

        and 도서명 > :last_book_nm -- 이전 페이지에서 출력된 마지막 도서명

)

where rownum <= 10;

Rows Row Source Operation

------- ---------------------------------------------------

0 STATEMENT

10 COUNT STOPKEY (cr=382 pr=0 pw=0 time=218 us)

10 VIEW (cr=382 pr=0 pw=0 time=197 us)

10 UNION-ALL (cr=382 pr=0 pw=0 time=180 us)

1 FILTER (cr=4 pr=0 pw=0 time=141 us)

1 TABLE ACCESS BY INDEX ROWID 도서 (cr=4 pr=0 pw=0 time=108 us)

1 INDEX RANGE SCAN 도시명_IDX (cr=3 pr=0 pw=0 time=137 us)(Object ID 52955)

9 TABLE ACCESS BY INDEX ROWID 도서 (cr=378 pr=0 pw=0 time=56979 us)

9 INDEX RANGE SCAN 도시명_IDX (cr=377 pr=0 pw=0 time=56916 us)(Object ID 52955)

- union all 위쪽 브랜치에서 한건, 아래쪽에서 9건 읽었다.

- 아래쪽 인덱스 스캔단계에서 377 블록을 읽었다.

- 도서명에 대한 범위검색 조건이 두 개인데 그 중 like 조건을 인덱스 액세스 조건으로 사용했기 때문.

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter(ROWNUM<=10)

4 - filter(:LAST_BOOK_NM LIKE :BOOK_NM||'%')

6 - access("도서명"=:LAST_BOOK_NM)

     filter("도서명" LIKE :BOOK_NM||'%' AND ROWID>CHARTOROWID(:LAST_RID))

8 - access("도서명" LIKE :BOOK_NM||'%')

     filter("도서명">:LAST_BOOK_NM AND "도서명" LIKE :BOOK_NM||'%')

- '오라클' 도서를 처음부터 스캔하다가 :last_book_nm 보다 큰 9개 레코드를 찾고서 멈추었다.

- 뒤쪽 페이지로 많이 이동할수록 그 비효율은 커진다.

 

(예제) 해결방안 : '도서명 > :last_book_nm' 조건이 인덱스 액세스 조건으로 사용하도록 한다.

select *

from (

   select /*+ index(도서 도시명_idx) */ rowid rid, 도서번호, 도서명, 가격, 저자, 출판사, isbn

   from 도서

   where 도서명 like :book_nm || '%'

       and 도서명 = :last_book_nm -- 이전 페이지에서 출력된 마지막 도서명

       and rowid > :last_rid -- 이전 페이지에서 출력된 마지막 도서의 rowid

   union all

   select /*+ index(도서 도시명_idx) */ rowid rid, 도서번호, 도서명, 가격, 저자, 출판사, isbn

   from 도서

   where rtrim(도서명) like :book_nm || '%'

              and 도서명 > :last_book_nm   -- 이전 페이지에서 출력된 마지막 도서명

 )

where rownum <= 10;

Rows Row Source Operation

------- ---------------------------------------------------

0 STATEMENT

10 COUNT STOPKEY (cr=7 pr=0 pw=0 time=143 us)

10 VIEW (cr=7 pr=0 pw=0 time=128 us)

10 UNION-ALL (cr=7 pr=0 pw=0 time=117 us)

1 FILTER (cr=4 pr=0 pw=0 time=93 us)

1 TABLE ACCESS BY INDEX ROWID 도서 (cr=4 pr=0 pw=0 time=72 us)

1 INDEX RANGE SCAN 도시명_IDX (cr=3 pr=0 pw=0 time=76 us)(Object ID 52955)

9 TABLE ACCESS BY INDEX ROWID 도서 (cr=3 pr=0 pw=0 time=123 us)

9 INDEX RANGE SCAN 도시명_IDX (cr=2 pr=0 pw=0 time=75 us)(Object ID 52955)

 

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter(ROWNUM<=10)

4 - filter(:LAST_BOOK_NM LIKE :BOOK_NM||'%')

6 - access("도서명"=:LAST_BOOK_NM)

      filter("도서명" LIKE :BOOK_NM||'%' AND ROWID>CHARTOROWID(:LAST_RID))

8 - access("도서명">:LAST_BOOK_NM)

      filter(RTRIM("도서명") LIKE :BOOK_NM||'%')

- 부등호 조건을 만족하는 첫 번째 레코드부터 스캔을 시작했기 때문에 스캔한 블록수는 2.  

(예제) OR-Expansion을 이용하는 방법과 주의사항

- use_concat 힌트를 사용하면 union all을 사용할 때보다 SQL 코딩을 줄인다.

- OR 조건에 대한 expansion(union all 분기)이 일어나면 뒤쪽 조건절이 먼저 실행된다.

select /*+ index(도서 도서명_idx) use_concat ordered_predicates */

            rowid rid, 도서번호, 도서명, 가격, 저자, 출판사, isbn

from 도서

where 도서명 like :book_nm || '%'

    and ((도서명 > :last_book_nm)  or   (도서명 = :last_book_nm and rowid > :last_rid) )

     and rownum <= 10;

- 버전에 따라 실행되는 순서가 달라진다.

   9i까지는 I/O 비용 모델, CPU 비용 모델을 불문하고 뒤쪽에 있는 조건 값을 먼저 실행

   10g CPU 비용 모델에서는 계산된 카디널리티가 낮은 쪽을 먼저 실행

- ordered_predicates 힌트 : 10g에서 값 분포에 상관없이 항상 뒤쪽에 있는 조건식이 먼저 처리

 

▣ rowid를 concatnation하면 결과에 오류 발생

- 아래처럼 작성하면 결과가 달라진다(결과가 틀려진다).

select /*+ index(도서 도서명_idx) */  rowid rid, 도서번호, 도서명, 가격, 저자, 출판사, isbn

from 도서

where 도서명 like :book_nm || '%'

     and 도서명 >= :last_book_nm

     and lpad(도서명, 50) || rowid > lpad(:last_book_nm, 50) || :last_rid

     and rownum <= 10;

=> 내부적으로 다음과 같은 변환이 일어난다.

    and lpad(도서명, 50) || rowidtochar(rowid) > lpad(:last_book_nm, 50) || :last_rid

 

- 문자형으로 변환된 rowid는 rowid 값 그대로 비교할 때와 정렬순서가 다르다.

select greatest('AAAH+WAAJAAAHxTAA9', 'AAAH+WAAJAAAHxTAA+') from dual;

GREATEST('AAAH+WAA

------------------

AAAH+WAAJAAAHxTAA9

select greatest( chartorowid('AAAH+WAAJAAAHxTAA9')

, chartorowid('AAAH+WAAJAAAHxTAA+') ) from dual;

GREATEST(CHARTOROW

------------------

AAAH+WAAJAAAHxTAA+  

 

▣ 인덱스를 스캔하면서 rowid를 필터링할 때 발생하는 비효율

- '이전' 페이지에서 출력된 마지막 레코드와 이름이 같은 도서를 찾는 질의 - 비효율적인 부분을 찾자

  rowid를 가지고 '='조건으로 바로 엑세스할 땐 어떤 액세스보다 빠르지만

 인덱스를 스캔하면서 rowid를 필터링할 때는 아니다.

select /*+ index(도서 도서명_idx) */ rowid rid, 도서번호, 도서명, 가격, 저자, 출판사, isbn

from 도서

where 도서명 like :book_nm || '%'

    and 도서명 >= :last_book_nm

    and rownum > :last_rid;

- 인덱스 rowid는 리프블록에만 있다. 이를 필터링하려면 다른 액세스 조건만으로 리프 블록을 찾아가

  거기서 스캔을 시작해 rowid를 필터링, 도서' 테이블에 같은 도서명을 가진 레코드가 '아주' 많다면

  뒤 페이지로 이동할수록 비효율.

- 인덱스 뒤쪽에 PK컬럼을 붙이고 질의를 수정한다.

 

create index 도서명_idx on 도서(도서명, 도서번호);

select /*+ index(도서 도서명_idx) */  rowid rid, 도서번호, 도서명, 가격, 저자, 출판사, isbn

from 도서

where 도서명 like :book_nm || '%'

    and 도서명 >= :last_book_nm

    and 도서번호 > :last_book_no;

 

- 인덱스 뒤에 PK 컬럼을 붙이기는 쉽지 않다.(PK가 다중 컬럼으로 구성된 경우는 더 복잡),

   중복 값이 아주 많은 경우가 아니라면 rowid를 이용하는 방안이 현실적이다.

(실험) ( 첨부파일 1-15.hwp )    1-15.hwp

 

(7) Between과 Like 스캔 범위 비교

(문제설명) 2개의 질의를 비교, like 가 코딩이 쉽기 때문에 질의 1을 작성하게된다. 그렇지만 ...

- 질의 1 : like 사용

select * from 월별고객별판매집계

where 판매월 like '2009%';

- 질의 2 : between 사용

select * from 월별고객별판매집계

where 판매월 between '200901' and '200912';

- 비교 : 검색을 위해 입력한 값과 테이블의 실제 데이터 상황에 따라 둘 간의 인덱스 스캔량이 다를 수 있다. between을 사용한다면 적어도 손해는 안본다.

 <p.154 그림 1-44>  

 

(실험 1) 세 질의는 결과는 같지만 I/O 발생량에서 큰 차이를 보인다.

실험 1

(샘플데이터 1) Index Skip Scan에서 이용한 '월별고객별판매집계' 테이블에서 2009년 1월과 2월 데이터가 추가로 입력, 판매구분으로는 'A'와 'B' 두 개의 값이 8.3%와 91.7% 비중.

<p.155 그림 1-44>  

예제 질의 1

(제일 좋음)

2009년 1월 데이터는 모두, 2009년 2월 데이터는 판매구분이 'A'인 데이터만 읽는다.

select count(*)

from 월별고객별판매집계

where 판매월 between '200901' and '200902' and 판매구분 = 'A';

예제 질의 2

2009년 1월과 2월 데이터를 모두 읽는다.

select count(*)

from 월별고객별판매집계

where 판매월 like '2009%' and 판매구분 = 'A';

예제 질의 3

'200902' || 'A' 구간만 읽고서 멈추지 못해 결국 like 질의과 같은 스캔량을 보인다.

select count(*)

from 월별고객별판매집계

where 판매월 >= '200901' and 판매월 < '200903' and 판매구분 = 'A';

 

(실험 2)

실험 2

(샘플 데이터 2) 'A'와 'B' 값의 비중이 91.7%와 8.3%로 입력된 상태에서 판매구분이 'B'인 레코드를 찾는 경우, 스캔량의 차이를 크게 나타내 보이려고 바꾸는 것일 뿐 스캔 범위 결정 원리와는 무관

 <p.155 그림 1-45>  

예제 질의 4

2009년 1월 데이터는 B만 읽고, 2009년 2월 데이터는 모두읽는다

select count(*)

from 월별고객별판매집계

where 판매월 between '200901' and '200902' and 판매구분 = 'B';

예제 질의 5

2009년 1월과 2월 데이터를 모두 읽는다.

select count(*)

from 월별고객별판매집계

where 판매월 like '2009%' and 판매구분 = 'B';

예제 질의 6

2009년 1월과 2월 데이터를 모두 읽는다.

select count(*)

from 월별고객별판매집계

where 판매월 between '200900' and '200902' and 판매구분 = 'B';

 

(실험 3)

실험 3

 

예제 질의 7

2009년 1월 데이터 중 B만 읽는다(좋음)

select count(*)

from 월별고객별판매집계

where 판매월 like '200901%' and 판매구분 = 'B';

예제 질의8

2009년 1월 데이터 중 B만 읽는다.

판매구분=‘A'이라면 200902 만나기전까지 스캔을 멈추지 못해 LIKE가 ’=‘나 BETWEEN 조건일 때보다 비효율적이다.

select count(*)

from 월별고객별판매집계

where 판매월 like '200901' and 판매구분 = 'B';

 

▣ 범위검색 조건의 스캔 시작점 결정 원리

- 범위검색 조건 뒤를 따르는 조건절은 스캔 범위를 줄이는 데에 영향을 미칠 수도 있고 그렇지 않을 수도 있다.

   사용자가 조건 비교를 위해 입력한 값이나 테이블의 실제 데이터 상황에 따라 다르다.

    적어도 between이 like보다 더 넓은 범위를 스캔하는 경우는 없다.(between 사용하라)

- 인덱스 컬럼 조건에 대해 범위검색 조건이 나타나면 이후 조건은 인덱스 스캔 범위를 줄여주지 못해 비효율 발생,

   위와 같이 범위검색 조건 뒤에 사용된 조건들도 제한적이나마 스캔 범위를 줄이는데 기여할 수 있다.

 

질의 5

- 질의 5의 like인 경우 판매구분 = 'B' 조건이 스캔 범위를 줄이는데에 기여하지 못했다.

(1) (판매원 = '2009' 이고 판매구분 = 'B')인 레코드를 목표로 수직적 탐색.

(2) 브랜치 블록에서 뒤쪽 엔트리부터 스캔을 시작해 비교 값보다 작은 값을 만나는 순간

      거기서 가리키는 하위 노드로 이동.

(3) 그림 1-45와 같은 상황에서는 '200812' || 'B'인 레코드가 담긴 가장 마지막 리프 블록에 도달.

(4) 만약 그 값이 포함되지 않는다면 다음 리프 블록의 처음 레코드 키 값이 '200901' || 'A'인 경우이다.

(5) 수직 탐색 과정에서 사용한 비교 조건이 '200812' || 'B' 와 '200901' || 'A' 사이 값이므로 이 블록부터 스캔한다면 원하는 값을 모두 읽을 수 있다.

(6) 이 때문에 불필요하게 '2009' || 'A' 구간까지 읽는다.

질의 4

(좋음)

- 질의 4의 between의 경우 여기서는 판매구분 = 'B' 조건이 스캔 범위를 줄이는 데에 큰 기여를 한다. 키 포인트는 실제 테이블에 존재하는 값을 수직적 탐색 조건으로 사용했다는 점이다.

(1) (판매월 = '200901' 이고 판매구분 = 'B')인 레코드를 목표로 수직적 탐색.

(2) 브랜치 블록에서 뒤쪽 엔트리부터 스캔하다가 위 조건보다 작은 값을 만나는 순간

     거기서 가리키는 하위 노드로 이동.

(3) 그러다 보면 그림 1-45에서 '200901' || 'B'인 첫 번째 레코드가 담긴 리프 블록에 도달하거나,

      '200901' || 'A'인 레코드가 담긴 가장 마지막 리프 블록에 도달하게 된다.

질의 6

- 질의 6과 같이 실제 테이블에 없는 데이터 값을 입력하고 조회한 경우에는

(1) '200812' || 'B' 인 레코드가 담긴 가장 마지막 리프 블록부터 스캔을 시작하게 되므로 불필요하게

      '200901' || 'A' 구간까지 읽게 된다.

질의 7

(좋음)

- 질의 7의 like 조건이더라도 실제 테이블에 있는 데이터 값을 입력하고 조회할 때는

   판매구분 = 'B' 조건이 스캔 범위를 줄이는 데에 큰 역할을 한다.

 

(8) 선분이력의 인덱스 스캔 효율

     항상 두 개의 부등호 조건을 함께 사용하는 선분이력에선 데이터 특성상 두 번째 부등호 조건이

     스캔 범위를 줄이는 데 전혀 도움을 주지 못한다.

 

▣ 선분이력이란?

점이력 : 고객의 변경이력을 관리할 때 이력의 시작시점만을 관리하는 것

- PK : 고객번호 + 변경일자

- 질의가 복잡하다

- PK 제약을 설정하는 것만으로 개체 무결성이 완벽히 보장 된다.

선분이력 : 고객의 변경이력을 관리할 때 이력의 시작시점과 종료시점을 관리하는 것

- 시작시점과 종료시점을 관리하는 것

- PK : 고객번호 + 시작일자 + 종료일자(99991231 or 99991231235959)

- 질의가 간단해 진다

- 이력이 추가될 때마다 기존 최종 이력의 종료일자도 같이 변경해 주어야 한다.

- DML 성능이 나빠지며 이력 데이터를 관리하는 프로그램이 복잡해 진다.

- 개체 무결성을 사용자(애플리케이션)에서 직접 관리해 주어야 한다.(에, 질의에서 선분의 중복 문제)

- 선분이 겹치거나 끊어지지 않도록 방지해야 하므로 프로그래밍할 때 동시성 제어 기법을 철저히 적용해야 한다.

- 사용자가 직접 데이터를 입력/수정하는 과정에서 정합성이 깨지는 문제는 근본적인 해결이 불가능하다   

   (트리거를 이용하지 않는 한)

- RDBMS 설계 사상에 맞지 않게 PK 값이 변경된다. PK 제약만으로 개체 무결성이 보장되지 않으니 인덱스

 스캔 효율이라도 높이기 위해 통상 PK 구성에 시작일자와 종료일자가 모두 포함되도록 설계하기 때문.

 (고객번호+시작일자) 또는 (고객번호+종료일자)로 PK를 구성한 상태에서 update/insert 순서를 잘 조절하면

 PK 값이 변경되지 않도록 구현할 수 있지만, 그때는 시작일자와 종료일자를 모두 포함한 인덱스를 별도로 생성해 주어야 한다.

 

▣ 선분이력 기본 조회 패턴

 

 

(예제 1)

임의의 시점 조회, <p.160, 그림 1-46>

select 연체개월수, 연체금액

from 고객별연체금액

where 고객번호 = '123' and  :dt between 시작일 and 종료일;

(예제 2)

현재 시점 조회, <161 그림 1-47>

select 연체개월수, 연체금액

from 고객별연체금액

where 고객번호 = '123' and 종료일=‘99991234’;   /* 종료일을 99991234로 약속 */

(예제 3)

프로그램 실행 시점에 조회할 경우

select 연체개월수, 연체금액

from 고객별연체금액

where 고객번호 = '123' and to_char(sysdate, 'yyyymmdd') between 시작일 and 종료일;

 

시작일 + 종료일 구성일 때 최근 시점 조회

- 인덱스 => 고객번호 + 시작일 + 종료일

(예제 1)  <p.162 그림 1-48>

select *

from 고객별연체금액

where 고객번호 = '123' and '20050131' between 시작일 and 종료일;

=>

select *

from 고객별연체금액

where 고객번호 = '123' and 시작일 <= '20050131' and 종료일 >= '20050131';

 

- 최근시점 조회이기 때문에 index_desc 힌트로 rownum <= 1을 추가하면 더 빠르다.

 

select /*+ index_desc(a idx_x01) */ *

from 고객별연체금액 a

where 고객번호 = '123' and '20050131' between 시작일 and 종료일 and rownum <= 1;

 

시작일 + 종료일 구성일 때 과거 시점 조회

- 인덱스 => 고객번호 + 시작일 + 종료일

(예제)  <p.164 그림 1-49>

select *

from 고객별연체금액

where 고객번호 = '123' and '20020930' between 시작일 and 종료일;

=>

select *

from 고객별연체금액

where 고객번호 = '123' and 시작일 <= '20020930' and 종료일 >= '20020930';

 

- '=' 조건인 고객번호 다음 인덱스 컬럼(시작일)이 '<=' 조건이지만

   2002년 9월 30일 이전의 데이터가 소량이므로 소량 이력 레코드만 스캔.

- 인덱스 스캔량이 얼마 되지 않지만 index_desc 힌트를 주고 rownum <= 1 조건을 추가하여 개선.

   ( 이것 맞나요 ? 과거와 현재 같은 방법이라.. )

 

select /*+ index_desc(a idx_x01) */ *

from 고객별연체금액 a

where 고객번호 = '123' and '20020930' between 시작일 and 종료일 and rownum <= 1;

 

종료일 + 시작일 구성일 때 최근 시점 조회

- 인덱스 => 고객번호 + 종료일 + 시작일

(예제) <p.165 그림 1-50>

select *

from 고객별연체금액

where 고객번호 = '123'

and '20050131' between 시작일 and 종료일;

=>

select *

from 고객별연체금액

where 고객번호 = '123' and 시작일 <= '20050131' and 종료일 >= '20050131';

 

- '=' 조건인 고객번호 다음 인덱스 컬럼(종료일)이 '<=' 조건이므로 2005년 1월 31일 이후 소량 이력 레코드만 스캔.  인덱스 스캔량이 얼마 되지 않지만 rownum <= 1 조건을 추가하여 개선.

 

select *

from 고객별연체금액

where 고객번호 = '123' and '20050131' between 시작일 and 종료일

and rownum <= 1;

 

종료일 + 시작일 구성일 때 과거 시점 조회

- 인덱스 => 고객번호 + 종료일 + 시작일

(예제) <p.166 그림 1-51>

select *

from 고객별연체금액

where 고객번호 = '123' and '20020930' between 시작일 and 종료일;

    =>

select *

from 고객별연체금액

where 고객번호 = '123' and 시작일 <= '20020930' and 종료일 >= '20020930';

 

- '=' 조건인 고객번호 다음 인덱스 컬럼(종료일)이 '<=' 조건이므로 2002년 9월 30일 이후의 많은

  이력 레코드를 모두 스캔. 스캔량해야 할 범위가 아무리 넓더라도 rownum <= 1 조건을 추가하여 개선.

 

select *

from 고객별연체금액

where 고객번호 = '123' and '20050131' between 시작일 and 종료일 and rownum <= 1;

 

▣ 중간 시점 조회

- 중간 시점 이력을 조회할 때는 인덱스 구성을 어떻게 하든 어느 정도 비효율을 감소.

- 인덱스 구성이 (고객번호 + 시작일 + 종료일) 일 경우는 index_desc 힌트 및 rownum <= 1 조건으로 개선.

- 인덱스 구성이 (고객번호 + 종료일 + 시작일) 일 경우는 rownum <= 1 조건으로 개선.

  (예제) <p.167 그림 1-52>

 

▣ 선분이력 스캔 효율을 높이는 방법 요약

- 선분이력처럼 between 검색 조건이 사용될 때는 어느 시점을 주로 조회하느냐에 따라 인덱스 전략을 달리 가져가라.

- 최근 데이터를 주로 조회할 경우는 (종료일 + 시작일)순으로 구성

- 과거 데이터를 주로 조회한다면 (시작일 + 종료일) 순으로 구성

- (시작일 + 종료일) 일 때는 index_desc 힌트와 rownum <= 1 조건을 추가(?)

- (종료일 + 시작일) 일 때는 rownum <= 1 조건만 추가

- 중간지점을 조회할 때도 위의 방식 사용.

- 미래 시점 데이터를 미리 입력하는 경우가 없다면, 현재 시점 데이터를 조회할 때는 (종료일 = '99991231') 조건을 사용하는 것이 효과적.

 

 

(9) Access Predicate 와 Filter Predicate

- access는 제공자, filter는 체크자.

(실험) explain plan 명령을 통해 실행계획을 수집하고 dbms_xplan 패키지(@?/rdbms/admin/utlxpls.sql 참조)를 통해

           실행계획을 확인해 보면 Predicate 정보를 확인할 수 있다.

           10gR2부터는 Autotrace 명령을 통해서도 Predicate 정보를 출력해 볼 수 있다

 

(예제)

create index emp_x01 on emp(deptno, job, sal, ename, mgr, comm);

set autotrace traceonly explain;

 

select /*+ ordered use_nl(e) index(e emp_x01) */ *

from dept d, emp e

where d.loc = 'CHICAGO'

            and e.deptno = d.deptno

            and e.job like 'SALE%'      

            and e.job between 'A' and 'Z'

            and e.sal >= 1000

            and e.ename like '%A%'

            and trim(e.ename) = 'ALLEN'

            and e.comm >= 300

            and to_char(e.hiredate, 'yyyymmdd') like '198102%' ;

(실험결과)

----------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|

----------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 57 | 5 (0)|

|* 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 37 | 2 (0)|

| 2 | NESTED LOOPS | | 1 | 57 | 5 (0)|

|* 3 | TABLE ACCESS FULL | DEPT | 1 | 20 | 3 (0)|

|* 4 | INDEX RANGE SCAN | EMP_X01 | 1 | | 1 (0)|

----------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter(TO_CHAR(INTERNAL_FUNCTION("E"."HIREDATE"),'yyyymmdd') LIKE 198102%')

3 - filter("D"."LOC"='CHICAGO')

4 - access("E"."DEPTNO"="D"."DEPTNO" AND "E"."JOB" LIKE 'SALE%' AND

            "E"."SAL">=1000 AND "E"."COMM">=300 AND "E"."SAL" IS NOT NULL)

    filter("E"."ENAME" LIKE '%A%' AND "E"."JOB" LIKE 'SALE%' AND

             "E"."SAL">=1000 AND TRIM("E"."ENAME")='ALLEN' AND

              "E"."JOB">='A' AND "E"."JOB"<='Z' AND "E"."COMM">=300)  

 

- 인덱스를 경유해 테이블을 액세스할 때는 최대 3가지 Predicate정보가 나타날 수 있다.

1. 인덱스 단계에서의 Access Predicate (id=4 access 부분)

   - 인덱스 스캔 범위를 결정하는데 영향을 미치는 조건절

    (Access Predicate에서 제외되는 부분)

     (1) 좌변 컬럼을 가공한 조건절 : trim(e.ename) = 'ALLEN'

     (2) 왼쪽 '%' 또는 양쪽 '%' 기호를 사용한 like 조건절 : ename like '%A%'

     (3) 같은 컬럼에 대한 조건절이 두 개 이상일 때,

          인덱스 액세스 조건으로 선택되지 못한 다른 조건절 : job between 'A' and 'Z'

2. 인덱스 단계에서의 Filter Predicate (id=4 filter 부분)

    (테이블로의 액세스 여부를 결정짓는 조건절)

3. 테이블 단계에서의 Filter Predicate (id=1 filter 부분)

    (테이블을 액세스하고 나서 최종 결과 집합으로의 포함 여부를 결정짓는 조건절)

 

- 인덱스를 경유하지 않고 테이블 전체를 스캔할 때

4. 테이블 단계에서의 Filter Predicate (id=3 filter 부분)

 

 

(10) Index Fragmentation

- 오라클에서는 B*Tree 인덱스의 'B'는 'Balanced'의 약자로서, 인덱스 루트에서 리프 블록까지 어떤 값으로 탐색하더라도

   읽는 블록 수가 같음을 의미, 루트로부터 모든 리프 블록까지의 높이(height)가 동일.

- Index Fragmentation에 의해 Index Skew 또는 Sparse 현상이 생기는 경우는 종종 있다. 인덱스 스캔 효율에 나쁜 영향을 미칠 수 있다.

 

▣ Index Skew

- 인덱스 엔트리가 왼쪽 또는 오른쪽에 치우치는 현상. <p.172 그림 1-53>

 

- 인덱스 왼쪽에 있는 리프 블록들은 텅비는 반면 오른쪽 블록들은 꽉 찬 상태가 된다.

  텅빈 인덱스 블록은 커밋하는 순간 freelist로 반환되지만 인덱스 구조상에는 그대로 남는다 .

 상위 브랜치에서 해당 리프 블록을 가리키는 엔트리가 그대로 남아 있어 인덱스 정렬 순서상 그곳에

  입력될 새로운 값이 들어오면 언제든 재사용될 수 있다. 재사용되더라도 이 경우에는

   곧바로 freelist에서 제거되지 않으며 나중에 빈 블록을 찾기 위해 freelist를 스캔하는 프로세스에 의해

  정리된다. 새로운 값이 하나라도 입력되기 전 다른 노드에서 인덱스 분할이 발생하면 이들 블록이

  재사용된다. 이때 상위 브랜치에서 해당 리프 블록을 가리키는 엔트리가 제거돼 다른 쪽

   브랜치의 자식 노드로 이동하고, freelist에서도 제거 된다. 레코드가 모두 삭제된 블록은 이처럼 언제든

  재사용 가능하지만 문제는 다시 채워질 때까지 인덱스 스캔 효율이 낮다는데 있다.

 

(실험)

create table t as select rownum no from dual connect by level <= 1000000 ;

create index t_idx on t(no) pctfree 0;

delete from t where no <= 500000 ;

commit;

 

set autotrace on statistics

 

- 맨 왼쪽부터 스캔하도록 조건 부여

select * from t where no > 0 and rownum <= 1;

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

1001 consistent gets

0 physical reads

0 redo size

420 bytes sent via SQL*Net to client

400 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

 

- 위와 같은 Index Skew 때문에 성능이 나빠지는 경우는 대개 Index Full Scan 할 때다.

  대량의 데이터를 매일 지웠다가 새로 입력하는 통계성 테이블일 때는 Index Skew가 발생하지 않도록

  트랜잭션 패턴에 신경을 써야 한다.

 

(실험 1) <p173 중간 프로그램, p174 위 프로그램>

- 보관주기가 3일인 일별고객별판매집계 테이블, 매일 10시에 트랜잭션 수행

       PK 인덱스 => 판매일자 + 고객번호

- 지난 데이터를 삭제 후 집계 데이터를 인서트 후 커밋

- PK 인덱스 왼쪽에 놓인 상당수 리프 블록들이 delete 문을 통해 비워진 상태

   (commit 전까지는 freelist 반환되지 않아 insert 과정에서 재사용 못함)

- 다음날 다시 insert시 재사용 되겠지만 delete문으로 인해 지워진 블록들이 새로 발생.

- delete 직후에 commit을 수행하여 insert 과정에서 재사용 되도록 변경.

   (업무의 중요도와 트랜잭션이 실패했을 때 재상 가능한지 등에 따라 판단)

(실험 2) < p174 중간 프로그램>

- 문장단위로 커밋을 하지만 insert를 먼저 수행하고 delete를 나중에 수행한 경우

- insert문에 의해 Skew가 해소 되었다가 곧 이은 delete문에 의해 왼쪽 리프 노드가 또다시 지워진다.

- insert문과 delete문 수행순서를 바꾸지 않는한 해결되지 않는다.

 

▣ Index Sparse

- 인덱스 블록 전반에 걸쳐 밀도(density)가 떨어지는 현상. <p.175 그림 1-54>

(실험) 첨부파일 1-17.hwp 1-17.hwp

-지워진 자리에 인덱스 정렬 순서에 따라 새로운 값이 입력되면 그 공간은 재사용되지만 

  대량의 delete 작업이 있고 난 후 한동안 인덱스 스캔 효율이 낮다.

- Index Sparse는 지워진 자리에 새로운 값이 입력되지 않으면 영영 재사용되지 않을 수 있다.

- 총 레코드 건수가 일정한데도 인덱스 공간 사용량이 계속 커지는 것은 대개 이런 현상에 기인한다.

 

▣ Index Rebuild

- coalesce : 여러 인덱스 블록을 하나로 병합(merge)하고, 그 결과로서 생긴 빈 블록들은 freelist에 반환한다.

인덱스 세그먼트에 할당된 미사용 공간(HWM 아래쪽에서 freelist에 등록된 블록과 HWM 위쪽 미사용 블록)은 반환되지 않는다.  

alter index t_idx coalesce;

 

shrink space : Index Fragmentation을 해소하면서 공간까지 반환, ASSM에서만 작동.

alter index t_idx shrink space;

 

shrink space compact : 공간을 반환하지 않는다. coalesce와 같다.

alter index t_idx shrink space compact;

 

- coalesce와 shrink는 레코드를 건건히 지웠다가 다시 입력하는 방식을 사용.

- 작업량이 많을 때는 rebuild 명령을 사용.

alter index t_idx rebuild;

alter index t_idx rebuild online;

- 인덱스 구조를 슬림화하면 저장 효율이나 스캔 효율은 좋아지지만

  일반적으로 인덱스 블록에는 어느 정도 공간을 남겨두는 것이 좋다.

 (맨 우측 리프 블록에만 값이 입력되는 right-growing 인덱스일 때는 pctfree를 0으로 설정하는 것이 좋다)

- 인덱스 블록에 공간이 전혀 없으면 인덱스 분할이 자주 발생해 DML 성능을 떨어 뜨린다.

- 인덱스 분할 때문에 Shared 모드 enq: TX - index contention 대기 이벤트가 자주 나타난다면 pctfree를 높게

   설정하는 것을 고려해 볼 수 있다.

   ( 그러나 pctfree를 높게 설정하는 것만으로는 전혀 효과가 없다. 인덱스에서의 pctfree는

      인덱스를 처음 생성하거나 rebuild할 때만 적용되므로 인덱스 경합을 줄이려면

       pctfree를 높이고 나서 인덱스를 rebuild해야 한다. 하지만 효과는 일시적,

      언젠가 빈 공간이 다시 채워지기 때문. 아래와 같이 예상효과가 확실할 때만 인덱스 rebuild를 고려)

     (1) 인덱스 분할에 의한 경합이 현저히 높을 때

     (2) 자주 사용되는 인덱스 스캔 효율을 높이고자 할 때, 특히 NL 조인에서 반복 액세스되는

            인덱스 높이가 증가했을 때

     (3) 대량의 delete 작업을 수행한 이후 다시 레코드가 입력되기까지 오랜 기간이 소요될 때

     (4) 총 레코드 수가 일정한대도 인덱스가 계속 커질 때

 

 

 

  • 오라클 고도화 원리와 해법 2 (bysql.net 2011년 1차 스터디)
  • 작성자: 박우창 (balto)
  • 최초작성일: 2011년 3월 6일
  • 본문서는 bysql.net 스터디 결과입니다 .본 문서를 인용하실때는 출처를 밝혀주세요. http://www.bysql.net
  • 문서의 잘못된 점이나 질문사항은 본 문서에 댓글로 남겨주세요. ^^

??