Science of Database

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
위의 내용 또한 이번에 출간될 책의 일부분이다. 블로그에 책의 내용이 많이 올라가서 걱정이다.^^

Hash Join Right (Semi/Anti/Outer) 의 용도 라는 글에서 Internal Hint Transformation 개념을 설명한적이 있다.
오늘은 예제 몇가지에 대하여 소개하려 한다.
먼저  Internal Hint Transformation 이 왜 일어나는지 설명하기 위하여 힌트의 종류를 나누어 보자.
오라클 Performace Tuning Guide 에 보면 힌트의 Type 에 대해서 아래와 같이 분류하고 있다.

1.Single Table Hints : 하나의 테이블이나 인덱스에 대하여 사용하는 힌트
                               index 관련 힌트나 use_nl, use_merge, use_hash 등이 여기 속한다.
2.Multi Table Hints : 여러 테이블이나 블럭에 대하여 사용하는 힌트.
                             leading 힌트나 index_join, index_combine 등이 여기에 해당된다.
3.Query Block Hints: 하나의 쿼리블럭에 사용하는 힌트
                             STAR_TRANSFORMATION, UNNEST, MERGE, PUSH_PRED,  USE_CONCAT, NO_EXPAND 등이 여기에 해당된다.
4.Statement Hints : 전체 SQL 단위로 사용하는 힌트
                           all_rows, first_rows_n 등이 여기에 해당된다.

가장 흔한 힌트변환은 use_nl(a, b) 를 leading(a b) use_nl(b) 로 바꾸는 것이다.
use_nl 은 Single Table Hints 이기 때문이다.
또한 use_nl_with_index 등을 사용하여도 각각 index, use_nl 힌트등으로 바꾸어 버린다.
이중에서 가장 극적인 Internal Hint Transformation 예제는 Index_Combine 이다.
아래 예제를 보자.

환경 :10.2.0.4


오라클은 위의 힌트를 어떻게 바꿀까?
dbms_xplan.display_cursor 의 결과중에 Outline Data 를 보면 아래와 같다.
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
      OPT_PARAM('_optim_peek_user_binds' 'false')
      OPT_PARAM('_bloom_filter_enabled' 'false')
      OPT_PARAM('_optimizer_connect_by_cost_based' 'false')
      OPT_PARAM('optimizer_index_cost_adj' 25)
      OPT_PARAM('optimizer_index_caching' 90)
      FIRST_ROWS(1)
      OUTLINE_LEAF(@"SEL$1")
      BITMAP_TREE(@"SEL$1""EMP"@"SEL$1" AND(("EMP"."DEPTNO") MINUS(("EMP"."MGR")) MINUS_NULL(("EMP"."MGR"))))
      END_OUTLINE_DATA
  */

이상하지 않은가?
Bitmap_tree 라는 힌트는 사용하지도 않았다.
오라클은 Index_Combine 힌트를 Bitmap_Tree 힌트로 바꾼것이다.
이 암호와도 같은 힌트를 간단히 바꾸면 아래와 같다.
BITMAP_TREE(emp and( (emp.deptno) minus((emp.mgr)) minus_null((emp.mgr))))
간단히 설명하면  deptno = 20 을 만족하는 집합에서  mgr = 7698  을 만족하는 집합을 뺴주는것(minus) 이다.
그렇다면 남은 minus_null 힌트는 무엇일까?
이힌트는 부정형으로 Bit Map 비교시에만 나타난다.
다시말하면 bit map 연산시 mgr 이 7698 이 아닌것을 나타내면 mgr is null 인 데이터가 포함이 되어 버린다.
따라서 mgr is null 인 집합도 빠져야 하기 때문에 옵티마이져는 minus_null(mgr) 힌트를 사용한 것이다.
이것은 Bit map Operation 의 특성에서 나온것이다.
참고로 where 절에 mgr is not null 이라고 명시하거나 혹은 not null Constraints 를 주게되면 minus_null 힌트는 사라진다.

아래의 Predicate Information 를 보면 상세히 알수 있다.

Predicate Information (identified by operation id):
---------------------------------------------------
 
   5 - access("DEPTNO"=20)            --> (("EMP"."DEPTNO")
   6 - access("MGR"=7698)              --> MINUS(("EMP"."MGR"))
   7 - access("MGR" IS NULL)         --> MINUS_NULL(("EMP"."MGR"))

결론 : Query Transfomation 에는 Internal Hint Transformation 도 포함 되어야 한다.
         Internal Hint Transformation 는 힌트의 용법을 정확히 지키지 않으면 거의 모든곳에서 나타날수 있다.
         또한 용법을 정확히 사용하여도 내부적으로 변환시키는 경우가 많이 있다.
         그러나 이런것이 나타난다고 해서 걱정할 필요는 없다.
         또한 Internal 힌트를 사용할 필요도 없다.
         단지 "옵티마이져가 내부적으로 이런일을 하고 있다." 라고 알고 있으면 당황하지 않을 것이다.

대용량 Parallel 쿼리 에서 조인을 사용할 경우 성능이 저하되는 경우가 많이 있다.
이경우의 원인은 여러가지가 있다.
원인 중에서 가장 치명적인 것은 잘못된 Row Distribution (Row 의 분배방법) 에 있다.
옵티마이져의 잘못된 Row Distribution 을 피하기 위하여 원리및 사용방법 그리고 최후의 방법으로 힌트를 통한 잘못된 Row Distribution 을 피하기 등에 대하여 알아본다.
필자가 이주제를 선택한 이유는 예전에 필자가 그랬듯이 이해하기가 힘들고 DBA 및 튜너라고 할지라도 모르는 사람들이 많이 있기 때문이다.
그렇기 때문에 최대한 이해하기 쉽게 설명 하였다.  

1. Row Distribution Method

Parallel 옵션을 사용한 Select 에서 조인시에 Row 의 분배방법에는 4 가지가 있다.
한가지씩 알아보자

1).Hash : 조인기준컬럼을 Hash Function 을 이용하여 양쪽 테이블을 Mapping 하는 방식임.
             조인컬럼 기준으로 각각의 Temp 성 매핑테이블을 만들고 마지막에 Join 하는 방식이다.
2).Broadcast : 조인된 양쪽테이블에서 한쪽 테이블의 모든 Row를 다른쪽 테이블을 Scan 시에
                     분배하는 방식이다.
                     BroadCast 받는 Table의 Scan 시에 자동으로 조인이 되므로 따로 Join Operation 이
                     필요가 없다.
                     하지만 Broadcast 하는측 테이블의 사이즈가 커지면 Parallel Slave 마다 Outer Table 을
                     반복적으로 BroadCast 해야 하므로 비효율이 커진다.
3).Partition : 파티션을 이용하여  조인이된 양쪽테이블의 Row 를 분배한다.
                  2개의 조인된 테이블 중에서 조인컬럼을 기준으로 반드시  한쪽 테이블은 파티션이
                  되어 있어야한다.
                  파티션이 안된 테이블을 조인컬럼을 기준으로 파티션하여 Row 를 분배하는 방식이다.
                  이분배방식은 Partition Wise Join 과 관계가 있다.
4).None : 이미 조인컬럼기준으로 파티션 된 테이블은 Row 가 파티션기준으로 자동으로 분배되거나
              Broadcast 방식일 경우 분배를 받는쪽 테이블의 Row 는 따로 분배가 필요 없으므로
              None 으로 표현된다.

2.조인시 Row Distribution 의 Combination

한테이블의 Row 분배방식을 알았으니 이젠 양측 테이블의 Row 분배를 조인을 위하여 결합해야 하는데
4지 분배방식 중에서 Oracle 에서 허용되는 Combination 은 아래처럼 6가지 이다.
보는 방법은 Comma( , ) 왼쪽이 Outer Table 오른쪽이 Inner Table 이다.
다시말하면 조인이 왼쪽테이블에서 오른쪽 테이블로 진행된다.

                           
3. PQ_DISTRIBUTE 힌트의 사용

다시한번 말하지만 파티션 분배방식을 제외하면 양측 테이블의 Size 가 비슷한 경우는 분배방식은 Hash, Hash 로 풀려야 하고 코드성 테이블과 같이 소형 테이블과 대형테이블의 조인인경우는 Broadcast, None 으로 풀려야 한다. 
그럼에도 불구하고 Optimizer 가 잘못된 분배방식의 Combination 을 선택하였다면 10중 8, 9 는 통계정보를 제대로 생성해주면 된다.
왜냐하면 파티션 분배방식을 제외하고 Broadcast 나 Hash 등의 분배방식을 선택할떄 Row 수 및 평균 Row 의 길이 등이 결정적인 영향을 끼치기 때문이다.
 
하지만 Temp 성 테이블이나 Global temp Table 등을 사용하면 통계정보가 아예 없다.
또한 통계정보가 있어도 Optimizer 잘못된선택을 할수도 있다.
이때 사용할수 있는 힌트중의 하나가 PQ_DISTRIBUE 이다.
아래의 힌트 옵션을 보고 실제 SQL 을 살펴보자.

힌트로 오라클 파라미터를 바꿀수는 없을까?