2. 테이블 스캔

조회 수 10635 추천 수 0 2010.01.13 00:49:19
balto *.217.121.48

CHAPTER 2. 테이블 스캔

발표/총정리 - 이창헌


시작하면서

/ 비용기반 옵티마이저의 네 가지 전락 요약

 1. 전통적인 방번 : 단순히 읽기 요청 횟수를 계산한다.
 2. 시스템 통계(1) : 읽기 요청의 크기와 시간도 계산식에 포함한다.
 3. 시스템 통계(2) : CPU비용, 읽기 요청의 크기와 시간도 계산식에 포함한다.
 4. 시스템 통계(3) : 캐싱 효과, CPU비용, 읽기 요청의 크기와 시간도 계산식에 포함한다.

 

옵티마이저가 계산하는 것과 런타임 시에 수행하는 것이 다를 수 있음을 인지 하자.


plan의 중요한 수치 의미
     -Cost : 누적 비용 
     -Card : 결과 로우 개수
    - Bytes : 결과 데이터양

 

 

오라클은 인스턴스를 시작할 때, 운영시스템이 허용하는 최대 물리 읽기 크기를 확인하여 그 값을 db_file_multiblock_read_count의 한계치로 사용한다.

 

calc_mbrc.sql 결과 : db_file_multiblock_read_count가 계속 증가하더라도 dbf_mbrc는 어느 지점을 지나서는 더 이상 변하지 않음
 


지속적인 개선 

     -블록 크기의 효과 

      64k 의미 : 표준 데이터베이스 블록 크기에 db_file_multiblock_read_count설정 값을 곱한 것 
    결론 오라클은 비표준 블록 크기(다중블록)를 다룰 때 표준 블록 크기에 기초한 읽기 크기를 지침으로 삼아 재해석하는 것으로 추정됨


 

      - CPU costing 

             Cost =  (#SRds * sreadtim + 
                            #MRds * mreadtim +
                            #CPUCycles / cpuspeed ) / sreadtim

 

각각이 의미하는 바는, 

- #SRDs is the number of single block reads

- #MRDs is the number of multi block reads

- #CPUCycles is the number of CPU Cycles *

- sreadtim is the single block read time

- mreadtim is the multi block read time

- cpuspeed is the CPU cycles per second  

- CPUCycles includes CPU cost of query processing (pure CPU cost) and CPU cost of data retrieval! (CPU cost of the buffer cache get)

 

CPU costing 설명 : 9i 이후 다중 블록 읽기의 전형적인 크기와 상대적인 수행속도를 의미하는 값에 의해 비용이 조정될 수 있게 하는 시스템 통계를 도입하였고, 메모리상의 블록을 읽고 해당 블록의 모든 로우로부터 데이터를 획득하는 데 소요되는 CPU비용을 추가..

        - CPU costing의 위력

                plan_table을 조회하면 cpu_cost와 filter_predicates컬럼(9i이후)값의 의미
                _pred_move_around 파라미터의 기본 설정의 true라는 사실


 

BCHR은 죽었다! BCHR은 영원하다 / 위충환

BCHR (Buffer Cache Hit Ratio) 정기적인 스냅샷을 통해서 일정 기간의 추세선으로 예외 발생 시 실마리 제공

  문제점 : 위의 역할이 시스템 전체에 걸친 평균에 불과, 이상한 통계정보를 가진 오브젝트 or 쿼리에 대해서 전혀 의미 없는 값

- 초기 비용기반 옵티마이저 결함 : 블럭 읽기 시 항상 물리 디스크 읽기가 필요한 것으로 가정하여 비용 계산

→ 실제 비용보다 추가된 비용으로 계산 가능

   해결 ☞ 오라클 8i - optimizer_index_cost_adj, optimizer_index_caching 2가지 파라미터 사용

              오라클 9i - 모든 데이터 세그먼트에 대한 논리적 블럭 요청 & 물리적 디스크 읽기 통계 수집


◆ 캐시와 관련 된 통계 수집 방법

- 9i : v$segstat 동적 성능 뷰 소개

- 10g : v$segstat 동적 성능 뷰의 통계 항목 확장

   ☞ v$segstat 동적 성능 뷰 역할

       경합 포인트가 어디인지 식별할 수 있도록 돕는 항목과 테이블 스캔과 index fast full scan 발생현황을 명시적으로 보여줌

- physical reads > logical reads 원인 : logical reads 통계는 샘플링에 의함

  1. 오브젝트에 대한 사용이 과도 할 경우
  2. 테이블에 대한 통계 정보 산정 시 DBMS_STATS 패키지에 의해 사용된

    Sample 절이 테이블 스캔의 런타임 메커니즘에 변형 → 논리 I/O 보다 물리 I/O가 더많게 보고 될 경우


◆ 오브젝트에 대한 논리 읽기 중 대개 얼마만큼이 물리 읽기로 바뀌는지를 설명하는 데이터를 가지고 있고

    그 정보로 부터 의미있는 분석값을 도출할 수 있다면 다음번 해당 오브젝트에 대한 쿼리를 최적화시 그 분석 정보 활용 가능

- sys.tab_stats$, sys.ind_stats$ 테이블 (오라클 10g) - obj#, cachehit 컬럼(논리적 오브젝트 수준의 cache hist ration의 한종류)포함

   다양한 시스템 내부적 쿼리에서 나타나기는 하지만, 기본적으로 데이터가 생성되는 것 같이 않음

- sys.cache_stats_1$ - inst_id 컬럼으로 각각의 RAC 인스턴스가 서로 다른 컬럼값을 가지며 cache hit ration는 지역적으로 각각 관리

                  DBMS_STATS 패키지에 캐시 통계를 수집하는 옵션 추가시 프로시저들이 cache_stats_1$ 테이블을 엑세스하는 것 발견

      execute dbms_stats.gather_table_stats(user,'t1', stattype => 'cache')

 

◆ hidden 파라미터

_cache_stats_monitor(디폴트 true) - 캐시 통계의 수집을 활성화

_optimizer_cache_stats (디폴트 false) - 최적화 과정에 캐시 통계의 사용 활성화

  (세션레벨에서 설정할수 있고 시스템에 손상을 주지 않음)

                   예)   _optimizer_cache_stats : false 시 테이블 스캔비용 = 5030

        _optimizer_cache_stats : true 시 테이블 스캔비용 = 5025

                            ☞ 캐시통계 활성시 최적화 과정 동안 어떤 sql도 cache_stats_1$에 접근하는것을 볼수 없음

                                반면 새로운 구분을 최적화시 옵티마이저는 비어있는 tab_stats$와 ind_stats$ 테이블에는 항상 접근


병렬 실행(Parallel Execution) / 남송휘



Select /*+ parallel (t1,1) */ count(*) from t1;
Select /*+ parallel (t1,2) */ count(*) from t1;
Select /*+ parallel (t1,3) */ count(*) from t1;
Select /*+ parallel (t1,4) */ count(*) from t1;
Select /*+ parallel (t1,5) */ count(*) from t1;
Select /*+ parallel (t1,6) */ count(*) from t1;
Select /*+ parallel (t1,7) */ count(*) from t1;
Select /*+ parallel (t1,8) */ count(*) from t1;

parallel_max_servers 페라메타를 최소 8로 둔 결과


degree
8i
9i(I/O)
10g(I/O)
9i(CPU)
10g(CPU)
serial
1,518
1,519
1,519
5,031
5,030
2
1,518 760
844
2,502
2,779
3
1,518 507
563
1,668
1,852
4
1,518 380
422
1,252
1,389
5
1,518 304
338
1,002
1,111
6
1,518 254
282
835
926
7
1,518 217
242
716
794
8
1,518 190
211
627
695





첫번째 부터 3번째 컬럼

  • 8i Cost at degree N = serial cost
  • 9i Cost at degree N = ceil (serial cost / N )
  • 10g Cost at degree N = ceil (serial cost / (0.9 * N))
  • 8i serial path로 비용을 계산하고, 병렬로 실행한다.
  • 9i 충돌이 없다면 100% 병렬로 실행된다.
  • 10g 병렬처리 인수를 90로 하여 계산한후 실행된다.
  • optimizer_percent_parallel 에 의해  처리되며
  • 8i에서는 0 (시리얼로 계산)
  • 9i에서는 101이 기본 (100% 병렬, _optimizer_percent_parallel)
  • 9i의 결과를 보면 병렬처리도 4는 380, 시리얼스캔일경우 1519 이다
  • 만약 _optimizer_percent_parallel 을 75로 두면 1519의 25% + 380의 75% 로하여 664.75가 된다.

parallel_adaptive_multi_user
- true (10g에서는 default) 로 변경하면 기본 병렬도로 제한적인 사람만이 처리할수있도록 허가된다.
9i에서는 한명, 10g에서는 두명이었고 (저자의 시스템환경에서)
한게설정은 _parallel_adaptive_max_users에서 한다
(하지만 오라클이 실행될때 parallel_max_servers의 계산에 사용되므로 수정하지 않는게 좋다)

parallel scan은 direct path read를 한다 (data buffer를 지나지 않음)
db_file_multiblock_read_count 의 크기에 필연적인 영향을 받음



5,6 번째 컬럼

  • system statistics 가 사용되어 MBRC statistic 계산에 옵티마이져가 저장되었있는 값을 사용하게 된다.


Index Fast Full Scan     

/ 이태경

Index Fast Full Scan
 
발생조건
  • Where절이나 Select절에 사용된 컬럼이 모두 하나의 인덱스에 구성된 컬럼인 경우
특징
  • Index Fast Full Scan 은 Table Full Scan 과 동일한 방식의 Multi Block I/O 를사용한다.
  • 정렬이 보장이 안됨.
  • 그 만큼 속도는 빠르다. (이론적으론, 정렬 비용이 디스크로부터 데이터를 빠르게 읽음으로써 얻을 수 있는 이익을 상쇄)
  • Parallel로 수행 가능하다.
  • Full Table Scan보다 읽어야 할 Block의 수가 적어 유리하다. 
 
Index Fast Full Scan 비용계산의 근거 수치는?
  • Leaf Block 통계값(필자의 추측)
        => Index Leaf Block 개수가 HWM(High Water Mark) 아래 전체 블록 개수의 1% 이내일 경우 Index Fast Full Scan을 수행
  • Leaf Block 개수라고 필자가 추측하게 된 이유
    1. dbms_stats.set_index_stats 패키지 프로시져를 사용하여 리프 블록 값을 변경시키면서 쿼리 비용 변화 관찰
    2. BLEVEL(Branch Level)을 바꾸면서 비용 변화 관찰
    3. 다른 통계 값을 바꿔보면서 비용변화 관찰 (참고. hack_stats.sql 스크립트)
 
부작용
  • 가정) 인덱스 리프 블록의 개수를 조작함으로써 Range Scan이 더 효일적임에도 Index Fast Full Scan을 수행
  • 원인분석) 만일 리프 블록에 '비어있는' 범위가 있으면, Analyze명령에서 전략적인 dbms_stats사용으로 전환했을 경우 뚜렷한 이유 없이 Index Fast Full Scan을 시작할 수 있음.(아래 표 참조)
 
명령

특징

analyze 명령

현재 인덱스 구조에 있는 리프 블록의 개수를 그대로 보고

dbms_stats.gather_index_stats 프로시져

실제 데이터를 가지는 리프 블록의 개수를 보고

 

 

 
 
  • 범위) 이런 문제는 오직 인덱스 컬럼 만으로 충족되는 쿼리일 때 나타날 수 있다.
  • 해결방안) 일부 인덱스를 주기적으로 Rebuild 해야함.

용어설명(HWM, Leaf Block, BLEVEL) : http://docs.google.com/View?id=dhsx8hc4_0gfj3z4gw


 

파티셔닝                      
/ 박우창

 

(요약) 테이블이 partition되어 있을 경우 질의최적기는 비용 예측에 오류를 발생시킨다.

? 파티션 정의및 장점 : 참고자료 - http://tong.nate.com/questron/25425911

(정의)

- Partitioning 이란 큰 Object 를 작고 Manage가 가능하게 분리하는 것을 의미하며,

Table 이나 Index 에서만 가능하고 Cluster, Snapshot 은 불가능 합니다. (Oracle8기준)

- 각 Partition 은 별개의 Segment에 저장 되어 집니다.

- Oracle8에서 Table은 기본이 되는 Key Value에 의해 Partition으로 분리되어 집니다.

- 각 Partition은 독립적으로 운영 됩니다.

- 예를 들면 Table Partition은 DML(insert, update, delete) 문에 의한 Transaction이

다른 Partition에 영향을 주지 않고 사용이 가능 합니다.

- DBA_TAB_PARTITIONS에 각 Partition의 Storage 정보가 있습니다.

(장점)

- 여러 분할 영역에서의 데이터 훼손 가능성이 감소됩니다.

- 각 분할 영역을 독립적으로 백업하고 복구 할 수 있습니다.

- 더 용이하게 관리할 수 있으며 가용성 및 성능을 향상시킵니다.

* (주의) 교재 43페이지 내용 수정 필요(rownum <= 10000000 => rownum <= 1000000)

? 실험 : 파티션의 경우 카디널리티 예측이 잘못되는 사례

(실험) partition.sql 실행

(파티션 예제) - 5개의 파티션이 있을 경우, 3가지질의로 실험

파티션 =>

part_col 값

0-200

200-400

400-600

600-800

800-1000

(질의조건)

40,000개

120,000개

200,000개

280,000개

360,000개

질의 1

250-350

질의 2

150-250

질의 3

:v1

:v2

:v1-:v2

(결과)

질의 1 : 결과 튜플이 파티션 내에서 있을때

=> 파티션 수준의 통계로 예측하여 카디널리티 예측 성공

(실제 카디널리티) 61,502

(예측 카디널리티) 61,502 = 120,000*(350-250)/199 +120,000*2/200

 

질의 2 : 결과 튜플이 파티션 경계를 넘어 존재할 경우

=> 테이블 수준의 통계로 예측하여 카디널리티 예측 오류 발생

(실제 카디널리티) 48,100 =

(예측 카디널리티) 102,000 = 1,000,000*((250-150)/999+2/1000)

 

질의 3 : 바인드 변수를 사용할 경우(파티션을 모르기 때문에)

=> 테이블 수준의 통계로 예측하여 카디널리티 예측 오류 발생

(실제 카디널리티) 40,000

(예측 카디널리티) 2500 = 1,000,000* 0.25%

* 9i,10g에서 바인드변수를 peeking 하는 기능을 이용하여 바이드 변수 예측 가능,
그러나 바인드 변수 값이 다시 실행할 때 변하면 실행계획이 적응을 못하는 문제가 발생함.

? 파티션 수준 통계는 파티션 내에서 카디널리티 계산을 하는 방법으로 필요없는 파티션의 데이터를 미리 배제하기 때문에 비교적 예측범위를 제한함. 테이블 수준 통계는 데이터가 파티션 경계를 넘어 있을 경우 해당되는 파티션들만을 가지고 계산해야함에도 그렇지 않고 테이블 전체를 대상으로 계산하기 때문에 데이터 분포가 스큐(skew, 치우침)이 있을 경우 당연히 예측에 오류가 있음.


 

요약                            

/ 이창헌


테스트 스크립트            

/ 이창헌





본자료는 durinuri.com 의 비용기반의 오라클원리 스터디 결과로 만들어진자료입니다.

자료를 사용하실경우 출처를 밝혀주시기바립니다. ^^