비용(Cost)이란?
SQL문의 Cost는 실행계획 같은 도구에 의해 만들어진 수치와 SQL이 수행될때 실제 소비되는 자원을 의미한다.
이 책에서의 \*비용*은 항상 옵티마이저에 의해서 수행된 계산결과를 의미한다.
옵티마이저 옵션
옵티마이저는 여러가지 옵션이 있다. 비용기반(all_rows, first_rows_N, first_rows)옵션과 rule, choose가 있다.
- cost
- all_rows : 모든 ROW를 리턴하는데 가장 빠른 실행계획을 찾음.
- first_rows_N : 전체데이터 중 n만큼 리턴하는데 소유되는 자원을 최소화하는 실행계획을 찾음.
- first_rows : 가장 빨리 첫번째 row를 리턴하는 실행계획을 찾음. 9i에서 deprecated됨.
- rule : 수년전에 deprecated되었기 때문에 여기서는 언급안함.
- choose
- 옵티마이저가 런타임시에 rule과 all_rows중을 선택함.
- 10g의 경우 통계정보를 자동으로 수집하는 JOB을 설치하므로 all_rows가 선택될 가능성이 높다.
비용(COST)의 의미
비용은 옵티마이저가 SQL구문을 수행하는데 소요될 최적의 시간을 추정하여 하나의 수치로 표현한것이다.
CBO가 오류를 범하는 주된 요인
- 비용모델이 몇가지 잘못된 가정을 포함하고있다
- 데이터 분포에 대한 통계정보가 없다.
- 통계정보가 있지만, 잘못 이해한다.
- 하드웨어 특성을 모른다.
- 현재 작업 부하를 모른다.
- 실행코드안에 버그가 있다.
=> 이러한 문제를 해결하기 위해, 옵티마이저는 진화해오고 있다.
버전에 따른 CBO의 변화
- 8i
- 단순히 I/O 요청횟수가 가장 적은 실행계획을 선택
- CPU 및 시간자원에 대한 비용에 대해서는 고려하지 않음
- 9i
- CPU costing이라는 기능을 소개하여 단일블록 읽기응답시간, 다중블록 읽기응답시간등을 비용계산식에 고려함.
- 10g
- 주요통계정보를 프로파일 형태로 저장하여, 데이터 값의 상관관계에 인해 비용계산이 잘못되는 문제를 보완.
CPU costing 모델
비용
비용 = (단일블록 읽기요청 횟수 * 단일블록 읽기시간 + 멀티블록 읽기요청 횟수 * 멀티블럭 읽기시간 + CPU Cycle수/초당 CPU Cycle수) / 단일블록 읽기 시간
=> 이 식에서 보면 비용은 SQL문을 수행할때 걸리는 시간을 단일블록 읽기 시간단위로 표현한 것임을 알수있음.
- CPU Costing 기능을 활성화하면 이전에 비하여, 테이블 스캔비용이 배수만큼 올라간다.
- 따라서 인덱스 액세스 경로가 더 선호된는 경향이 있는데 이는 다중블록 읽기가 단일블록 읽기가 더 많은 시간이 소요된다는 점을 인지하기 때문
- 단, 머지조인, 해시조인, 정렬처리로 인한 I/O에 소요되는 시간부분은 direct path write and read 방식을 사용하며, 이때 사용하는 I/O는 일반적인 다중블록 읽기 크기와 무관하다.
쿼리 변환과 비용 계산
오라클은 비용을 계산하기 전에 실행한 SQL을 \*동등한 다른 구문으로 우선 변형*하고 변형된 그문으로 비용을 계산한다.
오라클 버전에 따라 구문변형이 일어나거나, 일어나지 않거나, 일어났다가 비용이 많이 들어 폐기되기도 한다.
하나의 쿼리에 대해, 변형가능한 두가지 가능성에 대한 고찰
- 우리가 만든 SQL문은..
- View를 하나 생성하는데, 이 View는 특정 테이블에 대해 집계 뷰이다.
- 생성한 View와 테이블을 조인한다.
- 우리가 만든 SQL을 실행하면 오라클이 결과집합을 만드는 두가지 방법
- 뷰를 먼저 수행하여 중간집합을 만들고 그것을 테이블과 조인
- 뷰안에 있는 쿼리문을 풀어내어 테이블과 머지하는 방법
=> 둘중에 어떤 엑세스 경로가 더 좋은걸까? 그건 데이터 분포에 따라 그때그때 달라요\!
=> 그럼 옵티마이저는 어떤 실행계획을 선택할까? 그건 오라클 버전에 따라 다르지요\!
위지위그
옵티마이저가 수립한 실행계획은 런타임시 실행경로와 다를수 있다.
요약
같은 SQL인데도 불구하고, 다양한 결과가 나타는 이유는 버전에 따라 오라클도 기능개선을 하고 있고, 또한 여러 옵티마이저 모드가 있기 때문이기도 하다.
그래서 A는 B다라는 명쾌한 대답을 못주고, 그때 그때 달라요~라고 말할수 밖에 없는 이유다.
대신, 기초가 되는 계산식은 항상 같다는걸로 위안을 삼을수 있고, 이로인해 옵티마이저가 수행하는 것의 95%정도는 설명이 가능하다.
문서에 대하여