메뉴 건너뛰기

bysql.net

3. 단일 테이블 선택도

2009.12.28 10:16

ZoneWorker2 조회 수:8297

CHAPTER 3. 단일 테이블 선택도

 발표/총정리 :  위충환


시작하면서 / 위충환


? 1,200 명의 청중 중 12월 생일자의 수는 ?  CBO 행동 → 약 100명

     select count(*) from audience where month_no = 12;


     Execution Plan (9.2.0.6)
     ----------------------------------------------------------
        0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=3)
        1    0   SORT (AGGREGATE)
        2    1     TABLE ACCESS (FULL) OF 'AUDIENCE' (Cost=2 Card=100 Bytes=300)      


? 데이터 딕셔너리에 저장 된 통계 정보(user_tab_col_statistics)

     select   column_name,   num_distinct,   density

     from     user_tab_col_statistics

     where   table_name = 'AUDIENCE';

 

     COLUMN_NAME                        NUM_DISTINCT                            DENSITY

                                                    

     MONTH_NO                                               12                           .083333333        


              - num_distinct : null을 제외한 distinct값 개수
              - density : 쿼리에 의해 반활 될 것으로 예상되는 데이터의 비율, null값을 가진 로우 비율 계산 무시


? 컬럼에 히스토그램이 없을 시   density = 1 / num_distinct


? 히스토그램 생성 시 위의 식이 항상 일치하지 않음.

? 적절한 히스토그램 존재 시, 오라클 버전에 따라 조금씩 다른 방식으로 작동

  • 라클 10g 옵티마이저

      - 적절한 히스토그램이 없을 시, num_distinct 컬럼을 사용하여    

        카디널리티 = num_rows / num_distinct 계산


      - 적절한 히스토그램이 존재 시, density 컬럼을 사용하여            

        카디널리티 = num_rows * density 계산

  • 오라클 8i : 항상 density 사용.
  • 오라클 9i : shared pool을 flush 시 오라클 10g와 동일.

NULL 값 처리 54 / 위충환


? 1,200 명의 청중 중 10%가 자신의 생일을 기억하지 못 할 경우

  12월 생일자라는 확신하는 청중의 수는 ?  CBO 행동 → 약 90명

  • 기본 선택도 = 1 / 12   (density 또는 1 / num_distinct 이용)
  • num_nulls = 120
  • num_rows = 1200
  • 조정된 선택도 = 기본 선택도 * (num_rows - num_nulls) / num_rows
                                   = (1 / 12) * ((1200 - 120) / 1200) = 0.075
  • 조정된 카디널리티 = 조정된 선택도 * num_rows

                                    = 0.075 * 1200 = 90


      Execution Plan
      ----------------------------------------------------------
         0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=3)
         1    0   SORT (AGGREGATE)
         2    1     TABLE ACCESS (FULL) OF 'AUDIENCE' (Cost=2 Card=90 Bytes=270)      



IN-LIST 처리방식



select count(*) from audience where month_no in (1,2,3);

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=3)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'AUDIENCE' (Cost=2 Card=300 Bytes=900)


  • 9i,10g  -> 카디널리티 300
  • 8i -> 276
  • 이유

계산결과가 잘못된 경우

업그레이드에 따라 계산 방식이 변경


※ In-list 오류

month_no in (6,7,8)


옵티마이져는 month_no=6 or month_no=7 or month_no=8 로변환

8i에도 use_concat 힌트를 추가하면 union all형태로 변환 -> 8i도 결과가 동일하게 나타남 (300)


-> 8i의 다중접속 알고리즘의 오류로 인해 잘못된 결과가 나타남



DISTINCT 값이 1000개

* ROW 12,000개

 

select count(*) from audience where month_no in (1,2);
select count(*) from audience where month_no in (1,2,3);
select count(*) from audience where month_no in (1,2,3,4);
select count(*) from audience where month_no in (1,2,3,4,5);
select count(*) from audience where month_no in (1,2,3,4,5,6);
select count(*) from audience where month_no in (1,2,3,4,5,6,7);
select count(*) from audience where month_no in (1,2,3,4,5,6,7,8);
select count(*) from audience where month_no in (1,2,3,4,5,6,7,8,9);
select count(*) from audience where month_no in (1,2,3,4,5,6,7,8,9,10);
select count(*) from audience where month_no in (1,2,3,4,5,6,7,8,9,10,11);
select count(*) from audience where month_no in (1,2,3,4,5,6,7,8,9,10,11,12);
select count(*) from audience where month_no in (1,2,3,4,5,6,7,8,9,10,11,12,13);
select count(*) from audience where month_no in (1,2,3,4,5,6,7,8,9,10,11,12,13,14);



[표 3-1]

In-List 항목의 개수
12개의 distinct값 8i(9i,10g)
1000개의 dirsinct 값 8i(9i,10g)
1
100(100)
12(12)
2
192(200)
24(24)
3
276(300) 36(36)
4
353(400) 48(48)
5
424(500) 60(60)
6
489(600) 72(72)
7
548(700) 84(84)
8
602(800) 96(96)
9
652(900) 108(108)
10
698(1000) 120(120)
11
740(1100) 132(132)
12
778(1200) 144(144)
13
813(1200) 156(156)
14
846(1200) 167(168)
30
1112(1200) 355(360)

* in-list 항목의 개수가 적은데도 카디널리티 오차가 크게 발생




최대/최소값의 범위 실험




8i
9i,10g(10.1.0.4)
10g (10.2.0.1)
설명 (8i,9i)
where month_no=25 100
100
1
최대/최소값을 벗어나는지 인식못함
where month_no in (4,4) 100
100
100
중복값을 인식
where month_no in (3,25) 192
200
100
최대/최소값을 벗어나는지 인식못함
where month_no in (3,25,26) 276
300
101
최대/최소값을 벗어나는지 인식못함

where month_no in (3,25,25,26)

276
300
101
최대최소 구분못하지만 중복 구분
where month_no in (3,25,null) 276
300
200
null도 같이 계산
where month_no in (:b1,:b2,:b3) 276
300
300
상수일때의 카디널리티 사용


* 기본자료: 1 부터 12까지 / 1200개의 row를 가짐




NOT IN에 대한 실험



select count(*) from audience where month_no not in (1,2);
select count(*) from audience where month_no in (1,2);


  • 8i 일경우 카디널리티 1,200
  • 9i  1008   + 200 = 1208
  • 10g 1008   + 200 = 1208


10g 업데이트 61


  • CBO의 변화에 의해 10g 10.1.0.2 와 10.2 버젼의 결과가 다르다.
  • 10.2의 예측방법은 최대값에서 최소값을 뺀값 (12-1=11) 을 범위로
  • 최소값-11 / 최대값 +11 로 범위를 예측 한후
  • -11에서 1까지 증가하다가 12까지 평행 다시  23까지하향하여 좌우 각 값이 0으로 하여 계산한다.
  • 참고) 통계정보가 최신으로 유지되지 않을경우 원하지 않는 극적인 결과가 나타날수 있음



범위 기반 검색조건 62 /이태경

Case Predicate Card(8i) Card(9i /10g) 조건
1
month_no > 8 437 436 Unbounded, open
2
month_no >= 8 537 536 Unbounded, closed
3
month_no < 8 764 764 Unbounded, open
4
month_no <= 8 864 864 Unbounded, closed
5
month_no between 6 and 9 528 527 Bounded, closed, closed
6
month_no >= 6 and month_no <= 9 528 527 Bounded, closed, closed
7
month_no >= 6 and month_no < 9 428 427 Bounded, closed, open
8
month_no > 6 and month_no <= 9 428 427 Bounded, open, closed
9
month_no > 6 and month_no < 9 328 327 Bounded, open, open
10
month_no > :b1 60 60 Unbounded, open
11
month_no >= :b1 60 60 Unbounded, closed
12
month_no < :b1 60 60 Unbounded, open
13
month_no <= :b1 60 60 Unbounded, closed
14
month_no between :b1 and :b2 4 3 Bounded, closed, closed
15
month_no >= :b1 and month_no <= :b2 4 3 Bounded, closed, closed
16
month_no >= :b1 and month_no < :b2 4 3 Bounded, closed, open
17
month_no > :b1 and month_no < :b2 4 3 Bounded, open, open
18
month_no > :b1 and month_no <= :b2 4 3 Bounded, open, closed
19
month_no > 12 100 100 Unbounded, open
20
month_no between 25 and 30 100 100 Bounded, closed, closed

범위 기반 조건에 대한(8i, 9i/10g) 카디널리티 변화의 원인
  • 계산상의 오차
            진수 변환 과정에서 소수점 15번째 자리 뒤의 숫자에 대한 올림(8i)과 반올림(9i/10g)
  • 옵티마이저의 반올림 선택에 대한 코드상의 변화
        ?뭘까
카디널리티(이하 Card) 오류에 따른 부작용
  • 결과집합이 작을수록 실행계획에 더 심각한 영향을 준다.

범위에 따른 Card의 패턴
  • 리터럴(리터럴이란 column명, alias가 아닌 SELECT리스트에 포함된 문자,표현식 또는 숫자.) 사용
        ? 열린 조건과 닫힌 조건일 때의 Card 차이 = 100
  • 컬럼값의 범위를 벗어날 경우
        ? Card가 100으로 고정
  • 바인드 변수 사용
  1. 열린 조건과 닫힌 조건일 때의 Card 차이가 없다.
  2. 현실적이 못하다. (∵ 개별값 검색이 범위 검색보다 많은 로우를 리턴)

옵티마이저의 Card 계산식 추론(조건절의 변수 종류에 따른)
  • 바인드 변수
  1. 무한정 범위의 바인드 변수 ? 선택도를 5% (0.05 * 1200 = 60)
  2. 한정 범위의 바인드 변수 ? 선택도 0.25% (0.0025 * 1200 = 3)  Q)왜 5%의 5%로?
  3. 데이터 범위를 벗어남 ? 'column = 상수' 조건에 해당하는 Card
  • 리터럴 값
  1. 무한정 And 열린 범위 ? 해당범위/전체범위 * 총 Row 수
  2. 무한정 And 닫힌 범위 ? (해당범위/전체범위 + 1/num_distinct) * 총 Row 수
  3. between 양쪽 닫힌 범위 ? (해당범위/전체범위 + 1/num_distinct + 1/num_distinct) * 총 Row 수

전체 범위 사용시 주의
  • 범위를 벗어나는 데이터 삽입시 전체 범위에 영향을 주며 이것은 선택도의 오류를 유발한다.

바인드 변수 PEEKING

10g 업데이트 67

/ 이태경

  • (경계값을 벗어난 경우) 가능한 값의 범위에서 멀어질 경우 Card는 100을 사용하는 대신 점점 작아진다.(표 3-5)
Predicate Cardinality(10.1.0.2) Cardinality(10.1.0.4)
month_no between 6 and 9 527 527
month_no between 14 and 17 100 82
month_no between 18 and 21 100 45
month_no between 24 and 27 100 1



조건절이 두 개일 때의 처리방식 68

/ 박우창

조건절이 두 개일때의 처리 방식

? (사례) month_no가 (1,12)의 정수값을 가질때 1,200개의 튜플을 가진 테이블에서

조건 where month_no > 8 or month_no <=8 일때 카디널리티는?

(추정)

1. 1301 = 437(month_no > 8 의 추정값) + 864(month_no <= 8 의 추정값)

2. 1200 = (전체값 범위이므로)

3. 986 = (실제 수행해본 값)

? (조건절에서의 선택도 계산 방법)

- 조건1 and 조건2 = 조건1의 선택도 * 조건2의 선택도

- 조건1 or 조건 2 = 조건1의 선택도 + 조건2의 선택도 -조건1과조건2의 선택도

- not 조건1 = 1 - 조건1의 선택도

? month_no between :b1 and :b2의 선택도는 왜 0.0025 인가?

- 두 개의 조건을 AND로 처리하기 때문이다.

- month_no > :b1 의 선택도를 0.05로 처리한다(이유 없음?)

- not (column > :b1) 는 0.95가 아니라 0.05이다.

- not (month_no between :b1 and :b2) => (month_no > :b1 or month_no < :b2)

= 0.05 + 0.05 - 공통부분(:b1과 :b2 사이 값들)


다중 조건일 때의 문제점 71

/ 이창헌

옵티마이저는 사람과 다르다.
옵티마이저는 코드(공식)에 따라 계산할 뿐, 주어진 상황을 이해하지는 못한다.

 

   테이블 내 1,200개 로우를 100%완벽하게 만족하는 조건을 사용=>986의 카디널리티를 계산

    where month_no > 8  --조건1(P1)
       or month_no <= 8 --조건2(p2)

 

    1.단일 선택도 공식 = (요청한 범위/전체 범위)
      조건1 :  (12-8)/(12-1) = 4/11 = 0.363636

      조건2 :  (8-1)/(12-1)+1/12 = 7/11+1/12 = 0.719696

 

    2.( p1 or p2 ) 선택도 = (p1의 선택도) + (p2의 선택도) - ( p1 and p2의 선택도)
            0.363636 + 0.719696 - (0.363636 *  0.719696 ) = 0.8216


    3. 0.8216 * 1200 = 986


  카디널리티가 부정확 =>  부적절한 실행계획


요약

/ 위충환


테스트 스크립트

/ 위충환

번호 제목 글쓴이 날짜 조회 수
17 진행기록 운영자 2010.04.11 7617
16 비용기반의 오라클 원리 첫 모임 [4] 운영자 2010.01.04 5746
15 14. 10053 트레이스 파일 file 휘휘 2009.12.28 10196
14 13. 정렬과 머지 조인 실천하자 2009.12.28 15932
13 12. 해시 조인 file 헌쓰 2009.12.28 16884
12 11. NL(Nested Loops) 조인 휘휘 2009.12.28 11780
11 10. 조인 카디널리티 휘휘 2009.12.28 8753
10 9. 쿼리 변환 헌쓰 2009.12.28 14954
9 8. 비트맵 인덱스 ZoneWorker2 2009.12.28 7950
8 7. 히스토그램 ZoneWorker2 2009.12.28 9997
7 6. 선택도 이슈 ZoneWorker2 2009.12.28 7520
6 5. 클러스터링 팩터 balto 2009.12.28 14692
5 4. 단순 B-tree 액세스 file 휘휘 2009.12.28 8799
» 3. 단일 테이블 선택도 ZoneWorker2 2009.12.28 8297
3 2. 테이블 스캔 balto 2009.12.28 13117
2 1. 비용(COST)이란? ZoneWorker2 2009.12.28 17923
1 Front Page file 운영자 2009.12.21 130109