3.1.3. 옵티마이져의 최적화 절차 (by rnsl) [2009.04.30]
3.1.3. 옵티마이져의 최적화 절차
(1) 최초에 사용자가 실핸한 SQL은 데이터베이스의 데이터 딕셔너리를 참조하여 파싱을 수행한다.
옵티마이저는 파싱된 결과를 이용해 논리적으로 적용 가능한 실행계획 형태를 골라내고,
힌트를 감안하여 일차적으로 잠정적인 실행계획들을 생성해낸다.
(2) 옵티마이저는 데이터 딕셔너리의 통계정보를 기반으로 데이터의 분포도와 테이블의 저장 구조의 특성,
인덱스 구조, 파티션 형태, 비교연산자 등을 감안하여 각 실행계획의 비용을 계산한다.
여기에서 계산된 비용값이란 특정 실행 계획으로 수행했을 때 사용될 기대값에 대한 일종의 예상 계수라고 할 수 있다.
(3) 옵티마이저는 비용이 산출된 실행계획들을 비교하여 가장 최소의 비용을 가진 실행계획을 선택한다.
하지만 이 방법이 가장 최적의 방법이라고 할 수 없다. 앞에서 얘기 했듯이 판단의 착오가 있을 수 있기 때문이다.
가) 질의 변환기: 양호한 실행계획을 얻을 수 있도록 적절한 형태로 SQL의 모양을 변환하는 역할을 담당
\- 뷰병합
\- 서브쿼리 비내포화
\- 실체 뷰의 쿼리 재생성
\- OR 조건의 전개
\- 사용자 정의 바인드 변수의 엿보기
나) 비용 산정기: 연산에 대한 비용을 산정하기 위해 사용.
\- 선택도(Selectivity): 처리할 대상 집합에서 해당 조건을 만족하는 로우가 차지하는 비율
\- 카디널러티(Cardinality): 위에서 계산한 선택도(selectivity)와 전체 로우 수(NUM_ROWS)를 곱해서 계산해서 나온 값이다.
\- 비용(Cost): 각 연산들을 수행할 때 소요되는 시간비용을 상대적으로 계산한 예측치
스키마 객체에 대한 통계정보에 추가적으로 CPU와 메모리 상황, Disk I/O 비용도 고려되어 계산
각종 비용에 영향을 미치는 초기화 파라미터에도 영향을 받음
\* 아래는 선택도와 카디널러티에 대한 세부 내용이다.
\
-\-
l 통계 데이터 및 테스트 테이블
| SQL> select num_rows from user_tables 2 where table_name='HDDPI_DBL_QTY_ORG'; NUM_ROWS \--\--\--\--\--\- --\--\- 112,101,240 |
| select NUM_NULLS,NUM_DISTINCT,DENSITY from user_tab_columns where table_name='HDDPI_OPW_TMP_ORG' and column_name='POT_PND_YMD'; NUM_NULLS NUM_DISTINCT DENSITY \-\--\--\--\--\- \-\--\-\--\--\--\- \--\--\--\--\-\- 25650300 1869 .000535045 |
NUM_NULLS: Null 값의 개수
NUM_DISTINCT: 컬럼의 유일값의 개수
DENSITY: 컬럼의 밀집도
1. 선택도{}*(Selectivity){}가* 1.0 인 경우
l 경우: 조건 없이 테이블의 모든 내용을 읽은 경우
l 쿼리
| SELECT * FROM HDDPI_OPW_TMP_ORG |
l 실행 계획
| \ --\- \ | Id | Operation | Name | Rows | Bytes | Cost | \ --\- \ | 0 | SELECT STATEMENT | | 112M| 15G| 107K| \ | 1 | TABLE ACCESS FULL | HDDPI_OPW_TMP_ORG | 112M| 15G| 107K| \ --\- |
l Selectivity: 1.0
l 설명: 조건이 없으므로 모든 Row를 읽으므로 Selectivity는 1.0 임. Cardinality는 전체 로우 건수(112,101,240) * Selectivity(1.0) 하여, Cardinality는 112M 이 나옴.
2. 선택도{}*(Selectivity){}가* 0.0 ~ 1.0 사이인 경우
l 경우: WHERE 조건 뒤에 적절한 조건을 부여한 경우
Ex1) Equal 연산을 이용하여 단일 조건을 쓴 경우(Bind 변수 사용)
n 쿼리
| SELECT * FROM HDDPI_OPW_TMP_ORG WHERE POT_PND_YMD = :a1; |
n 실행 계획
| \ --\- \ | Id | Operation | Name | Rows | Bytes | Cost | \ --\- \ | 0 | SELECT STATEMENT | | 46255 | 6730K| 107K| \ | 1 | TABLE ACCESS FULL | HDDPI_OPW_TMP_ORG | \*46255 | 6730K| 107K| \ --\- |
n Selectivity: 0.00041
n 설명: 위에서 WHERE 조건 문 다음에 POT_PND_YMD=:a1 이라는 조건이 삽입되어 Cardinality는 전체 112M 에서 46255 까지 줄어듬. 출력되는 Row 건수가 줄어들게 되었음.
n 질문: 조건이 있는 경우 선택도는 어떻게 계산되는 것인가?
답: 단일 컬럼의 경우, 일반적으로 해당 컬럼에 Null 값이 존재하지 않는다면,컬럼의 유일값 갯수에 해당 되는 값인 NUM_DISTINCT 값을 1에 나누어 주면 Selectivity가 된다. 즉, NUM_DISTINCT가 100이면 Selectivity는 0.01이 된다. 이 경우 DENSITY값과 동일해 진다. 그러나 Null값이 존재하면 Selectivity를 계산하는 부분에서 조금 다르게 된다. 일반적으로 SQL문장에서 Equal(=)연산을 한다는 것은 Null값은 조회를 하지 않는다는 의미이다. 그러므로, NOT NULL 데이터에 대한 Selectivity 또한 고려하여 한다. 쉽게 설명하기 위해 다음을 보자. 위 쿼리는 실제 다음의 쿼리로 내부 변환된다.
| SELECT * FROM HDDPI_OPW_TMP_ORG WHERE POT_PND_YMD = :a1; |
\-->
| SELECT * FROM HDDPI_OPW_TMP_ORG WHERE POT_PND_YMD = :a1 AND POT_PND_YMD IS NOT NULL |
NULL 데이터가 포함된 상태에서 'POT_PND_YMD = :a1' 조건에 대한 Selectivity는 (1/1869)로써 0.00053 이고, 'POT_PND_YMD IS NOT NULL'에 대한 Selectivity는 0.77이다. 이 두 값을 곱하면 진정한 Selectivity 값인 0.00041이 나온다. 물론 전체 로우에 해당 Selectivity를 곱한 값인 Cardinality는 46255이다.
\* 참고사항
책 page 157에 인용되어 있기를 "{*}값의 종류가* 10{}{*}가지라면 선택도는* 0.1{}{*}이다{*}*.* 컬럼에 있는 특정 값을 '='{*}로 했을 때 전체의* 10%{*}를 억세스하게 된다는 것을 의미한다{*}*."* 라고 설명 되어 있는데, 이 말은 해석하기 나름이지만 옳지 않음. 컬럼에 존재하는 Null값의 수에 따라 선택도는 더 낮아질 수 있기 때문이다. 극단적인 예를 들 경우, 1억건 데이터가 있고 컬럼의 유일 값의 수는 10인 경우, 단순 계산으로는 Selectivity가 0.1이 되고 예상 건수는 1천만 건이 된다. 그러나, 실제 10건만 데이터가 존재하고 나머지는 Null인 경우 실제 예상 건수는 1건이 될 것이다. 선택도는 1/1억 으로 엄청나게 작아질 것이다.)
전체 데이터 건수는 USER_TABLES.NUM_ROWS에서 찾아볼 수 있고, null값의 수와 유일 값의 개수는 각각 USER_TAB_COLUMNS.NUM_NULLS와 NUM_DISTINCT에서 찾을 수 있다.
\
-\-
다) 실행 계획 생성기: 적용 가능한 실행 계획을 선별하고 비교 검토를 거쳐 가장 최소의 비용을 가진 것을 선택
\- 다양한 엑세스 경로들의 조합으로 구성되어 있음. 타양한 테이블의 조인으로 조인 순서 또한 중요한 요소님.
\- 실행 계획 생성기는 아래의 두가지 전략을 사용
적응적 탐색: 쿼리 수행에 예상되는 총 수행시간에 비해 최적화에 소요되는 시간이 일정비율을 넘지 않도록 하는 방식
초기치 선택: 탐색 도중 최적이라고 발생하면 실행계획을 더 이상 진행하지 않고 멈춤
\
--\-
문서에 대하여
- 이 문서는 오라클클럽 대용량 데이터베이스 스터디 모임에서 작성하였습니다.
- {*}이 문서의 내용은 이화식님의 새로쓴 대용량 데이터베이스 솔루션을 참고했습니다.*
- 이 문서를 다른 블로그나 홈페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^\^