[친절한 SQL 튜닝] 통계정보와 비용 계산 원리

선택도 : 전체 레코드 중에서 조건절에 의해 선택되는 레코드 비율

선택도 = 1 / NDV(Number of Distinct Value)

 

카디널리티 : 전체 레코드 중에서 조건절에 의해 선택되는 레코드 개수

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

 

예) 상품분류 컬럼이 ‘가전’, ‘의류’, ‘식음료’, ‘생활용품’ 네 개의 값이 있을 때,

where 상품분류 = ‘가전’ 해당 조건에 대한 선택 도는 25%(1/4)

만약 전체 레코드가 10만건이면 카디널리티는 2만 5천

 

 

통계정보

 

오브젝트 통계

테이블 통계

테이블 통계 정보 수집 방법

 

테이블 통계 정보 조회

또는 all_tab_statistics 뷰를 통해 조회 가능

 

테이블 통계항목

 

인덱스 통계

인덱스 통계 수집

 

인덱스 조회

 

인덱스 통계정보는 all_ind_statistics 뷰에서 조회가 가능하다

 

 

컬럼 통계(히스토그램 포함)

컬럼통계는 테이블 통계 수집할 때 함께 수집된다

 

컬럼 통계 조회

 

컬럼 히스토그램

컬럼 값별로 데이터 비중 또는 빈도를 미리 계산해 놓은 통계정보

실제 값을 읽어서 계산해둔 값으로 데이터 분포가 많이 변하지 않는 한 거의 정확하다

 

해스토그램 수집 방법

히스토그램을 수집하려면, 테이블 통계 수집 시 method_opt 파라미터를 지정하면 된다

 

수집된 컬럼 히스토그램 조회

all_tab_histograms 뷰에서 같은 저보 조회 가능

 

 

시스템 통계

애플리케이션 및 하드웨어 성능 특성을 측정한 것으로 아래 항목을 포함 한다

• CPU 속도
• 평균적인 Single Block I/O 속도
• 평균적인 Multiblock I/O 속도
• 평균적인 Multiblock I/O 개수
• I/O 서브시스템의 최대 처리량
• 병렬 Slave의 평균적인 처리량

sys.aux_stats$ 뷰에서 조회할 수 있다

 

 

비용 계산 원리

인덱스 키값을 모두 = 조건으로 검색할 때의 비용 계산

 

인덱스 키값이 모두 = 조건이 아닐 때는 컬럼 통계까지 활용하여 비용 계산

 

 

비용(Cost)

I/O 비용 모델 : I/O 비용 모델을 사용할 때 실행게획에 나타나는 Cost는 ‘예상 I/O Call 횟수’를 의미

CPU 비용 모델 : Single Block I/O 를 기준으로 한 상대적 시간을 표현

 

 

옵티마이저

• 비용기반(Cost-Based) 옵티마이저(CBO) : 사용자 쿼리를 위해 후보군이 될만한 실행계획들을 도출하고, 데이터 딕셔너리에 미리 수집해 둔 통계정보를 이용해 각 실행계획의 예상비용을 산정하고, 그 중 가장 낮은 비용의 실행계획 하나를 선택하는 옵티마이저
• 규칙기반(Rule-Based) 옵티마이저(RBO) : 통계정보를 활용하지 않고 단순한 규칙에만 의존하여 대량 데이터를 처리하는데 부적합

 

옵티마이저 모드

• ALL_ROWS : 전체 처리속도 최적화 : 전체를 읽는 것을 전제로 시스템 리소스를 가장 적게 사용하는 실행계획을 선택
• FIRST_ROWS : 최초 응답속도 최적화 : 앞쪽 일부만 읽다가 멈추는 것을 전제로 응답 속도가 가장 빠른 실행계획을 선택
• FIRST_ROWS_N : 최초 N건 응답속도 최적화 : 앞쪽 N개 로우만 읽고 멈추는 것을 전제로 응답 속도가 가장 빠른 실행계획을 선택

옵티마이저 모드 설정

힌트 적용

 

 

옵티마이저에 영향을 미치는 요소

• SQL과 연산자 형태 : SQL 형태, 연산자(=, IN, LIKE, BETWEEN, 부등호 등)
• 인덱스, IOT, 클러스터, 파티션, MV 등 옵티마이징 팩터
• 제약 설정 : PK, FK, Check, Not Null
• 통계정보
• 옵티마이저 힌트
• 옵티마이저 관련 파라미터 : 옵티마이저 파라미터 목록 확인

 

옵티아미저 한계

• 옵티마이저 행동에 가장 큰 영향을 미치는 통계정보를 필요한 만큼 충분히 확보하는 것은 불가능한 일, 통계정보를 수집하고 관리하는 데 어마어마한 시간과 비용이 들기 때문
• 바인드 변수를 사용한 SQL에 컬럼 히스토그램을 활용할 수 없다는 치명적인 단점
• 최적화에 허용되는 시간이 매우 짧다

 

개발자의 역할

- 필요한 최소 블록만 읽도록 쿼리 작성

1) 출력 대상이 아닌 게시물에 대해서도 GET_ICON 함수 적용

2) 회원, 게시판유형, 질문유형 테이블과 조인 → 출력 대상 집합 확정 후 조인 필요

 

 

- 최적의 옵티마이징 팩터 제공

최적화는 옵티마이저가 수행하지만, 잘 할 수 있도록 적절한 수단을 제공하는 것은 사용자의 몫

 

• 전략적인 인덱스 구성
• DBMS가 제공하는 다양한 기능 활용
• 옵티마이저 모드 설정
• 정확하고 안정적인 통계정보

옵티마이저 모드를 포함해 각종 파라미터를 적절한 값으로 설정하고, 통계정보를 잘 수집해 주는 것이 중요

그리고 난 후에 전략적인 인덱스 구성이 필수적으로 뒷받침되어야한다

또한 DBMS가 제공하는 기능을 적극적으로 활용해 옵티마이저가 최적의 선택을 할 수 있도록 다양한 수단을 제공해 주어야한다

 

- 필요시, 옵티마이저 힌트를 사용해 최적의 액세스 경로로 유도

 

 

데이터베이스 튜닝

SQL이 병목이나 지연 없이 빠르고 안정적으로 수행되도록 조치하는 모든 활동

• SQL 튜닝 : I/O 효율화, DB Call 최소화, SQL 파싱 최소화 등
• DB 설계 : 논리적 데이터 구조 설계, 물리적 저장 구조 설계 등
• 인스턴스 튜닝 : Lock/Latch 모니터링 및 해소, 메모리 설정, 프로세스 설정 등