비용기반의 오라클 원리 (2009년)
테이블 스캔 0 0 51,518

by 구루비스터디 CBO 테이블 스캔 [2018.09.27]


테이블 스캔


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
42,3964.17
81,5186.59
1696210.40
3261016.39
6438725.84
12824540.82


  • dbf_mbrc는 어느 지점을 지나서는 더이상 변하지 않음
    • 오라클은 인스턴스를 시작할때, 운영시스템이 허용하는 최대물리일기크기를 확인하여 db_file_multiblock_read_count의 한계치로 사용하므로
  • 조정된 dbf_mbrc는 비용계산목적으로 사용되며 실제 런타임시에 오라클은 테이블 스캔을 수행하기 위해 db_file_multiblock_read_count 파라미터를 사용.


지속적인 개선

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


블록 크기의 효과


블록크기가 비용에 미치는 영향
블록크기10,000블록 스캔비용조정된 dbf_mbrc80MB스캔비용
2KB61116.392,439
4KB96310.431,925
8KB1,5196.591,519
8KB ASSM1,540N/A1,540
16KB2,3974.171,199


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


병렬 실행

병렬도에 따른 비용의 변화
병렬도8i9i(I/O)10g(I/O)9i(CPU)10g(CPU)
Serial15181519151950315030
2151876084425022779
3151850756316681852
4151838042212521389
5151830433810021111
61518254282835926
71518217242716794
81518190211627695


  • 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이하의 블록개수 대신 인덱스 리프블록 개수를 비용계산에 사용한다.
  • 파티션테이블은 하나의 파티션일때에는 해당 파티션 통계를 사용하고, 아니면 테이블 수준의 통계를 사용한다. 그러므로 파티션 추가/변경/삭제시 테이블 수준의 통계도 재생성해야 오류를 피할수 있다
"코어 오라클 데이터베이스 스터디모임" 에서 2009년에 "비용기반의 오라클 원리 " 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/3975

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입