1. 통계정보와 비용 계산 원리
1.1 선택도와 카디널리티
- 선택도(Selectivity) : 전체 레코드 중에서 조건절에 의해 선택되는 레코드 비율
- 가장단순한 '=' 조건으로 검색할때의 선택도만 보면, 컬럼값 종류개수(NDV, Number of Distinct Values)를 이용해 아래와 같이 구한다
선택도 = 1 / NDV
- 카디널리티란, 전체 레코드중에서 조건절에 의해 선택되는 레코드 갯수
카디널리티 = 총 로우수 * 선택도 = 총 로우수 / NDV
- 예를들어 상품분류 컬럼에 가전, 의류, 식음료, 생활용품 네개의 값이 있을때, 아래 조건절에 대한 선택도는 25%(1/4)이다. 만약 전체 레코드가 10만 건이면, 카디널리티는 2만 5천이다.
WHERE 상품분류 = ' 가전'
- 옵티마이저는 카디널리티를 구하고, 그만큼 액세스하는데 드는 비용을 계산하여
테이블 액세스 방식, 조인 순서, 조인방식등을 결정 - 공식을 통해 알수있든 비용계산 출발점은 선택도이다, 선택도를 잘못 계산하면 카디널리티와 비용도 잘못 계산하고 결과적으로 비효율적인 액세스방식과 조인방식을 선택한다
- 선택도계산시 NDV를 사용하므로 통계정보 수집 과정에서 이값을 정확히 구하는것이 매우 중요
1.2 통계정보
- 오브젝트 통계와 시스템 통계가있다
- 오브젝트 통계는 다시 테이블 통계, 인덱스 통계, 컬럼 통계(히스토그램 포함)로 나뉜다
1.2.1 테이블 통계
- 테이블 통계를 수집하는 명령어
dbms_stats.gather_table_stats('scott','emp')
- 수집된테이블정보는 all_tables에서 조회할수있고, all_tab_statistics뷰에서도 확인할수있다
- 주요 테이블 통계항목
- NUM_ROWS : 테이블 저장된 전체 레코드 수
- BLOCKS : 테이블 블록수 ='사용된' 익스텐트에 속한 총 블록수
- AVG_ROW_LEN : 레코드당 평균 길이(BYTES)
- SAMPLE_SIZE : 샘플링한 레코드 수
- LAST_ANALYZED : 통계정보 수집일시
1.2.2 인덱스 통계
- 인덱스 통계 수집
--인덱스 통계만 수집
dbms_stats.gather_index_stats (ownname => 'scott', indname=> 'emp_x01');
-- 테이블 통계 수집하면서 인덱스 통계도 같이 수집
dbms_stats.gather_table_stats('scott','emp',cascade=>true); - 수집된 인덱스 통계정보는 all_indexes테이블, all_ind_statistics뷰에서 확인
- 주요 인덱스 통계항목
- BLEVEL : 브랜치 레벨, 인덱스 루트에서 리프블록도달 직전까지 읽는 블록 수
- LEAF_BLOCKS : 인덱스 리프블록 총 개수
- NUM_ROWS : 인덱스저장된 레코드 개수
- DISTINCT_KEYS : 인덱스 키값의 조합으로 만들어지는 값의 종류 개수
- AVG_LEAF_BLOCKS_PER_KEY : 인덱스 키값을 모두 = 조건으로 조회할 때 읽게 될 리프 블록 개수
- AVG_DATA_BLOCKS_PER_KEY : 인덱스 키값을 모두 = 조건으로 조회할 때 읽게 될 테이블 블록 개수
- CLUSTERING_FACTOR : 인덱스 키 값 기준으로 테이블 데이터가 모여있는 정도.
1.2.3 컬럼 통계
- 컬럼통계는 테이블통계수집할때 같이 수집된다.
- ALL_TAB_COLUMNS테이블이나 all_tab_col_statistics뷰에서 조회가능
- 주요 컬럼 통계항목
- NUM_DISTINCT : 컬럼값의 종류 개수(NDV), 예를들어 성별 컬럼이면 2
- DENSITY : = 조건으로 검색할때 선택도를 미리 구해놓은 값
- AVG_COL_LEN : 컬럼 평균 길이(Bytes)
- LOW_VALUE : 최소값
- HIGH_VALUE : 최대 값
- NUM_NULLS : 값이 NULL인 레코드 수
1.2.4 컬럼 히스토그램
- =조건에 대한 선택도는 1/NUM_DISTINCT공식으로 구하거나 미리구해놓은 DENSITY값을 이용
- 일반적으로 이공식이 비교적 맞지만, 데이터 분포가 균일하지 않은컬럼은 그렇지 않다.
- 선택도를 잘못 구하면 데이터 액세스 비용을 잘못 산정하게 되고, 결국 최적이아닌 실행계획으로 이어짐
- 그래서 옵티마이저는 일반적인 컬럼 통계 외 히스토그램을 추가로 활용.
- 오라클 12C에서 사용하는 히스토그램 유형
- 도수분포(FREQUENCY) : 값별로 빈도수 저장
- 높이균형(HEIGHT-BALANCED) : 각 버킷의 높이가 동일하도록 데이터 분포 관리
- 상위도수분포(TOP-FREQUENCY) : 많은 레코드를 가진 상위 n개 값에 대한 빈도수 저장(12c이상)
- 하이브리드 (HYBRID) : 도수분포와 높이균형 히스토그램의 특성 결합(12c이상)
- 히스토그램 수집하려면, 테이블 통계 수집할때 아래와같이 method_opt파라미터를 지정하면 된다.
dbms_stats.gather_table_stats('scott','emp', cascade=>false, method_opt=>'for columns ename size 10, deptno size 4');
dbms_stats.gather_table_stats('scott','emp', cascade=>false, method_opt=>'for all columns size 75');
dbms_stats.gather_table_stats('scott','emp', cascade=>false, method_opt=>'for all columns size auto'); - 수집된 컬럼 히스토그램은 all_histograms테이블, all_tab_histograms뷰에서 확인가능
1.2.5 시스템 통계
- 애플리케이션 및 하드웨어 성능 특성을 측정한것, 아래항목들을 포함
- CPU속도
- 평균적인 Single Block I/O속도
- 평균적인 Multiblock I/O속도
- 평균적인 Multiblock I/O개수
- I/O서브시스템의 최대 처리량(Throughput)
- 병렬 Slave의 평균적인 처리량(Throughput)
- 과거에는 이들항목을 고려하지않고, 옵티마이저 개발팀이 사용한 하드웨어사양에 맞춰진 고정상수값으로 처리
실제 오라클 설치 운영시스템사양이 그보다 좋거나나쁠때 최적이 아닌 실행계획을 수립할 가능성 발생 - 시스템 사양뿐아니라 애플리케이션 특징(OLTP, DW)및 동시 트랜잭션 발생량에 따라서도 이들 성능 특성이 달라진다
- 시스템 통계는 sys.aux_stats$뷰에서 조회가능
1.3 비용 계산 원리
- 인덱스 키값을 모두 = 조건으로 검색할때는 인덱스통계만으로도 쉽계 비용계산 가능
비용 = BLEVEL --인덱스 수직적 탐색 비용
+ AVG_LEAF_BLOCKS_PER_KEY -- 인덱스 수평적 탐색 비용
+ AVG_DATA_BLOCKS_PER_KEY --테이블 랜덤 액세스 비용 - 키값이 모두 = 조건이 아닐때는 컬럼통계까지 활용
비용 = BLEVEL
+ LEAF_BLOCKS * 유효 인덱스 선택도
+ CLUSTERING_FACTOR * 유효 테이블 선택도 - 유효 인덱스 선택도 : 전체 인덱스 레코드중 액세스조건에 의해 선택될것으로 예상되는 레코드 비중(%)
3장 그림 3-30 에서 1,2번 조건에 의한선택도를말하며, 이들 조건절에 의해 테이블 액세스 여부가 결정
2. 옵티마이저에 대한 이해
- 자동차 내비게이션과 흡사
2.1 옵티마이저 종류
- 비용기반 옵티마이저(CBO)는 사용자 쿼리를 위해 후보군이 될만한 실행계획들을 도출하고, 데이터 딕셔너리에 미리 수집해 둔 통계정보를 이용해 각 실행계획의 예상비용을 산정하고, 그중 가장 낮은 비용의 실행계획하나를 선택하는 옵티마이저
- CBO가사용하는 통계정보는 데이터량, 컬럼값의수, 컬럼 값 분포, 인덱스 높이, 클러스터링 팩터등
- 과거에는 액세스경로에대한 우선순위 규칙에따라 실행계획을 만드는 옵티마이저를 사용했었다(RBO)
- RBO는 데이터 특성을 나타내는 통계정보를 전혀 활용하지 않고 단순규칙에만 의존하여 대량 데이터 처리에 부적합
- RBO규칙도 어느정도 큰무리없던 시절이있었으나, 지금과같은 대용량 DB환경에선 전혀 대안이 될 수 없다.
2.2 옵티마이저 모드
- 내비게이션 목적지 검색시 검색모드를 선택한다 보통아래와같다
- 고속도로 우선 : 이동시간 단축
- 일반도로 우선 : 통행료 절감
- 최단거리 : 유류비 절감
- CBO에도 비슷한기능이 있다 최적화목표를 설정하는 기능으로 아래 세가지 옵티마이저 모드중 하나를 선택하면 된다.
- ALL_ROWS : 전체 처리속도 최적화
- FIRST_ROWS : 최초 응답속도 최적화
- FIRST_ROWS_N : 최초 N건 응답속도 최적화
- 옵티마이저 ALL_ROWS로설정시 옵티마이저는 쿼리 결과집합 '전체를 읽는 것을 전제로' 시스템 리소스(I/O, CPU, 메모리등)를 가장 적게 사용하는 실행계획을 선택, 전체처리 속도 최적화가 목표
- FIRST_ROWS는 전체 결과집합 중 '앞쪽 일부만 읽다가 멈추는 것을 전제로' 응답 속도가 가장빠른 실행계획을 선택, 최초응답속도 최적화가 목표
ALL_ROWS와 비교하면, TABLE FULL SCAN보다 인덱스를 더 많이 선택하고, 해시 조인, 소트머지조인보다 NL조인을 더 많이 선택하는 경향이있다 - FIRST_ROWS는 곧 사라질 모드, 그대신 FIRST_ROWS_N을 사용해야한다
- FIRST_ROWS_N은 '앞쪽 N개만 로우만 읽고 멈추는것을 전제로' 응답속도가 가장 빠른 실행계획을 선택
-- alter system또는 alter session명령어로 옵티머이저 모두 설정시 N으로 지정할수있는값은 1,10,100,1000SPRKWL
alter session set optimizer_mode = first_rows_1; -10,100,1000 - 아래와같이 FIRST_ROWS(n)힌트로 설정할때는 괄호안에 0보다 큰 어떤정수값도 입력가능
select /+ first_rows(30) / col1, col2 from t where....
- FIRST_ROWS는 사용자가 어느정도 데이터를 읽다가멈출지 몰라 정확한 비용산정이 어렵다
- FIRST_ROWS_N은 읽을 데이터 건수를 지정하였으므로 더 정확한 비용 산정이 가능, 더완벽한 CBO모드로 작동할수있다.
2.3 옵티마이저에 영향을 미치는 요소
SQL과 연산자 형태
- 결과가 같더라도 SQL을 어떤형태로 작성했는지, 어떤연산자(=,IN,LIKE,BETWEEN,부등호등)사용했는지에 따라 옵티마이저가 다른 선택을 할 수 있고, 궁극적으로 쿼리 성능에 영향
인덱스, IOT, 클러스터, 파티션, MV등 옵티마이징 팩터
- 쿼리가 같아도 인덱스, IOT, 클러스터, 파티션, MV등을 구성햇는지, 그리고 어떤식으로 구성했는지에 따라 실행계획과 성능이 크게 달라진다.
제약설정
- DBMS에 설정한 PK, FK, Check, Not Null같은 제약들은 데이터 무결성을 보장해 줄뿐아니라 옵티마이저가 쿼리 성능을 최적화 하는데 매우 중요한 메타정보로 활용된다.
통계정보
- 통계정보는 옵티마이저에 매우 강력한 영향
- 자동차 네비게이션이 사용하는 주소정보, 도로정보가 부정확할때 운전자가 곤란하고 위험해지는것처럼
- SQL옵티마이저가 사용하는 통계정보에 문제가 생기면 애플리케이션 성능이 갑자기 느려지고 심할땐 장애 상황으로 이어진다.
- 잘 작동하던 프로그램이 어느날 갑자기 느려졌다면 통계정보가 원인일 확률이 매우 높다.
- 시스템 장애가 발생하는 다양한 원인
- 특정 테이블 통계정보를 갑자기 삭제
- 대량 데이터를 지웠다 다시 입력하기직전, 데이터가 없는상태에서 자동으로 통계정보 수집
(스케줄링된 통계수집 패키지에 의해) - 3년간 갱신하지 않던 특정 테이블 통계정보를 어느날 갑자기 재수집
- 통계정보 없이 관리하던 테이블에 인덱스 재생성(10g부터 인덱스생성하면 인덱스 통계가 자동수집됨)
- 테이블이나 인덱스를 재생성하면서 파티션 단위로만 통계정보를 수집한다.(바인드변수를 사용하면 파티션통계가 아닌 Global통계가 사용됨)
옵티마이저 힌트
- 옵티마이저에 가장 절대적 영향을 미치는 요소
- 옵티마이저는 힌트를 명령어로 인식하고 그대로 따른다.
- 힌트가 잘 작동하지 않는다면 아래의 경우에 해당할 가능성이 높다
- 문법적으로 맞지않게 힌트를 기술
- 잘못된 참조 사용
- 의미적으로 맞지 않게 힌트를 기술
- 논리적으로 불가능한 엑세스 경로
- 버그
옵티머이저 관련 파라미터
- SQL,데이터,통계정보,하드웨어등 모든환경이 같은데 오라클버전 업그레이드시 옵티마이저가 다르게 작동할수있다.
- 대부분 옵타마이저 파라미터 추가또는 기본값 변경때문이다.
- 옵티마이저 행동에 영향을 미치는 파라미터목록은 v$sys_optimizer_env 뷰에서 확인할수있다.
- 위의뷰에서 전부를 보여주진않고, 기본적인 것들이있다
2.4 옵티마이저의 한계
- 주소정보, 도로벙보를 아무리 잘 업데이트해도 정보의 불완전성, GPS수신불량, 기계적판단등으로인해 네비게이션은 실수할수있다.
- 보편적으로 좋은 선택을 하지만, 그선택이 최선은 아니다.
- 네비게이션 2개를 동시사용하면 서로다른길로 안내하는것을 통해 알수있다
- SQL옵티마이저도 마찬가지, DBA가 통계정보를 아무리 잘 수집하고 개발자가 SQL을 아무리 잘 작성해도 실수가있기마련, 같은 SQL인데도 DBMS에따라, 버전에 따라 옵티마이저가 다른 실행계획을 생성한다는 사실을 통해 그 선택이 항상 최선이 아님을 알수있다.
- 옵티마이저 행동에 가장 큰 영향을 미치는 통계정보를 '필요한 만큼 충분히' 확보하는 것부터가 불가능한일
- 정보가 많을수록 좋지만, 그것을 수집하고 관리하는데 어마한 시간과 비용이 소모
- 통계정보를 아무리완벽하게 수집해도 바인드변수를 사용한 SQL에 컬럼 히스토그램을 활용할수 없다는 치명적 단점도있다.
- OLTP시스템에서 사용하는SQL은 대부분 바인드 변수를 사용하므로, 이와관련한 성능이슈를 해결해야하지만, DBMS벤더들의 노력에도 불구하고 아직완벽한 해결방안을 찾지 못함.
- SQL최적화기법은 계속발전중이지만, 위의한계와제약으로인해 완전할수는없다
2.5 개발자의 역할
- "안내사항이 실제 도로상황과 다를 수 있으니 이 시스템은 참고용으로 사용하십시오."
- 데이터베이스도 위의 네비 경고와같이 옵티마이저에만 의존하지말고 개발자스스로가 옵티마이저가 되야한다.
- 옵티마이저 결과물이 올바른 실행계획인지 항상점검하고, 더개선할 여지를 찾으려 노력해야한다.
SQL성능향상을 위해 개발자가 할일
- 필요한 최소 블록만 읽도록 쿼리 작성
- 결과집합을 논리적으로 잘정의하고 그결과집합을 만들기 위해 DB프로세스가 최소한의 일만 하도록 쿼리를 효율적으로 작성
- I/O효율에 DB성능이 달려있으므로 동일한 레코드를 반복적으로 읽지 않고, 필요한 최소한의블록만 읽도록한다.
- 최종결과에만 필요한 함수나 서브쿼리는 제일바깥블록에 기술한다.(페이징처리후라던지)
- 최적의 옵티마이징 팩터 제공
- 전략적인 인덱스 구성 : 인덱스는 DBA보다 개발자가 훨씬 잘이해한다(비즈니스로직)
- DBMS가 제공하는 다양한 기능 활용 : 파티션,클러스터,IOT,MV,Result Cache등
- 옵티마이저 모드 설정
- 정확하고 안정적인 통계정보
- 필요시, 옵티머이저 힌트를 사용해 최적의 액세스 경로로 유도
- 최적의 액세스경로를 찾고 실행계획을 그방식으로 유도할수있어야한다.
2.6 튜닝전문가 되는 공부방법
- SQL튜닝 : I/O효율화, DB Call 최소화, SQL파싱 최소화등
- DB설계 : 논리적 데이터 구조 설계, 물리적 저장구조 설계등
- 인스턴스 튜닝 : Lock/Latch 모니터링 및 해소, 메모리 설정, 프로세스 설정 등
- DBA : 데이터베이스 자체에 대한 연구, 설치, 백업/복구, 오브젝트 생성/변경, 보안
데이터베이스 아키텍처완벽숙지, DB운영시 생기는 여러 장애 상황 모니터링 및 해결하는 기술력과
자신만의 스크립트 지속적 개발 - SQL튜너
- SQL중심으로 공부
- 옵티마이저가 SQL을파싱하고 통계정보를 활용해 실행계획을 생성하는 원리
- 옵티마이저 쿼리변환 원리를 바탕으로 실행계획을 분석하는 방법
- 옵티마이저 힌트를 이용해 실행계획을 제어하는 방법
- 옵티마이저가 좋은 실행계획을 생성하도록 유도하기 위한 효과적인 SQL작성법
- 애플리케이션에서 SQL을 실행할때 사용하는 프로그래밍 인터페이스
- SQL을 빠르게 처리할수잇는 좋은 데이터 구조와 파티션/인덱스 설계
- 정확성과 안정성을 확보할 수 있는 통계정보 수집 정책
늙어가면서 기억을 남기는 개발자