테이블 스캔

CBO의 4가지 전략

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

• 시스템통계의 CPU관련 옵션은 비공식 파라미터를 통해 컨트롤 가능.
  • 오라클 커널코드가 확장됨에 따라, 운영환경을 보다 잘 인지하여, 적절한 실행계획 생성가능하게 됨.

• 단, 위의 4가지 환경이 테이블 스캔비용이 전부가 아니다.
  • ASSM기능과 관련된 예외적사실, 병렬처리를 사용한 테이블스탠여부, 파티션 테이블 스캔여부도 함께 고려해야한다.

시작하면서

• 테스트를 위해, 10000개의 로우를 가진 테이블을 생성하는데, 이 데이터들이 여러블록에 걸치도록 데이터 생성한다,
• db_file_multiblock_read_count크기를 변경 하면서 비용과 조정된 dbf_mbrc확인
• 조정된 dbf_mbrc = 테이블블록수/테이블스캔비용

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

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

지속적인 개선

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

블록 크기의 효과

블록크기가 비용에 미치는 영향

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

병렬 실행

병렬도에 따른 비용의 변화

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

• 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이하의 블록개수 대신 인덱스 리프블록 개수를 비용계산에 사용한다.
• 파티션테이블은 하나의 파티션일때에는 해당 파티션 통계를 사용하고, 아니면 테이블 수준의 통계를 사용한다. 그러므로 파티션 추가/변경/삭제시 테이블 수준의 통계도 재생성해야 오류를 피할수 있다