by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

2. 테이블 스캔




테이블 스캔

  • CBO의 4가지 전략
    • 전통적인 방법: 단순히 읽기요청 횟수 계산, 오라클 7에서 처음 시작
    • 시스템통계(1) : * 읽기기요청의 크기와 시간 *도 계산식에 포함, 9i에서 시작
    • 시스템통계(2) : * CPU비용 *, 읽기요청의 크기와 시간을 계산식에 포함
    • 시스템통계(3) : * 캐싱효과 *, CPU비용, 읽기요청의 크기와 시간을 계산식에 포함

=> 시스템통계의 CPU관련 옵션은 비공식 파라미터를 통해 컨트롤 가능.
오라클 커널코드가 확장됨에 따라, 운영환경을 보다 잘 인지하여, 적절한 실행계획 생성가능하게 됨.
=> 단, 위의 4가지 환경이 테이블 스캔비용이 전부가 아니다.
ASSM기능과 관련된 예외적사실, 병렬처리를 사용한 테이블스탠여부, 파티션 테이블 스캔여부도 함께 고려해야한다.

시작하면서

  • 테스트를 위해, 10000개의 로우를 가진 테이블을 생성하는데, 이 데이터들이 여러블록에 걸치도록 데이터 생성한다,
  • db_file_multiblock_read_count크기를 변경 하면서 비용과 조정된 dbf_mbrc확인
  • 조정된 dbf_mbrc = 테이블블록수/테이블스캔비용
    db_file_multiblock_read_count 비용 조정된 dbf_mbrc
    4 2,396 4.17
    8 1,518 6.59
    16 962 10.40
    32 610 16.39
    64 387 25.84
    128 245 40.82

    => dbf_mbrc는 어느 지점을 지나서는 더이상 변하지 않음
    오라클은 인스턴스를 시작할때, 운영시스템이 허용하는 최대물리일기크기를 확인하여 db_file_multiblock_read_count의 한계치로 사용하므로

  • 조정된 dbf_mbrc는 비용계산목적으로 사용되며 실제 런타임시에 오라클은 테이블 스캔을 수행하기 위해 db_file_multiblock_read_count 파라미터를 사용.

지속적인 개선

  • 9i부터 블록크기를 다르게하여 테이블스페이스 생성가능

블록 크기의 효과

  • 블록크기가 비용에 미치는 영향
    블록크기 10,000블록 스캔비용 조정된 dbf_mbrc 80MB스캔비용
    2KB 611 16.39 2,439
    4KB 963 10.43 1,925
    8KB 1,519 6.59 1,519
    8KB ASSM 1,540 N/A 1,540
    16KB 2,397 4.17 1,199

    => 디폴트 블록크기가 8KB인데, 테이블이 4KB크기의 테이블스페이스에 저장되어 있다면 옵티마이저는 db_file_multiblock_read_count를 사실상 2배 늘림으로써 비용계산전에 읽기 크기를 64KB로 고정시킴
    => 블록크기를 증가시키면 테이블 스캔비용이 감소
    => ASSM을 사용하는경우 테이블스켄비용이 약 1.5%증가 (ASSM은 공간관리를 위한 비트맵 영영으로 몇개블록을 사용하므로)

CPU costing

CPU costing의 위력

BCHR은 죽었다! BCHR은 영원하다!

병렬 실행

  • 병렬도에 따른 비용의 변화
    병렬도 8i 9i(I/O) 10g(I/O) 9i(CPU) 10g(CPU)
    Serial 1518 1519 1519 5031 5030
    2 1518 760 844 2502 2779
    3 1518 507 563 1668 1852
    4 1518 380 422 1252 1389
    5 1518 304 338 1002 1111
    6 1518 254 282 835 926
    7 1518 217 242 716 794
    8 1518 190 211 627 695

=> 8i:최적의 직렬경로에 대해서 쿼리비용을 계산해서 최적화후, 실제 런타임시에만 병렬로 수행
9i:서로 충돌이 없는 상태에서 100% 병렬수행이가능하다고 가정하고, 병렬도로 나누어진 크기만큼의 데이터 집합에 대해서 효율적으로 최적화 수행
10g:비용계산시 병렬 효율도가 90%로 가정

버전에 따른 병렬처리비용
8i 병렬도가 N일 때의 처리비용 = 직렬처리비용
9i 병렬도가 N일 때의 처리비용 = ceil(직렬처리비용/병렬도)
10g 병렬도가 N일 때의 처리비용 = ceil(직렬처리비용/(0.9*병렬도)
- optimizer_percent_parallel=75 : 9i에서 병렬도가 4인경우 최종비용은 1519의 25%와 380의 75%를 합산하여 664.75가 된다.

  • pararallel_adaptive_multi_user=true : 제한된 수의 사용자만이 디폴트 병렬도로 병렬쿼리를 수행할수 있고, 하나 또는 두개의 병렬쿼리가 동시에 수행된다는 가전하여 비용을 계산
  • 시스템통계를 활성하지 않으면 비용계산시 직렬스캔과 같은 조정된 dbf_mbrf를 사용
  • 병렬수행으로 가면 옵티마이저는 CPU비용을 잃어버린다.

Index Fast Full Scan

  •  Index Fast Full Scan은 다중읽기를 사용하여 인덱스 세그먼트의 물리적인 블록 순서대로 읽는다. 이는 인덱스항목이 인덱스 순서대로 리턴되지는 않으므로 정렬비용이 데이터를 빠르게 읽음으로써 얻을수 있는 이익을 상쇄하게 된다,
  • 비용계산시 테이블 스캔과 같은 공식을 사용하며, 근거로 사용하는 수치는 리프블록 개수 이다.
  • 그러므로 인덱스 통계를 수집하지 않더라도, 오라클은 인덱스 세그먼트 헤더블록의 HWM정보를 사용하여 올바른 답을 찾는다.

파티셔닝

파티션된 테이블에 대해서, 데이터를 가져오는 범위에 따라 실행계획의 차이를 확인한다.

  • 리터럴 값을 사용하고 액세스 범위를 하나의 특정파티션으로 제한하는 경우
    • 120000 * ((100/199) + 1/100) = 61502
  • 리터럴 값을 사용하고 두개의 연속된 파티션을 포함되는 조건절을 가지는 경우
    • 1000000 * ((250-150)/999 +2/1000) = 102100
  • 바인드 변수를 사용하는 경우
    • 0.0025*1000000

요약

  • 테이블스캔비용은 곧, 다중 블록일기 비용이다. 그 비용은 테이블에 사용되는 블록개수를 다중 블록 읽기 크기의 추정치를 나타내는 특정값으로 나눈다.
  • 8i에서는 다중 블록읽기 횟수를 계산하여, 그것을 비용으로 계산
  • 9i에서는 시스템 통계를 도입하였고 CPU비용도 추가하였다.
  • 10g를 보면 미래에 옵티마이저가 최신의 캐시이력을 반영할 것이라는 단서를 발견할수있다.
  • INDEX FAST FULL SCAN인경우 HWM이하의 블록개수 대신 인덱스 리프블록 개수를 비용계산에 사용한다.
  • 파티션테이블은 하나의 파티션일때에는 해당 파티션 통계를 사용하고, 아니면 테이블 수준의 통계를 사용한다. 그러므로 파티션 추가/변경/삭제시 테이블 수준의 통계도 재생성해야 오류를 피할수 있다

문서에 대하여

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.