Science of Database

오늘은 Sequence 의 오작동 (번호를 건너뛰는 Sequence) 라는 글에 이어서
Sequence 사용시 주의해야 되는 또다른 상황을 설명한다.
예전에 고객사에서 일어난 일이다.
Sequence 채번시 IF 조건에 따라 채번 할수도 혹은 안할수도 있는상황에서
Sequence 가 IF 조건을 무시하고 무조건 채번된다는 보고가 있었다.
아래는 T1 테이블에 INSERT 하는 아주 간단한 SQL 로서 t1_temp 테이블의 id 컬럼값이 null 이면 채번하고
null 이 아니면 id 컬럼값을 그대로 사용하는 로직이 들어있다.

하지만 DECODE 문과 관계없이 Sequence 는 무조건 채번되었다.
상황을 재현하기 위하여 테이블및 시퀀스를 생성한다.


이제 테이블및 Sequence 가 생성되었으므로 맨위의 SQL 을 실행하고 결과를 SELECT 해보자

위 count 쿼리의 결과는 0 이다.
분명히 5만1번부터 10만번까지 시퀀스로 채번이 되어 5만건이 count 되어야 한다.
뭔가 이상하지 않은가?
이것을 Sequence 버그라고 생각하는 사람도 있지만 필자는 조금 다르게 생각한다.
이유는 Sequence 의 원리에 있다.
Sequence 는 where 절 조건을 만족하면 무조건 채번이 되어버린다.
즉 decode 나 case 문 등의 분기 로직은 where 절이 아니라 select 절에 있기 때문에 무조건 채번 되는 것이다.
그렇다면 조건에 따라서 채번할수 있는 방법은 없는것일까?

Solution 1:
아래와 같은 간단한 함수를 만들어서 call 한다.

Solution 2:
insert 문을 id 컬럼 기준으로 null 일 때와 null 이 아닐때로 분리한다.

한가지 주의사항은 많은수의 Function call 이 부담이 되는 상황이라면 2번방법을 택해야 한다.
과도한 Function call 은 DBMS 에 무리를 주기 때문이다.
 
편집후기: 위의 글중에 일부가 오류가 있어서 일부 수정되었음을 밝혀둔다.

다중 INSERT 문을 사용해야 한다는 의견이 있었다.

가능할 것처럼 보이지만 위의 SQL 로도 해결이 안된다는걸 알아야 한다.

고객사의 DBA 로 부터 Sequence가 오작동 한다는 보고를 받았다.
현상은 Sequence 의 NEXTVAL 을 하면 몇만건이 없어진다는데 있었다.
일단 필자는 NOORDER 로 설정되어 있는지 질문을 하였으나 시스템이 RAC 가 아니었다.

해당 Sequence를 분석해보니 CACHE 가 10만이 잡혀 있었고 MAXVALUE가 99999999 으로 되어 있었으며
NOCYCLE 로 설정된 상태였다.
계산상으로 1년정도가 지나면 MAXVALUE 에 도달해서  해당 Sequence를 select 하는 순간 error 가
날수 있는  상황 이었다.

특이한점은 Sequence의 사용주기였다.
토, 일,월~목요일날 전혀 사용하지 않고 금요일에만 일괄배치로 최대 몇만건이 들어올수 있다는 것이었다.

그렇다면 이런 현상의 원인은 무었일까?
원인은 아주 간단하다.
자주 사용하지 않는 OBJECT 는 Shared Pool 에서 밀려난다.
밀려난 Sequence는 Caching 된 번호들이 메모리상에서 없어지게 됨으로 번호가 건너뛰는 것 같이 느낀것이다.
결국은 Sequence의 오작동이 아닌 자연스러운 현상인 것이다.

그렇다면 위와같이 Sequence의 사용주기가 긴(일주일에 한번) 시스템에서 이런현상을 방지하려면 어떤 방법이 있을까? 
이런경우는 dbms_shared_pool.keep 을 사용하는 것이 정답이다.
아래의 예제를 보자.

dbms_shared_pool.keep 사용후에 아래와 같은 스크립트로 적재여부를 확인 할수 있다.

dbms_shared_pool.keep을 사용하기 전에 아래와 같은 스크립트로 Object 의 크기를 확인하는 습관을 들여야 한다.

한가지 주의사항은 alter system flush shared_pool 명령을 수행해도 이미 keep 시킨 object는
Shared Pool 에서 떨어지지 않는다.
Aging Out 시키는 유일한 방법은 dbms_shared_pool.unkeep 을 사용하는 것이다.

최근의 많은수의 사람들이 DBMS_XPLAN 패키지를 사용하여 튜닝을 하고 있다.
필자는 DBMS_XPLAN 패키지에 대한 너무많은 질문공세 때문에 아예 블로그에 올릴 결심을 하였다.
오늘은 DBMS_XPLAN 패키지에 대한 첫번째 이야기로 가장중요한 실행계획에 대하여 조목조목 따져보려고 한다.

DBMS_XPLAN 패키지는 9i 부터 점점 발전하여 지금은 Trace + tkprof 보고서와 자웅을 겨룰 정도로 발전하고
있다.
DBMS_XPLAN 패키지내의 함수는 10g R2 기준으로 6개 이지만 가장 자주 사용하는 함수는 아래의 3가지 이다.

1.DISPLAY                 --> 예측 실행계획을 보여준다.
2.DISPLAY_CURSOR   --> 실제 실행된 실행계획을 보여준다.
3.DISPLAY_AWR         --> 실제 실행된 실행계획을 보여준다.

오늘의 주제는 실행계획상의 각항목에 대한 설명이므로 3개의 함수에 대한 자세한 설명은 다음에 계속하여
연재할 계획이다.

실행계획은 패키지 내의 3가지 함수(display, display_cursor, display_awr)를 통해 모두 조회가 가능하다.
아래의 스크립트는 display_awr 의 예제이며 sql_id 만 구하면 언제든지 실행될수 있다.

위 PLAN 은 DBMS_XPLAN 패키지의 format 항목을 Advanced 로 했을 경우에 나타나는 Plan 의 모습이다.
아래는 위의 Plan 항목 하나하나에 대한 자세한 설명이다.
물론 위의 예제는 실행계획의 모든 항목이 나온것은 아니다.
예를 들면 파티션테이블을 사용하지 않았으므로 Partiton 관련 항목이 빠진것이다.

DBMS_XPLAN 패키지의 실행계획의 항목은 아래처럼 크게 7개로 나눌수 있다.

주의사항:

파랑색부분( 5), 6), 7)번 에해당됨)은 실행통계가 있을경우만 해당된다.

display_cursor, display_awr 의  경우 statistics_level 파라미터를 all 로 설정하거나 SQL 에 gather_plan_statistics 를 사용한경우 실행통계를 볼수 있다.

 

1)Basics 항목 (Always Available)

Id                :각 Operation 의 ID 임.

                  * 가 달려있는 경우는 predicate 정보에 access 및 filter 에 관한정보가 나옴을 표시한것임.

Operation  : 각각 실행되는 JOB 을 나타냄

                    row source operation.의 줄임말임.

Name          : Operation 이 엑세스하는 테이블 및 인덱스를 나타냄.

2)Query Optimizer Estimations(옵티마이져의 예상 row 수 및 bytes, cost , temp 사용량)

Rows (E-Rows)  : 각 operation 이 끝났을 때 return되는 건수를 나타냄.

                              이것은 예측 건수 이므로 실제 건수와는 다름.

Bytes (E-Bytes) : 각 operation 이 return 한 byte 수.
                              예상치 이므로 실제 받은 byte와는 다름

TempSpc             : 각 operation. 이 temporary space 를 사용한 양(예상치임)

Cost (%CPU)      : 각 operation 의 Cost. (예상치 임)

                              괄호안의 내용은 CPU Cost 의 백분율임.

                               이값은 Child Operation 의 Cost 를 합친 누적치임.

Time                     : 예측 수행시간

 

3)Partitioning (파티션을 엑세스 할경우만 나타남)

Pstart  : 파티션을 엑세스 하는경우 시작파티션을 나타냄

             상수로 들어올때는 파티션 번호로 나타나며 변수로 들어올때는 KEY 로 나타남

Pstop  : 마지막 파티션을 나타냄.

              따라서 patart, pstop 를 이용하면 access 한 파티션을 알수 있음. 

4)Parallel and Distributed Processing (Parallel Processsing 을 사용하거나 DB-LINK 를 사용하는경우)

Inst         :  DB-LINK 명(사용하는 경우만 나타남).

TQ            :  PARALLEL SQL 사용시 table queue 명을 나타냄
                   TQ 는 PARALLEL SLAVE 간의 통신을 담당함.

IN-OUT      :  Parallel processing 시에 각각의 Operation 이 Serial 로 실행되는지 parallel 로 진행되는지를
                     나타냄.

PQ Distrib :  Parallel processing 시에 producers 와 consumers 간의 데이터의 분배방식을 나타냄.

* 이부분의 자세한 내용은 아래를 참조하기 바란다.
1.http://scidb.tistory.com/entry/Parallel-Query-의-조인시-Row-Distribution
2.http://scidb.tistory.com/entry/Parallel-Query-의-조인시-또다른-튜닝방법pxjoinfilter 

5)Runtime Statistics (실제 수행시간밑 실제수행건수)

Starts     : 각 operation 을 try 한 건수(예를 들어 nested loop join 이라면 인덱스를 여러 번 scan 함)

A-Rows  : 각 operation 이 return 한 건수

A-Time   : 실제 실행시간

0.1초까지 나타남 (HH:MM:SS.FF).

                 이값은 Child Operation 의 Cost 를 합친 누적치임.             
 

6)I/O Statistics (I/O 관련하여 READ / WRITE 한 블록수)

Buffers  : 각 Operation 이 메모리에서 읽은 block 수.

Reads   :각 Operation 이 disk 에서 읽은 block 수.

Writes   : 각 Operation 이 disk 에 write한 block 수.

 

7)Memory Utilization Statistics(hash 작업이나 sort 작업시 사용한 메모리 통계)

OMem         : optimal execution 에 필요한 메모리(예측치임).

1Mem          : one-pass execution. 에 필요한 메모리(예측치임)

O/1/M        : 각 operation 이 실행한 optimal/one-pass/multipass 횟수가 순서대로 표시됨.

Used-Mem : 마지막 실행시의 사용한 메모리

Used-Tmp  : 마지막 실행시 메모리가 부족하여 temporary space 를 대신 사용할 때 나타남.

                      보이는값에 1024 를 곱해야함.
                      예를들어 32K 로 나타나면 32MB 를 의미함.

Max-Tmp    : 메모리가 부족하여 temporary space 를 사용할 때 최대 temp 사용량임.

                     USED-TMP 와 다른점은 마지막 수행시가 아니라 SQL을 여러 번 수행했을경우에 
                     항상 최대값만 보인다는 것이다.
                     보이는값에 1024 를 곱해야함.
                     예를들어 32K 로 나타나면 32MB 를 의미함.

결론:
이상으로 PLAN 상에 나오는 각 항목에 대하여 빠짐없이 알아보았다.
특히 Runtime 통계,  I/O 통계및 Memory 통계중의 일부항목은 Tkprof 보고서에도 나오지않는 정보들로
튜닝시 요긴하게 사용할수 있다는 점을 기억 해야 한다.  
다음시간에는 3가지 함수의 여러가지 옵션에 대하여 알아볼것이다.

Reference :
1.Ttroubleshooting Oracle Performance (Christian Antognini)
2.Oracle 10g Manual : PLSQL Packages and Types Reference

프로젝트를 하다보면 결합인덱스의 2번째 혹은 3번째 컬럼에 IN 조건을 많이 사용하는것이 관찰된다.
오늘은 결합인덱스 에서 IN 을 사용한경우 나타날수있는3가지 경우(InList, Concatnation, Range Scan)에
대하여 알아 보겠다.

버젼 11.1.0.6
인덱스 : EMP_JOB_MGR_SAL_IDX (JOB_ID, MANAGER_ID, SALARY)  

아래는 인덱스의 첫번째 컬럼이 = 조건으로 들어오고 두번째 컬럼이 IN 변수조건으로 들어 왔을때의
전통적인 방식의 PLAN 이다.

------------------------------------------------------------
| Id  | Operation                    | Name                |
------------------------------------------------------------
|   0 | SELECT STATEMENT             |                     |
|   1 |  CONCATENATION               |                     |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES           |
|*  3 |    INDEX RANGE SCAN          | EMP_JOB_MGR_SAL_IDX |
|   4 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES           |
|*  5 |    INDEX RANGE SCAN          | EMP_JOB_MGR_SAL_IDX |
------------------------------------------------------------
 
별로 특별할것이 없는 전통적인 방식의 CONCATENATION Plan 이 나왔다.
그렇다면 Rule 힌트를 빼면 어떻게 되는겠는가?
CONCATENATION Plan 대신에 InList Plan 을 기대하는가?
아래의 Plan 을 보자.

---------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                     |     1 |    62 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES           |     1 |    62 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMP_JOB_MGR_SAL_IDX |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("JOB_ID"=:V_JOB)
       filter("MANAGER_ID"=TO_NUMBER(:V_MANAGER1) OR "MANAGER_ID"=TO_NUMBER(:V_MANAGER2))

InList Plan 이 나오지 않는다.
어떻게 된것인가?
혹자는 NO_EXPAND 힌트를 사용하라고 이야기 하지만 그것은 CONCATENATION Plan 을 방지하는 힌트일뿐
InList Plan으로 유도하지 않는다.

EXPLAIN PLAN FOR
SELECT /*+ NO_EXPAND */
       a.*
  FROM hr.employees a
 WHERE job_id = :v_job
   AND manager_id IN (:v_manager1, :v_manager2);

---------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                     |     1 |    62 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES           |     1 |    62 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMP_JOB_MGR_SAL_IDX |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("JOB_ID"=:V_JOB)
       filter("MANAGER_ID"=TO_NUMBER(:V_MANAGER1) OR "MANAGER_ID"=TO_NUMBER(:V_MANAGER2))

예상과는 다르게 Range Scan 이 나오고 IN 절은 Filter 조건으로 되고말았다.
그렇다면 이런경우 도데체 어떻게 InList Plan 으로 유도 한단 말인가?
이런경우에 사용할수 있는 힌트가 바로 NUM_INDEX_KEYS 이다.
아래의 SQL 을보자.

----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                     |     1 |    62 |     2   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR             |                     |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES           |     1 |    62 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | EMP_JOB_MGR_SAL_IDX |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("JOB_ID"=:V_JOB AND ("MANAGER_ID"=TO_NUMBER(:V_MANAGER1) OR
              "MANAGER_ID"=TO_NUMBER(:V_MANAGER2)))

깔끔하게 InList Plan 으로 유도 되었다.
그렇다면 NUM_INDEX_KEYS 힌트의 마지막 인자인 숫자 2는 무엇을 의미하는가?
결합 인덱스의 2번째 컬럼(MANAGER_ID) 까지는 InList Plan 으로 풀리는 것을 의미한다.
거꾸로 이야기하면 NUM_INDEX_KEYS(a EMP_JOB_MGR_SAL_IDX 2) 로 힌트를 주면 인덱스의 3번째
컬럼인 SALARY 조건에 IN 조건을 주어도 InList Plan 으로 풀리지 않는다.
아래의 SQL및 Plan 을 보고 InList Plan 이 나왔다고 생각하면 오산이다.

EXPLAIN PLAN FOR
SELECT /*+ NUM_INDEX_KEYS(a EMP_JOB_MGR_SAL_IDX 2) */
       a.*
  FROM hr.employees a
 WHERE job_id = :v_job
   AND manager_id IN (:v_manager1, :v_manager2, :v_manager3)
   AND salary IN (:v_sal1, :v_sal2, :v_sal3);

----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                     |     1 |    62 |     2   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR             |                     |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES           |     1 |    62 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | EMP_JOB_MGR_SAL_IDX |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("JOB_ID"=:V_JOB AND ("MANAGER_ID"=TO_NUMBER(:V_MANAGER1) OR
              "MANAGER_ID"=TO_NUMBER(:V_MANAGER2) OR "MANAGER_ID"=TO_NUMBER(:V_MANAGER3)))
       filter("SALARY"=TO_NUMBER(:V_SAL1) OR "SALARY"=TO_NUMBER(:V_SAL2) OR
              "SALARY"=TO_NUMBER(:V_SAL3))

위 Plan 에서의 InList 는 Salary 조건과는 상관없이 Job_id 와 Manager_id  두가지 조건만으로 InList 가
나온것이다.
Salary 조건은 역시 Filter 로 빠진것을 알수 있다.
아래처럼 3으로 바꾼다면 인덱스의 3번째컬럼 까지 InList 로 풀리는걸 볼수 있다.

EXPLAIN PLAN FOR
SELECT /*+ NUM_INDEX_KEYS(a EMP_JOB_MGR_SAL_IDX 3) */
        a.*
  FROM hr.employees a
 WHERE job_id = :v_job
   AND manager_id IN (:v_manager1, :v_manager2, :v_manager3)
   AND salary IN (:v_sal1, :v_sal2, :v_sal3);

----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                     |     1 |    62 |     2   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR             |                     |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES           |     1 |    62 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | EMP_JOB_MGR_SAL_IDX |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("JOB_ID"=:V_JOB AND ("MANAGER_ID"=TO_NUMBER(:V_MANAGER1) OR
              "MANAGER_ID"=TO_NUMBER(:V_MANAGER2) OR "MANAGER_ID"=TO_NUMBER(:V_MANAGER3)) AND
              ("SALARY"=TO_NUMBER(:V_SAL1) OR "SALARY"=TO_NUMBER(:V_SAL2) OR "SALARY"=TO_NUMBER(:V_SAL3)))

하지만 manager_id 종류가 많지않은 경우에는 Range Scan이 더 나은 성능을 보이는 경우가 많으므로
무조건 적용해서는 안된다.
오늘 사용한 Rule 힌트나 NUM_INDEX_KEYS 힌트는 Production 시스템에서는 사용하면 안된다.
사용하려면 deprecate 된 힌트나 undocument 힌트를 사용한 SQL 만 따로 목록을 만들어서 관리해야 할것이다.

PS: 테스트 환경은 11g 에서 테스트 됬지만 10g 에서 테스트 해도 동일한 결과를 얻을수 있다.

PRO * C 나 PL/SQL 에서 많은양의 데이터를 Cursor For Loop로  선언한후를 건건이 Update/Delete 등을 처리하는 경우가 많이 있다.
집합처리가 유리하겠지만 그것이 불가능한 경우나 집합처리가 가능하지만 SQL 이 너무 길어지는 경우는
유지보수의 관점에서 Cursor For Loop 를 사용해야 한다.
하지만 많은 개발자들이 일반적으로 Cursor For Loop 내에서 아직도 PK 인덱스나 Unique 인덱스를 사용하여
DML 문을 실행하고 있다.  
오늘은 Cursor for Loop 처리시 개발자들이 일반적으로 코딩하는 방식의 비효율성을 설명하고 Solution 을 제시한다.  
아래는 테스트용 테이블및 인덱스 생성 스크립트 이다.

1.아래는 Curso For Loop  를 사용하여  건건이 Update 하는 가장 흔하게 볼수있는 PL/SQL 패턴이다.

위 PL/SQL 의 ELAPSED TIME 은 11.80 초 이며 이수치는 10번을 실행하여 평균값을 취한것이다.

2.SOLUTION
  

위 PL/SQL 의 ELAPSED TIME 은 10.70 초 이며 이수치는 10번을 실행하여 평균값을 취한것이다.
물론 테스트를 정확히 하기위하여 각각의 테스트시에 buffer cache 를 flush 하였다.

PK 인덱스를 이용한 UPDATE 보다 Current of 를 사용한 Update 가 10% 정도 빠른걸 알수 있다.
그렇다면 10%의 차이는 어디서 오는가?

비밀은 v$sqlarea 에서 수행된 SQL 을 보면 알수 있다.

결과 :
---------------------------------------------------------------------------
UPDATE /*+ current_of_test */ T1 SET SCORE = SCORE + 0 WHERE ROWID = :B1

위의 결과에서 처럼 WHERE CURRENT OF 를 사용하면 ROWID 를 이용하여 Accsess 하는것을 알수 있다.
Rowid Access 는 오라클에서 가장 빠른 Access 방식이다.
주의할 점은 WHERE CURRENT OF 를 사용하려면  Main Cursor 가 반드시 SELECT FOR UPDATE 문이어야
한다는 것이다.
하지만 FOR UPDATE 가 없는 커서에서도 아래처럼 사용하면 같은 효과를 누릴수 있다.

결론:
다시한번 기본(SQL 및 PL/SQL)이 얼마나 중요한지 알수 있는 테스트 이다.
Where Current of 를 모르는 사람은 많이 없을것이다.
하지만 Where Current of 절을 사용하면 Index Access에 의한 부하를 없앨수 있다는 사실을 아는사람은
의외로 많지 않다.
여러분의 프로그램이 이런환경에서 PK 인덱스를 사용하도록 코딩이 되어 있다면 지금 당장 달려가서
프로그램을 수정하라고 말하고 싶다.

9월 달에 Using Sub query Method( Filter / Access sub Query ) 이란 글을 통하여 Access 서브쿼리를
설명한적이 있다.
오늘은 서브쿼리 사용시 함정이 몇가지 있지만 그중에서 가장 자주발생하는 Access 서브쿼리의 경우를 소개한다.

서브쿼리를 사용할때 Access sub Query plan (제공자 서브쿼리 plan)이 나오더라도 방심하면 안된다.
Plan 의 모습만 Access sub Query 일뿐 사실은 Filter sub Query(확인자 서브쿼리) 로 풀리는 경우가 많이 있기 때문이다.
 
실행환경 : Oracle 10.2.0.4
아래는 전형적인  Access sub Query plan 을 보여준다.

         
--------------------------------------------------------------------------
| Id  | Operation                      | Name    | Rows  | Bytes | Cost  |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |         |     1 |   108 |     4 |
|   1 |  TABLE ACCESS BY INDEX ROWID   | MIN_MAX |     1 |   108 |     2 |
|*  2 |   INDEX UNIQUE SCAN            | MM_PK   |     1 |       |     1 |
|   3 |    SORT AGGREGATE              |         |     1 |     8 |       |
|   4 |     FIRST ROW                  |         |    10 |    80 |     2 |
|*  5 |      INDEX RANGE SCAN (MIN/MAX)| MM_PK   |    10 |    80 |     2 |
--------------------------------------------------------------------------

위 쿼리의 실행순서는 id 기준으로 5 -> 4 -> 3 -> 2 -> 1 이라고 설명 하였다.
하지만 항상 그렇게 되는 것은 아니다.
아래 스크립트를 보자  

인덱스 현황 :
1)EMP 테이블  : EMP_IX04  (JOB, DEPTNO, HIREDATE)
2)DEPT 테이블 : DEPT_IDX1 (LOC)

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

---------------------------------------------------------------------------------------------
| Id  | Operation                      | Name      | Starts | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------
|*  1 |  FILTER                        |           |      1 |      1 |00:00:00.01 |       7 |
|   2 |   TABLE ACCESS BY INDEX ROWID  | EMP       |      1 |      1 |00:00:00.01 |       7 |
|*  3 |    INDEX RANGE SCAN            | EMP_IX04  |      1 |      1 |00:00:00.01 |       6 |
|*  4 |     TABLE ACCESS BY INDEX ROWID| DEPT      |      2 |      1 |00:00:00.01 |       4 |
|*  5 |      INDEX RANGE SCAN          | DEPT_IDX1 |      2 |      2 |00:00:00.01 |       2 |
---------------------------------------------------------------------------------------------

위 SQL 의 의도는 부서 테이블에서 위치가 CHICAGO 인 부서를 찾아서 EMP 테이블에 제공 하는것이다.
따라서 의도적으로 서브쿼리에 NO_UNNEST 힌트를 사용하였다.
PLAN 을 보면 정상적 이라고 생각할 수 있다.
그런데 Starts 컬럼(빨강색)을 보면 서브쿼리가 2번 실행된걸 알수 있다.
뭔가 이상하다.
제공자 서브쿼리는 단 한번만 실행 되어야 하는데...
아래의 Predicate Information 을 보면 더욱 이상한것을 발견할수 있다.

Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(TO_DATE('19801010')<=TO_DATE('19820101'))
   3 - access("A"."JOB"='CLERK' AND "A"."HIREDATE">='19801010' AND "A"."HIREDATE"<='19820101')
       filter(("A"."HIREDATE"<='19820101' AND "A"."HIREDATE">='19801010' AND  IS NOT NULL))
   4 - filter("B"."DEPTNO"=:B1)
   5 - access("B"."LOC"='CHICAGO')
 
id 3에서  access 정보를 보면 DEPTNO 가 빠져있다.
인덱스가 (JOB, DEPTNO, HIREDATE) 인 상황에서 JOB, HIREDATE 만 사용하였다.
또한 id 4에서 filter("B"."DEPTNO"=:B1) 이 발생하였다.
이것은 확인자 서브쿼리에서만 나올수 있는 정보이다.
뭔가 이상하지 않은가?

이것을 결정적으로 확인할수 있는것은 DBMS_XPLAN.DISPLAY_CURSOR 가 아니라
DBMS_XPLAN.DISPLAY 이다.

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY);

---------------------------------------------------------------------------------
| Id  | Operation                      | Name      | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |           |     1 |    32 |     3   (0)|
|*  1 |  FILTER                        |           |       |       |            |
|   2 |   TABLE ACCESS BY INDEX ROWID  | EMP       |     1 |    32 |     1   (0)|
|*  3 |    INDEX RANGE SCAN            | EMP_IX04  |     1 |       |     1   (0)|
|*  4 |     TABLE ACCESS BY INDEX ROWID| DEPT      |     1 |     9 |     1   (0)|
|*  5 |      INDEX RANGE SCAN          | DEPT_IDX1 |     1 |       |     1   (0)|
---------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(TO_DATE('19801010')<=TO_DATE('19820101'))
   3 - access("A"."JOB"='CLERK' AND "A"."HIREDATE">='19801010' AND
              "A"."HIREDATE"<='19820101')
       filter("A"."HIREDATE"<='19820101' AND "A"."HIREDATE">='19801010'
              AND  EXISTS (SELECT /*+ NO_UNNEST INDEX ("B" "DEPT_IDX1") */ 0 FROM  --> EXISTS 발생
              "DEPT" "B" WHERE "B"."LOC"='CHICAGO' AND "B"."DEPTNO"=:B1))
   4 - filter("B"."DEPTNO"=:B1)
   5 - access("B"."LOC"='CHICAGO')

Predicate Information 을 보면 분명히 Filter sub Query(확인자 서브쿼리) 로 풀리는걸 확인할수 있다.
많은 튜닝책에서 이런식으로 결합인덱스를 만들면 제공자 서브쿼리로 풀린다고 가이드 하고 있지만
불행히도 버젼 9i 이후부터는 그렇지못하다.
Access sub Query 으로 예상 했지만  Filter sub Query 로 풀리면 심각한 성능저하 현상이 발생할수 있다.
그렇다면 어떻게  Filter sub Query 를 Access sub Query(제공자 서브쿼리) 로 만들것인가?
오라클은 옵티마이져는 이러한 경우에 왠만 해서는 Access sub Query 를 선택하지 않는다.
이때 간단한 트릭을 생각할수 있다.
몇가지 방법이 있지만 가장 간단한 두가지의 방법을 소개한다.
서브쿼리가 항상 1건만 Return 한다는 정보를 옵티마이져에게 주면된다.

1.min/max 함수 사용하기

SELECT /*+ GATHER_PLAN_STATISTICS INDEX(A EMP_IX04) */ *
  FROM EMP A
 WHERE A.JOB = 'CLERK'
   AND A.DEPTNO IN (SELECT /*+ NO_UNNEST INDEX(B DEPT_IDX1) */  MAX(B.DEPTNO)
                                  FROM DEPT B
                                 WHERE B.LOC = 'CHICAGO')
   AND A.HIREDATE BETWEEN '19801010' AND '19820101' ;

----------------------------------------------------------------------------------------------
| Id  | Operation                       | Name      | Starts | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------
|*  1 |  FILTER                         |           |      1 |      1 |00:00:00.01 |       5 |
|   2 |   TABLE ACCESS BY INDEX ROWID   | EMP       |      1 |      1 |00:00:00.01 |       5 |
|*  3 |    INDEX RANGE SCAN             | EMP_IX04  |      1 |      1 |00:00:00.01 |       4 |
|   4 |     SORT AGGREGATE              |           |      1 |      1 |00:00:00.01 |       2 |
|   5 |      TABLE ACCESS BY INDEX ROWID| DEPT      |      1 |      1 |00:00:00.01 |       2 |
|*  6 |       INDEX RANGE SCAN          | DEPT_IDX1 |      1 |      1 |00:00:00.01 |       1 |
----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(TO_DATE('19801010')<=TO_DATE('19820101'))
   3 - access("A"."JOB"='CLERK' AND "A"."DEPTNO"= AND "A"."HIREDATE">='19801010' AND
              "A"."HIREDATE"<='19820101')
   6 - access("B"."LOC"='CHICAGO')

정상적으로 Access sub Query plan 이 나온것을 확인할수 있다.

2. IN 서브쿼리 대신에 = 서브쿼리로 바꾸기

SELECT /*+ GATHER_PLAN_STATISTICS INDEX(A EMP_IX04) */ *
  FROM EMP A
 WHERE A.JOB = 'CLERK'
   AND A.DEPTNO = (SELECT /*+ NO_UNNEST INDEX(B DEPT_IDX1) */  B.DEPTNO
                                 FROM DEPT B
                               WHERE B.LOC = 'CHICAGO')
   AND A.HIREDATE BETWEEN '19801010' AND '19820101';

---------------------------------------------------------------------------------------------
| Id  | Operation                      | Name      | Starts | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------
|*  1 |  FILTER                        |           |      1 |      1 |00:00:00.01 |       5 |
|   2 |   TABLE ACCESS BY INDEX ROWID  | EMP       |      1 |      1 |00:00:00.01 |       5 |
|*  3 |    INDEX RANGE SCAN            | EMP_IX04  |      1 |      1 |00:00:00.01 |       4 |
|   4 |     TABLE ACCESS BY INDEX ROWID| DEPT      |      1 |      1 |00:00:00.01 |       2 |
|*  5 |      INDEX RANGE SCAN          | DEPT_IDX1 |      1 |      1 |00:00:00.01 |       1 |
---------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(TO_DATE('19801010')<=TO_DATE('19820101'))
  3 - access("A"."JOB"='CLERK' AND "A"."DEPTNO"= AND "A"."HIREDATE">='19801010' AND
              "A"."HIREDATE"<='19820101')
   5 - access("B"."LOC"='CHICAGO')

두방법 모두 성공적으로 Access sub Query plan 으로 풀린다.
Buffers 도 7 에서 5 로 줄어들었다.
하지만 서브쿼리에서 2건 이상 RETURN 되는 경우는 어떻게 할것인가?
필자의 생각은 Access sub Query plan 을 포기하고 UNNEST 서브쿼리를 선택하라는 것이다.

---------------------------------------------------------------------------------------------
| Id  | Operation                      | Name      | Starts | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------
|*  1 |  FILTER                        |           |      1 |      1 |00:00:00.01 |       6 |
|   2 |   TABLE ACCESS BY INDEX ROWID  | EMP       |      1 |      1 |00:00:00.01 |       6 |
|   3 |    NESTED LOOPS                |           |      1 |      3 |00:00:00.01 |       5 |
|   4 |     TABLE ACCESS BY INDEX ROWID| DEPT      |      1 |      1 |00:00:00.01 |       3 |
|*  5 |      INDEX RANGE SCAN          | DEPT_IDX1 |      1 |      1 |00:00:00.01 |       2 |
|*  6 |     INDEX RANGE SCAN           | EMP_IX04  |      1 |      1 |00:00:00.01 |       2 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(TO_DATE('19801010')<=TO_DATE('19820101'))
   5 - access("B"."LOC"='CHICAGO')
   6 - access("A"."JOB"='CLERK' AND "A"."DEPTNO"="B"."DEPTNO" AND "A"."HIREDATE">='19801010'
              AND "A"."HIREDATE"<='19820101')

서브쿼리가 조인으로 바뀌었고 정상적으로  Access sub Query plan 과 같이
(JOB, DEPTNO, HIREDATE) 인덱스를 사용하였다.
위와 같은 상황에서는 Unnesting 을 적극 추천한다.
혹자는 "_unnest_subquery 파라미터 등을 세션단위에서 수정하여 제공자 서브쿼리로 만들면 되지않냐?"
고 말한다.
맞는 말이지만 연구소에서나 사용하여야 한다.
Production 시스템에서는 위와같이 히든 파라미터를 수정하는 것은 아주 위험하다.
현재는 버젼이 10g 이지만 Oracle을 Upgrade를 할때는 아마도 Oracle 버젼 13 혹은 14버젼을
사용할것이다.
그때에도 위와 같은 히든 파라미터가 있다고 보장할수 없다.
Deprecate 된 힌트도 마찬가지 일것이다.(대부분 3~4 버젼이 지나면 힌트가 없어짐)
물론 미래(Oralce Upgrade 시)에 모든 AS-IS 의 소스코드를 모조리 체크해서 새로운 힌트및 파라미터로 바꿀 각오가 되어 있다면 가능하다.

결론 :
마지막 예제와 같이 서브쿼리의 집합이 PK 컬럼이나 Unique 인덱스의 컬럼을 모두 RETURN 하는 경우는
Unnesting 될때 UNQUE 가 보장되므로 별도의 SORT UNIQUE 작업이나 HASH UNIQUE 작업이 일어나지 않는다.
따라서 서브쿼리의 결과 건수가 작고 별도의 Unique 작업이 없을경우 거의 Access sub Query 와
같은 성능을 보장하므로 굳이 Access sub Query plan (제공자 plan) 을 고집할 필요가  없다는 점을
기억 해야 한다.
서브쿼리 Unnesting 은 Using Sub query Method (Sub query Flattening ) 글을 참고하기 바란다.

필자는 Hash 조인의 튜닝시 주의사항(Work Area 의 튜닝) 이라는 이전글에서 Right Deep Tree,
Left Deep Tree Plan 의 개념 및 튜닝에 관한 글을 작성하였는데 Bushy Tree Plan 에 관한 질문을 받았다.
질문은 "Bushy Tree Plan 은 무엇이며 어떨때 사용하는가?" 였다.
간단히 설명하면 T1 과 T2 를 조인하고 T3 와 T4 를 조인하여 2개의 결과 집합을 조인하는 방식이다.
실제로 Bushy Tree Plan SQL 을 실행시켜보자

테이블 생성 스크립트는 Hash 조인의 튜닝시 주의사항(Work Area 의 튜닝) 시 사용했던 스크립트와 동일하다.
먼저 지난번에 언급 되었던  Right Deep Tree PLAN 을 보자.

-------------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | A-Rows |   A-Time   | Used-Mem |
-------------------------------------------------------------------------------
|*  1 |  HASH JOIN           |      |      1 |     90 |00:00:00.13 | 1212K (0)|
|*  2 |   TABLE ACCESS FULL  | T1   |      1 |     90 |00:00:00.04 |          |
|*  3 |   HASH JOIN          |      |      1 |  10000 |00:00:00.18 |   11M (0)|
|   4 |    TABLE ACCESS FULL | T2   |      1 |  10000 |00:00:00.03 |          |
|*  5 |    HASH JOIN         |      |      1 |  10000 |00:00:00.10 |   11M (0)|
|   6 |     TABLE ACCESS FULL| T3   |      1 |  10000 |00:00:00.03 |          |
|   7 |     TABLE ACCESS FULL| T4   |      1 |  10000 |00:00:00.03 |          |
-------------------------------------------------------------------------------

아래는 위의 SQL 을 Bushy Tree Plan 으로 유도하는 예제이다.

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ADVANCED ALLSTATS LAST'));

-------------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | A-Rows |   A-Time   | Used-Mem |
-------------------------------------------------------------------------------
|*  1 |  HASH JOIN           |      |      1 |     90 |00:00:00.09 | 1190K (0)|
|   2 |   VIEW               |      |      1 |     90 |00:00:00.06 |          |
|*  3 |    HASH JOIN         |      |      1 |     90 |00:00:00.06 | 1191K (0)|
|*  4 |     TABLE ACCESS FULL| T1   |      1 |     90 |00:00:00.04 |          |
|   5 |     TABLE ACCESS FULL| T2   |      1 |  10000 |00:00:00.03 |          |
|   6 |   VIEW               |      |      1 |  10000 |00:00:00.10 |          |
|*  7 |    HASH JOIN         |      |      1 |  10000 |00:00:00.10 |   11M (0)|
|   8 |     TABLE ACCESS FULL| T3   |      1 |  10000 |00:00:00.03 |          |
|   9 |     TABLE ACCESS FULL| T4   |      1 |  10000 |00:00:00.03 |          |
-------------------------------------------------------------------------------

Right Deep Tree PLAN 과 비교해보면 메모리 사용량이 거의 절반수준으로 떨어졌다.

참고로 DBMS_XPLAN.DISPLAY_CURSOR 사용시 ADVANCED 를 명시하면 튜닝차원에서 필요한 모든정보가
빠짐없이 출력된다.
예를 들면 Outline Data, Query Block Name, Predicate Information, Column Projection Information
등이 모두 출력이 되나 여기서는 지면관게상 생략 하였다.

결론:
Bushy Tree Plan 의 원래의 활용도는 인라인뷰 2개를 만들고 각인라인뷰 내에서 조인이 발생하며
독자적인 똑똑한 FILTER 조건이 있을때 각각의 인라인뷰를 먼저 실행시키고 조인이 완료된 인라인뷰 끼리
다시 조인하는 것이 Bushy Tree Plan의 최적 활용 방안이다.
하지만 원래의 목적과는 상관없이 Driving 테이블에 훌륭한 Filter 조건이 있는경우 Nested Loop Join 이나
Hash Join 시에 Left Deep Tree Plan 으로 유도가 안될때(조인조건의 문제가 제일 많음)
Bushy Tree Plan 으로 유도하여야 한다.
오늘의 예제는 후자를 나타낸것이다.

HASH 조인은 DW 뿐아니라 OLTP 의 배치업무및 심지어 OLTP 의 조회성 업무에까지 적용범위를
넓혀가고 있기 때문에 그중요성은 매우크다고 할수 있다.
한가지 아쉬운점은 개발자및 DBA, 튜너 들이 PLAN 을 보고 Driving 테이블만 제대로 나오면 검증하지 않고
그냥 넘어간다는 것이다.
좀더 꼼꼼한 튜너는 SWAP_JOIN_INPUTS 이나 LEADING, ORDERED 등의 힌트로 SIZE가 적은 집합순으로
HASH 조인을 하고 실행후 결과에서 모든 HASH 조인이 Optimal Pass(주1) 가 나오면 그것으로 튜닝을 끝낸다.
하지만 HASH 조인은 다른 조인들과 달라서 몇가지 더 검증해야될 부분이 있다.
오늘은 그중에 한가지에 대하여 소개한다.

실행환경 : Oracle 10g R2

아래는 테스트시 필요한 테이블과 인덱스 생성및 통계정보를 gathering 하는 스크립트 이다.

1.테스트 테이블및 인덱스 생성

3.Right Deep Tree, Left Deep Tree Plan 의 개념

먼저 테스트를 수행하기전에 간단하게 Right Deep Tree, Left Deep Tree Plan 에 대하여 알아보겠다.

-------------------------------------
| Id  | Operation            | Name |
-------------------------------------
|*  1 |  HASH JOIN           |      |
|*  2 |   TABLE ACCESS FULL  | T1   |
|*  3 |   HASH JOIN          |      |
|   4 |    TABLE ACCESS FULL | T2   |
|*  5 |    HASH JOIN         |      |
|   6 |     TABLE ACCESS FULL| T3   |
|   7 |     TABLE ACCESS FULL| T4   |
-------------------------------------

위와 같은 PLAN 을 오라클을 많이 사용하는 사람이라면 자주 보았을 것으로 예상한다.
아래로 내려갈수록 Operation 이 오른쪽으로 밀려난다.
이것이 Right Deep Tree Plan 이다.
필자는 Hash 조인을 사용하면서  T1 을 엑세스 할때 많은 양의 데이터가 filter 되는 경우 
Right Deep Tree Plan 이 나오면 일단 부정적으로 보고 튜닝을 시작한다.
왜냐하면 대부분의 경우 악성 plan 이기 때문이다.
위 plan 의 조인순서는 아래와 같다.
1. T3 와 T4를 조인한다.
2. T2 와 1번의 결과집합을 조인한다.
3. T1 과 2번의 결과집합을 조인한다.
따라서 실제 조인순서는 T3 --> T4 --> T2 --> T1 이지만 많은수의 개발자나 DBA 들은 이점을 놓치고 있다.
즉 T1 이 Driving 이면서 많은 양의 데이터가 filter 되는 경우 위와 같은 plan 이나오면 T1 과의 조인은
맨마지막에 실행되기 떄문에 T3 --> T4 --> T2 조인이 처리될때 까지 데이터의 범위를 줄일수가 없다.
다시말하면 전체건에 대하여 2번을 조인한 후에 T1 과 조인 하기 때문에 최악의 Hash 조인이라는 것이다.

아래의 PLAN 은 위의 plan 과 반대이며 Left Deep Tree PLAN 이라고 한다.
아래 plan 의 조인순서는 당연히 T1 --> T2 --> T3 --> T4 이며 T1 테이블의 엑세스 및 Filter 과정에서
많은수의 데이터가 FILTER 된다면 최적의 PLAN 이다.

-------------------------------------
| Id  | Operation            | Name |
-------------------------------------
|*  1 |  HASH JOIN           |      |
|*  2 |   HASH JOIN          |      |
|*  3 |    HASH JOIN         |      |
|*  4 |     TABLE ACCESS FULL| T1   |
|   5 |     TABLE ACCESS FULL| T2   |
|   6 |    TABLE ACCESS FULL | T3   |
|   7 |   TABLE ACCESS FULL  | T4   |
-------------------------------------

이제 2개의 plan 을 테스트 해보자.
아래의 Right Deep Tree Plan 테스트는 실제환경에서 나오지 말아야 할 PLAN 을 테스트 한것이다.
테스트를 위하여 힌트를 주어 일부러 악성 plan 을 만들었다.
특히 힌트부분을 주목하라.

4.Right Deep Tree Plan 테스트

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

-------------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | A-Rows |   A-Time   | Used-Mem |
-------------------------------------------------------------------------------
|*  1 |  HASH JOIN           |      |      1 |     90 |00:00:00.13 | 1212K (0)|
|*  2 |   TABLE ACCESS FULL  | T1   |      1 |     90 |00:00:00.04 |          |
|*  3 |   HASH JOIN          |      |      1 |  10000 |00:00:00.18 |   11M (0)|
|   4 |    TABLE ACCESS FULL | T2   |      1 |  10000 |00:00:00.03 |          |
|*  5 |    HASH JOIN         |      |      1 |  10000 |00:00:00.10 |   11M (0)|
|   6 |     TABLE ACCESS FULL| T3   |      1 |  10000 |00:00:00.03 |          |
|   7 |     TABLE ACCESS FULL| T4   |      1 |  10000 |00:00:00.03 |          |
-------------------------------------------------------------------------------

위 SQL 은 FILTER 조건 (T1.N1 < 50) 이 있지만 마지막 조인시 까지 조인건수를 줄일수 없으므로
hash area size 를 많이 쓰게 된다. 
그결과 수행속도가 느리며 특히 메모리 사용량(Used-Mem 참조)이 23.2(11MB + 11MB + 1212K) MB 에 달한다.
Hash 조인 3번의 수행시간(A-Time 참조) 또한 0.13 + 0.18 + 0.10 으로 0.41 초나 걸렸다.
3번의 조인 모두 Optimal Pass(빨강색 부분이 0 이면 Optimal 임) 라고 방심하면 안된다.
여기에는 엄청난 비효율이 숨어 있다.

아래는 Left Deep Tree PLAN 으로 튜닝하여 테스트를 수행한 결과이다.

4.Left Deep Tree PLAN 테스트

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

-------------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | A-Rows |   A-Time   | Used-Mem |
-------------------------------------------------------------------------------
|*  1 |  HASH JOIN           |      |      1 |     90 |00:00:00.07 | 1220K (0)|
|*  2 |   HASH JOIN          |      |      1 |     90 |00:00:00.10 | 1229K (0)|
|*  3 |    HASH JOIN         |      |      1 |     90 |00:00:00.07 | 1229K (0)|
|*  4 |     TABLE ACCESS FULL| T1   |      1 |     90 |00:00:00.04 |          |
|   5 |     TABLE ACCESS FULL| T2   |      1 |  10000 |00:00:00.03 |          |
|   6 |    TABLE ACCESS FULL | T3   |      1 |  10000 |00:00:00.03 |          |
|   7 |   TABLE ACCESS FULL  | T4   |      1 |  10000 |00:00:00.03 |          |
-------------------------------------------------------------------------------

Used-Mem 컬럼의 사용량이 Right Deep Tree Plan 에 비해 대폭 줄어든것에 주목해야 한다.
Hash 조인시 사용한 총 hash area size 사용량 = 1220K + 1229K + 1229K (약 3.6 MB) 이다.
Left Deep Tree PLAN 을 사용하는것으로 바꾸니 놀랍게도 메모리 사용량이 6.5 배나 줄었다.
또한 A-Time 의 수행시간도 약 2배나 차이가 난다.
테스트를 짧게 끝내기위해 각 테이블을 만건으로 한정 했지만 건수가 많아 질수록 차이는
더 벌어질 것이다.

결론 :
T1.N1 < 50 과 같은 훌륭한 filter 조건이 있는 경우에 통계정보의 부재, 부적절한 조인조건등 여러가지
이유로 인하여 Right Deep Tree Plan 이 나온다면 튜닝을 하여 Left Deep Tree  Plan 으로 만들어야 한다.
물론 예외적인 경우 Left Deep Tree PLAN 으로 유도 하는것이 불가능 할수도 있다.
하지만 그때에도 Bushy Tree Plan(주2) 등으로 유도하여 Hash 조인을 튜닝 하여야 한다.
그렇지 않을 경우 과도한 메모리 사용과 수행속도 저하를 막을수 없다.


주1 : Optimal Pass 란 Hash 조인시 build 테이블(Driving)을 Scan 하면서 메모리에 적재하는 과정을
       거치는데 이때 메모리 공간(Hash Area Size) 가 부족하면 메모리에 적재하지 못하고
       Temp 영역(DISK)에 적재를 하는 비효율이 있지만  Optimal Pass 는 메모리 공간이 충분하여
       Disk 작업이 없는 상태를 말한다.
주2: 간단히 설명하면 T1 과 T2 를 조인하고 T3 와 T4 를 조인하여 2개의 결과 집합을 조인하는 방식임.
     자세한 내용은 Hash 조인시 Bushy tree Plan 유도하기 를 참조하기 바란다.

필자는 며칠전 고객에게 아래와 같은 문의 사항을 받았다.


필자는 고객에게 Hypothetical Functions(가정에 근거한 함수) 를 추천 하였다.
이상하게도 개발자들 뿐아니라 DBA들 또한 Ranking Family (Rank/Dense Rank/Percent rank 등등) 함수는
즐겨사용함 에도 불구하고 가정함수(Hypothetical Functions) 는 쓰지 않는다.

아래는 2001년에 고객이 매출한 금액별로 순위를 정한 SQL 문이다.


  CUST_NBR CUST_SALES       RANK DENSE_RANK  CUME_DIST PERCENT_RANK
---------- ---------- ---------- ---------- ---------- ------------
.....................................................      중간생략
         1    1151162         23         21 .766666667    .75862069
         8    1141638         24         22         .8   .793103448
        16    1068467         25         23 .833333333   .827586207
        22    1036146         26         24 .866666667   .862068966
        21    1020541         27         25         .9   .896551724
        28     986964         28         26 .933333333   .931034483
         7     971585         29         27 .966666667   .965517241
        29     903383         30         28          1            1

분석함수의 전형적인 결과이다.
위쿼리의 결과를 보고 오라클에게 이렇게 질문할수 있다.
만약 내가 백만원(1000000 원)의 매출(CUST_SALES)을 올렸다면 나의 순위(RANK)는 몇등인가?
위결과에서 밑줄친 부분을 보고 생각을 해보면 나의 순위는 28위임을 알수 있다.
즉 백만원이 28등 이라는것만 알면되는 요구사항이다. --> 결과가 한줄로 나와야 한다.
하지만 이것을 SQL 로 구현하기란 만만치 않다는걸 알수 있다.
이럴때 아래처럼 Hypothetical Functions을 사용한다면 손쉽게 불필요한 고객들의 정보를 제외하면서
보고싶은 나의 순위만 화면에 출력된다.

SELECT
  RANK(1000000) WITHIN GROUP (ORDER BY SUM(tot_sales) DESC) hyp_rank,
  DENSE_RANK(1000000) WITHIN GROUP (ORDER BY SUM(tot_sales) DESC) hyp_dense_rank,
  CUME_DIST(1000000) WITHIN GROUP (ORDER BY SUM(tot_sales) DESC) hyp_cume_dist,
  PERCENT_RANK(1000000) WITHIN GROUP (ORDER BY SUM(tot_sales) DESC) hyp_pcent_rank
FROM orders
WHERE year = 2001
GROUP BY cust_nbr;

  HYP_RANK HYP_DENSE_RANK HYP_CUME_DIST   HYP_PCENT_RANK
---------- -------------- ------------- ----------------
        28             26    .903225806               .9

결과가 너무나 환상적이지 않은가?
원하는 결과 한줄만 나오는 것은 물론이고 순위는 28위(Dense Rank 로는 26위) 라고 친절하게 나온다.

결론:
위의 상황과 같이 순위나 백분율 등을 예측하고 싶을때 Hypothetical Functions 을 쓰지 않는다면
고생을 하는것은 물론이고 성능이 좋을리도 없음을 기억하자.

편집후기 : 참고로 필자는 Hypothetical Function 이라는 말을 싫어한다.
                What IF Function 이라고 기억해야 머리에 오래남을 것이다.

이글을 쓰기전에 필자는 QUEST 사와 아무런 관계가 없음을 먼저 밝혀둔다.
이글의 목적은 개발자, DBA, 튜닝 컨설턴트 들이 이런 리포트들을 뽑기위하여 OEM(엔터프라이즈 매니져) 를
사용할수 없는경우 발을 동동 구르고 있다는 사실이다.
하지만 사실은 우리주위에 있는 가장흔한 쿼리툴(TOAD) 로도 쉽게 출력이 가능하다.
아래처럼 토드에서 Database --> Monitor --> ADDM/AWR 을 클릭하면
1.AWR(Automatic Workload Repository),
2.ADDM(AUTOMATIC DATABASE DIAGNOSTIC MONITOR),
3.ASH(Active Session History) 를 관리및 출력할수 있는 화면이 나온다.
참고로 AWR 리포트는 9i 이하버젼에서 사용한 Statspack 리포트의 진화된 버젼이라고 할수 있다.
 

아래는 AWR 의 관리화면이다.
Snapshot Interval 은 10분이며 최장 보관기간은 30 일 임을 알수 있다.
이화면에서 관리및 변경이 가능하다.

아래는 ASH 를 출력할수 있는 탭을 보여준다.
그림 FROM ~ TO 로 START 와 END TIME 을 지정하고 RAC 인경우 인스턴스를 지정하고 상단에 있는 연두색 버트늘 누르면 ASH 보고서가 쉽계 출력된다.

아래는 AWR 보고서 화면이다.
SNAPSHOT 구간을 선택하고 인스턴스를 선택하고 상단의 연두색 버튼을 누르면 쉽게 보고서가 출력된다.


아래는 ADDM 리포트이다.
AWR 리포트와 출력하는 방법이 같다.


이상으로서 거칠것 없이 AWR 과 ASH, ADDM 의 보고서를 출력 해보았다.
복잡한 절차없이 몇번의 클릭만으로 3종류의 보고서를 출력하였다.
이 3개의 보고서를 잘활용하면 특정구간대의 DBMS 차원의 성능진단및 Wait Event, TOP SQL,
TOP Object 등을 도출해 낼수 있다.
특히 ADDM 보고서에는 문제가 무엇이고 어떻게 조치해야 되는지 까지 조언을 해준다.
또한 ASH 나 AWR 의 보고서에는 TOP 이벤트를 발생시키는 SQL 을 찾아줄뿐 아니라
연관된 TOP OBJECT 까지 찾아준다.
예를 들면 특정 SQL 이 Index Contention 을 발생시킬경우에 관련 인덱스가 무엇인지 쉽게 찾을수 있다.
오늘 소개한것은 토드의 일부기능에 불과하다.
우리주위에 흔히 볼수 있는 쿼리툴도 찾아보면 멋진 기능들이 많이 숨어있다.
단지 어디에 뭐가 있는지 몰라서 사용하지 않을뿐.....

필자가 7월달에  Parallel Query 의 조인시 Row Distribution 이라는 글을 통하여 Parallel + Join 시에
튜닝방법을 설명 한바 있다.
오늘은 최적의 Parallel Join 을 하기 위하여 또다른 튜닝방법을 제시한다.
필자가 이글을 쓰는 원래의 용도는 사내 DB 컨설턴트 들을 교육시키는데 사용하는 것이다.
그렇기 때문에 어려워도 실망하거나 우울증에 걸리지 말자.
최근 최진실씨 사태등등 해서 심히 걱정된다.^^
Parallel Join Filter 를 설명하려고 하는데 용어설명부터 해야겠다.
왜냐하면 어떤곳에서는 Parallel Join Filter 라고 이야기 하고 또다른 곳에서는 Bloom Filter 라고 하는데
그이유는 알고리즘을 최초로 개발한 사람이 오라클사의 Burton H. Bloom 이라는 사람이고 이는 1970 년의
일이다.
실제로 실행계획상에 Bloom 의 이름을 따서 필터명이 BF0000, BF0001, BF0003 .... 이렇게 생성된다.
어쨋든 이런사유로 인하여 2개의 용어가 혼용되는데 여기서는 Parallel Join Filter (힌트로는 px_join_filter) 만 사용할것이다.
아래는 테이블 생성 스크립트 이다.
테스트를 위하여 2개의 테이블이 필요하다.

제약사항
 Parallel Join Filter 는 10gR2 이상에서 실행가능함.

테스트용 테이블 생성 스크립트

테이블이 생성 되었으므로 테스트 스크립트를 실행시켜보자.

explain plan for
 SELECT /*+ full(t1) full(t2) parallel(t1 8) parallel(t2 8) leading(t1) use_hash(t2) NO_PX_JOIN_FILTER(t2) */
        *
  FROM emp_1 t1,
            emp_2 t2
 WHERE t1.empno = T2.empno
       and t1.BIG_ENAME > '1'  ;

아래 PLAN 을 설명하기전에 일단 TQ(Table queues) 개념을 알아야 한다.
복잡한 plan 같지만 원리를 알고 나면 간단하게 해석 할수 있다.
TQ 는 processes간의 데이터를 주고받는 기능을 한다.
하나의 TQ 는 여러개의 parallel Slave 를 가진다.
아래 PLAN 을 보면 TQ 가 3개(:TQ10000, :TQ10001, TQ10002 ) 생성되어 있다.(파란색 부분)

--------------------------------------------------------------------------------------
| Id  | Operation               | Name     | Cost (%CPU)|    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          | 12132   (1)|        |      |            |
|   1 |  PX COORDINATOR         |          |            |        |      |            |
|   2 |   PX SEND QC (RANDOM)   | :TQ10002 | 12132   (1)|  Q1,02 | P->S | QC (RAND)  |
|*  3 |    HASH JOIN BUFFERED   |          | 12132   (1)|  Q1,02 | PCWP |            |
|   4 |     PX RECEIVE          |          |  3054   (0)|  Q1,02 | PCWP |            |
|   5 |      PX SEND HASH       | :TQ10000 |  3054   (0)|  Q1,00 | P->P | HASH       |
|   6 |       PX BLOCK ITERATOR |          |  3054   (0)|  Q1,00 | PCWC |            |
|*  7 |        TABLE ACCESS FULL| EMP_1    |  3054   (0)|  Q1,00 | PCWP |            |
|   8 |     PX RECEIVE          |          |  3054   (0)|  Q1,02 | PCWP |            |
|   9 |      PX SEND HASH       | :TQ10001 |  3054   (0)|  Q1,01 | P->P | HASH       |
|  10 |       PX BLOCK ITERATOR |          |  3054   (0)|  Q1,01 | PCWC |            |
|  11 |        TABLE ACCESS FULL| EMP_2    |  3054   (0)|  Q1,01 | PCWP |            |
--------------------------------------------------------------------------------------

각 Id 단위의 설명 :
1. Q1,00 의 slave process 들은 emp_1  테이블을 full scan 하면서 t1.BIG_ENAME > '1'  조건을 FILTER
   하였고 process 간의 통신을 위하여 걸러진 데이터를 Q1,02 에 보낸다.
    (Id 기준으로 5~7 이 여기에 해당된다)
2. Q1,02 의 slave process 들은 1번에서 받은 데이터들을 이용해 hash table 을 만든다.
    (Id 기준으로 3~4 가 여기에 해당된다)
3. Q1,01 의 slave process 들은 emp_1  테이블을 full scan 하고 읽은 데이터를 Q1,02 에 보낸다.
    (Id 기준으로 9~11 가 여기에 해당된다)
4.  Q1,02 의 slave process 들은 3번에서 던진 데이터를 받아서 미리 만들어진 hash 테이블을
     검색하면서 조인작업을 진행하고 결과를 Query Cordinator 에 보낸다.
     (Id 기준으로 2~3 이 여기에 해당된다)
5. Query Cordinator 는 각 TQ 로 부터 데이터를 받아서 취합한후에 결과를 Return 한다.
     (Id 기준으로 0~1 이 여기에 해당된다)

위설명을 도식화 하면 아래그림과 같다.
다만 위의 SQL 대로라면 각 TQ 내의 SALVE 는 8개 여야 하지만 화면관계상 2개로 줄여서 나타 내었다.

위그림을 보면 무언가 비효율적인 것을 발견하게 된다.
Q1,01 의 모든 SLAVE 들은 Q1,02 의 모든 SLAVE 들에게 똑같은 데이터를 던져서 체크한후에 만족하면
조인에 성공하고 그렇지 않으면 조인에 실패하는 프로세스를 가지게 된다.
위쿼리를 예를들면 사번 10000100을  Q1,02 의 SLAVE 가 8개라면 8번 던져서 1/8 확률로 조인에 성공하면
다행이지만 아예조인에 실패할 확률도 있는것이다.
이런 비효율을 없애는 것이 Parallel Join Filter 이다.
Parallel Join Filter 의 개념은 Q1,01(후행테이블의 TQ) 이 Q1,02 에게 데이터를 전달하기전에 불필요한
데이터를 걸러 낸다는 것이다.
이제 parallel join filter 를 적용시켜보자.

explain plan for
 SELECT /*+ full(t1) full(t2) parallel(t1 8) parallel(t2 8) leading(t1) use_hash(t2) PX_JOIN_FILTER(t2) */
        *
  FROM emp_1 t1,
       emp_2 t2
 WHERE t1.empno = T2.empno
       and t1.BIG_ENAME > '1'  ;

필자의 연구결과 t1.ename > '1' 등 t1 의 filter predicate 가 없으면 Parallel Join Filter 는 결코 작동하지 않는다.
그럴때는 t1.empno > 0 등의 결과값의 영향을 끼치지 않는 filter 조건을 주는 트릭을 생각할수 있다.
또하나의 Tip 은 PX_JOIN_FILTER 사용시 후행테이블을 사용하여야 한다는것이다.
왜냐하면 아래의 PLAN 을 보면 Filter 의 생성은 t1 에서 하지만(id 가 4번) 사용은 t2 쪽(id 11번)에서
하기때문에 PX_JOIN_FILTER(t1) 을 주면 절대 filter operation 이 생기지 않는다.

---------------------------------------------------------------------------------------
| Id  | Operation                | Name     | Cost (%CPU)|    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |          | 12132   (1)|        |      |            |
|   1 |  PX COORDINATOR          |          |            |        |      |            |
|   2 |   PX SEND QC (RANDOM)    | :TQ10002 | 12132   (1)|  Q1,02 | P->S | QC (RAND)  |
|*  3 |    HASH JOIN BUFFERED    |          | 12132   (1)|  Q1,02 | PCWP |            |
|   4 |     PX JOIN FILTER CREATE| :BF0000  |  3054   (0)|  Q1,02 | PCWP |            |
|   5 |      PX RECEIVE          |          |  3054   (0)|  Q1,02 | PCWP |            |
|   6 |       PX SEND HASH       | :TQ10000 |  3054   (0)|  Q1,00 | P->P | HASH       |
|   7 |        PX BLOCK ITERATOR |          |  3054   (0)|  Q1,00 | PCWC |            |
|*  8 |         TABLE ACCESS FULL| EMP_1    |  3054   (0)|  Q1,00 | PCWP |            |
|   9 |     PX RECEIVE           |          |  3054   (0)|  Q1,02 | PCWP |            |
|  10 |      PX SEND HASH        | :TQ10001 |  3054   (0)|  Q1,01 | P->P | HASH       |
|  11 |       PX JOIN FILTER USE | :BF0000  |  3054   (0)|  Q1,01 | PCWP |            |
|  12 |        PX BLOCK ITERATOR |          |  3054   (0)|  Q1,01 | PCWC |            |
|  13 |         TABLE ACCESS FULL| EMP_2    |  3054   (0)|  Q1,01 | PCWP |            |
---------------------------------------------------------------------------------------

위 plan 은 원래의 PLAN(filter 적용전 plan) 에서 parallel join filter 부분만이 추가 되었다.(파란색 부분)
1. id 4 에서 parallel Join filter 를 생성(create) 하였고 filter 명은 :BF0000 이다.
2. id 11 에서 생성된 :BF0000 filter 를 사용하였다.

주의사항은  parallel Join filter 를 무조건 사용하지말라는 것이다.
걸러지는 데이터가 별로 없을경우 빨라지지도 않을 뿐더러  filter 부하가 더클수 있기 때문이다.
다음의 2가지 경우에  parallel Join filter 를 사용하여야 한다.
1. 많은양의 데이터가 조인에 실패하는경우
2. 1번을 만족하면서 RAC 에서 multi-node 로 Parallel Query 를 실행한경우.
    이경우는대부분 DOP(Degree Of Parallelism)가 클때 발생하며 추가적인 Network I/O 가 발생하므로
    parallel join filter 를 적용할경우 획기적인 성능향상을 기대할수 있다.

parallel Join filter에 의해서 filter 된 데이터를 보려면 아래와 같이 v$sql_join_filter 뷰를 사용하면된다.

select filtered, probed, proved - filtered as sent
   from  v$sql_join_filter
where qc_session_id = sys_context('userenv', 'sid');

결론 :
Parallel Join distribution 과 Parallel join filter 을 적절히 이용하면 최적화된 Parallel Join Query를 만들수 있다.
다시한번 말하지만 꼭필요한 경우에만 이런종류의 힌트를 사용하여야 한다.
 
편집후기 :만약 parallel Join filter 로직에 관심이 있어서 직접 구현하려면 아래의 1번 문서를 참조하기 바란다.

Reference :
1.Bloom Filters ( Cristian Anatognini )
2.Oracle Corp Manual (Data Warehousing Guide 11g)

쿼리 튜닝시 가끔 오라클이 똑똑하다고 느낄때가 있다
오늘은 그중에서 Query Transformation 이 되는 경우를 소개한다.
아래의 스크립트를 보자.

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |   205 |  4715 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |         |   205 |  4715 |     4   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    13 |     1   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS FULL          | EMP     |   205 |  2050 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

위스크립트를 보면  select 절에 b.dname 을 사용한다.
하지만 아래를 스크립트를 보면 인라인뷰 내에서는 dname 을 select 하지만 최종 select 절에서
b.dname 을 빼고 실행한 결과이다.
물론 아래상태에서 VIEW MERGE 가 진행 될것이다.

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |   205 |  2665 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS      |         |   205 |  2665 |     3   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN| PK_DEPT |     1 |     3 |     0   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| EMP     |   205 |  2050 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------

위 plan 을 보면 dept 에 rowid 로 테이블 엑세스 하는 operation 이 사라졌다.
신기하지 않은가?
오라클이 알아서 최종 select list 에서 d.dname 제거 한것을 알아채고 Query Transformation 을 하여
인덱스만 access 한것이다.
즉 아래의 스크립트 처럼 옵티마이져가 쿼리를 수정한것이다.



물론 옵티마이져가 사람이라면 아예 인라인뷰 b 를 빼고 아래처럼 재작성 할것이다.
아래처럼 해도 결과는 똑같기 때문이다.

오늘은 지난시간에 논의 했던 SubQuery Flattening 에 이어서 쿼리변형이 발생하지 않는 Access 서브쿼리 와 Filter 서브쿼리, Early Filter 서브쿼리 에 대해서 이야기 할것이다.

튜닝관점의 서브쿼리의 분류

1.Unnesting 서브쿼리 : 참조
                 
2.Semi Join/Anti Join : 참조

3.Access 서브쿼리 : 쿼리변형이 없음
    1)흔히말하는 제공자 서브쿼리임.(서브쿼리부터 풀려서 메인쿼리에 값이 제공된다.)
    2)위의 1번 2번과 다르게 Plan 에 메인쿼리와 서브쿼리의 Join 이 없다.
    3)힌트: 특별한 힌트없음.
               다만  /*+ no_unnest */ 를 사용하여 SubQuery Flattening 을 방지하고
               서브쿼리로부터 제공되는 메인쿼리의 컬럼에 인덱스가 생성되어 있으면됨.
    4) 주의사항: corelate 서브쿼리는 제공자 서브쿼리가 될수 없음.

4.Filter 서브쿼리 : 쿼리변형이 없음
    1)흔히 말하는 확인자 서브쿼리임.(메인쿼리의 값을 제공받아 서브쿼리에서 체크하는 방식임)
    2)위의 1번 2번과 다르게 Plan 에 메인쿼리와 서브쿼리의 Join 이 없고 Filter 로 나온다.
    3)Filter SubQuery 의 특징은 메인쿼리의 From 절에 있는 모든 테이블을 엑세스후에 가장마지막에
      서브쿼리가 실행된다는 것이다.
    4) 힌트: 특별한 힌트없음.
                다만  /*+ no_unnest */ 를 사용하여 SubQuery Flattening 을 방지하고
                메인쿼리로부터 제공되는 서브쿼리의 조인컬럼에 인덱스가 생성되어 있으면됨.

5.Early Filter 서브쿼리 : 쿼리변형이 없음
    1)Filter SubQuery 와 같은 방식이지만 서브쿼리를 최대한 먼저 실행하여 데이터를 걸러낸다.
    2)힌트 : 메인쿼리에 push_subq 힌트사용 (10g 이후부터는 서브쿼리에 힌트사용해야함)
    3)주의사항: 많은 튜닝책에서 "Push_subq 힌트를 사용하면 제공자 서브쿼리를 유도한다" 라고
                     되어 있으나 이는 잘못된 것이다.
                     push_subq 힌트를 사용하면 확인자 서브쿼리(Filter 서브쿼리)를 유도하지만 최대한
                     먼저 수행된다.

아래의 스크립트를 보자

3.Access 서브쿼리  select    small_vc  from     min_max mm1 where     mm1.id_parent = 100   and     mm1.id_child = (                     select    max(mm2.id_child)                     from      min_max mm2                     where     mm2.id_parent = 100           )  ;

 

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

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

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

|   0 | SELECT STATEMENT               |         |     1 |   108 |     4 |

|   1 |  TABLE ACCESS BY INDEX ROWID   | MIN_MAX |     1 |   108 |     2 |

|*  2 |   INDEX UNIQUE SCAN            | MM_PK   |     1 |       |     1 |

|   3 |    SORT AGGREGATE              |         |     1 |     8 |       |

|   4 |     FIRST ROW                  |         |    10 |    80 |     2 |

|*  5 |      INDEX RANGE SCAN (MIN/MAX)| MM_PK   |    10 |    80 |     2 |

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

위의 plan 을 보면 실행순서가 헷갈릴수 있다.
결론을 이야기 하자면 id 기준으로 실행 순서는 5 -> 4 -> 3 -> 2 -> 1 이다.

즉 맨밑에서부터 위로 실행된다. (서브쿼리부터 실행해서 메인쿼리에 1건을 제공하였다)
특정일자에 max 일련번호를 찾아서 처리해야할때 많이 사용하는 SQL 패턴이다.
corelate 서브쿼리가 아니고 서브쿼리로부터 제공되는 메인쿼리의 컬럼에 인덱스가 생성되어 있는 경우만이
Access 서브쿼리로 풀린다.(mm1.id_child 컬럼에 인덱스가 있어야 한다)

다음의 두가지 경우에서만 Access 서브쿼리를 사용해야한다.
1) 서브쿼리의 엑세스건수가 적고 서브쿼리의 결과를 제공받은 메인쿼리도 엑세스 건수가 적어야 한다.

2) 비록 서브쿼리의 엑세스 건수가 많지만 그결과를 제공받은 메인쿼리의 엑세스 건수가 적다면 사용할수 있다.
    왜냐하면 Access 서브쿼리는 단한번만 수행되기 때문이다.   
    이경우 메인쿼리의 테이블이 mm1.id_child 컬럼기준으로 클러스트링 팩터가 좋다면 서브쿼리가 힘을 얻게 된다.
    하지만 이경우는 반드시 Semi Join 이나 Unnesting 서브쿼리, Filter 서브쿼리등과 성능을 비교해 보아야 한다.

4.Filter 서브쿼리 select    small_vc from      t1 where     n2 between 100 and 200   or      exists    (                     select    null                     from      t2                     where     t2.n1 = t1.n1                     and       t2.mod1 = 15           );

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

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

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

|   0 | SELECT STATEMENT               |       |   597 | 11343 |    28 |

|*  1 |  FILTER                        |       |       |       |       |

|   2 |   TABLE ACCESS FULL            | T1    | 10000 |   185K|    28 |

|*  3 |   TABLE ACCESS BY INDEX ROWID  | T2    |     1 |     7 |     2 |

|*  4 |    INDEX RANGE SCAN            | T2_PK |     1 |       |     1 |

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

메인쿼리의 WHERE 절에 조건이 있고 OR 로 EXISTS 를 사용하게 되면 CBQT(Cost Based Query Transformation) 가 작동을 하지않는다.
따라서 위의 경우처럼 OR 가 있는 서브쿼리는 SubQuery Flattening 이 발생하지 않고 확인자 서브쿼리로 풀리게 된다.
위의 경우와는 반대로 10g 에 와서는 옵티마이져가 왠만하면  Semi Join 이나 서브쿼리 Unnesing등의 쿼리변형을 하게되므로 
대부분의 경우 강제로 NO_UNNEST 힌트를 사용해야지만  Filter 서브쿼리로 풀리게 된다.
주의할점은 10g 에서 서브쿼리가 filter 로 풀릴경우 Plan 에서는 Filter Operation 이 사라지는 경우가 많이 있다.
Plan 이 잘못된것이 아니니 참고하기 바란다.
상식이지만 노파심에서 다시한번 이야기 하지만 Filter 서브쿼리는 메인쿼리로부터 조인되는 컬럼(t2.n1)에 반드시 인덱스가
만들어져 있어야 한다.
그렇지 않으면 성능은 기대할수 없다.

Filter 서브쿼리는 다음의 두가지 경우에 사용하여야 한다.
1) 메인쿼리의 where 절에 똑똑한 조건들이 많아서 엑세스 건수가 적을때
    이경우는 filter Operation 이 몇번 발생하지 않게 되므로 당연히 유리하다.

2) 메인쿼리는 비록 엑세스건수가 많지만 서브쿼리의 체크조건이 True 인경우가 많은경우
   이경우는 특히 부분범위처리시 유리하다.
   왜냐하면  비록 건수가 많지만 서브쿼리의 체크조건이 True 인경우가 많으므로 화면에 바로바로 나오게 된다.
   하지만 배치 프로그램처럼 전체범위를 목적으로 하는경우는 성능이 저하되므로 주의하여야 한다.
   이때도 서브쿼리의 t2.n1 컬럼기준으로 서브쿼리 테이블의 클러스트링 팩터가 좋다면 성능이 향상되는데 물론 메인쿼리가
   sort 되는경우 이거나 인덱스의 사용등으로 자동 sort 가 되어 서브쿼리에 데이터가 공급되는 경우에 한해서다.

5.Early Filter 서브쿼리   SELECT par.small_vc1, chi.small_vc1   FROM PARENT par,             CHILD chi  WHERE par.id1 BETWEEN 100 AND 200    AND chi.id1 = par.id1    AND EXISTS (                         SELECT /*+ push_subq  */                                       NULL                            FROM subtest sub                          WHERE sub.small_vc1 = par.small_vc1                             AND sub.id1 = par.id1                             AND sub.small_vc2 >= '2'                         );

 

Early Filter 서브쿼리를 설명하려면 최소한 메인쿼리에 2개의 테이블이 있어야 한다.
위의 서브쿼리를 보면 PARENT 쪽 메인쿼리만 풀리면 서브쿼리가 동작할수 있다.
다시말하면 CHILD 쪽의 컬럼이 서브쿼리에 없으므로 PARENT 쪽의 컬럼만 상수화 되면 서브쿼리가 작동할수 있게
되는 것이다.
실행순서는 PLAN 에서 보는것과 같이 PARENT -> subtest -> CHILD 이다 하지만 불행하게도 오라클은 대부분의 Filter 쿼리에서 서브쿼리는 가장마지막에 작동한다.
즉 대부분의  Filter 쿼리에서 PARENT ->CHILD -> subtest 순으로 풀리게 된다.
이때 사용할수 있는 힌트가 push_subq 힌트이다.
최대한 먼저 데이터를 걸러내어 그다음 테이블과 조인시 건수를 줄이고 싶을때 탁월한 효과를 내는 힌트이다.
반드시 여러분의 환경에서 여러분들의 쿼리로  push_subq 힌트가 있을때와 없을때의 차이를 느껴보기 바란다.

결론:
이상으로 서브쿼리의 5가지 분류에 대하여 알아 보았다.
서브쿼리는 실제 프로젝트 환경에서 자주 사용하므로 5개의 분류는 나름대로 특징과 장단점이 있어서 적재적소에 사용할경우
엄청난 이득을 가져올수 있지만 그렇지 않은경우 독이 될수 있음을 기억하자.

Reference :
1)Query Optimization in Oracle Database10g Release 2(White Paper)
2)COST BASED QUERY TRANSFORMATIONS CONCEPT
   AND ANALYSIS USING 10053 TRACE(Riyaj Shamsudeen)
3)Cost Based Oracle Fundamentals(Jonathan Lewis) with Blog (http://jonathanlewis.wordpress.com)

편집후기 :
요즘 필자의 프로젝트가 막바지로 치달리고 있어서 엄청 바쁘지만 블로그는 블로그대로 관리를 해야하니 엄청 스트레스가 된다.
블로그만 쓰며 살수는 없는걸까?^^

튜닝 컨설팅을 하다보면 개발자들이 서브쿼리에 대하여 많은 관심을 보이며 또한 자주 사용하곤 한다.
하지만 정작 튜닝관점및  외형적인 관점에서 서브쿼리에 대하여 정의를 내릴수 있는 사람은 많지않다.  
오늘은 서브쿼리에 대하여 정의를 내려보자.

외형적관점에서 서브쿼리의 종류는 2가지 밖에 없다.
1.Non Corelate 서브쿼리  : (서브쿼리 내에 서브쿼리와 메인쿼리의 조인절이 없음)
2.Corelate 서브쿼리: (서브쿼리 내에 서브쿼리와 메인쿼리의 조인절이 있음)
그렇다면 튜닝관점에서 서브쿼리를 어떻게 분류해야 할까?
튜닝관점의 서브쿼리는 아래처럼 5가지로 분류할수 있다.

기획팀에서 이대리가 전산실에 와서 부탁을 한다.

기획실 이대리:"월별 부서별로 2002년도 실적을 좀 뽑아주실수 있나요?"

전산실 김대리:"네 오늘저녁 6시쯤 오시면 실적 보고서를 드릴수 있습니다."

기획실 이대리:"실적을 만드실때 주의사항이 있습니다.
                     월별 부서별로 실적을 뽑을때 만약 20번 부서에서 5월, 7월에
                     실적이 없다고 하더라고 5월,7월 실적을 0 으로 표시해주세요."

전산실 김대리:"네 알겠습니다. 그것은 별로 어렵지 않습니다."   

년월만 들어있는 테이블과 월별부서별실적 테이블의 구조는 아래와 같다.

월별 부서별 실적테이블의 2002년 실적은 다음 그림과 같다.
모든 월에 실적이 있는것은 아니다.(예를 들면 10번 부서는 2002년도에 1,3,6,7,8,10,11 월에 실적이 없다. )














30번 부서부터는 지면관계상 그림에서 생략하였다.




기획실 이대리의 요구사항은 아래그림과 같다.
(실적이 없는달은 실적을 0 으로 표시함)
































30번 부서의 실적부터는 지면관계상 그림에서 생략하였다.


다행히 월별, 부서별 실적 테이블이 존재하기 때문에 김대리는 묵묵히 월별 실적 SQL을 아래처럼 작성하였다.
아래처럼 작성한 이유는 부서가 20개(10번부터 200번까지) 있기 때문에 부서별로 무조건 12건(1월~12월)을 만들기 위해서 이다.

우연히 김대리의 작업을 지켜보던 전산실 박과장이 한마디 한다.
전산실 박과장 :"김대리 그작업할때 200번이나 노가다(Union All) 할생각이냐?
                     "SQL 공부좀해라"
              
김대리에게 호통을 친 박과장은 자신감 있게 아래의 SQL을 1분만에 만들었다.    
           

위의 SQL 의 핵심은 모든 부서에 대하여 1월~12월 까지 와꾸?(틀)를 만들어 놓고
부서별 월별실적 테이블과 아우터 조인을 하기위해서 이다.
위의 SQL 에서 EXISTS 를 사용한 이유는 2002 년도에 실적이 있는 부서만 뽑기 위해서다.
하지만 위의 SQL 도 비효율이 있다.
부서별 월별 실적테이블을 2번이나 ACCESS 하였다.

박과장의 작업을 옆에서 지켜보던 신입사원이 고개를 기우뚱 하며 박과장에게 말을 건낸다.
전산실 신입사원:"dept_sale_history" 테이블을 2번 사용하지 않고도 같은 실적을 뽑을수 있습니다."
전산실 박과장 :"그래? 그럼 한번해봐"

신입사원을 지켜보던 박과장은 경악을 금치 못한다.
신입사원이 20초만에 SQL 을 작성하고도 성능은 신입사원의 SQL이 우수했기 때문이다.
단 4줄의 SQL 로 기획팀 이대리의 요구사항을 해결하였다.
박과장은 SQL 을 사용한지 10년이 넘는 배테랑 개발자 이지만 10g 신기능은 써보지 못한 상태였다.
아래의 SQL이 신입사원의 SQL 이다.

신입사원이 위의 SQL 을 사용할수 있었던건 처음 배운 SQL 문법이 Oracle 10g 기준이었고
박과장은 Oracle 8 버젼의 SQL을 공부 해었기 때문이다.

위의 Partition Outer Join 은 10g 의 새기능이다.
Partition Outer Join 의 기능을 요약하면 부서별로 중간중에 빠진 월의 실적을 생성해주는 기능이다.     

결론 :
Partition Outer Join 은 10g 의 신기능중 일부에 불과하다.
버전별로 New Features의 중요성을 다시한번 강조하지만 위의 경우와 같이
신기능을 모르면 작업량이 늘어날수 밖에 없고 대부분 성능도 느리다.
또한 Oracle 8.0 시절에 최적화된 SQL 이 항상 Oracle 10g 에서 최적화된 SQL 이라고 볼수 없다.
Oracle 9i 가 나온지는 10년이 됬으며 Oralce 10g 가 나온지도 6년이 지났고 2년전에 Oracle 11g 가 나왔다.
신버젼이 나올때 마다 알라딘의 요술램프처럼 주인님이 사용해주기를 기다리는 마술 같은 여러가지 신기능이 숨어있다는 점을 기억하자.

얼마전에 필자는 한 지인으로 부터 페이징 처리가 소용이 없을것 같은 쿼리를 봐달라는 요청을 받았다.
SQL 을 보니 WHERE 절에 대해서는 인덱스가 적절하게 잡혀 있었으나 ORDER BY 절에 대해서는
인덱스로 해결될수 있는 성격의 쿼리가 아니었다.
다시말해 ORDER BY 절 대로 인덱스를 생성할 경우 WHERE 절이 다치는 경우가 종종 있는데 그 SQL 이
그런경우 였다..
그 지인은 웹환경에서 결과건수가  1000 건 이상이 될수도 있는 쿼리 임에도 불구하고 "ORDER BY 절 때문에 부분범위 처리가 되지 않으니 페이징 처리가 필요없다"   는 주장이 었다.
얼핏보면 전체범위가 될수 밖에 없으니 맞는말 같지만 그말은 페이징처리 (Oracle 의 Rownum) 의 특성을 모르는데서 기인한다.
페이지 처리나 TOP SQL 등은 인덱스 상황이나 ORDER BY 상황 등의 여부에 따라서 하느냐 안하는냐를 결정하는것이 아니다.
ROWNUM 처리는 무조건 하는것이 이득이다.
그이유는 3가지이다.

1.전체건을 client 로 다가져온뒤에 다버리고 첫번째 페이지만 보여주는것은 비효율적이다.
  DB 입장에서도 전체건을 fetch 하는 비효율을 범했고 client 측에서도 filtering 해서 첫화면만 보여주는 Logic이
  추가되어야 하기 때문이다.

2.전체건을 다가져오게되면 DB 에서 페이지 처리되어 첫번째 화면의 데이터만 가져오는경우와 비교해보면
  네트웍의 전송량이 많아진다.

3.인덱스가 없는 ORDER BY 에 대해서 페이지 처리(ROWNUM 처리)를 하면 전체범위에 대하여 SORT 를
  수행하지 않고 해당 페이지건만 SORT 한다.

여기서는 1, 2번에 대해서는 논하지 않고  3번문제에 대해서만 논한다.
그러면 ORDER BY 절에 관련된 인덱스도 없는데 어떻게 해당건만 SORT 를 할수 있을까?
그이유는 ORDER BY + ROWNUM  작업은 ROWNUM 이 없는 ORDER BY 작업과는 구현로직이 완전히 다르다는데 있는 것이다.
아래는 ORDER BY + ROWNUM 과 ROWNUM 이 없는 ORDER BY 와의 차이점을 잘보여준다.

테이블 건수가 100만건이고 가장큰값 MAX 10 개를 찾는걸로 가정하면

위의 가설을 증명하기위한 예제가 아래에 있다.
먼저  from 절의 테이블 T 는 어떤 테이블이라도 상관은 없으나 대용량 일수록 차이가 크다.
또한 order by 절의 컬럼은 인덱스에 없어야 한다.(있으면  sort order by 가 되지 않는다.)
그리고 테스트를 위하여 PL/SQL 이 필요하다.

결론 :
첫번째 경우는 건건이 100 만번 테이블을 읽으면서 최대 10건만 SORT 한다.(그나마 버리는건은 SORT 가 없다)
두번째 경우는 건건이 100 만번 테이블을 읽으면서 최대 백만건을 SORT 한다.
이 두가지의 차이는 어떤경우에서든 확연히 들어난다는걸 기억하자.

Reference : Effective Oracle by Design

얼마전에 필자는 다음과 같은 질문을 받았다.
"PL/SQL 의 기능중에 커서(Select 문)을 인자로 받아서 복잡한 계산을 수행후 결과를 집합으로 RETURN 하는 기능이 있습니까?"
이런 경우 필자는 예외없이 Pipelined Table Function 을 권장한다.(단 버젼이 8i 이상이라면)
Pipelined Table Function 를 사용하여야 하는 이유는 4가지 이다.

1.PL/SQL 의 유일한 단점은 부분범위처리가 안된다는 것이다.
  즉 모든처리가 끝나야만 결과가 화면에 Return 된다는 것이다.
  Pipelined Table Function 을 사용하면 이런단점을 극복할수 있다.
  당연히 조회화면등에서 성능이 개선된다.
  이개념을 이용하려면 Pipe Row 기능을 이해해야한다.
  Pipe Row 기능은 9i 이상에서만 사용가능하며 8i 라면 Table Function 만 사용이 가능하므로
  부분범위 처리가 불가능하다.

2.SQL 이 길어서 A4 용지 기준으로 1 ~ 2 페이지가 넘어가는 경우가 있다.
  이런경우 모니터링을 해보면 엄청난양의 SQL 이 네트웍을 타고 DBMS 에 전달 된다.
  이런 SQL 들이 여러명이 사용하고 자주 사용된다면 네트웍의 부하가 상당하므로
  Pipelined Table Function 을 사용하면 SQL 이 1~2줄로 줄어들므로 네트웍 튜닝이 가능해진다.
  이부분의 모니터링은 AutoTrace 의  "bytes received via SQL*Net from client" 부분을
 살펴보면 된다.
 아래그림의 선택된 부분이 문제의 네트웍 전송량이다.
 아래를 결과를 보면 DB 서버로 부터 결과를 전송받은 양보다 Client 에서 SQL 문을
 DB 서버로 전송한 데이터양이 더크다.

이런일이 많을경우 전체적인 시스템이 느려지게 되는데 왜느린지 알수가 없는경우가 많다.
왜냐하면 시스템 Wait Event 모니터링을 해도 이런종류의 Event 는 대부분의 DBA 들이 Idle Event 로 생각하기 때문이다.
현재 시중에 있는 일반적인 Wait Event 책이 사람들을 그렇게 생각하도록 만든다.
이런경우는 Idle Event 로 생각하면 안된다.
대부분의 그런종류의 책들은 Event 들의 원인 + 조치방법으로 되어 있다.
하지만 이런경우 해법을 찾을수 있는 책은 거의없다.
Rechmond See 의 "Oracle Wait Interface" 라는 책을 보면 SQL 에 문제가 있거나 네트웍 성능이 문제라고 되어 있지만 그렇지 않은 경우가 대부분이다.
왜냐하면 SQL 이 길다고 그 SQL 이 잘못된것은 아니며, 대부분 네트웍을 점검해봐도 정상이기 때문이다.  
유일하게 욱짜님의 책에 "실행횟수가 많은경우 DBMS CALL 을 줄이고 PL/SQL 로 처리하라" 고 되어있다.
하지만 SQL이 조회화면의 SELECT 문일 경우라면?
이경우는 DBMS CALL 을 줄일수도 없고 DML(INSERT/UPDATE/DELETE) 처럼 PL/SQL로 바꿔서 Array Processing 으로 처리할수도 없는 노릇이다.
이때의 Solution 은 단한가지이다.
SQL 이 Select 이면 아래 예제에서 사용될 Table 함수나 Ref 커서를 사용한 Procedure 를 이용하면 된다.
위의 기능들은 대부분의 사람들이 알고 있지만 위의 기능을 SQL*Net message from client Event  의 해법으로 생각하는 사람들이 거의없는 이유는 무었일까?

3.SQL 을 인자로 던질수 있으며 결과가 Multi Column + Multi Row 로 Return 될수 있다는 점이다.

4.모듈로써 공유가 가능하다는점
  이것이 안된다면 복잡한 계산을 해야하는 모든곳에서 기능을 구현하여야만 한다.

필자는 1,2번이 맘에 들지만 개발자들은 3,4 번을 가장 맘에 들어한다.(아마도 입장 차이인가 보다.^^)
아래의 Script 를 보자.
Script 상의 오른쪽의 주석을 참조하기 바란다.(Oracle 의 HR 스키마에서 테스트 하면됨)

1.먼저 패키지 Header 를 만든다.

CREATE OR REPLACE PACKAGE refcur_pkg IS
 
    TYPE refcur_t IS REF CURSOR            -- cursor type 을 선언한다.
    RETURN employees%ROWTYPE; 
   
    TYPE outrec_typ IS RECORD (            -- structure type을 선언한다.
       var_num employees.employee_id%type,
       var_char1 VARCHAR2(30),
       var_char2 VARCHAR2(30)   );
                             
    TYPE outrecset IS TABLE OF outrec_typ; -- 위에서 선언한 structure 를 배열로 type 으로 선언한다
     
    FUNCTION f_trans(p refcur_t)         -- 커서를 인자로 받아서 Structure 배열을 Return 하는 함수를 선언한다.
    RETURN outrecset PIPELINED;    -- 위에서 선언한 Structure 배열을 사용함.
                                                     -- 반드시 PIPELINED를 명시해야함.
   
END refcur_pkg;
/

 
2.패키지 Body 를 만든다.

CREATE OR REPLACE PACKAGE BODY refcur_pkg IS

    FUNCTION f_trans(p refcur_t)
    RETURN outrecset PIPELINED IS -- Structure 배열을 Return 하는 함수임.  
 
        out_rec outrec_typ;               -- PACKAGE Header 에서 선언한 structute type 을 변수로 선언한다.
        in_rec p%ROWTYPE;            -- p cursor 네의 의 모든컬럼을 변수로 선언한다.

        BEGIN

          LOOP

              FETCH p INTO in_rec;
              EXIT WHEN p%NOTFOUND;
              -- first row
              out_rec.var_num := in_rec.employee_id;
              out_rec.var_char1 := in_rec.first_name;
              out_rec.var_char2 := in_rec.last_name;
              PIPE ROW(out_rec);     --> employee_id, first_name, last_name 으로 1 row 를 즉시 return 한다.
              -- second row
              out_rec.var_char1 := in_rec.email;
              out_rec.var_char2 := in_rec.phone_number;
              PIPE ROW(out_rec);     --> employee_id, email, phone_number 으로 1 row 를 즉시 return 한다.

          END LOOP;

          CLOSE p;

        RETURN; -- return 하는 변수를 지정하지 않는다.(LOOP 내에서 모두 Return 되었기 때문이다.)

        END;
END refcur_pkg;
/

위 함수의 Logic 을  설명하면 함수는 사원 성명에 대하여 1줄 return 하고
사원의 번화번호및 email 에 대하여 또 한줄 return 한다.
위 함수의 특징은 Pipe Row 에 있다.
Pipe Row 를 명시하면 Loop 내에서 결과를 즉시  Return 한다.
즉 모든 Loop 가 끝나길 기다릴 필요가 없는것이다.
물론 전체를 처리해야만 하는경우는 Pipe Row 를 명시하지 않으면 되고 Bulk Collect 기능을 권장한다.
이때는 함수 선언시 PIPELINED 를 명시하면 안되며 RETURN 시의 변수도 지정해야한다.
Pipe Row 와 PIPELINED 는 항상 Pair 로 움직여야 한다.

3.만들어진 Pipelined Table Function 를 사용한다.

SELECT *
FROM  TABLE(refcur_pkg.f_trans(CURSOR(SELECT *
                                                               FROM employees
                                                             WHERE department_id = 60) ) );

4.결과

결론 : Pipelined Table Function 함수는 부분범위 처리가 가능하며 결과를 Row Set 으로 Return 할수있다.
         이기능은 SQL*Net message from client Event  과다현상의 훌륭한 해결책이다.
         이기능을 잘 사용하면 다양한 분야에 활용할수 있다.

Reference : 10g PL/SQL User's Guide and Reference 의 Tuning PL/SQL Applications for Performance 부분.

편집후기 :
Table 함수와 테이블간의 조인이 가능한지 질문이 들어왔다.
당연히 된다.
한가지 주의할점은 조인절이 따로 필요없고 Table 함수의 인자로 컬럼의 Value 가 필요하다는 것이다.
아래에 예제를 참조하라.
아래 예제에서 CAST 함수를 쓴이유는 버젼이 8i 이기 때문이다. (9i 이상은 필요없음)

select X.SUBCON_CD,
           X.SUBCON_NM,
           X.SUBCON_CONTI_CLS,
           X.SUBCON_DESC,
           Y.COM_CLS4_NM,
           Y.COM_CLS4_ALIAS_CD,
           Y.COM_CLS2,
           Y.COM_CLS4_DESC
   From TOLC_S_SUBCONTINENT X,
           TABLE( CAST( COMM.get_com_info(X.SUBCON_CONTI_CLS) AS COMLIST_T) ) Y
        

업무적으로 볼때 조회화면의 검색조건들의 조합은 참으로 다양하다.
아래의 SQL 을 보면 WHERE 절의 모든 변수(:v_grade , :v_loc , :v_hiredate )에 값이 생략이 가능하다.
즉 모든 변수에 값이 들어올수도 있고 일부만 들어올수도 있고 전체가 안들어 올수도 있다.
where 절의 대부분이  그런조건이라고 가정하면 그런 SQL 들은 튜닝하기가 참 난감하다.
이럴때 당신이라면 어떻게 할것인가?
전통적인 튜닝 방법인 UNION ALL 로 모두 쪼개서 분리 할것인가?
아래의 인덱스 구조와 SQL 을 보자.

EMP 인덱스:
1) PK_EMP ( EMPNO )
2) EMP_IDX1 ( SAL )
3) EMP_IDX2 ( HIREDATE )
4) EMP_IDX3 ( DEPTNO )

DEPT 인덱스 :
1) PK_DEPT ( DEPTNO )
2) DEPT_IDX1 ( LOC )

SALGRADE 인덱스:
1) PK_SALGRADE( GRADE )
2) SALGRADE( HISAL, LOSAL)

1. 다양한 조건검색을 OR 로 처리할경우(원본 SQL)




-------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Starts | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------
|   1 |  NESTED LOOPS                 |          |      1 |      1 |00:00:00.01 |      21 |
|   2 |   NESTED LOOPS                |          |      1 |      1 |00:00:00.01 |      19 |
|*  3 |    TABLE ACCESS FULL          | SALGRADE |      1 |      5 |00:00:00.01 |       8 |
|*  4 |    TABLE ACCESS BY INDEX ROWID| EMP      |      5 |      1 |00:00:00.01 |      11 |
|*  5 |     INDEX RANGE SCAN          | EMP_IDX1 |      5 |     14 |00:00:00.01 |       3 |
|*  6 |   TABLE ACCESS BY INDEX ROWID | DEPT     |      1 |      1 |00:00:00.01 |       2 |
|*  7 |    INDEX UNIQUE SCAN          | PK_DEPT  |      1 |      1 |00:00:00.01 |       1 |
-------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - filter((:V_GRADE IS NULL OR "C"."GRADE"=TO_NUMBER(:V_GRADE)))
   4 - filter((:V_HIREDATE IS NULL OR "A"."HIREDATE"=TO_DATE(:V_HIREDATE,'YYYY-MM-DD')))
   5 - access("A"."SAL">="C"."LOSAL" AND "A"."SAL"<="C"."HISAL")
   6 - filter(("B"."LOC"=:V_LOC OR :V_LOC IS NULL))
   7 - access("A"."DEPTNO"="B"."DEPTNO")

해석 : 위 PLAN 을 보면   :v_hiredate 에 값이 들어 왔으므로 당연히 EMP_IDX2  인덱스를 먼저
         ACCESS 해야 하지만 엉뚱한 테이블 부터 ACCESS 하여서 비효율이 발생 하였다.
         즉 동적으로 변수값이 들어옴에 따라 PLAN 을 최적화 하지 못한다는 의미이다.
        물론 버젼이 11g 라면 동적으로 최적화 할수 있는 기능(Adaptive Cursor sharing)이 있지만
        항상 그렇게 되는건 아니다.
        그러면 이런 문제를 해결하기위해 어떻게 해야 할까?
        아래의 2~4 번에 해답이 있다.

2.엑세스 형태별로 UNION ALL 로 분리함

  먼저 UNION ALL 로 분리하는 기준은 똑똑한 조건에 먼저 우선순위를 주었다.
  다시말하면 :v_hiredate 는 굉장히 똑똑한 조건이므로 값이 들어오면   :v_grade 나 :v_loc 에 값이
  들어오던 들어오지 않던 대세에 지장이 없다는 의미이다.
  마찬가지 방법으로 :v_hiredate 가 들어오지 않는 상황에서는 두번째로 똑똑한 조건인 :v_grade 에
  값이 들어오면 :v_loc 이 들어오던 들어오지 않던 중요하지 않다는 의미이다.
  따라서 순서는  :v_hiredate --> :v_grade --> :v_loc 로 하였다.
  실행시에 다른변수에는 값을 넣지않고  :v_hiredate 만 '1980-12-17' 값을 대입한다.
 


---------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name          | Starts | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   1 |  UNION-ALL                       |               |      1 |      1 |00:00:00.05 |       8 |
|*  2 |   FILTER                         |               |      1 |      1 |00:00:00.05 |       8 |
|*  3 |    TABLE ACCESS BY INDEX ROWID   | SALGRADE      |      1 |      1 |00:00:00.05 |       8 |
|   4 |     NESTED LOOPS                 |               |      1 |      3 |00:00:00.08 |       7 |
|   5 |      NESTED LOOPS                |               |      1 |      1 |00:00:00.04 |       5 |
|   6 |       TABLE ACCESS BY INDEX ROWID| EMP           |      1 |      1 |00:00:00.02 |       3 |
|*  7 |        INDEX RANGE SCAN          | EMP_IDX2      |      1 |      1 |00:00:00.01 |       2 |
|*  8 |       TABLE ACCESS BY INDEX ROWID| DEPT          |      1 |      1 |00:00:00.02 |       2 |
|*  9 |        INDEX UNIQUE SCAN         | PK_DEPT       |      1 |      1 |00:00:00.01 |       1 |
|* 10 |      INDEX RANGE SCAN            | SALGRADE_IDX1 |      1 |      1 |00:00:00.01 |       2 |
|* 11 |   FILTER                         |               |      1 |      0 |00:00:00.01 |       0 |
|  12 |    NESTED LOOPS                  |               |      0 |      0 |00:00:00.01 |       0 |
|  13 |     NESTED LOOPS                 |               |      0 |      0 |00:00:00.01 |       0 |
|  14 |      TABLE ACCESS BY INDEX ROWID | SALGRADE      |      0 |      0 |00:00:00.01 |       0 |
|* 15 |       INDEX RANGE SCAN           | PK_SALGRADE   |      0 |      0 |00:00:00.01 |       0 |
|  16 |      TABLE ACCESS BY INDEX ROWID | EMP           |      0 |      0 |00:00:00.01 |       0 |
|* 17 |       INDEX RANGE SCAN           | EMP_IDX1      |      0 |      0 |00:00:00.01 |       0 |
|* 18 |     TABLE ACCESS BY INDEX ROWID  | DEPT          |      0 |      0 |00:00:00.01 |       0 |
|* 19 |      INDEX UNIQUE SCAN           | PK_DEPT       |      0 |      0 |00:00:00.01 |       0 |
|* 20 |   FILTER                         |               |      1 |      0 |00:00:00.01 |       0 |
|* 21 |    TABLE ACCESS BY INDEX ROWID   | SALGRADE      |      0 |      0 |00:00:00.01 |       0 |
|  22 |     NESTED LOOPS                 |               |      0 |      0 |00:00:00.01 |       0 |
|  23 |      NESTED LOOPS                |               |      0 |      0 |00:00:00.01 |       0 |
|  24 |       TABLE ACCESS BY INDEX ROWID| DEPT          |      0 |      0 |00:00:00.01 |       0 |
|* 25 |        INDEX RANGE SCAN          | DEPT_IDX1     |      0 |      0 |00:00:00.01 |       0 |
|  26 |       TABLE ACCESS BY INDEX ROWID| EMP           |      0 |      0 |00:00:00.01 |       0 |
|* 27 |        INDEX RANGE SCAN          | EMP_IDX3      |      0 |      0 |00:00:00.01 |       0 |
|* 28 |      INDEX RANGE SCAN            | PK_SALGRADE   |      0 |      0 |00:00:00.01 |       0 |
|* 29 |   FILTER                         |               |      1 |      0 |00:00:00.01 |       0 |
|  30 |    MERGE JOIN                    |               |      0 |      0 |00:00:00.01 |       0 |
|  31 |     SORT JOIN                    |               |      0 |      0 |00:00:00.01 |       0 |
|* 32 |      HASH JOIN                   |               |      0 |      0 |00:00:00.01 |       0 |
|  33 |       TABLE ACCESS FULL          | DEPT          |      0 |      0 |00:00:00.01 |       0 |
|  34 |       TABLE ACCESS FULL          | EMP           |      0 |      0 |00:00:00.01 |       0 |
|* 35 |     FILTER                       |               |      0 |      0 |00:00:00.01 |       0 |
|* 36 |      SORT JOIN                   |               |      0 |      0 |00:00:00.01 |       0 |
|  37 |       INDEX FULL SCAN            | SALGRADE_IDX1 |      0 |      0 |00:00:00.01 |       0 |
---------------------------------------------------------------------------------------------------
Predicate Information (지면관계상 생략)

해석 : PLAN 이 개발자가 의도한대로 분리되었고 A-Rows 와 Buffers 를 보면 분리된 SQL 중에서 첫번째
       SQL 만 값이 있다.
       하지만 최적의 SQL 이 되려면 아직도 멀었다.

3.UNION ALL 로 분리된 각각의 SQL 최적화

:v_grade 에 값이 들어오지 않는다면 더이상 SALGRADE 테이블은 필요가 없다.
과감히 FROM 절에서 삭제하자.
물론 a.sal 컬럼의 값에 NULL 이 있다면 답이 달라지므로 주의해야 한다.

SELECT ....
FROM   EMP a,
       DEPT b,
       SALGRADE c
 WHERE a.deptno = b.deptno
   and a.sal between c.losal and c.hisal
   and (c.grade = :v_grade or :v_grade is null)
   and (b.loc = :v_loc or :v_loc is null)
   and a.hiredate = :v_hiredate
   and :v_hiredate is not null
UNION ALL
SELECT ....
  FROM EMP a,
       DEPT b,
       SALGRADE c
 WHERE a.deptno = b.deptno
   and a.sal between c.losal and c.hisal
   and c.grade = :v_grade
   and (b.loc = :v_loc or :v_loc is null)
   and :v_hiredate is null
   and :v_grade is not null
UNION ALL  
SELECT ....
  FROM EMP a,
       DEPT b             --> SALGRADE 테이블은 필요가 없음
 WHERE a.deptno = b.deptno
   and b.loc =:v_loc
   and :v_hiredate is null
   and :v_grade is null
   and :v_loc is not null
UNION ALL  
SELECT ....
  FROM EMP a,
       DEPT b            --> SALGRADE 테이블은 필요가 없음
 WHERE a.deptno = b.deptno
   and :v_hiredate is null
   and :v_grade is null
   and :v_loc is null  ;

PLAN 정보 및 Predicate Information (지면관계상 생략)


4.NVL 혹은 DECODE 함수의 활용

UNION ALL 로 분리하면 옵티마이져 입장에서는 환영할 일이지만 개발자 입장에서 보면 반복적인 코딩이 증가하고 유지보수시 일량이 늘어나는 단점이 있다.
그렇다면 코딩량을 줄일수 있는 최적의 방법은 없는것일까?
물론 방법이 있다.
아래의 SQL 을 보자.
아래의 SQL 은 UNION ALL로 분리된 SQL 중에서 마지막 2개의 SQL 을 합친 것이다.

SELECT a.empno, a.ename, a.job, b.dname, a.sal
  FROM EMP a,
       DEPT b
 WHERE a.deptno = b.deptno
   and b.loc = decode(:v_loc, null,  b.loc, :v_loc) --:V_LOC 에 'CHICAGO' 대입
   and :v_hiredate is null
   and :v_grade is null
   and :v_loc is not null

----------------------------------------------------------------------------------------------
| Id  | Operation                       | Name      | Starts | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------
|   1 |  CONCATENATION                  |           |      1 |      6 |00:00:00.03 |       7 |
|*  2 |   FILTER                        |           |      1 |      0 |00:00:00.01 |       0 |
|   3 |    TABLE ACCESS BY INDEX ROWID  | EMP       |      0 |      0 |00:00:00.01 |       0 |
|   4 |     NESTED LOOPS                |           |      0 |      0 |00:00:00.01 |       0 |
|   5 |      TABLE ACCESS BY INDEX ROWID| DEPT      |      0 |      0 |00:00:00.01 |       0 |
|*  6 |       INDEX FULL SCAN           | DEPT_IDX1 |      0 |      0 |00:00:00.01 |       0 |
|*  7 |      INDEX RANGE SCAN           | EMP_IDX3  |      0 |      0 |00:00:00.01 |       0 |
|*  8 |   FILTER                        |           |      1 |      6 |00:00:00.03 |       7 |
|   9 |    TABLE ACCESS BY INDEX ROWID  | EMP       |      1 |      6 |00:00:00.03 |       7 |
|  10 |     NESTED LOOPS                |           |      1 |      8 |00:00:00.15 |       5 |
|  11 |      TABLE ACCESS BY INDEX ROWID| DEPT      |      1 |      1 |00:00:00.01 |       3 |
|* 12 |       INDEX RANGE SCAN          | DEPT_IDX1 |      1 |      1 |00:00:00.01 |       2 |
|* 13 |      INDEX RANGE SCAN           | EMP_IDX3  |      1 |      6 |00:00:00.01 |       2 |
----------------------------------------------------------------------------------------------
                                                                                                               
Predicate Information (identified by operation id):                                                            
---------------------------------------------------                                                            
                                                                                                               
   2 - filter((:V_LOC IS NOT NULL AND :V_GRADE IS NULL AND :V_HIREDATE IS NULL AND :V_LOC IS NULL))            
   6 - filter("B"."LOC" IS NOT NULL)                                                                           
   7 - access("A"."DEPTNO"="B"."DEPTNO")                                                                       
   8 - filter((:V_LOC IS NOT NULL AND :V_GRADE IS NULL AND :V_HIREDATE IS NULL AND :V_LOC IS NOT NULL))        
  12 - access("B"."LOC"=:V_LOC)                                                                                
  13 - access("A"."DEPTNO"="B"."DEPTNO")         
 
해석 : DECODE 함수를 사용함으로써 맨마지막 2개의 SQL 을 합쳤으나 옵티마이져가 조건이 들어오는 경우와
         들어오지 않는경우를 옵티마이져는 자동으로 UNION ALL 로 분리하였다.
         그러나 항상 이렇게 분리되는것은 아니므로 주의를 요한다.

5.SQL 의 최종모습

     
결론 : 검색화면의 경우 다양한 검색조건들이 들어올수 있다.
         기본적인 전략은 아래와 같이 순서대로 3가지 이다.
         1.똑똑한 조건을 기준으로 UNION  ALL 로 분리한다.(2번에 해당)
         2.UNION ALL 로 분리된 각각의 SQL 을 최적화 한다.(3번에 해당) --> FROM 절의 테이블 갯수가 달라진다.
         3.DECODE 나 NVL 을 사용함으로서 과도한 UNION ALL 로 분리되어 거대해지고  
            Shared Pool 에 무리를 줄수도 있는 SQL 을 통합하여 하나로 만든다.(4번에 해당)  

Min/Max 처리시 인덱스가 있을경우 대부분의 Plan 이 자동으로 INDEX RANGE SCAN (MIN/MAX) - FIRST ROW 처리가 된다.
위의 처리가 지원 되면서 부터는 전통적인 튜닝 방법인 INDEX_DESC 힌트 사용후에 WHERE 절에 ROWNUM = 1 을 추가하는 방식이 더이상 필요없는걸까?
모든규칙에는 예외가 있다는 말을 기억하자.
파티션이 되어있는 테이블은 PLAN 상에 INDEX RANGE SCAN (MIN/MAX) - FIRST ROW 로 처리가 되어
이상이 없는것 처럼 보이지만 비효율이 있다.
심지어 INDEX_DESC + ROWNUM = 1 조합도 비효율이 있다.
아래의 스크립트를 보자.

아래는 전형적인 MAX 일자를 구하는 SQL 이다.
계좌번호별로의 특정일자보다 작은일자중에 MAX 일자를 구하는 로직이다.
인덱스는 계좌번호 + 거래일자 로 되어있고 해당테이블은 거래일자기준으로 월별로 RANGE 파티션이 적용되어 있다.
물론 인덱스는 Global 이 아니다.
 

explain plan for
select max(거래일자)
from   일별계좌거래내역 b
where  b.계좌번호 = '1234567890'
and    b.거래일자 < '20080729';

select plan_table_output
from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
Plan hash value: 654469635
 
----------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                | Rows  | Bytes | Cost (%CPU)| Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                     |     1 |    23 |    34   (0)|       |       |
|   1 |  SORT AGGREGATE               |                     |     1 |    23 |            |       |       |
|   2 |   PARTITION RANGE ITERATOR    |                     |   128 |  2944 |    34   (0)|    67 |     1 |
|   3 |    FIRST ROW                  |                     |   128 |  2944 |    34   (0)|       |       |
|   4 |     INDEX RANGE SCAN (MIN/MAX)| PK_일별계좌거래내역 |   128 |  2944 |    34   (0)|    67 |     1 |
----------------------------------------------------------------------------------------------------------

해석 : 뭔가 특별히 이상할것이 없는 PLAN  이다. 즉 예측 실행계획으로는 정확한 정보를 알수가 없다.
       단지 Rows 컬럼과 Pstart/Pstop 컬럼이 좀 이상하긴 하다.

그러면 위의 쿼리를 다른방식으로 좀더 자세히 살펴보자.
SQL TRACE 를 사용해도 되지만 여기서는 dbms_xplan.display_cursor 로 실행된 SQL 의 Plan을 구한다.  

1. 원본쿼리

select /*+ gather_plan_statistics  */
       max(거래일자)
from   일별계좌거래내역 b
where  b.계좌번호 = '1234567890'
and    b.거래일자 < '20080729';

select plan_table_output
from table(dbms_xplan.display_cursor(null,null,'typical ALLSTATS LAST'));

 
Plan hash value: 654469635
 
---------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                | Starts | Pstart| Pstop | A-Rows | Buffers |
---------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE               |                     |      1 |       |       |      1 |      91 |
|   2 |   PARTITION RANGE ITERATOR    |                     |      1 |    67 |     1 |     11 |      91 |
|   3 |    FIRST ROW                  |                     |     67 |       |       |     11 |      91 |
|*  4 |     INDEX RANGE SCAN (MIN/MAX)| PK_일별계좌거래내역 |     67 |    67 |     1 |     11 |      91 |
---------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("B"."계좌번호"='1234567890' AND "B"."거래일자"<'20080729')
  
해석 : Plan 테이블의 Starts 컬럼을 보면 min/max 구하는 작업을 모든파티션마다 67번 반복하였다.
       Pstart, Pstop 컬럼에서도 보듯이 67 개의 파티션을 모두 엑세스 하였다.
       그결과 buffers 컬럼에 91 블럭이 나왔다  
  
  
  
2. 1번쿼리에서 index_desc 힌트와 where 절에 rownum = 1 을 추가한다.(전통적인 튜닝방법) 

select /*+ gather_plan_statistics index_desc(b) */ --> 힌트추가
       max(거래일자)
from   일별계좌거래내역 b
where  b.계좌번호 = '1234567890'
and    b.거래일자 < '20080729'
and    rownum = 1;                                 --> where 절 추가

select plan_table_output
from table(dbms_xplan.display_cursor(null,null,'typical ALLSTATS LAST'));

Plan hash value: 4244920012
 
----------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                | Starts | Pstart| Pstop | A-Rows | Buffers |
----------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE                |                     |      1 |       |       |      1 |       6 |
|*  2 |   COUNT STOPKEY                |                     |      1 |       |       |      1 |       6 |
|   3 |    PARTITION RANGE ITERATOR    |                     |      1 |    67 |     1 |      1 |       6 |
|*  4 |     INDEX RANGE SCAN DESCENDING| PK_일별계좌거래내역 |      2 |    67 |     1 |      1 |       6 |
----------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(ROWNUM=1)
   4 - access("B"."NMS_LN_ACNO"='1234567890' AND "B"."CAM_CLC_BAS_DT"<'20080729')
 
해석 : Pstart, Pstop 컬럼을 보면 67 개의 파티션을 모두 엑세스 한거 같지만 rownum = 1 이 추가되어 있으므로 Starts 컬럼을 보면 min/max 구하는 작업을 단 두번만 하였으므로  buffers 컬럼에 6 블럭이 나왔고
Starts 컬럼으로 예상해 보면 단 두개의 파티션만을 엑세스 하였다.
하나의 파티션만 엑세스 해야 하지만 Index Range Scan 시에 1 row 를 더 Scan 하는것과 같은 이치라고 할수 있다.
   
 
3. 특정파티션만 access 하게 쿼리를 b.거래일자 >= '20070701' 추가함. (최적의 방법)

select /*+ gather_plan_statistics index(b) */
       max(거래일자)
from   일별계좌거래내역 b
where  b.계좌번호 = '1234567890'
and    b.거래일자 < '20080729'
and    b.거래일자 >= '20080701';           --> 파티션을 특정월로 고정함

select plan_table_output
from table(dbms_xplan.display_cursor(null,null,'typical ALLSTATS LAST'));

 
Plan hash value: 161631393
 
---------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                | Starts | Pstart| Pstop | A-Rows | Buffers |
---------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE               |                     |      1 |       |       |      1 |       3 |
|   2 |   PARTITION RANGE SINGLE      |                     |      1 |    67 |    67 |      1 |       3 |
|   3 |    FIRST ROW                  |                     |      1 |       |       |      1 |       3 |
|*  4 |     INDEX RANGE SCAN (MIN/MAX)| PK_일별계좌거래내역 |      1 |    67 |    67 |      1 |       3 |
---------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("B"."NMS_LN_ACNO"='1234567890' AND "B"."CAM_CLC_BAS_DT"<'20080729' AND
              "B"."CAM_CLC_BAS_DT">='20080701')
 
해석 : Pstart, Pstop 컬럼에서 보듯이 단하나의 파티션(67번째 파티션) 에서 min/max 작업을 하였다.
       그결과 buffers 컬럼에 3 블럭이 나왔다.


4 결론
min/max - first row Plan 은 index_desc + rownum 과 성능상 같을수 있으나 파티션이 적용된 테이블이면서 Local 인덱스를 사용하는경우 전체파티션에 대하여 min/max - First Row 처리를 하므로 index_desc + rownum 처리를 하여야 비효율을 없앨수 있다. 하지만 이경우도 최적은 아니므로 특정파티션을 지정하여야 단 하나의 파티션만 엑세스 한다는 점을 기억하자.  

대용량 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 을 살펴보자.