1. SQL과 옵티마이져 (by joang77) [2009.05.01]
1장 SQL의 활용
=> 관계형 데이터베이스에서 SQL이 차지하는 비중은 그 무엇보다 크다.
=> SQL은 결코 쉽지 않다.
=> SQL은 단순 명령어가 아니라 하나의 애플리케이션이다.
=> SQL를 최적화해야만 최적의 효과를 얻을 수 있고 그러기 위해 개발자는 집합적 사고와 비절차형 언어에 익숙해져야 한다.
SQL과 옵티마이져
- SQL의 실행계획에 영향을 미치는 요소
- 인덱스와 클러스터(생성 주기, 생성시점)
- 옵티마이져 모드, 버젼
- 통계정보.(생성여부, 생성시기, 생성주기)
- 네트워크 부하
- 옵티마이져의 궁극적인 목표는 어느길로가면 가장 처리범범위를 최소화 시킬 수 있는냐는 것.
- 옵티마이져는 새로운 최적의 길을 만들어내는 것이 아니라 이미 존재하는 여러 길 중 최적의 길을 선택하려 노력하고 그 선택에 대한 책임은 옵티마이져보다 사용자의 책임이 훨씬 크다.(인덱스, 사용자된 조건, 클러스터링 여부..)
1.1 옵티마이져와 우리의 역할
- 관계형 데이터베이스의 이론 물리적인 연결고리가 없이 논리적인 연결고리만 있으면 원하는 데이터 액세스 가능하다.
- 과거 이론적인 수준을 옵티마이져가 발전함에 따라 관계형 데이터베이스 발전에 일조하게 되었고 관계형 데이터베이스의 많은 사용으로 이어졌다.
- 그러나, 옵티마이져는 전지전능하지 못함.
- 때문에 옵티마이징 팩터와 요구서(SQL)가 최소효과로 최대 효과를 얻도록 하는 것이 개발자의 도리.
1.2 옵티마이져의 최적화 절차
- 옵티마이저의 입장에서 질의(query) 처리 과정
Query Process단계
- ParseSyntax, Security, Semantics의 체크 및 Simple Transformation 수행
- Query Rewrite 서브질의와 뷰의 병합 수행,OR Expansion작업 수행
- Optimization질의에 대한 액세스 경로를 결정
- QEP Generation질의를 실행하는데 필요한 상세한 정보를 만든다.
- Query ExecutionQEP에 따라 SQL문장을 실행한다.
- Simple transformation의 예
| Example Expression(From) | Transformation(to) |
|---|---|
| ename LIKE 'WARD' | ename='WARD' |
| ename IN ('KING','WARD') | ename='KING' OR ename = 'WARD' |
| ename=ANY/SOME('KING','WARD') | ename='KING' OR ename = 'WARD' |
| deptno != ALL(10,20) | deptno != 10 AND deptno !=20 |
| sal BETWEEN 2000 AND 3000 | sal >= 2000 AND sal <= 3000 |
| NOT(sal<1000 OR comm is null) | sal >= 1000 AND comm IS NOT NULL |
- 뷰병합예
| Example Expression(From) | View Merging(tO) |
|---|---|
| {code:sql} CREATE VIEW emp_d10 AS SELECT * FROM emp WHERE deptno = 10 AND empno > 11910; {code} | {code:sql} SELECT empno FROM emp WHERE deptno = 10 AND empno > 11910; {code} |
- 서브질의 병합 예(Single Row Sub-Query)
| Example Expression(From) | Sub-Query Mergin(to) |
|---|---|
| {code:sql}SELECT * FROM dept WHERE deptno = (SELECT deptno FROM emp WHERE empno < 12501 );{code} | {code:sql}SELECT * FROM dept WHERE deptno = <evaluated_value>;{code} |
소프트 파싱과 하드파싱
- SQL문장이 옵티마이져에 의해 처리된 후 결과물로 QEP가 생기게된다.
- 이 QEP는 SGA의 공유 풀(Shared pool)에 캐쉬화 관리되고 같은 SQL문장이 실행되면 재활용하게 된다.
- SQL이 실행되면 문장 텍스트 스르링을 해쉬함수를 통과시켜 결과 값에 해당하는 버킷에 메달린 체인 정보에서 같은 SQL문장이 존재하는지 찾는 처리절차를 수행하게 된다.
- 같은 SQL문장을 찾은 후 같은버젼의 SQL이 존재하는지 찾게된다.(소프트파싱)
- 같은버젼이란, 같은 SQL문장이지만 대/소문자, 공백, 스키마(SCOTT.EMP OR SYS.EMP), 변수타입, 변수길이 등에 의해서 서로 다른 버젼이 된다.
- 만약 체인 정보에서 같은 SQL문장 발견하지 못하면, 해당 문장(Parsing/optimizing된)을 저장하기 위해 공유 풀로부터 메모리를 확보 받고 저장(체인생성)하게 된다.(하드파싱)
- 하드파싱은 소프트파싱에 비해 작업량이 많다.
1.3 옵티마이져의 형태
1.3.1 규칙기준의 옵티마이져
- 현실을 감안해 관계형 데이터베이스를 상품화하려는 사람들에 의해 만들어진 임시형
- 규칙기준의 옵티마이져는 우선순위
| 규칙기준의 옵티마이져의 우선순위 |
|---|
|
- 예
| emp테이블에 A인덱스가 deptno로 구성 B인덱스가 deptno + empno로 구성되어 있다면 옆 SQL 문장은A인덱스를 사용 하게 된다. 조건이 Bounded Range Search(Between)로 왔기 때문에 SQL 문장에서 (A)와 (B)의 랭킹은 (A) ==> Rank 9 , (B) ==> Rank 10 조건이 되므로 싱글 칼럼 인덱스를 사용하게 된다. |
- 단점
- 1,000,000로우를 가진 TAB1과 100로우를 가진 TAB2의 로우 수를 알지 못 해 어느쪽을 전체 테이블 스캔하는 것이 유리한지 구별하지 못한다.
- 장점
- 매우 규칙적이고 분명하여 사용자가 정확히 예측이 가능하며 이는 사용자가 미리 예측하여 원하는 방법으로 실행계획을 정확히 제어가 가능하다는 것을 의미한다.
- 보편타당성(현실적으로 키큰 사람이 작은 사람에 비해 농구를 잘 할 수 있는 확률)
- 변화
- 오늘날의 규칙기준 옵티마이져는 많이 변해있음.
- 명확한 우선순위에 의존하지 않는 원칙을 알 수 없는 경우가 발생.(테이블의 크기등에 따른 다른 가중치 반영 예상)
- 최대장점인 명확한 기준이 흔들려 이도 저도 아닌 상황이 되었음.
- 실습
규칙기준 옵티마이져(RBO)
1.3.2 비용기준 옵티마이져
- 관계형 데이터베이스가 추구하는 이상형
- 처리방법들에 대한 비용을 산정(통계정보를 참조)해 보고 그 중에서 가장 적은 비용이 들어가는 방법을 선택
- 장점
- 현실을 감안한 판단을 할 수 있다는 것
- 사용자의 실수로 인해 매우 나빠지는 실행계획은 어느정도 피할 수 있다.
- 단점.
- 실행계획을 예측하기 너무 어렵다.
- 인덱스 재생성, 통계정보 생성 누락, 통계정보 생성 주기에 따라 실행계획이 변해 버릴지도 모름.(사용자가 예측하기 힘듬)
- 실습
비용기준 옵티마이져(CBO)
비용기준 옵티마이져도 규칙기준 옵티마이져와 마찬가지로 전략적인 인덱스 구성과 효과적인 SQL을 사용하는 것이 우선적이다
DBMS_STATS패키지
- 해당 시스템만의 독특한 성능특성을 실제적으로 측정함으로써 옵티마이져에게 더 정확한 정보를 제공할 수 있게 한다.
- Oracle 8.1.5이상부터 통계정보 수집에 DBMS_STATS 패키지를 권장
참고 2006년6월3일스터디자료
1.4 옵티마이져의 한계
- 통계정보를 토대로한 정확한 처리범위를 예측할 수 있느냐에 대한 한계.
- 모든 값들에 대한 정확한 분포도를 알 수 있다면 정확한 실행계획 수립이 가능하지만, 일일이 모든 분포도를 보유한다는 것은 불가능.
- 결합된 컬럼에 대해 일일이 분포도를 보유할 수 없다.
1.5 개발자의 역할
- 개발자는 SQL을 잘 활용하는 방법을 익혀 두는 것이 필요하다.
SQL을 잘 활용한다는 것
=>
지금까지 구현해왔던 거의 모든 처리 방법의 변화
처리를 위한 접근방법의 변화
사고의 혁신
=>
비절차형 처리
집합적 사고에의한 처리
- SQL은 명령어가 아니다 하나의 애플리케이션이다.