권순용의 실전 SQL Tuning 5강
00:55 2017-01-06 금
SQL 5강 SQL은 옵티마이저에 의해 변경
ㅁ 학습목표
구문 분석 및 SQL의 실행 절차를 설명
Query Transformation
옵티마이저의 특징
(1) SQL 실행 절차
(2) 구문분석
(3) Query Transformation
(4) 옵티마이저
ㅁ 구문분석
SQL을 수행하기 위해 SQL의 문법, 권한 등을
확인하는 초기 단계를 의미한다.
ㅁ Query Transformation
SQL이 구문분석 과정을 마치고
실행이 되기 전에 수행한 SQL을 변형하는 단계임.
ㅁ 옵티마이저
데이터 베이스의 두뇌에 해당.
SQL의 결과를 추출하기 위해 어떻게 수행될지를 결정한다.
ㅁ 서브쿼리
Query Transformation 단계에서 발생하게 된다.
서브쿼리 Merging은 In절, Any 절, Not in 절, All 절을
NOT EXISTS 절로 변경하는 단계이다.
ㅁ 뷰 머징
Query Transformation 단계에서 가장 먼저 수행되는 단계는 “뷰 머징”단계이다.
인라인 뷰 또는 뷰를 사용한 SQL을 옵티마이저가 변경하여 뷰의 쿼리가
주 쿼리와 통합되어 뷰가 제거되거나
뷰에 조건이 삽입되는 경우를 말한다.
1. SQL 실행 절차
구문 분석 -> 쿼리 트랜스포메이션 -> Cost 계산 후 실행계획 생성
ㅁ SQL 실행절차
SQL 수행 -> 구문분석 (Query Transformation 포함) -> 옵티마이저 선택 -> 비용 계산 -> 실행 계획 생성
ㅁ Query Transformation
– SQL 을 변경 하는 단계. “구문분석”에 포함된다.
뷰 Merging, 서브 쿼리 Merging, Transitivity, OR Expansion 및 Query Rewrite 과정을 수행함.
– 해당 과정을 통해 SQL에 조건을 삽입하거나 생성하고
경우에 따라서는 쿼리 전체를 변경하여 최대한 처리 범위를 감소시킬 수 있는 형태의 SQL로 변경하게 됨
– DB 버전이 높을수록 Query Transformation 과정은 더욱 활발하게 발생함.
이 과정을 거치면서 우리가 수행한 SQL의 대부분은 변경이 됨.
처리 범위를 감소시켜 성능을 향상시키기 위한 목적임. SQL이 변경된 이후에도 변경 전과 동일한 데이터가 추출됨
– 결국 Query Transformation은 SQL의 성능을 향상시키기 위해 옵티마이저가 단독으로 수행하는 과정임.
ㅁ 옵티마이저 선택
– “비용 기반” 옵티마이저와 “규칙 기반” 옵티마이저 중 하나를 선택
– 대부분의 DB는 “비용 기반” 옵티마이저가 기본값
– 오라클도 기본값으로 “비용 기반” 옵티마이저를 선택
ㅁ 비용계산
– SQL 이 수행될 수 있는 모든 경우에 대해 추출하고자 하는 데이터의 비율을 계산하는 Selectivity를 추출한 후
Selectivity 에 전체 건수를 곱하여 추출하고자 하는 데이터의 전체 건수를 Cardinality 로 도출하여
도출된 Cardinality 와 다른 통계 정보를 통해 비용을 계산함.
– 비용 계산 결과 가장 적은 비용이 소요되는 형태의 SQL 수행 방식을 선택함.
ㅁ 실행 계획 생성
– 비용 계산 결과 가장 적은 비용이 소요되는 SQL의 수행 방법을 선택한 다음에는
해당 방법을 “실행 계획”으로 생성하게 됨.
– 실행 계획이 생성되면 해당 SQL은 실행 계획에 의해서 수행이 되므로,
이와 같은 실행 계획을 통해 “SQL 최적화”를 수행하게 됨.
2. 구문 분석
1) 구문 분석 절차
SQL의 수행 여부를 확인하는 과정이 구문 분석.
구문 분석 과정 중에 옵티마이저는 Query Transformation 을 수행하게 됨. => 가장 먼저 수행
구문 분석 절차는 6단계임
검색 -> 문법 확인 -> Semantic 확인 -> Query Transformation -> 권한 확인, TM(Table Management Lock)
———-
<구문분석의 과정>
검색 -> 신택스 -> 시멘틱 -> 권한 -> TM락
소프트 파싱 : “검색”을 수행해서, 메모리에 있으면 더 이상 진행하지 않고 아까의 실행계획 다시 수행
하드 파싱 : “검색”을 수행해서, 메모리에 없으면 아래 로직을 수행함.
1. 검색
2. 신택스 확인 (문법, 맞춤법)
3. 시멘틱 체크 (테이블, 컬럼 존재 체크)
4. 권한 확인
5. TM LOCK (테이블 매니지먼트 락==티엠 락.) : 다른 접근에 의한 변경을 막는 락이다.
구문 분석 내에 쿼리 트랜스포메이션이 있다.
쿼리 트랜스포메이션 : 뷰 머징, 서브쿼리 머징, 트랜시티비티, OR 익스팬션, 쿼리 리라이트
———-
13/27
<뷰 머징>
뷰 머징(주 쿼리의 조건이 인라인 뷰로 삽입되는 형태)이 발생한 것.
조건이 삽입되더라도 해당 조건을이용하여 처리 범위를 감소시키고자 한다면
그에 맞는 인덱스가 존재해야 함.
(- 인덱스가 존재하지 않는다면 조건이 삽입되더라도
처리 범위가 감소되지 않으므로
의도대로 성능 향상을 기대할 수 없음)
14/27
<서브쿼리 머징>
서브쿼리 머징도 쿼리 트랜스포메이션 단계에서 발생.
IN, ANY, NOT IN, ALL 을 -> EXISTS 또는 NOT EXISTS 로 변경한다.
존재유무 쿼리로 바꾸면, 반드시는 아니지만, 성능향상.
15/27
<트랜시티비티>
해당 SQL에 조인이 사용되는 경우,
논리적으로 추가할 수 있는 조건을 추가
논리적으로 가능한 조건을 추가 -> 처리 범위를 감소시킨다.
(사용자가 명시적으로 해주면 더 좋다고 함.)
17/27
쿼리 리라이트(Query Rewrite)
보통, Meterialized View를 이용하는 과정에 발생하는 경우 많음.
Meterialized view를 사용해서 성능이 향상된다면,
쿼리를 이걸로 대체해서 쓴다.
이 경우를 대비해, View에 인덱스를 넣어줘야 한다.
18/27
<OR Expansion>
SQL 의 IN 절과 OR 절은 많은 경우, UNION ALL 절로 변경된다.
참고로, WHERE 가맹점번호 IN (‘111’, ‘112’, ‘113’) 일 경우,
뒤에서부터 UNION ALL 을 한다.
(113 UNION ALLl 112 UNION ALL 111).
그러므로 이 순서로 정렬된다.
19/27
<옵티마이저>
1. 비용계산법
2. 옵티마이저 종류
옵티마이저는 비용 계산을 수행하여 가장 적은 비용의 실행 계획 선택.
셀렉티비티 (조건을 만족하는 데이터가 차지하는 비율. 0 ~ 1 사이)
카디널리티 : 셀렉티비티 * 전체 건수 = 카디널리티. (셀렉티비티는 비율이므로 대용량 테이블에서는 셀렉티비티가 낮아도 무의미)
비용 : 통계정보 + 디스크 아이오 + 메모리 + 시피유
20/27
옵티마이저
1. 규칙 기반 : SQL문, 인덱스를 참조하여 실행계획 생성
2. 비용 기반 : 통계를 바탕으로 실행계획 생성. 융통성 있음. (but, 일부 통계정보 이용시 종종 잘못될 수도 있음.)
21/27
보충 및 심화학습
옵티마이저에 영향을 주는 요소
– SQL 형태, 연산자, 인덱스/테이블 구조, 힌트사용, 옵티마이저 모드, DBMS 버전, 통계정보…
(주의깊게 관리해야 하는 요소들. 옵티마이저에 영향을 주기 때문임.)
실행계획이 변한다는 것 = SQL 의 성능이 달라진다는 것.
22/27
쿼리에 뷰가 있었는데,
인라인 뷰가 실행계획에 없다면?
“인머저블 인라인 뷰”가 아니라, “머저블 인라인 뷰”였던 것임.
“뷰”라는 실행계획이 생성되지 않았을 경우.
23/27
데이터베이스는 제가 수행한 SQL을 그대로 수행할까요?
아니다. 옵티마이저가 최적화함.
퀴즈 1. 쿼리 트랜스포메이션에 해당하지 않는 항목은?
1. 뷰 머징
2. 트랜시티비티
3. 인라인 뷰
4. OR 익스팬션
답 : 3번
해설 : Query Transformation에 해당하는 항목은 뷰 머징, 서브쿼리 머징, 트랜시비티비, 쿼리 리라이트, 오어 익스팬션입니다.
퀴즈2. 옵티마이저에 영향을 미치는 요소가 아닌 것은?
1. 인덱스
2. 테이블 구조
3. 데이터베이스 버전
4. Client Tool의 종류
답 : 4번
해설 : 옵티마이저에 영향을 미치는 요소로는 힌트 사용, 옵티마이저 Mode, DBMS버전, 통계정보, 연산자, SQL형태, 인덱스/테이블 구조 등이 있습니다.
퀴즈3. 구문 분석의 단계가 아닌 것은?
1. 신택스 확인
2. 시멘틱 확인
3. SQL수행
4. 기존 실행계획 검색
답 : 3번
해설 : 구문 분석 단계는 기존 실행 계획 검색, 신택스 확인, 시멘틱 확인, 권한 확인으로 구성됩니다.
00:48 2017-01-10 화