Post

<오라클 성능 고도화 원리와 해법2> Ch03-07 비용

Posted
By Mijong Go 19 min read

오라클 성능 고도화 원리와 해법2 - Ch03-07 비용

오라클 옵티마이저가 사용하는 비용 모델에는 I/O 비용 모델과 CPU 비용 모델 두 가지가 있다. I/O 비용 모델은 예상되는 I/O 요청(Call) 횟수만을 쿼리 수행 비용으로 간주해 실행 계획을 평가하는 반면 CPU 비용 모델은 여기에 시간 개념을 더해 비용을 산정한다.

비용 모델을 선택할 때 사용하는 파라미터는 optimizer cost model이고, 아래 세 가지 옵션이 제공된다.

• IO: I/O 비용 모델 • CPU: CPU 비용 모델 • CHOOSE: 시스템 통계가 있으면 CPU 비용 모델, 없으면 I/O 비용 모델 (optimizer mode를 choose로 설정했을 때 오브젝트 통계가 있으면 CBO, 없으면 RBO로 동작하는 것과 같은 개념)

기본값은 CHOOSE이고, 9i에서는 기본적으로 시스템 통계를 갖지 않으므로 관리자가 시스템 통계를 생성해줄 때만 CPU 비용 모델 방식으로 작동한다. 10g에서는 관리자가 Workload 시스템 통계를 생성하지 않더라도 CPU 비용 모델이 선택되도록 하려고 NoWorkload 시스템 통계를 추가로 도입하였다.

참고로, 아래 두 힌트를 이용하면 쿼리 레벨로도 비용 모델을 선택할 수 있다.

• cpu_costing • no_cpu_costing

(1) I/O 비용 모델

I/O 비용 모델에서의 비용은 디스크 I/O Call 횟수(논리적/물리적으로 읽은 블록 개수가 아닌 I/O Call 횟수)를 의미한다.

인덱스를 경유한 테이블 액세스 비용

인덱스를 경유한 테이블 액세스 시에는 Single Block I/O 방식이 사용된다. 이는 디스크에서 한 블록을 읽을 때마다 한 번의 I/O Call을 일으키는 방식이므로 읽게 될 물리적 블록 개수가 액세스 비용과 일치한다.

1장 4절에서 클러스터링 팩터와 함께 잠시 언급했듯이 인덱스를 이용한 테이블 액세스 비용은 아래와 같은 공식에 의해 구해진다.

1
2
3

비용 = blevel +                          -- 인덱스 수직적 탐색 비용
      (리프 블록 수 X 유효 인덱스 선택도) +    -- 인덱스 수평적 탐색 비용
      (클러스터링 팩터 X 유효 테이블 선택도)    -- 테이블 Random 액세스 비용
  • 브랜치 레벨 (blevel): 리프 블록에 도달하기 전에 읽게 될 브랜치 블록 개수를 의미함.
  • 유효 인덱스 선택도: 전체 인덱스 레코드 중에서 조건절을 만족하는 레코드를 찾기 위해 스캔할 것으로 예상되는 비율 (%). 리프 블록에는 인덱스 레코드가 정렬된 상태로 저장되므로 이 비율이 방문할 리프 블록 비율을 의미함.
  • 유효 테이블 선택도: 전체 레코드 중에서 인덱스 스캔을 완료한 후에 최종적으로 테이블을 방문할 것으로 예상되는 비율 (%). 클러스터링 팩터는 인덱스를 경유해 전체 로우를 액세스할 때 읽힐 것으로 예상되는 테이블 블록 개수이므로 여기에 유효 테이블 선택도를 곱함으로써 조건절에 대해 읽힐 것으로 예상되는 테이블 블록 개수를 구할 수 있음.

공식에 사용된 두 선택도의 의미를 1장 7절 (9)항에 설명한 Access Predicate, Filter Predicate와 연관시키면 아래와 같다.

  • 유효 인덱스 선택도: 인덱스 Access Predicate에 의해 결정됨.
  • 유효 테이블 선택도: 인덱스 Access Predicate와 Filter Predicate에 의해 결정됨.

테이블 액세스 후 최종 선택도는 테이블 Filter Predicate까지 포함한 모든 조건에 의해 결정된다.

  • (최종) 테이블 선택도: 테이블 Filter Predicate까지 포함한 모든 조건에 의해 결정됨.

조건절이 아래와 같은 형태가 아니라면, 인덱스 컬럼에 대한 조건은 모두 인덱스 Access Predicate에 포함된다고 1장에서 설명했다. 바꿔 말하면, 아래 경우에만 ‘인덱스 Access Predicate와 인덱스 Filter Predicate가 다르다.

  • 좌변 컬럼을 가공한 조건절
  • 왼쪽 % 또는 양쪽 ‘ % 기호를 사용한 like 조건절
  • 같은 컬럼에 대한 조건절이 두 개 이상일 때, 인덱스 액세스 조건으로 선택되지 못한다는 조건절

선택도는 각 조건절 컬럼의 선택도를 곱해서 구하므로 결론적으로, SQL이 위 조건절을 포함하는 경우를 제외하면 유효 인덱스 선택도와 유효 테이블 선택도는 항상 같다.

이 선택도는 numdistinct 컬럼 통계를 이용해 구한다. 그리고 브랜치 레벨과 리프 블록 수, 클러스터링 팩터는 모두 인덱스 통계에서 얻을 수 있다.

Full Scan에 의한 테이블 액세스 비용

테이블을 Full Scan할 때는 HWM 아래쪽 블록을 순차적으로 읽어들이는 과정에서 발생하는 I/O Call 횟수로 비용을 계산한다. Full Scan할 때는 한 번의 I/O Call로써 여러 블록을 읽어들이는 Multiblock I/O 방식을 사용하므로 이론적으로는 총 블록 수를 db.filemultiblockread.count 파라미터로 나눈만큼 I/O Call이 발생해야 한다. 하지만 내부적으로 조정된 값으로 비용을 계산하기 때문에 실제 테스트해보면 우리의 예상치와 정확히 일치하지 않는다.

그렇더라도 Multiblock I/O 단위를 증가시킬수록 I/O Call 횟수가 줄고 예상 비용도 준다는 사실은 쉽게 확인할 수 있다.

I/O 비용 모델의 비현실적인 가정

디스크 I/O Call 횟수로써 테이블 액세스 비용을 평가한다는데에는 아래 두 가지 중요한 의미가 내포돼 있다.

  • Single Block I/O와 Multiblock I/O는 비용이 같다.
  • 캐싱 효과를 전혀 고려하지 않는다.

한 번에 한 블록씩 읽는 비용과 128 블록씩 읽는 비용이 같다거나 모든 블록을 디스크에서 읽는다는 것은 매우 비현실적인 가정이다. 따라서 이를 보정하기 위한 아래 두 파라미터가 오라클 8 버전부터 제공되기 시작하였다.

optimizer_index_cost_adj

이 파라미터는 인덱스 탐색 비용을 조정하고자 할 때 사용하며, 설정할 수 있는 값의 범위는 1~10,000이다. 기본값으로 설정된 100이란 수치는, 한 번의 I/O Call을 통해 Single Block Read 방식으로 한 블록을 읽는 비용과 Multiblock Read 방식으로 여러 블록을 읽는 비용을 같게 평가하라는 뜻이다. 만약 이 값을 25로 변경하면 옵티마이저는 Single Block Read 방식의 I/O Call 비용을 Multiblock Read 방식의 I/O Call 비용의 25%로 간주한다.

일반적으로 Single Block Read는 인덱스를 경유한 테이블 액세스시에 사용되고, Multiblock Read는 Full Table Scan 시 사용한다. 따라서 이 파라미터는 인덱스를 경유할 때의 물리적 I/O 비용을 Full Table Scan 할 때의 물리적 I/O 비용과 대비한, 다시 말해 상대적 비용을 표현한 것이라고 할 수 있다.

이 값을 낮게 설정할수록 옵티마이저는 테이블 스캔보다 인덱스를 이용한 테이블 액세스를 선호하게 된다.

optimizer_index_caching

옵티마이저는 NL 조인 시 Inner 테이블 쪽 인덱스 블록을 매번 디스크에서 읽는다고 가정(opimizer_indexcaching=)하지만 이는 비현실적이다. 특히 작은 테이블일 때는 대부분 캐싱된 블록을 읽게 된다. optimizer_index_caching은 NL 조인에서 inner 쪽 인덱스 블록이 캐싱돼 있을 가능성을 옵티마이저에게 알려주는 파라미터이며, W-List Iterator 방식으로 인덱스를 탐색할 때 읽게 되는 인덱스 블록 액세스 비용에도 영향을 미친다.

설정할 수 있는 값의 범위는 0~10이며, 이 값을 높게 설정할수록 옵티마이저는 인덱스를 이용한 NL 조인을 선호하게 된다.

(2) CPU 비용 모델

모든 데이터베이스 오퍼레이션은 CPU를 사용하며, 경우에 따라서는 I/O보다 성능에 더 큰 영향을 끼치기도 한다. 아래는 블록 I/O가 소량인데도 쿼리 수행 시간이 상당히 오래 걸리는 경우다.

  • 해시 조인할 때, 해시 체인에 달린 레코드가 많아 해시 체인을 스캔하는 부하가 심할 때
  • 캐싱된 블록을 반복적으로 읽는데, 한 블록 내에서 매번 비효율적으로 많은 레코드를 스캔할 때 (예를 들어, NL 조인 Inner 쪽 인덱스 선두 컬럼이 between 조건일 때)
  • 버퍼를 Pin한 상태에서 같은 블록을 반복 액세스할 때
  • 다량의 레코드를 읽으면서 건건이 여러 개의 사용자 정의 함수를 반복 호출할 때
  • 메모리 소트를 반복할 때

아래와 같은 경우에도 CPU 사용량이 다소 증가한다.

  • 조건절 개수가 아주 많을 때
  • 조건이나 select-list에 연산 집약적인 작업을 많이 포함할 때

이에 오라클은 쿼리 수행에 필요한 I/O뿐만 아니라 예상되는 CPU 리소스 사용량까지 비용 계산식에 포함하는 새로운 비용 모델, 이른바 CPU 비용 모델을 91에서 선보였고, 이로써 CBO가 이전보다 더 나은 실행 계획을 수립할 수 있게 되었다.

CPU 비용 모델에서의 비용 계산식은 다음과 같다.

1
2
3
4

Cost = (  #SRds * sreadtim +
          #MRds * mreadtim +
          #CPUCycles / cpuspeed
        )  /  sreadtim
  • #SRds - Single Block I/O 요청 횟수
  • #MRds - Multiblock I/O 요청 횟수
  • #CPUCycles - 쿼리 수행에 필요한 CPU 사이클 수
  • sreadtim - Single Block I/O에 소요되는 시간(ms)
  • mreadtim - Multiblock I/O에 소요되는 시간(ms)
  • cpuspeed - 초당 처리할 수 있는 CPU 사이클 수

sreadtim, mreadtim, cpuspeed는 mbrc와 함께 시스템 통계 수집 시 미리 측정해둔 값이다. 4절 통계 정보 I에서 (4) ‘시스템 통계’를 참조하기 바란다.

cpuspeed의 의미를 오라클 매뉴얼에서 찾아보면 ‘CPU 속도를 초당 사이클 수로 표현한 것’이라고 단순하게 설명되어 있다. 이에 대한 좀 더 구체적인 설명을 Jonathan Lewis의 저서 Cost-Based Oracle Fundamentals에서 찾을 수 있는데, 이 책에서 소개한 Christian Antognini의 주장에 따르면 ‘사용자의 플랫폼에서 오라클이 수행해본 몇 가지 표준 오퍼레이션의 초당 수행 횟수를 표현한 것’이라고 한다.

I/O 비용

공식에서 알 수 있는 것처럼 Single Block I/O 요청 횟수(#SRds)와 Multiblock I/O 요청 횟수 (#MRds)에, 미리 측정해놓은 평균 소요 시간 (sreadim, mreadim)을 각 곱함으로써 I/O 일량을 시간으로 표현하였다. 그러면서 인덱스 스캔에 의한 테이블 액세스 비용과 테이블 스캔 비용 간에 상대적인 시간 차이가 있음을 계산식에 포함시켰다.

CPU 비용

I/O 비용뿐만 아니라 CPU 비용을 계산식에 포함하고 있다 (4장 12절 3항조건비교순서 참고). CPU 비용은 ‘예상 CPU 사이클 수 (#CPUCycles)를’ 초당 처리할 수 있는 CPU 사이클 수 (cpuspeed)로 나눈 값으로 계산한다. 쿼리 수행에 필요한 ‘예상 CPU 사이클 수에는 순수한 CPU 연산 비용과 버퍼 캐시에서 데이터를 읽는 CPU 비용까지를 포함한다.

이처럼 I/O 시간과 CPU 연산 시간을 더한 시간 개념을 빌어 쿼리 수행 비용을 평가한다는 것이 CPU 비용 모델의 핵심이다. 또 다른 주목할 만한 사실은, 이렇게 I/O 시간과 CPU 연산 시간을 더 한 값으로 Single Block I/O 평균 소요 시간 (sreadim)으로 나눔으로써 Single Block I/O에 소요되는 시간과의 상대적인 시간 비용으로 표현했다는 것이다.

결론적으로 CPU 비용 모델에서 말하는 비용은, 쿼리의 예상 총 수행 시간을 Single Block I/O 시간 단위로 표현한 것이라고 할 수 있다. 예컨대, 비용이 10으로 계산됐다면 10번의 Single Block I/O를 수행하는 만큼의 시간이 걸릴 것임을 의미한다.

비용에 시간 개념을 더한 것에 그치지 않고 오라클은 캐싱 효과를 비용 계산식에 포함하려는 움직임을 보이고 있다. 10g에서 아래 쿼리를 수행해보면 테이블별로 캐싱된 블록 수와 캐시 히트율을 보여주는데, 이 값이 언제부터 어떻게 활용될지는 아직 알 수 없다.

1
2

select table_name, avg_cached_blocks, avg_cache_hit_ratio
from user_tab_statistics;
Database, SQL Tuning
SQLP
This post is licensed under CC BY 4.0 by the author.