영화 <마이너리포트>의 주인공인 톰 크루즈가 사용한 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을 옵티마이져가 성능에 문제가 된다고 판단하여 아래처럼 변경시킨 것이다.
 

SELECT VW_WIF_1.ITEM_1 EMPLOYEE_ID, VW_WIF_1.ITEM_2 FIRST_NAME,
       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


 

Posted by extremedb
,
Posted by extremedb
,

오라클 11.2 버전은 아래의 링크에서 다운받을 수 있다.
http://www.oracle.com/technology/software/products/database/index.html


실습 스크립트 다운로드
실습을 진행하기 위한 스크립트는 아래와 같다.  

1. Schema Generation Script : Oracle 11gR1 과 11gR2중 버젼을 선택해서 다운 받으면 된다.
    다운받은후 User를 생성하고 권한부여 후 Import를 하면 실습 준비가 완료된다. 실습을 진행하려면
    TLO 계정으로 접속해야 한다. TLO 계정의 비밀번호는 transformer이다.
2. Part 1 Script : SQL 파일
3. Part 2 Script : SQL 파일과 10053 Trace 파일 포함
4. Part 3 Script : SQL 파일과 10053 Trace 파일 포함
5. Part 4 Script : SQL 파일과 10053 Trace 파일 포함
6. Appendix Script : 부록의 예제 스크립트임. SQL 파일

모두 다운 받으면 아래와 같이 총 15 개의 압축 파일이 된다.

사용자 삽입 이미지

용량이 크므로 7z 를 이용하여 압축 하였지만 일반적인 압축 프로그램으로 압축을 풀수 있다. 압축을 해제하면 위와 같은 폴더의 모습이 된다.
각 폴더의 용량을 합쳐 586 MB가 나오면 정상이다.
아래의 압축 파일을 모두 Download 하기 바란다.
데이터 import 시 에러가 나는 부분은 무시해도 된다. 정상적으로 처리된 것이다.
 


invalid-file

Schema 생성 Script for Oracle 11.2.0.1

invalid-file

Schema 생성 Script for Oracle 11.1.0.6

invalid-file

Scripts for Part1

invalid-file

Scripts for Part2

invalid-file

Scripts for Part3

invalid-file

Scripts for Part4

invalid-file

Scripts for Appendix

invalid-file

서브쿼리의 From 절에 테이블이 2개 이상일때 CBQT가 발생하는 예제




PS
한가지 걱정은 블로그 구독자 정도의 수준이라면 이책을 읽을 수 있으나 초보가 띠지의 내용등에 혹 해서 사면 어쩌나 하는 것이다.  주위에 그런사람들이 있다면 말려주기 바란다. 이 책은 초보용이 아니다.



구독자분이 스키마를 exp 형태 대신에 script 형태로 제공해 달라는 요청을 받았다.
아래의 스크립트를 이용하면 된다. 단 Oracle Sample 스키마인 SH와 HR 이 존재해야 한다.
 



'The Logical Optimizer' 카테고리의 다른 글

The Logical Optimizer Part 1 - PPT  (17) 2010.07.26
The Logical Optimizer-서점  (0) 2010.04.27
The Logical Optimizer-오타와 오류등록  (27) 2010.04.20
저자와의 대화  (36) 2010.04.20
The Logical Optimizer  (62) 2010.04.05
Posted by extremedb
,
오타와 오류를 발견하신 독자는 댓글을 이용해 주세요.


Page

부분

수정 전

수정 후

53

밑에서 세 번째 줄

~DBA들의 취약점 중 많은 부분이 장의 내용을~

~DBA들의 취약점 중 많은 부분이 장의 내용을~

77

outline data

ELIMINATE_JOIN(@"SEL$1" "P"@"SEL$1")

ELIMINATE_JOIN(@"SEL$1" "D"@"SEL$1")

80

제목

2.4 OJE Outer-Join Elimination)

2.4 OJE (Outer-Join Elimination)

104

SQL 윗부분

SELECT e.employee_id, e.email, d.department_id

SELECT /*+ no_merge(@sel$1)  */
         e.employee_id, e.email, d.department_id

104

SQL 아랫부분

SQL의 결과는 아래와 같다.

SQL에 힌트를 사용한 이유는 Lateral View를 보존하기 위해서이다. View Merge 된다면 실행계획에서 Lateral View를 볼 수 없다. SQL의 결과는 아래와 같다.

106

직원구분코드 컬럼생성 부분

ALTER TABLE EMPLOYEE ADD EMP_KIND VARCHAR2(1) DEFAULT '1' NOT NULL;

ALTER TABLE EMPLOYEE ADD EMP_KIND VARCHAR2(1) DEFAULT 1 NOT NULL;

108

SQL 윗부분

SELECT /*+ GATHER_PLAN_STATISTICS ORDERED */

SELECT /*+ GATHER_PLAN_STATISTICS ORDERED NO_MERGE(@SEL$1) NO_MERGE(@SEL$3) */

109

위에서 네 번째 줄

ANSI SQL을 이용함으로써 선택적으로 ~

여기서도 View Merging을 발생하지 않게 하기위해 NO_MERGE 힌트를 사용하였다. ANSI SQL을 이용함으로써 선택적으로~

109

위에서 일곱 번째 줄

ANSI SQL을 사용할 수 없는 경우는 아래와 같이 조인절에 DECODE CASE 문을 사용하여도 같은 효과를 누릴 수 있다.

삭제 후 추가될 내용



내용이 많아 첨부파일로 처리함.

109

밑에서 다섯 번째 줄

Lateral View ANSI SQL 뿐만 아니라
일반적인 뷰를 Outer 조인하는 경우, ~

Lateral View 사용하지 않으면서도 선택적으로 조인하고 있다. 이 방법은 ANSI SQL을 사용할 수 없는 환경에서 훌륭한 해결책이 될 것이다.

Lateral View
ANSI SQL 뿐만 아니라 일반적인 뷰를 Outer 조인하는 경우, ~

120

위에서 두 번째 줄

Subsumtion

Subsumption

121

SQL 윗부분

Subsumtion

Subsumption

158

위에서 아홉 번째 줄

따라서 Null인 데이터를 찾자마자 Scan
 
멈출 수 있는 것이다.

따라서 Null인 데이터를 찾자마자 Scan을 멈출 수 있는 것이다. 추가될 내용의 위치




내용이 많아 첨부파일로 처리함.
관련내용: http://scidb.tistory.com/120

158, 159

158 페이지 위에서 열 번째 줄부터 ~ 159 페이지 세 번째 줄 까지

하지만 모든 Not In 서브쿼리에 Is Null 조건을 추가하면 결과가 틀려지지 않을까?  부터

이처럼 서브쿼리의 조건절이 추가된다면 그에 따라 적응적 탐색(Adaptive Null Aware Scan)을 하므로 걱정할 것이 없다.
까지 SQL과 실행계획 포함하여 모두 삭제

삭제 후 추가될 내용



내용이 많아 첨부파일로 처리함.
관련내용: http://scidb.tistory.com/121

162

10053 Trace 위의 제약사항 부분

두 번째로는 Where 절의 Is Null 조건에는 Outer 쪽 테이블의 PK 컬럼만 올 수 있다. 물론 PK 컬럼으로 조인이 되어야 함은 당연한 것이다.

두 번째로는 Outer Join에 사용된 컬럼과 Is Null 조건에 사용된 컬럼이 동일해야 한다. d.department_idOuter Join하고 d.department_name으로 Is Null 조건을 주면 OJTAJ가 발생되지 않는다.

105

1)번 부분

employee department간의 관계가 N:1 이면서 department 쪽이 Outer Join을 사용하였기 때문이다.

employee department간의 관계가 N:1 이면서 department 쪽이 Outer Join을 사용하였기 때문이다. 반대로 메인쿼리와 Lateral View 1:N의 관계라면 Lateral View는 결과건수에 영향을 미친다.

105

3)번 부분

이러한 모든 상황에서 Later View 내부의 테이블에 ~

이러한 모든 상황에서 Lateral View 내부의 테이블에 ~

197

Column Projection Information 윗부분

10053 Trace에는 파라미터 Pivot2를 적용할 경우 SQL 정보가 없다. 부터 197페이지 마지막 까지 모두 삭제

삭제 후 추가될 내용

 

내용이 많아 첨부파일로 처리함.




빨강색은 삭제이고 파랑색은 추가 입니다.


'The Logical Optimizer' 카테고리의 다른 글

The Logical Optimizer Part 1 - PPT  (17) 2010.07.26
The Logical Optimizer-서점  (0) 2010.04.27
The Logical Optimizer-Script Download  (37) 2010.04.20
저자와의 대화  (36) 2010.04.20
The Logical Optimizer  (62) 2010.04.05
Posted by extremedb
,

저자와 이야기 나누실 독자는 이 페이지의 댓글을 이용하세요.




유수익님이 질문하신글(http://scidb.tistory.com/112#comment4410920)의 답변입니다.
아래의 첨부파일을 참조하세요.









'The Logical Optimizer' 카테고리의 다른 글

The Logical Optimizer Part 1 - PPT  (17) 2010.07.26
The Logical Optimizer-서점  (0) 2010.04.27
The Logical Optimizer-Script Download  (37) 2010.04.20
The Logical Optimizer-오타와 오류등록  (27) 2010.04.20
The Logical Optimizer  (62) 2010.04.05
Posted by extremedb
,

bypass_ujvc 힌트와 관련하여 필자와 의견을 달리하는 전문가도 있음을 밝혀둔다. 특정 버젼에서 특정 상황에서 힌트를 확실히 이해하고 성능문제가 큰 경우일 때만 사용한다면 된다는 것이다. 제약사항을 4가지나 붙였으므로 공감이 가는 부분이 있다. 아래의 댓글을 반드시 읽어보기 바란다. 2010-04-19 (추가)

ANSI SQL
UPDATE문은 오라클과 달리 FROM 절이 존재하며 여러 테이블 혹은 뷰와 자유로이 조인할 수 있다.
아래의 SQL을 보자.

 

UPDATE DEPT

    SET DEPT_COUNT = E.CNT

FROM DEPT D,

(SELECT DEPTNO, COUNT(*) CNT

FROM EMP

WHERE JOB = ‘CLERK’

GROUP BY DEPTNO) E

WHERE D.DEPTNO = E.DEPTNO ;

 

위의 SQL MS-SQL 서버에서 사용할 수 있는 UPDATE문이지만 오라클에서 사용할 수 없다. 위의 SQL을 오라클로 바꾼다면 조인이 불가능하므로 아래처럼 스칼라 서브쿼리와 서브쿼리를 사용해야 한다.

 

UPDATE DEPT D

SET DEPT_COUNT = (SELECT COUNT(*)  

FROM EMP E

WHERE E.DEPTNO = D.DEPTNO

AND E.JOB = ‘CLERK’)

WHERE EXISTS (SELECT 1

FROM EMP E

WHERE E.DEPTNO = D.DEPTNO

AND E.JOB = ‘CLERK’) ;

 

중복 조인을 피할 수 있나? 

언뜻 보기에도 비효율이 극심하게 드러난다. EMP와 조인이 두 번 발생한 것이다. 중복된 조인을 피하기 위해서 아래처럼 인라인뷰와 스칼라 서브쿼리를 혼합하여 사용할 수 있지만 이 또한 중복 조인을 피할 수 없다. 보기에는 중복조인이 없는 것처럼 보이지만 Query Transformation을 공부하였다면 중복 조인이 보일 것이다.


UPDATE (SELECT d.deptno, d.dept_count,
               (SELECT COUNT (*)
                  FROM emp e
                 WHERE e.deptno = d.deptno
                   AND e.job = 'CLERK') cnt
          FROM dept d)
   SET dept_count = cnt
 WHERE cnt > 0;


 
--------------------------------------------------------------------------------------

| Id  | Operation                      | Name           | Rows  | Bytes | Cost (%CPU)|

--------------------------------------------------------------------------------------

|   0 | UPDATE STATEMENT               |                |     4 |    64 |     5   (0)|

|   1 |  UPDATE                        | DEPT           |       |       |            |

|*  2 |   FILTER                       |                |       |       |            |

|   3 |    TABLE ACCESS FULL           | DEPT           |     4 |    64 |     3   (0)|

|   4 |    SORT AGGREGATE              |                |     1 |    11 |            |

|*  5 |     TABLE ACCESS BY INDEX ROWID| EMP            |     1 |    11 |     2   (0)|

|*  6 |      INDEX RANGE SCAN          | EMP_DEPTNO_IDX |     5 |       |     1   (0)|

--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter( (SELECT COUNT(*) FROM "SCOTT"."EMP" "E" WHERE "E"."DEPTNO"=:B1

              AND "E"."JOB"='CLERK')>0)

   5 - filter("E"."JOB"='CLERK')

   6 - access("E"."DEPTNO"=:B1)

 

Predicate Information을 보면 ID 기준으로 2번에서 서브쿼리가 FILTER로 사용되었고 6번에서 다시 스칼라 서브쿼리가 사용되었으므로 두 번 조인이 발생한 것이다. 이해가 되지 않는 독자는 스칼라 서브쿼리를 서브쿼리로 변환하라 글을 읽기 바란다.

 

또 다른 제약

조인이 안 된다는 제약을 피하기 위해 VIEW 혹은 인라인뷰를 UPDATE 하곤 한다. 하지만 아래의 새로운 예제를 본다면 또 다른 제약이 있음을 알 수 있다.

 

create or replace view v_emp

as

select  e.empno, e.ename, e.job, e.sal, d.dname, d.deptno

from   emp e, dept d

where  e.deptno = d.deptno;

 

update V_EMP

   set dname = ‘NO_DEPT’ --> DEPT 쪽을 UPDATE 하고 있으므로 에러

 where empno = ‘7369’;

 

ERROR at line 2:

ORA-01779: cannot modify a column which maps to a non key-preserved table

 

Deptemp 1:N의 관계인데 1쪽을 Update 하지 못하는 제약을 만나게 된다. 이 제약을 피하기 위해서 bypass_ujvc 힌트를 사용하는 사람이 있는데 절대 사용하면 안 되는 힌트이다. 힌트를 해석하자면 Updatable Join View Check By-Pass(무시) 하겠다는 뜻이다. 실제로 Wrong Result(답이 잘못됨)가 나오므로 사용해서는 안 된다.

 

끝없는 제약

ODS 시스템이나 데이터를 이행하기 위한 Temp성 테이블에는 Key가 없는 경우가 많다. 아래는 PK를 제거한 상태에서 인라인뷰를 UPDATE 해보았다. 단순히 사번이 들어오면 부서번호가 부서 테이블에 존재하는지 체크하여 급여를 UPDATE 하는 SQL이다. 
 

ALTER TABLE SCOTT.DEPT MODIFY CONSTRAINT PK_DEPT DISABLE; 

Update (select a.empno, a.ename, a.sal, b.dname

          from emp a, dept b

         where a.deptno = b.deptno

           and a.empno = 7369)
set sal = 5000;


ORA-01779: cannot modify a column which maps to a non key-preserved table

 

Key를 사용할 수 없으므로 뷰 혹은 인라인뷰를 update할 때 키 보존 제약이 걸리게 된다. 이때 마찬가지로 bypass_ujvc를 사용하면 에러는 피할 수 있지만 결과를 보장 하지 않는다. 이 힌트는 건널목 신호등에 빨강 불이 들어왔지만 알아서 건너가시오. 자동차에 부딪혀도 책임지지 않습니다.” 로 비유할 수 있다.

 

Bypass_ujvc 힌트를 사용하지 않고 해결해야 해

위에서 언급한 세가지 제약조건(조인이 안됨, 뷰에서 1 update 안됨, 뷰에서 키가 없으면 update 불가)과 한가지 문제(중복 조인)를 피할 수 있는 방법이 있다.  

1) 조인이 안 되는 문제와 중복 조인문제 해결

 

MERGE INTO dept d

USING (SELECT deptno, COUNT (*) cnt

         FROM emp

        WHERE job = 'CLERK'

        GROUP BY deptno) e

   ON (e.deptno = d.deptno)

 WHEN MATCHED THEN

UPDATE SET d.dept_count = e.cnt;

 

Merge successfully completed.

 

----------------------------------------------------------------------------------

| Id  | Operation                      | Name    | A-Rows |   A-Time   | Buffers |

----------------------------------------------------------------------------------

|   1 |  MERGE                         | DEPT    |      1 |00:00:00.01 |      18 |

|   2 |   VIEW                         |         |      3 |00:00:00.01 |      13 |

|   3 |    NESTED LOOPS                |         |      3 |00:00:00.01 |      13 |

|   4 |     NESTED LOOPS               |         |      3 |00:00:00.01 |       9 |

|   5 |      VIEW                      |         |      3 |00:00:00.01 |       7 |

|   6 |       SORT GROUP BY            |         |      3 |00:00:00.01 |       7 |

|*  7 |        TABLE ACCESS FULL       | EMP     |      4 |00:00:00.01 |       7 |

|*  8 |      INDEX UNIQUE SCAN         | PK_DEPT |      3 |00:00:00.01 |       2 |

|   9 |     TABLE ACCESS BY INDEX ROWID| DEPT    |      3 |00:00:00.01 |       4 |

----------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   7 - filter("JOB"='CLERK')

   8 - access("E"."DEPTNO"="D"."DEPTNO")

 

2) 1쪽이 UPDATE 안 되는 문제 해결

 

MERGE INTO dept d

USING emp e

   ON (e.deptno = d.deptno AND e.empno = '7369')

 WHEN MATCHED THEN
UPDATE SET d.dname = 'NO_DEPT' ;

 

Merge successfully completed.

 

----------------------------------------------------------------------------------

| Id  | Operation                      | Name    | A-Rows |   A-Time   | Buffers |

----------------------------------------------------------------------------------

|   1 |  MERGE                         | DEPT    |      1 |00:00:00.01 |       5 |

|   2 |   VIEW                         |         |      1 |00:00:00.01 |       4 |

|   3 |    NESTED LOOPS                |         |      1 |00:00:00.01 |       4 |

|   4 |     TABLE ACCESS BY INDEX ROWID| EMP     |      1 |00:00:00.01 |       2 |

|*  5 |      INDEX UNIQUE SCAN         | PK_EMP  |      1 |00:00:00.01 |       1 |

|   6 |     TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |00:00:00.01 |       2 |

|*  7 |      INDEX UNIQUE SCAN         | PK_DEPT |      1 |00:00:00.01 |       1 |

----------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   5 - access("E"."EMPNO"=7369)

   7 - access("E"."DEPTNO"="D"."DEPTNO")

 

 

3) 키가 없으면 UPDATE가 불가한 문제 해결

 

ALTER TABLE SCOTT.DEPT MODIFY CONSTRAINT PK_DEPT DISABLE;

 

MERGE /*+ USE_HASH(D) */ INTO emp e

USING dept d

   ON (e.deptno = d.deptno AND e.empno = 7369)

 WHEN MATCHED THEN
UPDATE SET e.sal = 5000;

 

Merge successfully completed.

 

--------------------------------------------------------------------------------------------

| Id  | Operation                      | Name   | A-Rows |   A-Time   | Buffers | Used-Mem |

--------------------------------------------------------------------------------------------

|   1 |  MERGE                         | EMP    |      1 |00:00:00.01 |      12 |          |

|   2 |   VIEW                         |        |      1 |00:00:00.01 |       9 |          |

|*  3 |    HASH JOIN                   |        |      1 |00:00:00.01 |       9 |  316K (0)|

|   4 |     TABLE ACCESS BY INDEX ROWID| EMP    |      1 |00:00:00.01 |       2 |          |

|*  5 |      INDEX UNIQUE SCAN         | PK_EMP |      1 |00:00:00.01 |       1 |          |

|   6 |     TABLE ACCESS FULL          | DEPT   |      4 |00:00:00.01 |       7 |          |

--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - access("E"."DEPTNO"="D"."DEPTNO")

   5 - access("E"."EMPNO"=7369)

 


결론

위의 SQL 세가지는 큰 문제 4가지를 해결한 것이므로 익혀서 적재적소에 활용하기 바란다.
MERGE
문의 문법은 매우 간단하다. 하지만 이 문법을 보고 그것을 어디에 어떻게 활용할 것인가는 전혀 다른 문제이다. DBMS의 버전이 올라가면 신기능이 탄생한다. 그때마다기능을 어디에 사용하면 가장 큰 효과가 나타날 것인지를 생각해 보라. 오늘보다 더 발전된 내일의 당신을 위해서.

 

Posted by extremedb
,

원래 3월에 출간 예정이 었으나 마음대로 되지 않았다. 회사 내/외부에서 책이 왜 늦어지냐고 원성을 많이 들었다.
여러분들에게 사과드린다.
 
필름 마감
드디어 인쇄용 필름이 마감되었다. 은행에도 일 마감이 있듯이 출판에도 필름 마감이라는게 있다. 이 과정이 끝나면 인쇄가 시작된다. 오늘 인쇄작입이 시작될 것이다. 1월에 원고를 완성했지만 여러가지 문제(오탈자 수정 작업, 표지 디자인, 띠지 디자인, 메켄토시용 워드로 변환 과정에서 오류및 페이지수가 달라지는 현상, 페이지가 달라졌으므로 목차 및 색인 재작업, 인쇄용지 부족현상, ISBN 번호 취득, 표지와 띠지 그리고 본문의 용지 선택, 최종 필름의 검증) 과정에서 시간을 많이 소모 하였다. 이 모든 과정이서 작가의 의견이 직 간접적으로 들어가야 한다. 이제 남은건 서점과의 계약인데 4월 20일 정도에 YES24나 교보문고 등에서 주문이 가능할 것이다.

그럼 이제 책의 겉모습을 보자.



사용자 삽입 이미지


삼장법사와 손오공의 관계는?
표지는 빈티지 스타일로 처리하여 케케묵은 고서(오래된 책)의 느낌을 받도록 하였다. 앞 표지의 그림은 삼장법사와 손오공이다. 이 그림은 Logical Optimizer와 Physical Optimizer의 관계를 나타낸 것이다. 제일 아래의 미리보기 파일을 보면 상세한 내용을 알 수 있다. 총 430 페이지 이므로 책등을 보더라도 그다지 두껍지는 않다.

이제 표지에 띠지를 입혀 보자.


사용자 삽입 이미지

그림을 클릭하면 크게 볼 수 있다. 띠지가 너무 강렬하다는 의견도 있었으나 바꿀 경우 작업시간 때문에 출간일자가 늦어지므로 그냥 가기로 하였다. 나중에 알고보니 띠지가 강렬한 것이 아니라 띠지의 표준색이 빨강이라 한다. 평소에 띠지를 주의 깊게 보지 않아서 오해한 것이다.


책을 집필 하게된 원인
2006
년 늦은 가을의 한 사건 때문에 이 책이 나올 수 있었다. 그 사건이 아니었다면 Logical Optimizer로 인한 문제가 실무에서 얼마나 중요한지 알 수 없었을 것이다. 아래에 그 사건과 관련된 에피소드를 소개한다.

Episode

영화 <아바타>에는 영혼의 나무를 통하여 생명체와 교감하며 평화로운 생활을 영위하는 판도라 행성의 나비족이 등장한다. 하지만 이 행성의 광물에 눈이 먼 지구인들은 무력을 통해 이들을 짓밟게 되고, 인간의 탐욕에 치를 떤 지구인 제이크 셜리는 인간을 등지고 나비족의 편에 선다. 하지만 그 과정에서 나비족의 신뢰를 받지 못한 제이크는 무모하게도 나비족 역사 이래 5번밖에 소유하지 못했던 영적 동물 토르쿠 막토를 획득하려는 불가능한 시도를 하게 된다. 천신만고 끝에 얻어낸 토르쿠 막토는 모든 상황을 급 반전시킨다. 결국 그는 토르쿠 막토의 힘을 빌려 나비족의 새로운 지도자가 되고 인간과의 전쟁을 승리로 이끈다.


토르쿠 막토, 우리가 가질 수 있나
영화가 아닌 현실에서도 모든 상황을 한번에 해결할 만한 토르쿠 막토 같은 위력적인 무기를 가질 수 있을까? 지금부터 그것을 손에 넣었던 필자의 경험담을 소개한다.

2006년 늦은 가을이었던가? 필자는 새로운 사이트에 투입되어 DBA들과 튜닝 중에 있었다. 개발자들이 튜닝을 의뢰하면 먼저 DBA들이 튜닝을 실시하고, DBA가 해결하지 못하는 SQL은 필자에게 튜닝 요청이 들어온다. 하지만 그 당시 한 달이 넘게 DBA들과 필자가 튜닝 작업에 고심하였음에도 요청되는 튜닝 건수에 비해 해결되는 건수가 턱없이 부족했다. 베테랑 DBA 3명이나 있었음에도 불구하고 해결되지 않는 SQL의 건수는 계속해서 쌓여가고 있었다.

도대체 왜?
한 달째인 그날도 밤 12시가 넘었지만 퇴근하지 못했으며 이것이 어쩔 수 없는 컨설턴트의 숙명이거니 하는 자포자기의 심정이 들었다. 새벽 한 시가 되어 주위를 둘러보니 사무실엔 아무도 없었다. 얼마 후 건물 전체가 소등되었고 모니터의 불빛만이 남아있었다. 암흑과 같은 공간에서 한동안 적막이 흘렀다. 바로 그 순간 요청된 SQL에는 일정한 패턴이 있지 않을까 하는 생각이 번쩍 들었다. 갑자기 든 그 생각으로 필자는 퇴근할 생각도 잊은 채 SQL에 대한 패턴을 분석하기 시작했다. 그리고 몇 시간 후 동 틀 무렵, 놀라운 결과를 발견할 수 있었다.

필자에게 튜닝을 요청한 SQL의 많은 부분이 Query Transformation(이하 QT) 문제였다. Logical Optimizer의 원리만 알았다면 필자를 비롯한 DBA들은 저녁 7시 이전에 일을 마칠 수 있었을 것이다. QT Logical Optimizer가 성능 향상의 목적으로 SQL을 재 작성(변경)하는 것을 말한다. 하지만 옵티마이져가 완벽하지 못하므로 많은 경우에 문제를 일으키게 된다.

베테랑 DBA들의 아킬레스건은 고전적인 튜닝 방법에 의존하는 것
DBA들은 지금껏 전통적인 튜닝 방법 3가지(Access Path, 조인방법, 조인순서)에 대한 최적화만 시도하고, 그 방법으로 해결되지 않으면 필자에게 튜닝을 요청한 것이다. 그들에게 QT를 아느냐 물었을 때 대답은 거의 동일했다. 그들이 아는 것은 Where 조건이 뷰에 침투되는 기능, 뷰가 Merging(해체)되는 기능, OR 조건이 Union All로 변경되는 기능, 세 가지 뿐이었다. 실무에서 발견되는 대부분의 문제를 해결하려면 최소한 30가지 이상은 알아야 한다. 그런데 세 가지만 알고 있다니...... 충격적인 결과였다. 10개 중에 9개를 모르는 것과 같았다.

하지만 QT와 관련된 적절한 교재나 교육기관이 전무한 상태였기 때문에 이러한 문제에 대해 DBA들을 탓할 수는 없을 것이다(이 사실은 2006년이 아닌 2010년 현재도 마찬가지이다). 필자는 다음날부터 삼 일 동안 튜닝을 전혀 하지 않기로 마음 먹었다. 대신에 DBA들에게 Query Transformation에 대한 교육을 하기로 작정했다. 필자의 입장에서는 교육을 진행하지 않아도 그때까지 쌓여있는 튜닝 이슈만 해결하면 프로젝트를 마무리 할 수 있었다. 하지만 열정 때문인지 아니면 윤리적 의무감이 원인인지 모르겠으나 교육을 진행하지 않은 상태에서 프로젝트를 끝낼 수 없다고 생각하고 있었다.


난관
다음날 필자는 DBA들과 담당 책임자를 불러서 교육에 관한 회의를 하였다. 책임자는 삼 일간 18시간의 교육 때문에 튜닝 실적이 거의 없게 되므로 교육은 불가능하다는 것이었다. 업무시간 중 교육을 하게 됨으로 필자 뿐만 아니라 모든 DBA들의 튜닝실적이 없게 되는 것이다. 책임자와 DBA들은 해결되지 않는 튜닝문제의 대부분이 Logical Optimizer 때문이라는 사실을 필자의 분석자료를 통해 알고 있었다. 하지만 책임자는 상부에 튜닝 실적을 보고해야 되는 처지였으므로 교육은 불가하다고 하였다.

필자는 교육 후에 가속도가 붙을 것이므로 실적을 충분히 따라잡을 것 이라고 책임자를 설득하였다. 그는 실적 대신에 교육 후에 향상된 DBA들의 문제 해결능력을 상부에 보고하겠다고 하였다. 다행스러운 일 이었다. 그런데 이번에는 DBA들이 교육을 완강히 거부했다. 그들은 튜닝 이외에 Database 관리업무도 진행해야 하는데 삼 일의 교육기간 중 업무를 처리하지 못하게 된다는 것이었다. 따라서 교육 후에 밤을 세워서라도 밀린 업무를 수행해야 되는 처지였으므로 교육을 부담스러워 했다. 또한 Logical Optimizer의 원리보다는 고전적인 튜닝 방법을 신뢰하고 있었기 때문에 며칠간의 교육으로 문제가 해결될지 의심하고 있었다.


설득의 방법
필자는 강한 반대 의견 때문에  ‘억지로 교육을 해야 하나?’ 라는 생각이 들었다. 마지막 이라는 심정으로 설득의 방법을 바꾸어 보았다. DBA들이 교육을 통해서 무엇을 얻을 것인가(WIFM) 관점보다는 교육을 받지 못하면 손해를 보게될 상황을 설명 하였다. 즉 튜닝 프로젝트가 끝나고 필자가 나간 뒤에도 같은 패턴의 튜닝 문제가 발생할 것인데 지금 교육을 받지 않는다면 그때가 되어도 튜닝을 할 수 없을 것이라고 강조하였다. 또한 업무시간 후에 교육을 받으면 시간을 거의 뺏기지 않을 것 이라고 설명하였다.

마침내 설득은 효과를 발휘했다. 업무시간을 제외한 저녁 7시부터 10시까지 총 6일간 교육을 진행하기로 모두가 합의하였다. 3일 간의 교육이 6일간의 교육으로 늘어지긴 하였지만 교육을 진행할 수 있게 되었다는 사실만으로도 아주 다행스런 결과였다. 교육시간에 실무에서 가장 발생하기 쉬운 QT 기능들의 원리와 튜닝방법부터 설명하였다. 일주일의 교육을 마치자 곧바로 효과가 나타났다. 교육 후 필자에게 들어오는 튜닝 의뢰 건수가 절반으로 줄어든 것이다. 비로소 필자는 정상적인 시간에 퇴근할 수 있게 되었다
.

기적은 필자에게만 일어난 것이 아니었다. 교육 이전에 DBA들은 밤 11시가 넘어서야 퇴근 하였다. 왜냐하면 필자에게 튜닝 요청을 하기 전에 성능이 개선되지 않는 SQL을 짧게는 몇 시간, 길게는 며칠 동안 붙잡고 고민하다가 요청하기가 일쑤였기 때문이었다. 교육 이후로는 DBA들이 SQL을 보는 관점부터 달라졌으며 필자가 없어도 QT 문제를 스스로 해결할 수 있는 능력을 갖게 되었다. 기대 반 우려 반의 심정으로 교육을 허락한 책임자의 얼굴에도 화색이 돌았다. 지난 수 년간 진행되었던 Logical Optimizer의 원리에 대한 연구가 한 순간에 빛을 발하고 있었다
.

그 사이트의 문제가 해결되고 얼마 후 지난 2년간 다른 프로젝트에서 요청 받았던 튜닝 문제를 같은 방법으로 분석 하였는데 원인 중 절반이 QT 문제였다. 이 같은 경험은 우리에게 시사하는 바가 크다. 어떤 문제로 베테랑 DBA들이 밤을 세우는지, 어떤 기술로 문제를 해결 할 수 있는지 혹은 어떤 기술이 고급 튜너로 가기 위한 것인지 알 수 있다. 혹시 당신이 속한 프로젝트에 DBA, 튜너 혹은 고급 개발자들이 퇴근을 못하고 밤새 일하고 있다면
고심해 보라. Logical Optimizer의 원리가 상황을 반전 시킬 수 있는지를.
의심해 보라. 그 원리가 토르쿠 막토가 아닌지를......

<본문 내용 중에서>

 
이 책의 가장 큰 특징은 목차만 보고 어떤 기능을 하는 것인지 떠올릴 수 있다는 것이다. 물론 책을 한번 읽은 상태에서 가능하다. 복습할 때 가장 유용한 것이 목차만 보고 요약이 되는 것인데 Part 2와 Part 3가 이런 접근법을 따르고 있다.   

아래에 책의 미리보기(Preview)파일을 올린다. 에피소드, 서문, 감사의 글, 책의 구성과 책을 읽는 방법, 목차, 종문, 참조문서, 색인 등을 볼 수 있다.
   

invalid-file

The Logical Optimizer 미리보기


PS
글을 준비하고 작성하는데 5년이나 걸렸고 글을 실물의 책으로 만드는 과정에서 3개월이 소모되었다. 맡은 프로젝트 + 전공이외의 Study + 블로그 관리+ 옵티마이져의 연구 및 집필을 동시에 진행하는 것은 고통의 연속이었다. 이제 좀 쉬어야 겠다. 몇년뒤에 다음 책이 나올 수 있을지.....
지금의 심정으로는 자신이 없다.



위에서 언급한 필자의 에피소드가 한국 오라클의 2010년 매거진 여름호에 실려있다. 아래의 PDF 파일을 참고하기 바란다.
(2010년 7월 추가)
사용자 삽입 이미지

오라클 매거진 2010년 여름호



THE LOGICAL OPTIMIZER (양장)
국내도서>컴퓨터/인터넷
저자 : 오동규
출판 : 오픈메이드 2010.04.05
상세보기



'The Logical Optimizer' 카테고리의 다른 글

The Logical Optimizer Part 1 - PPT  (17) 2010.07.26
The Logical Optimizer-서점  (0) 2010.04.27
The Logical Optimizer-Script Download  (37) 2010.04.20
The Logical Optimizer-오타와 오류등록  (27) 2010.04.20
저자와의 대화  (36) 2010.04.20
Posted by extremedb
,