영화 <마이너리포트>의 주인공인 톰 크루즈가 사용한 Dragging Board는 이미 몇 년전에 구현되었고 아이폰과 아이패드의 탄생으로 누구나 사용하게 되었다. 영화 <메트릭스>나 <터미네이터>를 보면 인간보다 우월한 기계들에 의해 지배를 당하거나 고통을 받는다. 이런 일을 먼 미래의 것으로 치부해 버리기에는 기술의 발전속도가 너무 빠르다. 이미 우리는 그런 세상에 살고 있다. 근거가 뭐냐고? 현재 적지 않은 수의 개발자들이 기계(옵티마이져) 보다 SQL의 작성능력이 떨어지기 때문이다.
예를 들면 옵티마이져가 재작성하는 SQL은 튜닝을 모르는 개발자가 작성한 것 보다 우월하다. 즉 개발자(인간)가 SQL을 작성했지만 옵티마이져는 품질이 떨어진다고 판단되는 SQL을 주인의 허락 없이 변경시켜 버린다.
인간이 Software 보다 못한 것인가?
“같은 블록을 반복해서 Scan 하면 성능이 느려진다” 라는 문구는 비단 개발자, DBA, 튜너만 생각하는 것이 아니다. 옵티마이져는 분석함수를 이용하여 위의 문구를 직접 실천한다. 다시 말하면 같은 테이블을 중복해서 사용하는 경우 옵티마이져는 비효율을 없애기 위해 분석함수를 이용하여 SQL을 변경시킨다. 아래의 SQL을 보자.
WITH v AS (SELECT /*+ INLINE */
department_id, SUM (salary) AS sal
FROM employee
WHERE job_id = 'ST_CLERK'
GROUP BY department_id )
SELECT d.department_id, d.department_name, v.sal
FROM department d, v
WHERE d.department_id = v.department_id
AND v.sal = (SELECT MAX (v.sal)
FROM v ) ;
위의 SQL을 보면 인라인뷰 V를 먼저 정의해놓고 아래의 Select 절에서 두 번 사용한 것을 알 수 있다. 다시 말하면 같은 테이블을 세 번(Temp 테이블에 Loading, 메인쿼리에 한번, 서브쿼리에 한번) 사용한 것이다. 아래의 실행계획을 보고 우리의 예상이 맞는지 확인해보자.
------------------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
------------------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 6 | |
| 1 | MERGE JOIN | | 5 | 275 | 6 | 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID | DEPARTMENT| 27 | 432 | 2 | 00:00:01 |
| 3 | INDEX FULL SCAN | DEPT_ID_PK| 27 | | 1 | 00:00:01 |
| 4 | SORT JOIN | | 5 | 195 | 4 | 00:00:01 |
| 5 | VIEW | | 5 | 195 | 3 | 00:00:01 |
| 6 | WINDOW BUFFER | | 5 | 80 | 3 | 00:00:01 |
| 7 | HASH GROUP BY | | 5 | 80 | 3 | 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE | 6 | 96 | 2 | 00:00:01 |
| 9 | INDEX RANGE SCAN | EMP_JOB_IX| 6 | | 1 | 00:00:01 |
------------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
4 - access("D"."DEPARTMENT_ID"="V"."DEPARTMENT_ID")
4 - filter("D"."DEPARTMENT_ID"="V"."DEPARTMENT_ID")
5 - filter("V"."SAL"="ITEM_0")
9 - access("JOB_ID"='ST_CLERK')
우리의 예상과는 달리 Employee 테이블에 대한 액세스가 단 한번 나왔다. 놀랍지 않은가? URSW라는 기능으로 인하여 중복 액세스를 제거해 버린 것이다. 즉 Logical Optimizer가 SQL을 아래와 같이 재작성 한 것이다.
SELECT d.department_id, d.department_name, v.sal sal
FROM department d,
( SELECT e.department_id, SUM (e.salary) sal,
MAX (SUM (e.salary)) OVER () item_0
FROM employee e
WHERE e.job_id = 'ST_CLERK'
GROUP BY e.department_id ) v
WHERE d.department_id = v.department_id
AND v.sal = v.item_0 ;
옵티마이져가 재작성한 SQL을 보면 employee 테이블을 단 한번 사용하고 있으므로 Plan 상에도 엑세스가 한번 나온 것이다. 이 기능은 Oracle 11gR2에서 추가되었다.
위의 예제는 Uncorrelated Subquery(비상관 서브쿼리)를 사용하는 예제이다. 비상관 서브쿼리라 함은 서브쿼리 내에 메인 쿼리와의 조인절이 없다는 뜻이다. 그런데 옵티마이져는 상관 서브쿼리에서도 같은 방식을 사용한다. 아래의 SQL을 보자.
SELECT a.employee_id, a.first_name, a.last_name, b.department_name
FROM employee a, department b
WHERE a.department_id = b.department_id
AND a.employee_id = (SELECT MAX (s.employee_id)
FROM employee s
WHERE s.department_id = b.department_id);
부서별로 MAX 사원번호에 해당하는 정보를 구하는 SQL이다. 이 SQL의 Plan은 아래와 같다.
----------------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 6 | |
| 1 | VIEW | VW_WIF_1 | 106 | 7208 | 6 | 00:00:01 |
| 2 | WINDOW BUFFER | | 106 | 6466 | 6 | 00:00:01 |
| 3 | MERGE JOIN | | 106 | 6466 | 6 | 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID | DEPARTMENT| 27 | 540 | 2 | 00:00:01 |
| 5 | INDEX FULL SCAN | DEPT_ID_PK| 27 | | 1 | 00:00:01 |
| 6 | SORT JOIN | | 107 | 4387 | 4 | 00:00:01 |
| 7 | TABLE ACCESS FULL | EMPLOYEE | 107 | 4387 | 3 | 00:00:01 |
----------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - filter("VW_COL_5" IS NOT NULL)
6 - access("A"."DEPARTMENT_ID"="B"."DEPARTMENT_ID")
6 - filter("A"."DEPARTMENT_ID"="B"."DEPARTMENT_ID")
Plan을 보면 employee 테이블을 단 한번만 엑세스 한다. 이것 역시 사람이 작성한 SQL을 옵티마이져가 성능에 문제가 된다고 판단하여 아래처럼 변경시킨 것이다.
VW_WIF_1.ITEM_3 LAST_NAME, VW_WIF_1.ITEM_4 DEPARTMENT_NAME
FROM (SELECT A.EMPLOYEE_ID ITEM_1, A.FIRST_NAME ITEM_2,
A.LAST_NAME ITEM_3, B.DEPARTMENT_NAME ITEM_4,
CASE A.EMPLOYEE_ID
WHEN MAX (A.EMPLOYEE_ID) OVER (PARTITION BY A.DEPARTMENT_ID)
THEN A.ROWID
END VW_COL_5
FROM TRANSFORMER.DEPARTMENT B, TRANSFORMER.EMPLOYEE A
WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID) VW_WIF_1
WHERE VW_WIF_1.VW_COL_5 IS NOT NULL
부서별로 MAX(EMPLOYEE_ID)의 값과 EMPLOYEE_ID를 비교하여 같으면 ROWID를 출력하고 있다. 따라서 ROWID 값이 NULL이 아니라면 EMPLOYEE_ID는 부서별로 MAX(EMPLOYEE_ID)와 같음을 보장한다. 그러므로 중복 엑세스가 제거될 수 있는 것이다. 이 사실은 VW_COL_5 IS NOT NULL 조건이 추가된 이유이기도 하다. 이 기능은 Oracle10g R2 에서 추가되었다.
SQL을 재작성하는 튜너는 옵티마이져에 포함되어 있다. 내가 작성한 SQL과 PLAN이 어떻게 변경되었는지 관심을 가져야 한다. 더 나아가서 훈수를 두려면 옵티마이져에 포함되어 있는 튜너보다 더 나아야 할 것이다. “지식의 대융합”(이인식 저)이라는 책을 보면 2030년을 기점으로 하여 인간이 기계보다 더 나은 점을 발견하기 힘들 것이라 한다. 이 책의 내용은 전문가들이 작성한 논문과 책을 종합한 것이므로 함부로 무시 할 수 없다.
사람이 기계보다 우월하려면 기계(옵티마이져)의 기능과 한계를 분석하고 이해해야 한다. 영화 <메트릭스>에서 인간과 기계 사이에 평화가 찾아온 이유는 기계의 한계(약점)를 이해하고 그것을 고쳐주었기 때문이 아닌가?
참조서적: The Logical Optimizer의 2.18 장, 2.19장
'Oracle > Optimizer' 카테고리의 다른 글
| 공지 - Cardinality Feed Back이 위험할 때 (1) | 2010.10.22 |
|---|---|
| union과 union all의 숨겨진 차이점 (6) | 2010.10.18 |
| 해결사 되기 (15) | 2010.02.04 |
| Distinct Elimination : 불필요한 Distinct를 제거하라 (6) | 2010.01.25 |
| Index Unque Scan은 SQL을 변경시킨다 (6) | 2010.01.20 |
