비용(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됨.

choose

• 옵티마이저가 런타임시에 rule과 all_rows중을 선택함.
• 10g의 경우 통계정보를 자동으로 수집하는 JOB을 설치하므로 all_rows가 선택될 가능성이 높다.

비용(COST)의 의미

• 비용은 옵티마이저가 SQL구문을 수행하는데 소요될 최적의 시간을 추정하여 하나의 수치로 표현한것이다.

CBO가 오류를 범하는 주된 요인

• 비용모델이 몇가지 잘못된 가정을 포함하고있다
• 데이터 분포에 대한 통계정보가 없다.
• 통계정보가 있지만, 잘못 이해한다.
• 하드웨어 특성을 모른다.
• 현재 작업 부하를 모른다.
• 실행코드안에 버그가 있다.

• => 이러한 문제를 해결하기 위해, 옵티마이저는 진화해오고 있다.

버전에 따른 CBO의 변화

8i

• 단순히 I/O 요청횟수가 가장 적은 실행계획을 선택
• CPU 및 시간자원에 대한 비용에 대해서는 고려하지 않음

9i

• CPU costing이라는 기능을 소개하여 단일블록 읽기응답시간, 다중블록 읽기응답시간등을 비용계산식에 고려함.

10g

• 주요통계정보를 프로파일 형태로 저장하여, 데이터 값의 상관관계에 인해 비용계산이 잘못되는 문제를 보완.

CPU costing 모델

• CPU Costing 기능을 활성화하면 이전에 비하여, 테이블 스캔비용이 배수만큼 올라간다.
• 따라서 인덱스 액세스 경로가 더 선호된는 경향이 있는데 이는 다중블록 읽기가 단일블록 읽기가 더 많은 시간이 소요된다는 점을 인지하기 때문
• 단, 머지조인, 해시조인, 정렬처리로 인한 I/O에 소요되는 시간부분은 direct path write and read 방식을 사용하며, 이때 사용하는 I/O는 일반적인 다중블록 읽기 크기와 무관하다.

쿼리 변환과 비용 계산

• 오라클은 비용을 계산하기 전에 실행한 SQL을 \*동등한 다른 구문으로 우선 변형*하고 변형된 그문으로 비용을 계산한다.
• 오라클 버전에 따라 구문변형이 일어나거나, 일어나지 않거나, 일어났다가 비용이 많이 들어 폐기되기도 한다.

하나의 쿼리에 대해, 변형가능한 두가지 가능성에 대한 고찰

• 우리가 만든 SQL문은..
  • View를 하나 생성하는데, 이 View는 특정 테이블에 대해 집계 뷰이다.
  • 생성한 View와 테이블을 조인한다.

• 우리가 만든 SQL을 실행하면 오라클이 결과집합을 만드는 두가지 방법
  • 뷰를 먼저 수행하여 중간집합을 만들고 그것을 테이블과 조인
  • 뷰안에 있는 쿼리문을 풀어내어 테이블과 머지하는 방법

• => 둘중에 어떤 엑세스 경로가 더 좋은걸까? 그건 데이터 분포에 따라 그때그때 달라요\!
• => 그럼 옵티마이저는 어떤 실행계획을 선택할까? 그건 오라클 버전에 따라 다르지요\!

위지위그

• 옵티마이저가 수립한 실행계획은 런타임시 실행경로와 다를수 있다.

요약

• 같은 SQL인데도 불구하고, 다양한 결과가 나타는 이유는 버전에 따라 오라클도 기능개선을 하고 있고, 또한 여러 옵티마이저 모드가 있기 때문이기도 하다.
• 그래서 A는 B다라는 명쾌한 대답을 못주고, 그때 그때 달라요~라고 말할수 밖에 없는 이유다.
• 대신, 기초가 되는 계산식은 항상 같다는걸로 위안을 삼을수 있고, 이로인해 옵티마이저가 수행하는 것의 95%정도는 설명이 가능하다.