I/O 튜닝의 중요점

1. sequential 액세스의 선택도를 높이다
2. random 액세스 발생량을 줄인다

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

• 인덱스: 같은 값을 갖는 레코드( = ) 들이 항상 서로 군집

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

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

• 인덱스 컬럼중 일부가 조건절에서 생략되거나 ‘=’ 조건이 아니더라도, 그것이 뒤쪽 컬럼일대는 비효율이 없다.
• 선행컬림이 조건절에 누락되거나 between, 부등호, like 같은 범위검색 조건이 사용되면 인덱스 스캔단계에서 비효율이 발생
  

select * from 매물아파트매매
where 아파트세세코드=’a0002’
and 평형=’59’
and 평형타입=’A’
and 인터넷매물 between ‘1’ and ‘2’
order by 입력일 desc


인덱스:  아파트 시세+평형+평형타입+인테냇 매물

아파트 시세코드     평형     평형타입     인터넷 매물

a0001             15         A         0
a0001             21         B         1
a0002             59         A         1   
a0002             59         A         2
a0003             22         A         0
a0003             59         D         3
a0004             23         A         1
a0004             33         D         2

인덱스 선행 컬럼이 모두 ‘=’ 조건일때는 필요한 범위만 스캔


인덱스:  인테넷 매물+아파트 시세+평형+평형타입

인터넷 매물     아파트 시세코드     평형     평형타입    

0        a0001             15         A    
0        a0003             22         A    
1        a0001             21         B    
1        a0002             59         A        
1        a0004             23         A    
1        a0004             33         D   
2        a0002             59         A    
3        a0003             59         D    

선두컬럼이 BETWEEN 이므로 결과값은 흩어져 있게되어 조건에 만족하지 않는 레코드 까지 스캔하고 서 버려야 하는 비효율이 발생

• 인덱스 스캔의 시작점 탐색시 인터넷 매물=1 AND 시세 <’A002’ AND 평형 <’59’ AND 평형타입 <’A’ 로
  필터링 후 스캔시작 종료역시 조건 값에 따라 필터링후 스캔 종료

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

• IN 으로 변경
  (2)의 예중 인터넷 매물 을 in 조건으로 바꾸면
  

select * from 매물아파트매매
where 아파트세세코드=’a0002’
   and 평형=’59’
   and 평형타입=’A’
   and 인터넷매물 in (‘1’, ‘2’)
order by 입력일 desc

• 실행계획상 INLIST ITERATOR 가 발생하게된다
• 이것은 IN 조건을 = 조건으로 변경하여 UNION ALL 한것과 동일하므로
• ‘=’ 조건으로 스캔이 일어나게 되어 성능향상의 효과를 볼수 있다.

• BETWEEN 조건을 IN-LIST 조건으로 바꿀 때  주의 사항

• IN-LIST 개수가 많지 않아야 함
• 범위 스캔의 비효율은 사라지지만 인덱스 수직 탐색이 여러번 발생
• 인덱스 스캔과정에서 선택되는 레코드들이 서로 멀리 떨어져 있을때만 유용
• BETWEEN 조건의 선행컬럼이 많은 인덱스 리프블록을 스캔하더라도 I/O면에서는 소량일 경우가 많음
  (인덱스 리프블록은 매우 많은 레코드가 담김)

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

• 인덱스 선두 컬림이 누락됐을 때뿐만 아니라 부등호,BETWEEN,like 같은 범위검색 조건일때도 유용하게 사용

자료 생성
create table month_sales_sum
as
select rownum cno
,'2008' || lpad(ceil(rownum/100000),2,'0') month
,decode(mod(rownum,12),1,'A','B') styp
,round(dbms_random.value(1000,100000),-2) samt
from dual
connect by level<=1200000;

create index month_sales_sum_idx1 on month_sales_sum(styp,month);
create index month_sales_sum_idx2 on month_sales_sum(month,styp);

각 조건별 결과
SQL> select count(*)
 2  from month_sales_sum t
 3  where styp='A'
 4 and month between '200801' and '200812';

 COUNT(*)
----------
    100000


Execution Plan
----------------------------------------------------------
Plan hash value: 3159671310

------------------------------------------------------------------------------------------
| Id  | Operation         | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                      |     1 |    10 |   292   (2)| 00:00:04 |
|   1 |  SORT AGGREGATE   |                      |     1 |    10 |            |          |
|*  2 |   INDEX RANGE SCAN| MONTH_SALES_SUM_IDX1 |   111K|  1086K|   292   (2)| 00:00:04 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  2 - access("STYP"='A' AND "MONTH">='200801' AND "MONTH"<='200812')

Note
-----
  - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
        28  recursive calls
         0  db block gets
       362  consistent gets
       601  physical reads
         0  redo size
       411  bytes sent via SQL*Net to client
       384  bytes received via SQL*Net from client
         2  SQL*Net roundtrips to/from client
         0  sorts (memory)
         0  sorts (disk)
         1  rows processed

SQL>



idx1 사용

• cost: 292
• consistent gets: 362 

SQL> select /*+ index ( t MONTH_SALES_SUM_IDX2 ) */ count(*)
2 from month_sales_sum t
3 where styp='A'
4 and month between '200801' and '200812';

 COUNT(*)
----------
    100000


Execution Plan
----------------------------------------------------------
Plan hash value: 1604684706

------------------------------------------------------------------------------------------
| Id  | Operation         | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                      |     1 |    10 |  3473   (2)| 00:00:42 |
|   1 |  SORT AGGREGATE   |                      |     1 |    10 |            |          |
|*  2 |   INDEX RANGE SCAN| MONTH_SALES_SUM_IDX2 |   111K|  1086K|  3473   (2)| 00:00:42 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  2 - access("MONTH">='200801' AND "STYP"='A' AND "MONTH"<='200812')
      filter("STYP"='A')

Note
-----
  - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
         4  recursive calls
         0  db block gets
      3169  consistent gets
      3089  physical reads
         0  redo size
       411  bytes sent via SQL*Net to client
       384  bytes received via SQL*Net from client
         2  SQL*Net roundtrips to/from client
         0  sorts (memory)
         0  sorts (disk)
         1  rows processed

SQL>

idx2 사용

• cost: 3473
• consistent gets: 3169 

SQL> select  count(*)
2 from month_sales_sum t
3 where styp='A'
4 and month in ('200801','200802','200803','200804','200805','200806','200807','200808','200809','200810','200811','200812');


 COUNT(*)
----------
    100000


Execution Plan
----------------------------------------------------------
Plan hash value: 2540619797

-------------------------------------------------------------------------------------------
| Id  | Operation          | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                      |     1 |    10 |   284   (2)| 00:00:04 |
|   1 |  SORT AGGREGATE    |                      |     1 |    10 |            |          |
|   2 |   INLIST ITERATOR  |                      |       |       |            |          |
|*  3 |    INDEX RANGE SCAN| MONTH_SALES_SUM_IDX1 |   111K|  1086K|   284   (2)| 00:00:04 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  3 - access("STYP"='A' AND ("MONTH"='200801' OR "MONTH"='200802' OR
             "MONTH"='200803' OR "MONTH"='200804' OR "MONTH"='200805' OR "MONTH"='200806' OR
             "MONTH"='200807' OR "MONTH"='200808' OR "MONTH"='200809' OR "MONTH"='200810' OR
             "MONTH"='200811' OR "MONTH"='200812'))

Note
-----
  - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
         4  recursive calls
         0  db block gets
       393  consistent gets
         0  physical reads
         0  redo size
       411  bytes sent via SQL*Net to client
       384  bytes received via SQL*Net from client
         2  SQL*Net roundtrips to/from client
         0  sorts (memory)
         0  sorts (disk)
         1  rows processed

SQL>

in-list 로 변경 

• cost: 284
• consistent gets: 393 

SQL> select /*+ index_ss ( t MONTH_SALES_SUM_IDX2 ) */ count(*)
2 from month_sales_sum t
3 where styp='A'
4 and month between '200801' and '200812';

 COUNT(*)
----------
    100000


Execution Plan
----------------------------------------------------------
Plan hash value: 3665529068

-----------------------------------------------------------------------------------------
| Id  | Operation        | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                      |     1 |    10 |   195  (26)| 00:00:03 |
|   1 |  SORT AGGREGATE  |                      |     1 |    10 |            |          |
|*  2 |   INDEX SKIP SCAN| MONTH_SALES_SUM_IDX2 |   111K|  1086K|   195  (26)| 00:00:03 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  2 - access("MONTH">='200801' AND "STYP"='A' AND "MONTH"<='200812')
      filter("STYP"='A')

Note
-----
  - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
         4  recursive calls
         0  db block gets
       379  consistent gets
         8  physical reads
         0  redo size
       411  bytes sent via SQL*Net to client
       384  bytes received via SQL*Net from client
         2  SQL*Net roundtrips to/from client
         0  sorts (memory)
         0  sorts (disk)
         1  rows processed

SQL>

index_ss 사용

• cost: 195
• consistent gets : 379  

• idx1 사용
  
• cost 292
• consistent gets 362 
  
• idx2 사용
  
• cost 3473
• consistent gets 3169
• in-list 사용 (idx2)
  
• cost 284
• consistent gets 393
• index_ss 사용  (idx2)
  
• cost 195
• consistent gets 379 
  

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

* like 조건을 기준으로 설명

쿼리1

select 고객id, 상품명, 지역...
from 가입상품
where 회사 = :com
and 지역 =:reg
and 상품명 like :prod || ‘%’;

쿼리2
select 고객 id, 상품명, 지역...
from 가입상품
where 회사 =:com
and 상품명 like :prod || ‘%’;

쿼리3
select 고객 id, 상품명, 지역,...
from 가입상품
where 회사=:com
and 지역 like ;reg || ‘%’
and 상품명 like :prod ||’%’

조건1
회사=’C70’
지역=’02’
상품=’보급'

조건2
회사=’C70’
상품=’보급'

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

회사	지역	상품명	쿼리1	쿼리2	쿼리3	쿼리3
			조건1	조건2	조건1	조건2
C60	03	일반
C70	02	고급
C70	02	보급1	V	V	V	V
C70	02	보급2	V	V	V	V
C70	02	일반	V	V	V	V
C70	02	일반		V	V	V
C70	02	일반		V	V	V
C70	02	일반		V	V	V
C70	02	일반		V	V	V
C70	03	고급		V	V	V
C70	03	보급1		V		V
C80	02	고급		V		V

• 쿼리3의 조건 1과 쿼리 1의 조건 1을 비교해보면 LIKE 연산을 사용함으로서 스캔 범위가 늘어남을 볼수 있으므로

• 무분별한 LIKE 조건의 사용을 자제 하는것이 좋다.

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

• 도서 table에 도서명으로 index가 있을경우

WHERE 도서명 like :book_nm || ‘%’

and 도서명 > :last_book_nm

or-expansion을 이용하는 방법과 주의 사항

• use_concat 힌트를 사용하면 or 조건에 대한 expansion(union all 분기) 이 일어나게 되어 union all을 사용할때보다
  sql코딩량을 줄일수 있음

• 주의 점

• 9i 까지
  
•   i/o 비용 모델, cpu비용모델을 불문하고 뒤쪽 조건을 먼저 실행
• 10g 부터
  
•  cpu비용 모델에서는 계산된 카디널리티가 낮은 쪽을 먼저 실행
  뒤쪽조건을 먼저 처리하도록 하려면 ordered_predicates 힌트를 명시해야함

• rowid 를 concatenation하면 결과에 오류 발생

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

GREATEST('AAAH+WAA
------------------
AAAH+WAAJAAAHxTAA9

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

GREATEST(CHARTOROW
------------------
AAAH+WAAJAAAHxTAA+

SQL>

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

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

SQL> create index month_sales_sum2_idx2 on month_sales_sum2(month,styp);

SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;

System altered.

SQL> select  /*+ index( t month_sales_sum2_idx2 ) */ count(*)
2 from month_sales_sum2 t
3 where month between '200801' and '200802'
4 and styp='A';
 COUNT(*)
----------
    16667


Execution Plan
----------------------------------------------------------
Plan hash value: 1365354869

-------------------------------------------------------------------------------------------
| Id  | Operation         | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                       |     1 |    10 |   590   (2)| 00:00:08 |
|   1 |  SORT AGGREGATE   |                       |     1 |    10 |            |          |
|*  2 |   INDEX RANGE SCAN| MONTH_SALES_SUM2_IDX2 | 17237 |   168K|   590   (2)| 00:00:08 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  2 - access("MONTH">='200801' AND "STYP"='A' AND "MONTH"<='200802')
      filter("STYP"='A')

Note
-----
  - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
         0  recursive calls
         0  db block gets
       304  consistent gets
       304  physical reads
         0  redo size
       413  bytes sent via SQL*Net to client
       384  bytes received via SQL*Net from client
         2  SQL*Net roundtrips to/from client
         0  sorts (memory)
         0  sorts (disk)
         1  rows processed

SQL> select  /*+ index( t month_sales_sum2_idx2 ) */  count(*)
from month_sales_sum2 t
where month like '2008%'
and styp='A';  2    3    4

 COUNT(*)
----------
    16667


Execution Plan
----------------------------------------------------------
Plan hash value: 1365354869

-------------------------------------------------------------------------------------------
| Id  | Operation         | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                       |     1 |    10 |   590   (2)| 00:00:08 |
|   1 |  SORT AGGREGATE   |                       |     1 |    10 |            |          |
|*  2 |   INDEX RANGE SCAN| MONTH_SALES_SUM2_IDX2 | 17237 |   168K|   590   (2)| 00:00:08 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  2 - access("MONTH" LIKE '2008%' AND "STYP"='A')
      filter("STYP"='A' AND "MONTH" LIKE '2008%')

Note
-----
  - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
         0  recursive calls
         0  db block gets
       560  consistent gets
       256  physical reads
         0  redo size
       413  bytes sent via SQL*Net to client
       384  bytes received via SQL*Net from client
         2  SQL*Net roundtrips to/from client
         0  sorts (memory)
         0  sorts (disk)
         1  rows processed

SQL> select /*+ index( t month_sales_sum2_idx2 ) */ count(*)
from month_sales_sum2 t
where month >= '200801'
and month < '200803'
and styp='A';
 2    3    4    5
 COUNT(*)
----------
    16667


Execution Plan
----------------------------------------------------------
Plan hash value: 1365354869

-------------------------------------------------------------------------------------------
| Id  | Operation         | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                       |     1 |    10 |   590   (2)| 00:00:08 |
|   1 |  SORT AGGREGATE   |                       |     1 |    10 |            |          |
|*  2 |   INDEX RANGE SCAN| MONTH_SALES_SUM2_IDX2 | 17237 |   168K|   590   (2)| 00:00:08 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  2 - access("MONTH">='200801' AND "STYP"='A' AND "MONTH"<'200803')
      filter("STYP"='A')

Note
-----
  - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
         0  recursive calls
         0  db block gets
       560  consistent gets
         0  physical reads
         0  redo size
       413  bytes sent via SQL*Net to client
       384  bytes received via SQL*Net from client
         2  SQL*Net roundtrips to/from client
         0  sorts (memory)
         0  sorts (disk)
         1  rows processed

SQL>

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

• 실제 사용에서는 상황에 따라다르지만 적어도 between이 like보다
  더 넓은 범위를 스캔하지는 않으므로 between을 사용하는것이 유리하다.

• 인덱스 스캔
  
•  브랜치 블록에서 뒤쪽 엔트리부터 스캔을 시작해 비교값보다 작은 값을 만나는 순간에 가리키는 하위 노드로 이동

• like 조건으로 검색할경우 ‘판매월=’2009’이고 판매구분=’B’인 레코드를 목표로 수직적탐색
• 하위노드를 탐색하다가 ’2009’보다 작은  ‘2008’||’12’||’B’레코드가 담긴 마지막 리프블록에 도달한후 멈춤

• between 일때는 ‘판매월=’200901’ 이고 판매구분 =’B;인 레코드를 목표로 수직적 탐색
• 하위노드를 탐색하다가 ‘200901’||’A’가 담긴 마지막 리프블록에 도달한후 멈춤
  

• like라도 인덱스에서 실제 찾아지는값 (‘200801’) 을 입력하면 스캔 범위를 줄일수 있으면
• between 이라도 실제 없는 값 (‘200800’) 으로 조회하면 like와 같은 스캔량을 가지게 됨

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

선분이력이란?

• 점이력
• 이력의 시작점만을 관리하는 모델 {고객번호+변경일자}
• 선분이력
• 시작지점과 종료시점을 함께 관리 {고객번호+시작일자+종료일자}
  최근 이력의  마지막 종료일이 없으므로 종료일은 ‘99991231’

• 장점
• 선분형태로 관리하면 between 조인을 이용하여 간단하게 이력시점을 조회할수 있다.
• ‘20090505’ between b.시작일 and b.종료일 형태인 간단한 쿼리로 처리할수 있어  성능상 유리해질수도 있다
  

• 단점
• 이력이 추가될때마다 최종 이력의 종료일자도 같이 변경해 주어야함으로 DML성능이나
  데이터 관리 프로그램등이 복잡해질수 있다(과거이력 일괄 보정)
• 개체 무결성을 사용자가 직접 관리해 주어야 함

선분이력 기본 조회 패턴

SELET 연체개월수, 연체금액
FROM 고객별연체금액
where 고객번호 = ‘123’
and :dt between 시작일 and 종료일

• 현 시점은 ‘99991231’등으로 두어야 함으로 현시점을 검색할때도
•  and 종료일=’99991231’   만으로 검색가능
• 하지만 자료가 미리 등록되어있는 등의 경우 ( 가격변경등) 에는 to_char(sysdate,’yyyymmdd’)등을 활용하여야 한다.
  
  
  

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

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

을 풀어쓰면

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

• 이 되어 인덱스 스캔시 = 조건을 찾은후 시작일 컬럼의 <= 을 모두 스캔하고 종료일 >=  조건을 모두 스캔하므로
• 두 조건 모두 범위를 줄이는데 기여하지 못하게 된다.
• 하지만 두 조건 모두 index 컬럼에 포함되어있어 테이블 random 액세스 없이 필터링 할수 있지만 이력이 많은 고객일경우
• 한건을 스캔하기 위해 많은 인덱스를 스캔해야 한다.
• 그럴경우  index_desc 힌트에 rownum<=1 조건을 추가해주만 index를 거꾸로 스캔하면 빠르게 찾을수 있다.
  

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

• index_desc 힌트를 주고 rownum<=1 조건을 추가해 주면 스캔범위를 한건으로 줄일수 있음

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

 

• index:  { 고객번호+종료일+시작일 }

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

• = 조건 다음 종료일이 >= 이므로 2005년 1월 31일 이후 이력 레코드를 모두 스캔해야하지만 
• rownum<=1 조건을 추가해 준다면 스캔범위를 1건으로 줄일수 있음
  

 

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

where 고객번호=’123’
and ‘20020930’ between 시작일 and  종료일

• ‘=’ 조건 다음 종료일 >= 조건이므로 2002년 9월 30일 이력을 모두 스캔하여야 하지만
• rownum<=1 조건을 추가해 준다면 1건으로 줄일수 있음

중간시점 조회

• 중간시점 역시 위와 같이 인덱스 구성에따라
• index_desc 힌트 + rownum<=1 을 사용하거니 rownum<=1을 사용하면 빠르게 찾을수 있음

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

• 최근데이터를 주로 조회한다면 {종료일+시작일}
• 과거데이터를 주로 조회한다면 {시작일+종료일}  순으로 구성하는 것이 효과적

• {시작일+종료일} 일 경우 index_desc 힌트와 rownum<=1조건을 추가
• {종료일+시작일} 일 경우 rownum<=1 조건만 추가해주면 된다

 

실험  - 김범석 추가

CREATE TABLE IDX_DESC_TEST ( USER_ID VARCHAR2(20), STT_DTM DATE, END_DTM DATE);

 

COMMIT;

DECLARE
    I NUMBER := 1;
    J NUMBER := 1;
    BEGIN
        WHILE I < 500001 LOOP
        INSERT INTO IDX_DESC_TEST  SELECT '1'|| LPAD(MOD( I, 2) + 1, 5, '0') AS USER_ID, TO_DATE(SYSDATE, 'YY-MM-DD') +  I AS STT_DTM, TO_DATE(SYSDATE, 'YY-MM-DD') + (I + J) AS END_DTM FROM DUAL;
        I := I + 1;
        END LOOP;
    END;
/

 

COMMIT;

 

-- 1.인덱스 => 고객번호 + 시작일 + 종료일
CREATE INDEX IDX_ID_STT_END ON IDX_DESC_TEST(USER_ID, STT_DTM, END_DTM);

-- 2.인덱스 => 고객번호 + 종료일 + 시작일
CREATE INDEX IDX_ID_END_STT ON IDX_DESC_TEST(USER_ID, END_DTM, STT_DTM);

 

SELECT * FROM IDX_DESC_TEST;

 

-- 1.인덱스 => 고객번호 + 시작일 + 종료일
-- 시작일 + 종료일 구성일 때 최근 시점 조회
SELECT *
FROM IDX_DESC_TEST
WHERE USER_ID = '100002' AND '3131-08-07' BETWEEN STT_DTM AND END_DTM;

/*
-----------------------------------------------------------------------------------
| Id  | Operation        | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                |    31 |   930 |   251   (1)| 00:00:04 |
|*  1 |  INDEX RANGE SCAN| IDX_ID_END_STT |    31 |   930 |   251   (1)| 00:00:04 |
-----------------------------------------------------------------------------------
*/

 

SELECT *
FROM IDX_DESC_TEST
WHERE USER_ID = '100002' AND STT_DTM <= '3131-08-07' AND END_DTM >= '3131-08-07';

/*
-----------------------------------------------------------------------------------
| Id  | Operation        | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                |    31 |   930 |   251   (1)| 00:00:04 |
|*  1 |  INDEX RANGE SCAN| IDX_ID_END_STT |    31 |   930 |   251   (1)| 00:00:04 |
-----------------------------------------------------------------------------------
*/

 

SELECT /*+ INDEX_DESC(A IDX_ID_STT_END) */ *
FROM IDX_DESC_TEST A
WHERE USER_ID = '100002' AND '3131-08-07'  BETWEEN STT_DTM AND END_DTM AND ROWNUM <= 1;

/*
-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |     1 |    30 |    34   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY               |                |       |       |            |          |
|*  2 |   INDEX RANGE SCAN DESCENDING| IDX_ID_STT_END |    31 |   930 |    34   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
*/

 

-- 2.인덱스 => 고객번호 + 종료일 + 시작일
-- 시작일 + 종료일 구성일 때 과거 시점 조회
SELECT *
FROM IDX_DESC_TEST
WHERE USER_ID = '100002' AND '2011-03-19' BETWEEN STT_DTM AND END_DTM;

/*
-----------------------------------------------------------------------------------
| Id  | Operation        | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                |    10 |   300 |     3   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IDX_ID_STT_END |    10 |   300 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------
*/

 

SELECT *
FROM IDX_DESC_TEST
WHERE USER_ID = '100002' AND STT_DTM <= '2011-03-19' AND END_DTM >= '2010-03-19';

/*
-----------------------------------------------------------------------------------
| Id  | Operation        | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                |    10 |   300 |     3   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IDX_ID_STT_END |    10 |   300 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------
*/

 

SELECT /*+ INDEX_DESC(A IDX_ID_END_STT) */ *
FROM IDX_DESC_TEST A
WHERE USER_ID = '100002' AND '2011-03-19' BETWEEN STT_DTM AND END_DTM AND ROWNUM <= 1;

/*
-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |     1 |    30 |   125   (1)| 00:00:02 |
|*  1 |  COUNT STOPKEY               |                |       |       |            |          |
|*  2 |   INDEX RANGE SCAN DESCENDING| IDX_ID_END_STT |    10 |   300 |   125   (1)| 00:00:02 |
-----------------------------------------------------------------------------------------------
*/

-- 1. 최신날짜로 검색을 했을시에는 앞에서 만들어 졌던 IDX_ID_END_STT 인덱스로 범위스캔
-- 2. 과거날짜로 검색을 했을시에는 앞에서 만들어 졌던 IDX_ID_STT_END 인덱스로 범위스캔
-- 한다는 사실을 알게되었다.

 

-- 결국 둘이 비교했을 경우

SELECT /*+ INDEX_DESC(A IDX_ID_STT_END) */ *
FROM IDX_DESC_TEST A
WHERE USER_ID = '100002' AND '2011-03-19'  BETWEEN STT_DTM AND END_DTM AND ROWNUM <= 1;

/*
-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |     1 |    30 |     3   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY               |                |       |       |            |          |
|*  2 |   INDEX RANGE SCAN DESCENDING| IDX_ID_STT_END |    10 |   300 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
*/

SELECT /*+ INDEX_DESC(A IDX_ID_END_STT) */ *
FROM IDX_DESC_TEST A
WHERE USER_ID = '100002' AND '2011-03-19' BETWEEN STT_DTM AND END_DTM AND ROWNUM <= 1;

/*
-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |     1 |    30 |   125   (1)| 00:00:02 |
|*  1 |  COUNT STOPKEY               |                |       |       |            |          |
|*  2 |   INDEX RANGE SCAN DESCENDING| IDX_ID_END_STT |    10 |   300 |   125   (1)| 00:00:02 |
-----------------------------------------------------------------------------------------------
*/

SELECT /*+ INDEX_DESC(A IDX_ID_STT_END) */ *
FROM IDX_DESC_TEST A
WHERE USER_ID = '100002' AND '3131-08-07'  BETWEEN STT_DTM AND END_DTM AND ROWNUM <= 1;

/*
-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |     1 |    30 |    34   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY               |                |       |       |            |          |
|*  2 |   INDEX RANGE SCAN DESCENDING| IDX_ID_STT_END |    31 |   930 |    34   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
*/

SELECT /*+ INDEX_DESC(A IDX_ID_END_STT) */ *
FROM IDX_DESC_TEST A
WHERE USER_ID = '100002' AND '3131-08-07' BETWEEN STT_DTM AND END_DTM AND ROWNUM <= 1;

/*Plan hash value: 1689508773

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |     1 |    30 |    11   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY               |                |       |       |            |          |
|*  2 |   INDEX RANGE SCAN DESCENDING| IDX_ID_END_STT |    31 |   930 |    11   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
*/

 

(9)  Access Predicate 와 Filter Predicate

• dbms_xplan패키지를 통해 실행 계획을 출력해보면 Predicate 정보를 확인할 수있다.

SQL> 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%' ;
SQL> SQL>   2    3    4    5    6    7    8    9   10   11
Execution Plan
----------------------------------------------------------
Plan hash value: 3070931621

------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |     1 |    57 |     4   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID  | EMP      |     1 |    37 |     2   (0)| 00:00:01 |
|   2 |   NESTED LOOPS                |          |     1 |    57 |     4   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| DEPT     |     1 |    20 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | DEPT_IDX |     1 |       |     1   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN           | EMP_IDX  |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  1 - filter("E"."ENAME" LIKE '%A%' AND TRIM("E"."ENAME")='ALLEN' AND
             TO_CHAR(INTERNAL_FUNCTION("E"."HIREDATE"),'yyyymmdd') LIKE '198102%' AND
             "E"."SAL">=1000 AND "E"."COMM">=300)
  4 - access("D"."LOC"='CHICAGO')
  5 - access("E"."DEPTNO"="D"."DEPTNO" AND "E"."JOB" LIKE 'SALE%')
      filter("E"."JOB" LIKE 'SALE%' AND "E"."JOB">='A' AND "E"."JOB"<='Z')

SQL>

1. 인덱스 단계 Access Predicate    5
2. 인덱스 단계 Filter Predicate        5
3. 테이블 단계 Filter Predicate        1

1. 인덱스 단계에서의 Access Predicate

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

• 범위 검색 조건을 뒤따르는 조건이거나 선행 컬럼이 조건에서 누락될지라도 액세스 범위를 결정하는데 기여
• 수직적 탐색과정에서 모든 인덱스 컬럼을 비교 조건으로 사용

• 영향을 안 미치는 경우
• 좌변 컬럼을 가공한 조건절
  trim(e.ename)=’ALLEN’
• 왼쪽 ‘%’ 또는 양쪽 ‘%’ 기호를 사용한 LIKE 조건절
  ename like ‘%A%’
• 같은 컬럼에 대한 조건절이 두개 이상일때, 액세스 조건으로 선택( job like ‘SALES%’) 되지 못한 조건 
  job between ‘A’ and ‘Z’

2. 인덱스 단계에서의 Filter Predicate

• 테이블로의 액세스 여부를 결정짓는 조건
• 첫번째 나타나는 범위 검색 조건부터 이후 모든 조건절 컬럼들이 포함하며
  조건절에서 누락된 컬럼 뒤쪽에 놓인 인덱스 컬럼들도 포함

3. 테이블 단계에서의 Filter Predicate   

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

• 인덱스를 경유하지 않고 테이블 전체를 스캔할때는 테이블 단계에서의 Filter Predicate만 나타 남

(10) Index Fragmentation

• B*Tree 인텍스의 ‘B’ 는 ‘Balanced’의 약자로 인덱스 루트에서 리프 블록까지 어떤 값으로 탐색하더라도
  읽는 블록 수가 같음을 의미 (모든 리프블록까지의 높이(height) 가 동일)
• 불균형은 없지만 index fragmentation에 의한 index skew 또는 sparse현상이 생길수 있음

Index skew 

• 인덱스 엔트리가 왼쪽또는 오른쪽에 치우치는 현상

• 대량의 delete작업을 마치고 나면 왼쪽에 있는 리프 블록들은 텅비고 오른쪽 블록은 꽉차는 등의 현상이 나타 날수도 있음
• 텅빈 인덱스 블록은 커밋하는 순간 freelist로 반환되지만 인덱스 구조상에는 그대로 남께 되면
• 언제든 재사용이 가능하지만 다시 채워질때 까지 인덱스 스캔(대개 Index Full Scan) 효율이 낮아지게 된다.

• 통 계성 테이블등과 같이 매일 delete & insert 가 있을경우
  delete , insert 작업을 완료후 commit 을 하게 되면 왼쪽 블록은 항상 빈상태로 남아있으므로
  delete , commit , insert , commit 순으로 작업을 하게 된다면 삭제된 블록이 재사용되어 효율적이다.
• insert & commit & delete & commit 는 의미가 없다.

Index Sparse

• 인덱스 블록 전반에 걸쳐 밀도 (density)가 떨어지는 현상

• 블록이 텅비면 곧바로 freelist 로 반환되 언제든 재사용 되지만 index sparse는
  지워진 자리에 새로운 값이 입력되지 않으면 영영 재사용되지 않을 수도 있다.

Index Rebuild

• Fragmentation 때문에 인덱스 크기가 계속 증가하고 스캔 효율이 나쁠대는
  coalesce 명령을 수행하여 처리

• 여러 인덱스 블록을 하나로 병합하고, 결과로 생긴 빈블록들은 freelist에 반환한다.
  단 인덱스 세그먼트에 할당된 미사용공간 은 반환되지 않는다.

• index fragmentation을 해소하면서 공간까지 반환할경우 사용 단 ASSM에서만 동작한다.

• 공간은 반환하지 않으므로 coalesce와 같은 명령어가 된다.

• colaesce와 shrink는 레코드를 건건이 지웠다가 입력하므로 작업량이 많을 경우
  rebuild를 사용하는것이 효율적일수 있다.

• 이와 같이 인덱스 구조를 슬림화하면 저장효율과 스캔효율은 좋아지지만 블록 공간이 전혀없으면
  인덱스 분할이 자주 발생해 DML성능을 떨어뜨릴수 있으므로 상황에 맞게 사용하여야한다.
• 인덱스 분할에 의해 Shared 모두 enq:TX-index contention 대기 이벤트가 자주나타면
  pctfree를 높이고 나서 인덱스를 rebuild를 하면 경합을 줄일수 있다.
  (인덱스에서 pctfree는 인덱스를 처음 생성하거나 rebuild때만 적용)

인덱스  rebuild를 고려 사항

• 인덱스 분할에 의한 경합이 현저히 높을때
• 자주 사용되는 인덱스 스캔 효율을 높일때 (NL조인에서 반복 액세스되는 인덱스 높이가 증가했을때)
• 대량의 delete작업후 다시 입력될때까지 장기간이 필요할때
• 레코드수는 일정한대 인덱스가 계속 커질때

인덱스 freelist

• 테이블
• pctfree와 pctused는 각각 freelist에서 제외되는 시점과, 다시 등록되는 시점을 지정
• 인덱스
• 값이 입력될때마다 freelist를 참조하지 않아도 되며 분할로  빈블록이 필요할 때만 참조, pctused  파라미터는 없음

PCTFREE

• 테이블
• 블록에 더이상 INSERT가 발생하지 못하도록 FREELIST에서 제외되는 시점이며 남겨진 공간은 UPDATE를 위해 사용
• 인덱스
• 지정한 비율만큼 공간을 남겨두었다가 나중에 INSERT 를 위해 사용

PCTUSED

• FREELIST에서 제거된 테이블 블록에 빈공간이 일정 수준 이상 확보되었을경우 다시 FREELIST에
  등록하기 위한 파라미터

• PCTFREE가 10이고 PCTUSED가 80이면 freelist에서 제거된 블록에
  10%정도 delete가 발생하는 순간 해당 블록을 다시 freelist에 등록

• {100-pctfree} 만큼의 공간이 차면 freelist에서 제거
  pctused만큼만 남기고 레코드가 모두 지워질때 다시 freelist에 등록

• 인덱스에서의 freelist는 인덱스 분할에 사용 가능 빈 블록들을 관리
• delete에 의해 지워진 인덱스 블록은 커밋 시점에 freelist에 반환되지만
• insert 시점에 다시 값이 입력되더라도 곧바로 freelist에서 제거되지 않음.
• freelist에 그대로 두었다가 인덱스 분할때문에 freelist를 스캔하는 프로세스에 의해 정리
• (freelist에서 얻은 블록이 비어있지 않으면 다른 블록을 재요청하기 전에 일단 해당 블록을 freelist에서 제거- 커밋 시점에 수행해야할 일량을 최소화 하기 위함)

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