지난시간의 DEUI라는 기능에 이어서 이번시간에는 그 사촌격인 DE에 대해서 논의해보자.

DE (Distinct Elimination)란 무엇인가
DE는 Unique한 집합일 경우 불필요한 Distinct를 제거하는 기능이다. 이렇게 함으로써 Sort와 중복제거 등의 부하가 많은 작업을 수행하지 않을 수 있다. 이 기능은 Oracle 11g에서 추가되었다. 이제 DE가 어떻게 수행되는지 알아보자.

SELECT distinct d.department_id, l.location_id

  FROM department d, location l

 WHERE d.location_id = l.location_id ;

 

----------------------------------------+-----------------------------------+

| Id  | Operation           | Name      | Rows  | Bytes | Cost  | Time      |

----------------------------------------+-----------------------------------+

| 0   | SELECT STATEMENT    |           |       |       |     3 |           |

| 1   |  NESTED LOOPS       |           |    27 |   270 |     3 |  00:00:01 |

| 2   |   TABLE ACCESS FULL | DEPARTMENT|    27 |   189 |     3 |  00:00:01 |

| 3   |   INDEX UNIQUE SCAN | LOC_ID_PK |     1 |     3 |     0 |           |

----------------------------------------+-----------------------------------+

Predicate Information:

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

3 - access("D"."LOCATION_ID"="L"."LOCATION_ID")

Unique를 보장하는 컬럼이 있으면 Distinct가 필요없다
실행계획에서 Distinct에 해당하는 Operation인 Sort Unique 혹은 Hash Unique가 사라졌다. 이유는 Transformer가 위의 SQL을 분석해서 Distinct가 없어도 Unique 함을 알았기 때문이다. Select 절에 있는 d.department_id와 l.location_id는 From 절에 있는 두 테이블의 PK 이다.  따라서 Distinct는 당연히 필요 없음으로 Logical Optimizer가 삭제한 것이다.

아래는 DE와 관련된 10053 Event의 Trace 내용이다.
 

OBYE:   Considering Order-by Elimination from view SEL$1 (#0)
***************************

Order-by elimination (OBYE)

***************************

OBYE:     OBYE bypassed: no order by to eliminate.

Eliminated SELECT DISTINCT from query block SEL$1 (#0)

이후생략


10053 Trace 상에서는 DE가 OBYE 자리에서 발생하였다. 이것은 OBYE 수행여부를 체크할 때 DE의 수행여부를 같이 체크하므로 DE가 OBYE 자리에서 발생되는 것 같다.

함정에 주의할 것
Unique 하다고 해서 항상 DE가 발생될까? 그렇지 않다. 아래의 SQL을 보자.

SELECT distinct d.department_id, d.location_id

  FROM department d, location l

 WHERE d.location_id = l.location_id ;


위의 SQL은 location_id를 department 테이블의 컬럼으로 대체하였다는 점을 제외하면 최초의 SQL과 완전히 같다.
-----------------------------------------+-----------------------------------+

| Id  | Operation            | Name      | Rows  | Bytes | Cost  | Time      |

-----------------------------------------+-----------------------------------+

| 0   | SELECT STATEMENT     |           |       |       |     4 |           |

| 1   |  HASH UNIQUE         |           |    27 |   270 |     4 |  00:00:01 |

| 2   |   NESTED LOOPS       |           |    27 |   270 |     3 |  00:00:01 |

| 3   |    TABLE ACCESS FULL | DEPARTMENT|    27 |   189 |     3 |  00:00:01 |

| 4   |    INDEX UNIQUE SCAN | LOC_ID_PK |     1 |     3 |     0 |           |

-----------------------------------------+-----------------------------------+

Predicate Information:

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

4 - access("D"."LOCATION_ID"="L"."LOCATION_ID")

 

Unique를 보장하는 모든 테이블의 컬럼이 Select 절에 사용되어야
컬럼 하나만이 바뀌었을 뿐인데 실행계획에 Hash Unique가 생긴 것이다. 여기서 알 수 있는 것은 From 절에 나열된 모든 테이블의 PK 컬럼 혹은 Unique 컬럼이 Select 절에 나와야 Distinct가 제거된다는 사실이다. 이것은 아직 DE 기능이 완성되지 않은 것을 의미한다. 논리적으로는 Select 절에 d.location_id를 사용하거나 l.location_id를 사용해도 같기 때문에 DE가 발생 해야 하지만 아직 이런 기능이 없다는 것이 아쉽다
.

DE 기능을 Control 하는 파라미터는 _optimizer_distinct_elimination이며 Default로 True 이다. 하지만 이 파라미터로는 DEUI 기능을 Control 할수 없다. 한가지 주의사항은 DE가 버전 10gR2(10.2.0.4)에서도 수행된다는 점이다. 다만 _optimizer_distinct_elimination 파라미터가 없다는 것이 11g와 다른 점이다.

결론
만약 이런 일이 대용량 테이블에서 발생한다면 결과는 심각한 성능저하로 나타날 수 있으므로 조인된 컬럼을 사용할 것인지 아니면 참조되는 컬럼을 사용할 것인지 아주 신중히 결정해야 한다.

성능저하가 예상되는 부분
예를들면 서브쿼리가 Unnesting되어 Distinct가 자동으로 추가된 인라인 뷰에 CVM이 발생하면 인라인뷰가 해체되므로 전체집합에 대해서 Sort Unique 혹은 Hash Unique가 발생된다. 전체집합이 대용량이라면 성능은 심각하게 저하될 것이다. 이 사실은 어떤 컬럼을 Select 절에서 사용할 것인지 아주 신중히 결정해야 하며 Merge 힌트를 얼마나 조심스럽게 사용해야 하는지를 잘 나타내 주고 있다.

PS
이렇게 나가다간 아마 책을 출판할 필요가 없을듯 하다.^^ 하지만 책보다는 블로그가 우선이다.

신고
Posted by extremedb

댓글을 달아 주세요

  1. feelie 2010.01.25 09:38 신고  댓글주소  수정/삭제  댓글쓰기

    select list에 기술되는 컬럼명 사용에 이런 분명한 차이도 있었군요.
    사소한 쿼리라도 실행계획을 꼭확인하는 습관을 들이라는 말씀이 이런경우일까요?
    내용잘봤습니다..

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.01.25 10:53 신고  댓글주소  수정/삭제

      핵심을 잘 파악하셨습니다.
      실행계획을 잘 살펴보면 비효율 적인 부분이 보이지요. 이때 Query Transformation이 하는 일을 알고 본다면 문제의 해결이 가능 합니다.

  2. Eddy 2010.01.26 19:19 신고  댓글주소  수정/삭제  댓글쓰기

    "책보다는 블로그가 우선이다."라는 말씀에 감사드립니다.
    책내용의 90%가 블로그에 있어도 아마도 독자의 대부분은 책을 구입할 것입니다.

    좋은 책을 세상에 내어 놓았지만 블로그가 없는 경우가 있어 섭섭함이 있었는데,
    extremedb님의 결정은 참 좋아보입니다. 참 감사한 결정이라고 생각합니다. ㅎㅎ

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.01.27 00:17 신고  댓글주소  수정/삭제

      방형욱님 반갑습니다.
      저와 블로그 구독자에게는 좋은 결정이지만 회사에서 뭐라고 할지 걱정이 되네요...
      감사합니다.

  3. 서상서 2010.02.10 16:15 신고  댓글주소  수정/삭제  댓글쓰기

    버전에 따라 약간의 차이가 있는것 같은 생각이 듭니다. 버전 11.1.0.7.0입니다.
    제가 테스트한 버전에서는 이렇게 풀리면서 더욱 효율적으로 옵티마이저가 생각을 하는것 같은데요.
    옵티마이저가 부서테이블에 DEPT_LOCATION_IX 아마도 이것이 결합인덱스인것 같읍니다.
    인덱스만 가지고 끝내는것 같군요.(location_id가 not null제약조건이 있는것으로 판단)

    location테이블은 엑세스를 하지 않네요.비용도 제시한 것보다 위의것보다 작은것 같구요.
    한번 돌려보았읍니다. 제 로컬PC에서 말이죠.
    SQL> EXPLAIN PLAN FOR
    2 SELECT distinct d.department_id,l.location_id
    3 FROM departments d,locations l
    4 WHERE d.location_id = l.location_id;

    해석되었습니다.

    SQL> SELECT * FROM table(dbms_xplan.display(null,null,'all'));

    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    Plan hash value: 3680985111

    ----------------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
    -----------------------------------------------------------------------------------------------
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 27 | 270 | 2 (0)| 00:00:01 |
    | 1 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 27 | 270 | 2 (0)| 00:00:01 |
    |* 2 | INDEX FULL SCAN | DEPT_LOCATION_IX | 27 | | 1 (0)| 00:00:01 |

    -----------------------------------------------------------------------------------------------
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    Query Block Name / Object Alias (identified by operation id):
    -------------------------------------------------------------
    1 - SEL$120E9FF1 / D@SEL$1
    2 - SEL$120E9FF1 / D@SEL$1

    Predicate Information (identified by operation id):
    ---------------------------------------------------
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    2 - filter("D"."LOCATION_ID" IS NOT NULL)

    Column Projection Information (identified by operation id):
    -----------------------------------------------------------
    1 - "D"."DEPARTMENT_ID"[NUMBER,22], "D"."LOCATION_ID"[NUMBER,22]
    2 - "D".ROWID[ROWID,10], "D"."LOCATION_ID"[NUMBER,22]

    26 개의 행이 선택되었습니다.

    결국은 실제로 돌려보지 않고 이런것이 있구나 이렇게 넘어가면 안되겠구나 하는 그런 생각을 갖게 됩니다.
    고맙읍니다.

    버전은 혹시 어떻게 되시나요? 11R1버전에서는 이렇게 실행되었읍니다.
    그리고 많이 감사합니다. 좋은글이요.

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.02.10 17:09 신고  댓글주소  수정/삭제

      반갑습니다.
      통계정보의 상태에 따라 인덱스만 탈 수도 있고 FULL TABLE SCAN을 할 수도 있지만 Distinct Elimination이 테이블을 제거할 수는 없습니다. 테이블을 제거하는 기능은 JE(Join Elimination) 이라고 하며 7가지로 분류할 수 있습니다. 자세한 내용은 3월달에 나올 책을 참조 하시기 바랍니다.

      서상서님이 보여주신 기능은 Primary Key-Foreign Key 관계를 이용한 JE 입니다. FK를 삭제하시면 location을 access 할것 입니다.
      감사합니다.

INDEX UNIQUE SCAN비밀
당신은 INDEX UNIQUE SCAN쉬운 Operation쯤으로 여길 것이다. 하지만 여기에는 숨겨진 기능이 있다. 대표적인 경우가 Unique 인덱스를 사용하여 INDEX UNIQUE SCAN Operation나오면 Distinct제거하는 기능이다. 기능의 이름이 없으므로 DEUI*(Distinct Elimination using Unique Index)부르기로 하자. 이제 아래의 SQL보자.

SELECT DISTINCT d.department_id, l.city, l.country_id

  FROM department d, location l

 WHERE d.location_id = l.location_id

   AND d.department_id = 10 ;

 

--------------------------------------------------+-----------------------------------+

| Id  | Operation                     | Name      | Rows  | Bytes | Cost  | Time      |

--------------------------------------------------+-----------------------------------+

| 0   | SELECT STATEMENT              |           |       |       |     3 |           |

| 1   |  NESTED LOOPS                 |           |     1 |    22 |     2 |  00:00:01 |

| 2   |   TABLE ACCESS BY INDEX ROWID | DEPARTMENT|     1 |     7 |     1 |  00:00:01 |

| 3   |    INDEX UNIQUE SCAN          | DEPT_ID_PK|     1 |       |     0 |           |

| 4   |   TABLE ACCESS BY INDEX ROWID | LOCATION  |    23 |   345 |     1 |  00:00:01 |

| 5   |    INDEX UNIQUE SCAN          | LOC_ID_PK |     1 |       |     0 |           |

--------------------------------------------------+-----------------------------------+

 

Predicate Information:

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

3 - access("D"."DEPARTMENT_ID"=10)

5 - access("D"."LOCATION_ID"="L"."LOCATION_ID")

 

SQL 변경되었다
Distinct의한 Sort Unique 혹은 Hash Unique사라졌다. 이유는 테이블 departmentlocation에서 모두 INDEX UNIQUE SCAN사용하였기 때문이다. 하지만 반대로 Unique 인덱스를 사용하지 않는다면 DEUI절대 수행되지 않는다. 아래의 SQL위의 SQL에서 힌트만 추가한 것이다.
 

SELECT /*+ FULL(d) */ DISTINCT d.department_id, l.city, l.country_id

  FROM department d, location l

 WHERE d.location_id = l.location_id

   AND d.department_id = 10 ;

 

---------------------------------------------------+-----------------------------------+

| Id  | Operation                      | Name      | Rows  | Bytes | Cost  | Time      |

---------------------------------------------------+-----------------------------------+

| 0   | SELECT STATEMENT               |           |       |       |     5 |           |

| 1   |  HASH UNIQUE                   |           |     1 |    22 |     5 |  00:00:01 |

| 2   |   NESTED LOOPS                 |           |       |       |       |           |

| 3   |    NESTED LOOPS                |           |     1 |    22 |     4 |  00:00:01 |

| 4   |     TABLE ACCESS FULL          | DEPARTMENT|     1 |     7 |     3 |  00:00:01 |

| 5   |     INDEX UNIQUE SCAN          | LOC_ID_PK |     1 |       |     0 |           |

| 6   |    TABLE ACCESS BY INDEX ROWID | LOCATION  |    23 |   345 |     1 |  00:00:01 |

---------------------------------------------------+-----------------------------------+

 

Predicate Information:

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

4 - filter("D"."DEPARTMENT_ID"=10)

5 - access("D"."LOCATION_ID"="L"."LOCATION_ID")

 

 

순간의 실수로
Department 테이블을 Full Scan 하자 Plan 상에 HASH UNIQUE나타났다. 다시 말해서 옵티마이져의 실수로 다른 종류의 인덱스를 사용하거나 Full Scan된다면 Query Transformation발생되지 않는다.
 

결론

우리가 무심코 사용하는 Unique Index INDEX UNIQUE SCAN Operation DEUI라는 기능을 내장하고 있다. 당연한 기능이라고 여기겠지만 이것을 모르면 많은 것을 놓친다. 서브쿼리가 Unnesting되어 Driving 집합이 되면 메인쿼리의 집합을 보존하기 위하여 Default Distinct가 추가된다. 이제 INDEX UNIQUE SCAN을 사용하는 서브쿼리가 Unnesting되어 Driving 집합이 될 때 Distinct가 사라진 비밀을 이야기 할 수 있겠는가? Unique 한 값을 가지는 컬럼이나 컬럼조합을 Normal 인덱스가 아닌 Unique 인덱스로 만들어야 할 이유를 알겠는가? 단순한 Operation 하나라도 무시할 수 없는 이유가 된다.

PS
다음시간에는 DEUI의 사촌격인 DE에 대하여 논의하자.
이번 내용도 집필중인 책의 일부분이다. 이제 어떤 것을 주제로 책인지 감이 오지 않는가?

신고
Posted by extremedb

댓글을 달아 주세요

  1. Eddy 2010.01.20 18:06 신고  댓글주소  수정/삭제  댓글쓰기

    pk, uk 컬럼에 unique index를 미리 만들면 제약과 관련된 다음 기능을 사용할 수 없게 됩니다.

    - enable novalidate
    - deferrable 제약

    그래서 normal index를 생성하게 하는데, 그 경우 DEUI 기능을 놓칠 수 있겠군요.

  2. feelie 2010.01.20 18:37 신고  댓글주소  수정/삭제  댓글쓰기

    알고 있는내용이라고 생각했는데 서버쿼리와 연결해서 생각하니 참...

    아직 기본도 부족하고, 운용도 부족한가 보네요..
    내용 잘봤습니다..

  3. 서상서 2010.02.10 17:41 신고  댓글주소  수정/삭제  댓글쓰기

    11 R1에서는 Sort Unique같이 실행계획이 나타나지는 않지만 실행계획이 언급하신것과 같이는 풀리지 않는것 같읍니다.혹시 이전에 언급하신 히든 파리미터때문인가요?

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.02.10 17:59 신고  댓글주소  수정/삭제

      DE를 이야시하시는 건지 DEUI를 이야기 하시는 건지 알수가 없네요.
      DE든 DEUI든 Distinct(Sort Unique 혹은 Hash Unique)제거하는 기능입니다. 그외에 다른 테이블에 엑세스 하지 않는 기능은 JE(Join Elimination)라는 기능이 따로 존재합니다. 또한 인덱스를 타고 안타고는 JE 나 DE, DEUI 와는 상관없이 여러가지 통계정보나 인덱스의 여부에 따라 Cost Estimator의 Return 값에 따라 판단되는것 입니다.

지인으로부터 아래와 같은 질문을 받았다.

 

질문 : Predicate Information을 참조하려면 DBMS_XPLAN 패키지를 사용해야만 합니까?

       저희 개발자들은 DBMS_XPLAN 패키지를 사용할 권한이 없습니다.
       따라서
오렌지나 TOAD에서 간단히 볼 수 있는 방법이 필요합니다. 가능 합니까?

 

답변 : 볼 수 있습니다. 단 PLAN_TABLE을 볼수 있는 권한은 있어야 합니다.


요청: 그건 있습니다. 방법을 블로그에 올려주시면 나머지 사람들도 볼수 있겠네요. 올려주시죠.

이렇게 해서 이글을 작성 하게 되었다. 이런 질문을 받았다는 것은 2가지 의미로 해석할 수 있다. 첫번째, 의외로 오렌지나 TOAD의 기능을 모르는 사람이 많이 있을 수 있다는 의미다. 두번째, 튜닝에 필요한 권한이 개발자에게 없다는 안타까움 이다. 이 정책은 매우 아쉬운 선택이며 앞으로 개선되기를 기대해본다. 하지만 수정이 필요한 법이나 악법도 법이므로 수정되기 전까진 따라야 한다.

Predicate Information과 관련된 가장 흔한 오류는 10046 이벤트 + tkprof를 사용하면 Predicate Information을 볼수 있다고 착각 하는 것이다. 절대 볼수 없다. 

Predicate Information이 뭐지?
Predicate Information
이란 인덱스 scan 시의 컬럼 액세스 정보, 조인정보, filter 정보를 각 Opreation 단위로 나타낸 것이다. 아래의 예제를 보자.
 

explain plan for

SELECT /*+ LEADING(e) USE_NL(d) */

       e.employee_id, e.first_name, e.last_name, e.email, e.salary

  FROM employee e, department d

 WHERE e.department_id = d.department_id

   AND e.job_id = 'SH_CLERK';

  

select * from table(dbms_xplan.display);  


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

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

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

|   0 | SELECT STATEMENT            |            |    20 |   860 |     3   (0)|

|*  1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEE   |    20 |   860 |     3   (0)|

|*  2 |   INDEX RANGE SCAN          | EMP_JOB_IX |    20 |       |     1   (0)|

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

 

Predicate Information (identified by operation id):

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

   1 - filter("E"."DEPARTMENT_ID" IS NOT NULL) --> FILER 정보가 출력됨

   2 - access("E"."JOB_ID"='SH_CLERK')         --> INDEX SCAN 정보 혹은 JOIN 정보가 출력됨

 

FILTER ACCESS 정보는 중요하다

위에서 출력된 Predicate Information을 보면 FILTER INDEX SCAN 정보를 정확히 볼 수 있다. 특히 인덱스가 여러 개의 컬럼으로 구성된 경우 몇 번째 컬럼까지 액세스 되었는지 보려면 Predicate Information이 필수적인 것이다. 예를 들어 인덱스가 COL1 + COL2 + COL3로 되어 있는데 Predicate Information에서 INDEXSCAN에 사용된 컬럼이 하나뿐이고 COL2 COL3는 테이블의 FILTER로 풀린다면 성능에 문제가 될 수 있다. 따라서 Predicate Information을 확인 하는 것은 매우 중요한 것이다.

 

문제는 이처럼 중요한 정보를 DBMS_XPLAN 패키지를 사용하지 않고 'TOAD나 오렌지에서 어떻게 볼수 있냐' 이다.

지금부터 따라 해보기 바란다.

 

1. TOAD에서 Predicate Information 보기

먼저 토드 화면에서 EXPLAIN PLAN을 실행한다. EXPLAIN PLAN은 구급차 아이콘을 누르면 된다.

사용자 삽입 이미지

그러면 위와 같은 화면이 출력될 것이다. 위의 화면에서는 Predicate Information가 없다. 지금부터 Predicate Information을 추가해보자.

사용자 삽입 이미지

먼저 TOAD 화면의 하단(Explain Plan) 탭에서 오른쪽 버튼을 클릭한다. 연이어 Adjust Content를 선택한다.


사용자 삽입 이미지

그러면 위와 같은 화면이 뜨는데 여기서 Access Predicates Filter Predicates Visible 항목을 체크하고 OK를 클릭한다.

사용자 삽입 이미지

위와 같이 TOAD에서 Access Predicates Filter Predicates가 깔끔하게 출력되었다.


2. 오렌지에서
Predicate Information 보기

오렌지를 사용한다면 PLAN TOOL에서 Show Plan버튼을 클릭하거나 Function F5를 누르고 하단의 세번째 탭을 클릭하면 아래의 그림처럼 Predicate Information을 볼 수 있다.

사용자 삽입 이미지

확인하는 습관이 필요해
이로써 어디서든 무엇을 사용하든 버튼 클릭 만으로 Predicate Information을 볼 수 있게 되었다. 이제부터 Predicate Information을 애용하기 바란다. 특히 Index Scan 시에 몇번째 컬럼까지 이용하였는지 확인하는 습관이 필요하다.

PS :
필자는 TOAD나 오렌지의 제조사나 판매사와는 상관없는 사람이다. 단지 가끔 이용할 뿐...



신고
Posted by extremedb

댓글을 달아 주세요

  1. 익명 2010.01.12 18:14 신고  댓글주소  수정/삭제  댓글쓰기

    실제 오렌지나 토드에 나오는게 run time predicate information 인가요?

  2. feelie 2010.01.13 09:21 신고  댓글주소  수정/삭제  댓글쓰기

    TOAD을 꽤 오랜기간 사용했는데도 아직 TOAD의 기능 대부분을 사용안하는것 같네요.
    TOAD만 제대로 사용해도 왠만한 성능 문제를 해결할수 있다고 들었는데 아직
    메뉴얼한번 제대로 읽어본적이 없네요..
    제 생각은 오라클의 내부적인 부분을 이해하고 그다음에 TOAD의 기능을 사용할려고 하는데요.
    오라클 내부적인 부분을 이해하는데 무척 오랜 시간이 필요한것 같네요..( 아직 많이 모자라서...)
    조금씩 나질것으롤 생각하고, 열심히 할렵니다..

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.01.13 10:11 신고  댓글주소  수정/삭제

      좋은 접근방법입니다.
      처음부터 토드등을 사용한다면 나중에 SQL PLUS를 사용할수도 없게 될것입니다.내부원리를 알고 토드의 기능을 사용하는 것도 올바른 접근법 입니다.

  3. 백면서생 2010.01.15 09:58 신고  댓글주소  수정/삭제  댓글쓰기

    안녕하세요. extremedb님 .
    늘 좋은 자료와 tip을 올려주시네요.
    늦었지만 새해 복 많이 받으시고 올해도 뜻하시는 일이 잘 이루어지길 바랍니다.
    물론 가족과 님의 행복을 포함해서요~

    p.s) 책 너무 오래 출판 안하시면 안사버릴겁니다.ㅋ

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.01.15 10:31 신고  댓글주소  수정/삭제

      오랜만입니다.
      잘 지내시죠?
      백면서생님께서도 새해 소망하는 일 모두 이루시길 기원합니다.
      P.S)말씀하신대로 기한을 넘기지 않도록 하겠습니다.
      한줄의 압력으로 책임이 무거워 지네요.^^

  4. Favicon of http://1ststreet.tistory.com BlogIcon SITD 2011.05.12 21:20 신고  댓글주소  수정/삭제  댓글쓰기

    오렌지에서 해당 쿼리를 블럭으로 선택하고 Ctrl + Shift + E를 누르면 한번에 플랜으로 가더군요.

    은근히 편해요.

    그리고 플랜에서 Ctrl + Enter을 치면 쿼리가 실행되고, F5를 누르면 실행계획이 나오더군요

Query Transformation 모르면 튜닝을 없다

위의
말을 보고 많은 독자들이 말도 된다고 생각할 것이다. Logical Optimizer 결과물인 Query Transformation 알지 못했지만 지금껏 튜닝을 성공적으로 했다고 생각하는 사람이 많이 있기 때문이다. 하지만 과연 그럴까? 아래의 SQL 보고 Query Transformation Logical Optimizer 얼마나 중요한지 알아보자
.

여기서 한단계 더 나아가서 뷰(인라인뷰가 아니다) 내부의 테이블에 대하여 조인순서 및 Access Path 를 바꿀 수 있는 방법에 대해 논의 해보자.

준비

테스트를 위하여 인덱스 3개와 하나를 만들자.

CREATE INDEX loc_postal_idx ON location (postal_code);

CREATE INDEX dept_name_idx ON department (department_name);

CREATE INDEX coun_region_idx ON country (region_id);

 

CREATE OR REPLACE VIEW v_dept AS

SELECT d.department_id, d.department_name, d.manager_id, l.location_id,

       l.postal_code, l.city, c.country_id, c.country_name, c.region_id

  FROM department d, location l, country c

 WHERE d.location_id = l.location_id

   AND l.country_id = c.country_id;


실행시켜보자

이제 모든 준비가 끝났다. 아래는 매우 짧고 쉬운 SQL 이다. SQL 실행시키고 연이어DBMS_XPLAN.display_cursor 실행한 후의 결과 중에서 필요한 부분만 발췌 하였다.

 SELECT /*+ gather_plan_statistics  */

       e.employee_id, e.first_name, e.last_name, e.job_id, v.department_name

  FROM employee e, v_dept v

 WHERE e.department_id = v.department_id

   AND v.department_name = 'Shipping'

   AND v.postal_code = '99236'

   AND v.region_id = 2

   AND e.job_id = 'ST_CLERK';

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

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

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

|   1 |  NESTED LOOPS                   |                   |     20 |00:00:00.01 |      11 |

|   2 |   NESTED LOOPS                  |                   |     45 |00:00:00.01 |       8 |

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

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

|   5 |      TABLE ACCESS BY INDEX ROWID| DEPARTMENT        |      1 |00:00:00.01 |       3 |

|*  6 |       INDEX RANGE SCAN          | DEPT_NAME_IDX     |      1 |00:00:00.01 |       2 |

|*  7 |      TABLE ACCESS BY INDEX ROWID| LOCATION          |      1 |00:00:00.01 |       2 |

|*  8 |       INDEX UNIQUE SCAN         | LOC_ID_PK         |      1 |00:00:00.01 |       1 |

|*  9 |     INDEX UNIQUE SCAN           | COUNTRY_C_ID_PK   |      1 |00:00:00.01 |       1 |

|* 10 |    INDEX RANGE SCAN             | EMP_DEPARTMENT_IX |     45 |00:00:00.01 |       2 |

|* 11 |   TABLE ACCESS BY INDEX ROWID   | EMPLOYEE          |     20 |00:00:00.01 |       3 |

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

 

Predicate Information (identified by operation id):

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

   6 - access("D"."DEPARTMENT_NAME"='Shipping')

   7 - filter("L"."POSTAL_CODE"='99236')

   8 - access("D"."LOCATION_ID"="L"."LOCATION_ID")

   9 - access("L"."COUNTRY_ID"="C"."COUNTRY_ID")

       filter("C"."REGION_ID"=2)

  10 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

  11 - filter("E"."JOB_ID"='ST_CLERK')

 

조인순서를 바꿀 있겠는가

위의 실행계획에서 조인순서는 V_dept --> Employee 이다. 만약 상태에서 여러분이 조인의 순서를 Employee --> V_dept 바꿀 있겠는가? 아마 아래처럼 힌트를 사용할 것이다.

 

SELECT /*+ gather_plan_statistics LEADING(E V) */

       e.employee_id, e.first_name, e.last_name, e.job_id, v.department_name

..이후생략

 

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

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

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

|   1 |  NESTED LOOPS                   |                   |     45 |00:00:00.01 |      11 |

|   2 |   NESTED LOOPS                  |                   |     45 |00:00:00.01 |       8 |

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

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

|   5 |      TABLE ACCESS BY INDEX ROWID| DEPARTMENT        |      1 |00:00:00.01 |       3 |

|*  6 |       INDEX RANGE SCAN          | DEPT_NAME_IDX     |      1 |00:00:00.01 |       2 |

|*  7 |      TABLE ACCESS BY INDEX ROWID| LOCATION          |      1 |00:00:00.01 |       2 |

|*  8 |       INDEX UNIQUE SCAN         | LOC_ID_PK         |      1 |00:00:00.01 |       1 |

|*  9 |     INDEX UNIQUE SCAN           | COUNTRY_C_ID_PK   |      1 |00:00:00.01 |       1 |

|* 10 |    INDEX RANGE SCAN             | EMP_DEPARTMENT_IX |     45 |00:00:00.01 |       2 |

|  11 |   TABLE ACCESS BY INDEX ROWID   | EMPLOYEE          |     45 |00:00:00.01 |       3 |

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

 

힌트가 무시 되었다 원인은?

조인의 순서가 전혀 변하지 않았다. 이상하지 않은가? 간단하게 생각되는 SQL 조인순서도 변경할 없다. 이유는 Query Transformation 때문이다. Outline 정보를 보면 실마리를 찾을 있다.

Outline Data

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

 

  /*+

      BEGIN_OUTLINE_DATA

      중간생략

      MERGE(@"SEL$2")

      중간생략

      END_OUTLINE_DATA

  */

 

원인은 Logical Optimizer 의한 Query Transformation 이다

V_dept View Merging 발생한 것이다. View Merging Query Transformation 종류이며 뷰를 해체하여 정상적인 조인으로 바꾸는 작업이다. Query Transformation 발생하면 많은 경우에 쿼리블럭명이 바뀌어 버린다. 따라서 바뀐 쿼리블럭명을 지정하여 힌트를 사용하거나 Query Transformation 발생하지 않게 하면 힌트가 제대로 적용된다. 

SELECT /*+ gather_plan_statistics NO_MERGE(V) LEADING(E V) */

       e.employee_id, e.first_name, e.last_name, e.job_id, v.department_name

  FROM employee e, v_dept v

 WHERE e.department_id = v.department_id

   AND v.department_name = 'Shipping'

   AND v.postal_code = '99236'

   AND v.region_id = 2

   AND e.job_id = 'ST_CLERK';

 

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

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

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

|*  1 |  HASH JOIN                      |                 |     20 |00:00:00.02 |       8 |

|   2 |   TABLE ACCESS BY INDEX ROWID   | EMPLOYEE        |     20 |00:00:00.02 |       2 |

|*  3 |    INDEX RANGE SCAN             | EMP_JOB_IX      |     20 |00:00:00.02 |       1 |

|   4 |   VIEW                          | V_DEPT          |      1 |00:00:00.01 |       6 |

|   5 |    NESTED LOOPS                 |                 |      1 |00:00:00.01 |       6 |

|   6 |     NESTED LOOPS                |                 |      1 |00:00:00.01 |       5 |

|   7 |      TABLE ACCESS BY INDEX ROWID| DEPARTMENT      |      1 |00:00:00.01 |       3 |

|*  8 |       INDEX RANGE SCAN          | DEPT_NAME_IDX   |      1 |00:00:00.01 |       2 |

|*  9 |      TABLE ACCESS BY INDEX ROWID| LOCATION        |      1 |00:00:00.01 |       2 |

|* 10 |       INDEX UNIQUE SCAN         | LOC_ID_PK       |      1 |00:00:00.01 |       1 |

|* 11 |     INDEX UNIQUE SCAN           | COUNTRY_C_ID_PK |      1 |00:00:00.01 |       1 |

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

 

Query Transformation 발생하지 않으면 조인순서 변경이 가능해

No_merge 힌트를 사용하여 Query Transformation 발생하지 않게 하였더니 조인 순서가 Employee --> V_dept 바뀌었다. 이제 알겠는가? Query Transformation Logical Optimizer 모른다면 힌트도 먹통이 된다. 이래서는 제대로 튜닝을 없다.

내부의 테이블에 대한 조인순서의 변경은 가능한가
단계 나아가 보자. No_merge 힌트를 사용한 상태에서 내부의 테이블들에 대해서 조인순서를 바꾸고 싶다. 조인순서를 Employee --> (Country --> Location --> Department) 바꾸어야 한다. 이때 여러분은 어떻게 것인가? 아래처럼 Global Hint 사용하면 된다.

SELECT /*+ gather_plan_statistics NO_MERGE(V) LEADING(E V) LEADING(V.C V.L V.D) */

       e.employee_id, e.first_name, e.last_name, e.job_id, v.department_name

  FROM employee e, v_dept v

 WHERE e.department_id = v.department_id

   AND v.department_name = 'Shipping'

   AND v.postal_code = '99236'

   AND v.region_id = 2

   AND e.job_id = 'ST_CLERK';

 

Leading 힌트를 사용하였다. 이유는 위의 SQL 쿼리블럭 2개로 구성되어 있기 때문이다. 전체 SQL 대한 Leading 힌트가 필요하며 V_dept 대한 Leading 힌트가 각각 필요하다. V_dept 대한 Leading 힌트를 사용할 Dot 표기법을 사용해야 한다. 내부에 존재하는 테이블들의 Alias 사용해야 한다. 방법은 Global Hint 사용 방법 중에 Dot 표기법을 사용한 것이다.

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

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

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

|*  1 |  HASH JOIN                       |                 |     20 |00:00:00.01 |       9 |

|   2 |   TABLE ACCESS BY INDEX ROWID    | EMPLOYEE        |     20 |00:00:00.01 |       2 |

|*  3 |    INDEX RANGE SCAN              | EMP_JOB_IX      |     20 |00:00:00.01 |       1 |

|   4 |   VIEW                           | V_DEPT          |      1 |00:00:00.01 |       7 |

|   5 |    NESTED LOOPS                  |                 |      1 |00:00:00.01 |       7 |

|   6 |     NESTED LOOPS                 |                 |      1 |00:00:00.01 |       6 |

|*  7 |      HASH JOIN                   |                 |      1 |00:00:00.01 |       4 |

|*  8 |       INDEX RANGE SCAN           | COUN_REGION_IDX |      5 |00:00:00.01 |       1 |

|   9 |       TABLE ACCESS BY INDEX ROWID| LOCATION        |      1 |00:00:00.01 |       3 |

|* 10 |        INDEX RANGE SCAN          | LOC_POSTAL_IDX  |      1 |00:00:00.01 |       2 |

|* 11 |      INDEX RANGE SCAN            | DEPT_NAME_IDX   |      1 |00:00:00.01 |       2 |

|* 12 |     TABLE ACCESS BY INDEX ROWID  | DEPARTMENT      |      1 |00:00:00.01 |       1 |

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

 
Global Hint 매우 유용해

성공적으로 조인순서가 Employee --> (Country --> Location --> Department) 바뀌었다. Global Hint 사용하자 힌트가 제대로 적용된다. 방법은 특히 뷰나 인라인뷰를 Control 유용하므로 반드시 익혀두기 바란다.

Query Transformation 발생했을 경우는 힌트를 어떻게 적용할 있나

이제 원래 목적인 Query Transformation 발생했을 경우에 조인순서를 바꾸는 방법에 대해 논의 해보자. 위에서 배운 Global Hint 여기에 적용할 것이다.

 SELECT /*+ gather_plan_statistics LEADING(E V.C V.L V.D) */

       e.employee_id, e.first_name, e.last_name, e.job_id, v.department_name

  FROM employee e, v_dept v

 WHERE e.department_id = v.department_id

   AND v.department_name = 'Shipping'

   AND v.postal_code = '99236'

   AND v.region_id = 2

   AND e.job_id = 'ST_CLERK';

 

위의 SQL No_merge 힌트가 사라졌으므로 Query Transformation 발생된다. 그래서 위에서 배운 대로 Dot 표기법을 활용하여 Leading 힌트를 사용 하였다. 힌트가 적용될까?

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

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

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

|   1 |  NESTED LOOPS                   |                   |     20 |00:00:00.01 |      11 |

|   2 |   NESTED LOOPS                  |                   |     45 |00:00:00.01 |       8 |

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

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

|   5 |      TABLE ACCESS BY INDEX ROWID| DEPARTMENT        |      1 |00:00:00.01 |       3 |

|*  6 |       INDEX RANGE SCAN          | DEPT_NAME_IDX     |      1 |00:00:00.01 |       2 |

|*  7 |      TABLE ACCESS BY INDEX ROWID| LOCATION          |      1 |00:00:00.01 |       2 |

|*  8 |       INDEX UNIQUE SCAN         | LOC_ID_PK         |      1 |00:00:00.01 |       1 |

|*  9 |     INDEX UNIQUE SCAN           | COUNTRY_C_ID_PK   |      1 |00:00:00.01 |       1 |

|* 10 |    INDEX RANGE SCAN             | EMP_DEPARTMENT_IX |     45 |00:00:00.01 |       2 |

|* 11 |   TABLE ACCESS BY INDEX ROWID   | EMPLOYEE          |     20 |00:00:00.01 |       3 |

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

 
실패사례: 힌트가 무시 되었다

힌트가 전혀 먹혀 들지 않는다. 이유는 View Merging 발생하여 새로운 쿼리블럭이 생성되었기 때문이다. 아래의 Query Block Name 정보를 보면 쿼리블럭명과 테이블의 Alias 조회할 있다. / 기준으로 왼쪽이 쿼리블럭명이고 오른쪽이 테이블의 Alias 이다. 제일 왼쪽의 숫자는 실행계획상의 Id 일치한다.

Query Block Name / Object Alias (identified by operation id):

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

   1 - SEL$F5BB74E1

   5 - SEL$F5BB74E1 / D@SEL$2

   6 - SEL$F5BB74E1 / D@SEL$2

   7 - SEL$F5BB74E1 / L@SEL$2

   8 - SEL$F5BB74E1 / L@SEL$2

   9 - SEL$F5BB74E1 / C@SEL$2

  10 - SEL$F5BB74E1 / E@SEL$1

  11 - SEL$F5BB74E1 / E@SEL$1

 

힌트에 쿼리블럭명과 Object Alias 사용해야 가능해

쿼리블럭명은 SEL$F5BB74E1 이며 Object Alias 들은 D@SEL$2, L@SEL$2, C@SEL$2, E@SEL$1 임을 있다. 따라서 정보들을 이용하여 아래처럼 힌트를 바꾸어 보자.


SELECT /*+ gather_plan_statistics LEADING(@SEL$F5BB74E1 E@SEL$1 C@SEL$2 L@SEL$2 D@SEL$2  ) */

       e.employee_id, e.first_name, e.last_name, e.job_id, v.department_name

  FROM employee e, v_dept v

 WHERE e.department_id = v.department_id

   AND v.department_name = 'Shipping'

   AND v.postal_code = '99236'

   AND v.region_id = 2

   AND e.job_id = 'ST_CLERK';

 

위의 힌트처럼 쿼리블럭명을 처음에 지정하고 뒤에는 조인될 순서대로 Object Alias 배치하기만 하면 된다. 

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

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

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

|*  1 |  HASH JOIN                     |                 |     20 |00:00:00.01 |       8 |

|*  2 |   HASH JOIN                    |                 |     20 |00:00:00.01 |       5 |

|   3 |    MERGE JOIN CARTESIAN        |                 |    100 |00:00:00.01 |       3 |

|   4 |     TABLE ACCESS BY INDEX ROWID| EMPLOYEE        |     20 |00:00:00.01 |       2 |

|*  5 |      INDEX RANGE SCAN          | EMP_JOB_IX      |     20 |00:00:00.01 |       1 |

|   6 |     BUFFER SORT                |                 |    100 |00:00:00.01 |       1 |

|*  7 |      INDEX RANGE SCAN          | COUN_REGION_IDX |      5 |00:00:00.01 |       1 |

|   8 |    TABLE ACCESS BY INDEX ROWID | LOCATION        |      1 |00:00:00.01 |       2 |

|*  9 |     INDEX RANGE SCAN           | LOC_POSTAL_IDX  |      1 |00:00:00.01 |       1 |

|  10 |   TABLE ACCESS BY INDEX ROWID  | DEPARTMENT      |      1 |00:00:00.01 |       3 |

|* 11 |    INDEX RANGE SCAN            | DEPT_NAME_IDX   |      1 |00:00:00.01 |       2 |

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

쿼리블럭 표기법은 Leading Hint 뿐만 아니라 모든 힌트에 적용 가능해

조인 순서가 Employee --> Country --> Location --> Department 바뀌었다. 방법은 Global Hint 사용 방법 중에 쿼리블럭 표기법을 사용한 것이다. 방법은 특히 View Merging 같은 Query Transformation 발생하여 쿼리블럭이 새로 생성된 경우 매우 유용하다. 이 방법으로 모든 힌트를 사용할 수 있다. 아래의 Outline Data 는 이런 점을 잘 설명 해준다.

Outline Data

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

  /*+

      중간생략

      MERGE(@"SEL$2")

      중간생략

INDEX_RS_ASC(@"SEL$F5BB74E1" "E"@"SEL$1" ("EMPLOYEE"."JOB_ID"))

      INDEX(@"SEL$F5BB74E1" "C"@"SEL$2" ("COUNTRY"."REGION_ID"))

      중간생략

      USE_HASH(@"SEL$F5BB74E1" "D"@"SEL$2")

      END_OUTLINE_DATA

  */

PS
위의 내용 또한 이번에 출간될 책의 일부분이다. 블로그에 책의 내용이 많이 올라가서 걱정이다.^^


신고
Posted by extremedb

댓글을 달아 주세요

  1. 유수익 2010.01.04 11:34 신고  댓글주소  수정/삭제  댓글쓰기

    감사합니다.. 얼른 책을 출간해 주세요....!!!

  2. feelie 2010.01.04 17:44 신고  댓글주소  수정/삭제  댓글쓰기

    좋은 내용 감사합니다.
    2010에도 작년처럼 열심히 해주시면 저와같은 민초에게는 더할나위가 없겠네요..
    아무튼 올해도 부탁드립니다.
    새해 복많이 받으세요...

  3. Eddy 2010.01.05 09:49 신고  댓글주소  수정/삭제  댓글쓰기

    널리 퍼져 있는, 뿌리 깊은 선입견에 도전하고,
    부드럽게 설득하는 작업은 참 멋져 보입니다.

    "Query Transformation을 모르면 튜닝을 할 수 없다"

    이 사실을 많은 분들이 당연한 것으로 여기는 때가
    빨리 오기를 기대합니다.

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.01.05 11:16 신고  댓글주소  수정/삭제

      반갑습니다.
      Eddy 님처럼 튜닝시에 Query Transformation 의 이해가 옵션이 아닌 필수 조건으로 생각하시는 분들이 많이 늘어 난다면 튜닝의 암흑시대가 사라질것 입니다..
      감사합니다.

  4. 혈기린 2010.01.06 13:43 신고  댓글주소  수정/삭제  댓글쓰기

    튜닝의 기법도 나날이 발전해 가는거 같네요 역시 이분야는 끊임없이 공부하고 연구해야 하는 분야인거같네요
    2월에 발간하실 책 기대됩니다 저자의 사인도 받도 싶네요 ㅎㅎ

    좋은자료 감사드립니다 ^^

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.01.06 19:18 신고  댓글주소  수정/삭제

      반갑습니다.
      말씀하신대로 끊임없는 연구하는길이 최고가 되는 길입니다.
      출간 세미나를 할것인지는 고려중입니다.
      만약 한다면 블로그를 통해서 알리도록 하겠습니다.
      새해 복많이 받으세요.

  5. 2010.01.07 11:09  댓글주소  수정/삭제  댓글쓰기

    비밀댓글입니다

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.01.07 12:34 신고  댓글주소  수정/삭제

      반갑습니다.
      말씀하신대로 튜닝의 자동화는 아주 조금씩 진행되고 있습니다. 하지만 컴퓨터가 사람을 상대로 체스는 이길수 잇어도 바둑을 못이기는 것처럼 튜닝이라는 분야는 계속 발전할수 있습니다. 더욱이 데이터 건수가 급격하게 늘어나고 있으니 이분야는 전망이 어둡지 많은 않습니다. 계획한대로 열심히 하시면 분명 성과가 있을 것입니다.
      새해 복많이 받으세요.