비용기반의 오라클 원리 (2009년)
비용이란? 0 0 52,571

by 구루비스터디 Cost CBO 비용 [2018.09.27]


비용(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 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%정도는 설명이 가능하다.
"코어 오라클 데이터베이스 스터디모임" 에서 2009년에 "비용기반의 오라클 원리 " 도서를 스터디하면서 정리한 내용 입니다.

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

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

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

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