SQL) 옵티마이저 원리 Ⅱ

jinsung·2026년 1월 4일
SQLPoraclesql데이터자격증

SQL

목록 보기
35/46
post-thumbnail

4. 통계정보 Ⅰ

실행계획을 수립할 때 CBO는 SQL 문장에서 액세스할 데이터 특성을 고려하기 위해 통계정보를 이용한다.
따라서 최적의 실행계획을 위해서는 통계정보가 데이터 상태를 정확하게 반영하도록 관리해 주어야 한다.

1. 테이블 통계

테이블 통계만 수집할 때는 아래 명령어를 사용하며, compute는 전수 검사, estimate는 표본 조사를 뜻한다.

ANALYZE TABLE jiyun compute statistics for TABLE;
ANALYZE TABLE jiyun estimate statistics sample 5000 rows for TABLE;
ANALYZE TABLE jiyun estimate statistics sample 50 percent for TABLE;

지금의 오라클은 dbms_stats 패키지를 사용해 테이블 통계를 수집해서, analyze 명령어를 사용하지 않는 것이 오라클의 공식 입장이다.

begin
	dbms_stats.gather_table_stats('jinsung', 'jiyun'
    	, cascade => false, method_opt => 'for columns'); 
end;
/

cascade는 인덱스 통계가 수집되지 않기 위해 false로 지정했다.
method_opt는 컬럼 통계가 수집되지 않기 위해 for columns로 지정했다.

2. 인덱스 통계

인덱스 통계를 수집할 때는 아래 명령어를 사용한다.

ANALYZE INDEX jiyun_pk compute statistics;   -- 특정 인덱스 통계 수집
ANALYZE TABLE jiyun compute statistics for ALL INDEXES;   -- 테이블의 모든 인덱스 통계 수집
ANALYZE TABLE jiyun compute statistics for TABLE for ALL INDEXES;   -- 테이블 내의 테이블 통계와 인덱스 통계 모두 수집

dbms_stats 패키지를 이용하는 방법

begin    -- 특정 인덱스 통계만 수집
	dbms_stats.gather_index_stats
    (ownname => 'jinsung',
    indname => 'jiyun_pk');
end;
/

begin    -- 테이블에 속한 모든 인덱스 통계도 같이 수집
	dbms.stats.gather_index_stats('jinsung', 'jiyun', cascade=>true);
end;
/

인덱스를 최초 생성하거나 재생성할 때 compute statistics 옵션을 주면 자동으로 인덱스 통계까지 수집된다.

create index jiyun_pk on jiyun(id) COMPUTE STATISTICS;

alter index jiyun_pk rebuild COMPUTE STATISTICS;

10g 부터는 이 옵션을 명시하지 않아도 알아서 오라클이 인덱스 통계까지 수집해 준다.

3. 컬럼 통계

아래는 테이블, 인덱스 통계는 제외하고 컬럼 통계만 수집하는 방법이다.

ANALYZE TABLE jiyun compute statistics for ALL COLUMNS SIZE 254;

size 옵션은 히스토그램 최대 버킷 개수를 지정하는 옵션으로, 지정하지 않으면 기본 값 75이고, 히스토그램이 생성되지 않도록 하고 싶을 때는 1로 명시하면 된다.

ANALYZE TABLE jiyun compute statistics for COLUMNS id SIZE 10, name SIZE 20; -- size 컬럼 각각 설정
ANALYZE TABLE jiyun compute statistics for COLUMNS SIZE 20 id, name, grade; -- size 컬럼 모두 같게 설정

테이블, 인덱스, 컬럼 통계를 한번에 수집하기

ANALYZE TABLE jiyun compute statistics
for TABLE
for ALL INDEXES
for INDEXED COLUMNS SIZE 254;

dbms_stats 패키지로 컬럼 통계만 수집하는 방법은 없다.
테이블 통계와 항상 같이 수집된다.

4. 시스템 통계

시스템 통계는 I/O, CPU 성능 같은 하드웨어적 특성을 측정한 것으로서, 아래 항목들을 포함한다.

  • CPU 속도

  • 평균적인 Single Block I/O 속도

  • 평균적인 Multiblock I/O 속도

  • 평균적인 Multiblock I/O 개수

  • I/O 서브시스템의 최대 처리량 (Throughput)

  • 병렬 Slave의 평균적인 처리량 (Throughput)

오라클은 9i부터, 제품이 설치된 하드웨어 및 애플리케이션 특성에 맞는 시스템 통계를 수집하고 이를 활용함으로써 옵티마이저가 보다 합리적으로 선택할 수 있도록 하였다.

✅ Workload 시스템 통계

9i에 처음 도입된 Workload 시스템 통계는, 애플리케이션으로부터 일정 시간 동안 발생한 시스템 부하를 측정, 보관함으로써 그 특성을 최적화 과정에 반영할 수 있게 한 기능이다.

통계를 수집하는 동안 애플리케이션이 I/O 집약적인 쿼리를 주로 수행했다면 통계정보에 그것이 반영될것이므로 이를 적용한 이후 옵티마이저는 덜 I/O 집약적인 실행계획을 선택할 것이다.

통계항목설명
cpuspeed현재 시스템에서 단일 CPU가 초당 수행할 수 있는 표준 오퍼레이션 개수 (단위 : 백만/초)
sreadtim평균적인 Sigle Block I/O 속도 (단위 : ms = 1/1000초)
mreadtim평균적인 Multiblock I/O threh (단위 : ms = 1/1000초)
mbrcMultiblock I/O 방식을 사용할 때 평균적으로 읽은 블록의 수
maxthrI/O 서브시스템의 최대 처리량 (단위 : 바이트/초)
slavethr병렬 Slave의 평균적인 처리량 (단위 : 바이트/초)

Workload 시스템 통계를 제대로 활용하려면 통계 수집 전략을 잘 세워야 한다.

✅ NoWorkload 시스템 통계

오라클 10g 부터 관리자가 명시적으로 선택하지 않더라도 CPU 비용 모델이 기본 비용 모델로 사용되게 하려고 NoWorkload 시스템 통계를 도입하였다.
CPU 비용 모델은 시스템 통계가 있을때만 활성화되기 때문이다.

시스템 통계를 제대로 활용하려면 Workload 시스템 통계를 활용하는 것이 바람직하지만 이를 수집하기 어려운 환경에서는 NoWorkload 시스템 통계를 사용한다.
Workload든 NoWorkload든 적당한 부하를 준 상태에서 시스템 통계를 수집해 주어야 한다.

Workload는 실제 애플리케이션에서 발생하는 부하를 기준으로 각 항목의 통계치를 측정하는 반면
NoWorkload는 모든 데이터파일 중에서 오라클이 무작위로 I/O를 발생시켜 통계를 수집한다.
따라서 시스템 부하 정도가 심할 때 NoWorkload 시스템 통계를 수집하면 구해진 값들도 달라진다.

5. 카디널리티

1. 선택도

선택도란 전체 레코드 중에서 특정 조건에 의해 선택될 것으로 예상되는 레코드 비율을 말한다.
선택도를 가지고 카디널리티를 구하고, 다시 비용을 구함으로써 인덱스 사용 여부, 조인 순서와 방법 등을 결정하므로 선택도는 최적의 실행계획을 수립하는 데 있어 가장 중요한 요인이라고 할 수 있다.

히스토그램이 있으면 그것으로 선택도를 산정하며, 단일 컬럼에 대해서는 정확도도 비교적 높다.
히스토그램이 없거나, 조건절에 바인드 변수를 사용하면 옵티마이저는 데이터가 균일분포하다고 가정한 상태에서 선택도를 구한다.

✅ 히스토그램 없이 등치(=) 조건에 대한 선택도를 구하는 공식

선택도 = 1 / Distinct Value 개수 = 1 / num_distinct

✅ 히스토그램 없이 부등호, Between 같은 범위검색에 대한 선택도를 구하는 공식

선택도 = 조건절에서의 범위 / 전체 값 범위

>= 부등호 일때는 1/num_distinct를 더하고 between 일때는 2/num_distinct을 더한다.
컬럼 히스토그램이 없을 때 옵티마이저는 '조건절에서 요청한 값 범위'에 속한 값들이 '전체 값 범위'에 고르게 분포돼 있음을 가정하고 선택도를 구한다.

2. 카디널리티

카디널리티란 특정 액세스 단계를 거치고 나서 출력될 것으로 예상되는 결과 건수를 말한다.

  • 카디널리티 = 총 로우 수 * 선택도

히스토그램이 없을 때

  • 카디널리티 = 총 로우 수 * 선택도 = num_rows / num_distinct

3. NULL 값을 포함할 때

기존 공식에 Null 값이 아닌 로우 비중을 곱하고, 분모인 Distinct Value 개수에서 Null 값을 제외시키면 된다.

선택도 = 1/ Distinct Value 개수 * Null 값을 제외한 로우 수 / 총 로우 수
= 1 - (num_nulls / num_rows) / num_distinct

4. 조건절이 두 개 이상일 때

각 컬럼의 선택도와 전체 로우 수 를 곱해 주기만 하면 된다.

select * from jiyun where job = :job and deptno = :deptno

5. 범위 검색 조건일 때

옵티마이저는 히스토그램이 없을 때 조건절에서 요청한 범위에 속한 값들이 전체 값 범위에 고르게 분포돼 있음을 가정한다.
그래서 다르게 분포돼 있어도 카디널리티가 항상 똑같이 계산된다.

6. cardinality 힌트를 이용한 실행계획 제어

옵티마이저가 계산한 카디널리티가 부정확할 때는 힌트를 이용해 사용자가 직접 카디널리티 정보를 제공할 수 있다.

select /*+ use_hash(d e) cardinality(d 16) */ *
from dept d, emp e
where d.deptno = e.deptno

예를 들어 dept 테이블의 카디널리티를 16으로 주어 옵티마이저에게 dept 테이블을 Build Input으로 두라는 힌트를 주는 것이다.

10g 부터는 opt_estimate 힌트를 이용할 수 있다.
opt_estimate(table, d, scale_row=n) 으로 예상한 카디널리티에 n을 곱하라는 힌트이다.

profile
jinsung
Data Engineer
이전 포스트

SQL) 옵티마이저 원리 Ⅰ

다음 포스트

SQL) 옵티마이저 원리Ⅲ

0개의 댓글