SQL 과 Optimizer
* Optimizer : SQL로 요구된 결과를 최소의 비용으로 처리할 수 있는 경로를 결정
* SQL 과 Optimizer
- SQL : 결과에 대한 요구일 뿐
- Optimizer : 처리절차를 생성 (진정한 Programmer)
- 없는 길을 생성해주는 것이 아니라, 이미 존재하는 길을 단지 찾아줄 뿐
- 사용자가 부여한 조건들에 따라 논리적으로 존재하는 최적은 달라진다.
- 그 최적이라는 것은 상황에 따라 늘 달라진다.
- 같은 결과가 나오긴 하나 그 경로에 따라 효율성의 차이는 크다.
- Optimizer는 절대 전지전능하지 않다. 사용자에게 그 책임이 있다.
Optimizer에 영향을 미치는 요소
1. SQL 형태 : 오류가 없도록
2. INDEX, CLUSTERING
3. 연산자 형태 : 연산자에 따라 INDEX의 사용여부가 결정
4. 통계 정보 : Rule-base, Cost-base
5. Hint 사용
6. Optimizer Mode
7. 분산DB
분산DB 에서의 Optimizer 전략은 Stand-alone 환경과 다르다.
서울본사에서 단가를 관리하고, 부산지점에서 고객과 주문을 관리하는 분산DB 환경이 있을 경우
SELECT A.prod_cd, A.qty * B.price FROM ORDER A, PRICE B WHERE A.prod_cd = B.prod_cd AND A.cust_id = :v_cust_id;
위의 SQL문은 당연히 A가 먼저 Driving 된다고 생각할 것이다.
하지만 Optimizer는 Network 속도에 의존하길 싫어한다.
Remote 환경에서의 JOIN보다는 Remote Table에서 Full-Scan하여 모두 가져온 다음에
Local 환경에서 JOIN 하는 것을 더 선호한다.
그래서 본사 B Table을 먼저 Full-Scan 한 다음에 B와 JOIN 한다.
해결책은 없을까 ?
본사쪽에 A, B 를 prod_cd 로 JOIN 한 VIEW를 생성해 놓고 지점에서는 해당 VIEW를 이용하면 된다.
그럼 A의 cust_id 로 Filtering 된 결과만을 본사에서 JOIN을 하고, 지점에서는 그 결과만 가져올수 있다.
8. DBMS 종류와 Version
Optimizer의 역할
Optimizer는 주어진 문제를 푸는 학생이다.
1. 먼저 주어진 조건중에 무자격 조건을 배제한다.
NOT 조건 배제, NULL 조건 배제 등등...
2. 사용가능한 Access형태를 선별한다.
LIKE 조건이네 ? INDEX가 있나... BETWEEN도 있군. INDEX가 있는가 ???
3. 사용할 조건을 선택한다.
tab1 부터 할까 2,3을 먼저 할까 ?
tab1 부터 하기로 하자. 그럼 2,3중 뭐를 먼저해야 할까 ?
Optimizer의 종류
1. Rule-based Optimizer
- Index 구조나 사용 연산자에 부여된 순위로써 최적경로 결정
정해진 Ranking에 맞게 순서대로 Access한다.
1. ROWID로 1 Row Access
2. Cluster JOIN에 의한 1 Row Access
3. Unique Hash Cluster에 의한 1 Row Access
4. Unique Index에 의한 1 Row Access
5. Cluster JOIN
6. Non Unique Hash Cluster Key
7. Non Unique Cluster Key
8. Non Unique Merge Index
...
15. Table Full-Scan
- 통계정보를 전혀 가지지 않음
- 수립될 처리경로 예측 가능 (Optimizer를 아는 사람에게만)
- 사용자가 원하는 처리경로로 유도하기 좋다. (역시 아는 사람에게만)
- 생각보다 신뢰도가 높다.
- 하지만 경우에 따라 비현실적인 경로로 처리하기도 한다.
- 현실성을 무시하고 규칙만을 따르기 때문에 성공적으로 수행되도록 확률을 높여주는 것은 사용자의 몫이다.
2. Cost-based Optimizer
- 통계정보를 이용해 비용을 계산하여 최소비용 선택
Height-balanced Histogram의 예제이다.
Histogram의 높이를 동일하게 유지하여 일정 넓이 이상이 넘어가는 Key에 대해서는 Full-Scan한다.
Width-balanced Histogram에서는 너비를 일정하게 유지하여 일정 높이 이상에 대해서는 Full-Scan한다.
- 전문지식이 부족하더라도 극단적인 악성 실행계획은 피할 수 있음
- 이론적으로는 Rul-base보다 더 진보된 형태이긴 하나
- 실행계획의 예측이 곤란하여 (럭비공 같이)
- 원하는 처리경로로 유도하기 힘들다.
- 그냥 고만고만한 성능은 내지만 완벽한 실행계획은 얻을 수 없다.
- Hint 로 FIRST_ROWS (초기결과가 빨리 나오는 방법) , ALL_ROWS (전체 처리시간 최적화) 를 사용할 수 있다.
Optimizer의 한계
1. 현재의 정보만으로 미래를 예측해야 함
- 아무리 많은 정보를 가지고 있는다 한들 그걸로 정확한 미래 예측이 가능한가 ?
- 얼마나 많은 정보를 가지고 있어야 하나 ?
2. 분포도 산정의 어려움
- Column 사용 연산자별로 정확한 분포도를 얻을 수 있나 ?
- Column 결합에 따른 정확한 분포도를 얻을 수 있나 ?
(2개의 결합이라 하더라도 각 Column당 5가지 상태가 있다면, 5 * 5 = 25가지의 분포도를 다 가지고 있어야 하나 ?)
- 모든 결합형태에 대한 통계정보를 보유할 수 있나 ?
(문자열을 like '%'로 비교한다고 했을때 문자열의 한글자의 종류가 30가지라고만 해도 30의 2의 n개의 통계정보를 다 ?)
- Column간의 결합 분포도는 궁합에 따라 크게 달라진다.3. 논리적으로 이미 존재하는 길만을 찾아 줄 뿐
- 없는 길을 만들 수 없고,
- SQL 사용 능력과 전략적인 INDEX 구성은 결국 사람의 몫
- 콩심은데 콩나고 팥심은데 팥난다.
4. 현실에서 대부분 SQL문은 값을 :Variable로 부여
- 설사 정확한 분포도를 판단할수 있다 하더라도 어떤 변수가 들어올지 미리 알고 ?
- 결국은 기존의 통계정보의 평균값에 의존할 뿐
- 그렇다고 매번 Dynamic SQL로 처리하여 다시 Parsing ? 이건 말이 안됨
개발자의 역할
- 비행기(Oracle)를 두고 자동차(C++)를 타고 갈 것인가 ?
- 물론 C++의 Logic으로는 불가능 한것이 없다. 내가 원하는대로 얼마든지 만들 수 있다.
- 하지만 모든걸 직접다 ? 그래야만 직성이 풀리는 사람도 있겠지만,
- 그 비싼 Oracle에게는 단순 SQL의 반복적인 Call로 단순반복 작업만 시키고,
- 모든걸 스스로 다 처리 하는게 과연 좋을까 ?
- Oracle의 Optimizer에게 맡겨놓고
- 나는 숙제를 내고 검사하는 선생님 역할을 하면 어떨까 ?
- 물론 Optimizer를 배우고 이해하는게 어렵겠지만,
- 그 원리만 이해하면 몇천배 몇만배 더 빠르게 처리가 가능하다.
댓글 없음:
댓글 쓰기