지난시간의 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
,

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
,

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

 

질문 : 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
,

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
,