Oracle SQL Tuning 교육 정리 - Day 5

11. Introduction to Optimizer Statistics Concepts

옵티마이저 통계 정보

Index, Table, Column, System

 

DBA_TAB_STATISTICS

- 전체 레코드 수, 평균 row길이, 총 blocks수, 최근 통계정보 수집 일자

DBMS_STATS

ANALYZE -- EMPTY_BLOCKS, AVE_ROW_LEN, CHAIN_CNT 값을 수집할 수 있는 명령어

 

DBA_IND_STATISTICS

- 인덱스 이름, B-tree Level, leaf block수, key값 수 등

 

Index Clustering Factor란

인덱스로 데이터를 검색할 때,

high clustering factor -- A를 검색하기 위해 3개 blocks을 읽어야 한다 --> 다른 검색 방법이 더 좋을것 같다

low clustering factor -- A를 검색하기 위해 1개 block을 읽어야 한다 --> index range scan 방법이 좋다

index를 통해 검색되는 block 개수를 최소화 하는게 좋다 (index clustering factor가 낮을수록 좋음)

dba_ind_statistics.clustering_factor로 확인 가능!

단 bitmpa index의 경우 clustering factor와 상관 없음

 

DBA_TAB_COL_STATISTICS

- 컬럼명, distinct값 개수, NULL 수, bucket수, density(density가 높다=중복값이 많다)

 

DBA_TAB_HISTOGRAMS

옵티마이저는 컬럼 별 데이터 값이 uniform dist 따른다고 가정한다.

컬럼 데이터 분포를 수집 - frequency, top-frequency, hybrid

리소스를 많이 쓰는 작업이므로 주의해야 함

· Frequency histograms

BEGIN DBMS_STATS.GATHER_TABLE_STATS(
    ownname => 'SH', 
    tabname => 'COUNTRIES', 
    method_opt => 'FOR COLUMNS COUNTRY_SUBREGION_ID');
END;

· Top-Frequency 

  - Frequency는 NDV < 254인 경우에만 만들 수 있다

  - popular value(자주 검색되는 값)에 대해 만든다 (unpopular value는 무시할 수도)

· Hybrid

  - Height-balanced의 상위 버전

  - NDV > 254인 경우에 사용

  - 2번째 bucket에 1,2,3,4 / 3번째 bucket에 4,4,4,4 -> 이런 경우는 2번째 bucket의 4를 3번째 bucket으로 옮긴다

  - AUTO_SAMPLE_SIZE 지정 되어야만 생성됨

  - histogram 유형은 옵티마이저가 정한다 명시적으로 적어주지 않음

BEGIN DBMS_STATS.gather_table_STATS(
	OWNNAME =>'OE', 
    TABNAME=>'INVENTORIES',
    METHOD_OPT => 'FOR COLUMNS SIZE 10 QUANTITY_ON_HAND');
END;

컬럼 데이터 분포가 uniform dist이 아닐 때 히스토그램 정보는 중요하다

where, join 조건에 많이 사용되는 컬럼에 대해서 필요하나, 그렇지 않은 경우에는 불필요

unique column에 대해 equality 조건으로 쓸 때 불필요

bucket는 최대 254개이다 중요중요

히스토그램이 만병통치약은 아니다 그렇지만 성능 관리에 도움이 되기도 한다

METHOD_OPT => 'FOR ALL COLUMNS AUTO' --> 오라클이 알아서 필요한 컬럼들에 대해 알아서 만들어라

TRUNCATE vs DROP + RE-CREATE 왜 truncate이 더 좋을까?

truncate 하더라도 통계정보 유지된다

E-rows vs A-rows 차이가 크다면 selectivity, cardinality 정확성을 위해 히스토그램을 만든다

히스토그램이 있으면 bind peeking 작업이 실행할 확률이 높아진다 

 

Extended Statistics

column group statistics : 한 테이블 내 여러 컬럼을 묶어서 통계 정보를 수집할 수도 있다

 

Global Temp Table

세션 별로 각각 GTT의 통계정보를 가질 수 있다 (not shared)

세션이 끊어지거나 commit/rollback되면 GTT 데이터 사라진다

근데 왜 굳이 통계정보를 수집하나? 세션 내서 GTT로 작업을 많이 하면 통계정보가 유용하다

 

System Statistics

 

12. Using Bind Variables

Bind variable Peeking

하나의 plan은 모든 bind value에는 맞지 않아

CURSOR_SHARING

FORSE 리터럴이더라도 다른 문장이 동일하다면 cursor를 공유하도록 하겠다

즉 SQL내의 리터럴 상수를 시스템이 생성한 바인드 변수(:SYS_B_0)로 변경한다

EXACT 완전히 동일한 문장만 공유하도록 하겠다

adaptive cursor가 있으므로 위 parameter 설정이 절대적이지 않다

 

Adaptive Cursor sharing이란 (12c부터 자동)

동일한 plan을 사용할건지 판단해서 더 나은 plan이 있으면 plan 생성 (통계 정보 기반으로 판단)

bind-sensitivie cursor --> 신규 plan이 만들어진 상태

bind-aware cursor --> child cursor가 만들어진 상태

처음 plan을 만들 때 기존 selectivity 기준으로 selectivity cube를 정의

다른 bind value에 대한 selectivity가 포함되면 기존 plan을 사용하고

포함되지 않으면 새로운 plan을 만들고

다른 selectivity가 기존 cube에 포함되지 않지만 신규 cube가 기존 cube와 겹치면 기존 plan을 사용하고 cube 영역을 넓힌다

 

Parse의 Elapsed time에서 CPU time이 50% 미만이라면 hard parse가 너무 오래 걸리는 것

분석계(DW)에서는 cursor sharing 하는게 좋다.

 

13. SQL Plan Management

SPM이란, 검증된 plan만 사용할 수 있도록 하는 기능

DB변경으로 인해 plan 성능 저하를 예방하기 위한 것 / 상황에 맞는 plan을 적절하게 사용할 수 있게 하는 것

옵티마이저가 plan을 관리한다. 

 

Plan Capture > Plan Selection > Plan Evolution

 

SQL Plan Baseline

SQL문이 반복 수행되어야 Plan baseline에 저장된다

plan 검증 : plan history에는 검증되지 않은 승인되지 않은 plan들이 들어있다. (기존 plan보다 성능이 좋은지 검증)

SQL profile은 object 같은 것?

 

SPM을 수행하려면?SPM 활성화 config 변경  OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=FALSE --> 모든 plan이 baseline이 들어간다  OPTIMIZER_USE_SQL_PLAN_BASELINES=TRUE --> baseline의 plan을 확인baseline에 plan을 올린다  SQL문이 반복 실행되면 plan을 baseline에 올린다  DBMS_SPM cursor cache의 plan을 basline에 올린다 (수동으로)수동으로 baseline을 evolve baseline의 plan 지우기