태터데스크 관리자

도움말
닫기
적용하기   첫페이지 만들기

태터데스크 메시지

저장하였습니다.

Hint에 대해 당신이 모르는 것 - Full Hint Naming Convention

오라클 2008.09.11 11:35
보통 Hint를 사용할 때 Full Naming Convention을 쓸 일이 없다. 따라서 Full Naming Convention 자체를 아예 접하지 못하는 경우도 많다.

하지만 간혹 Full Naming Convention을 사용해야만 하는 경우들이 있다.
(다행히 아주 간혹이다)

간단한 예를 통해 Full Hint Naming Convention을 사용법을 공유해 보자.

다음과 같이 필요한 Object를 만든다.

UKJA@ukja102> set serveroutput on
UKJA@ukja102>
UKJA@ukja102> drop table t1 purge;

Table dropped.

Elapsed: 00:00:00.15
UKJA@ukja102> create table t1(c1 int, c2 int);

Table created.

두 개의 Index가 존재한다.

Elapsed: 00:00:00.01
UKJA@ukja102> create index t1_n1 on t1(c1);

Index created.

Elapsed: 00:00:00.00
UKJA@ukja102> create index t1_n2 on t1(c2);

Index created.

Elapsed: 00:00:00.01
UKJA@ukja102>
UKJA@ukja102> insert into t1
  2  select level, level
  3  from dual
  4  connect by level <= 10000
  5  ;

10000 rows created.

Elapsed: 00:00:00.75
UKJA@ukja102>
UKJA@ukja102> @gather t1
UKJA@ukja102> exec dbms_stats.gather_table_stats(user, '&1');

PL/SQL procedure successfully completed.

우리의 목적은 USE_CONCAT Hint를 이용해 OR 조건을 Concatentation(Union All과 비슷) Operation으로 변환하는 것이다.

Elapsed: 00:00:00.34
UKJA@ukja102>
UKJA@ukja102> explain plan for
  2  select /*+ use_concat */ *
  3  from t1
  4  where c1 = :b1 or c2 = :b2
  5  ;

Explained.

Elapsed: 00:00:00.00
UKJA@ukja102>
UKJA@ukja102> @plan
UKJA@ukja102> select * from table(dbms_xplan.display)
  2  /

PLAN_TABLE_OUTPUT                                                              
--------------------------------------------------------------------------------
Plan hash value: 82564388                                                      
                                                                               
--------------------------------------------------------------------------------
------                                                                         
                                                                               
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time
     |                                                                         
                                                                               
--------------------------------------------------------------------------------
------                                                                         
                                                                               
|   0 | SELECT STATEMENT             |       |     2 |    14 |     4   (0)| 00:0
0:01 |                                                                         
                                                                               
|   1 |  CONCATENATION               |       |       |       |            |    
     |                                                                         
                                                                               
|   2 |   TABLE ACCESS BY INDEX ROWID| T1    |     1 |     7 |     2   (0)| 00:0
0:01 |                                                                         
                                                                               
|*  3 |    INDEX RANGE SCAN          | T1_N2 |     1 |       |     1   (0)| 00:0
0:01 |                                                                         
                                                                               
|*  4 |   TABLE ACCESS BY INDEX ROWID| T1    |     1 |     7 |     2   (0)| 00:0
0:01 |                                                                         
                                                                               
|*  5 |    INDEX RANGE SCAN          | T1_N1 |     1 |       |     1   (0)| 00:0
0:01 |                                                                         
                                                                               
--------------------------------------------------------------------------------
------                                                                         
                                                                               
                                                                               
Predicate Information (identified by operation id):                            
---------------------------------------------------                            
                                                                               
   3 - access("C2"=TO_NUMBER(:B2))                                             
   4 - filter(LNNVL("C2"=TO_NUMBER(:B2)))                                      
   5 - access("C1"=TO_NUMBER(:B1))                                             

19 rows selected.

성공적이다. 하지만 여기서 한가지 요구 사항이 있다.
c2 = :b1 조건에 대해서는 Full Table Scan을
c1 = :b1 조건에 대해서는 Index를 경유하고자 한다.

다음과 같이 FULL Hint를 부여하면?

UKJA@ukja102>
UKJA@ukja102> explain plan for
  2  select /*+ use_concat full(t1) */ *
  3  from t1
  4  where c1 = :b1 or c2 = :b2
  5  ;

Explained.

Elapsed: 00:00:00.00
UKJA@ukja102>
UKJA@ukja102> @plan
UKJA@ukja102> select * from table(dbms_xplan.display)
  2  /

PLAN_TABLE_OUTPUT                                                              
--------------------------------------------------------------------------------
Plan hash value: 130649462                                                     
                                                                               
---------------------------------------------------------------------------    
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |    
---------------------------------------------------------------------------    
|   0 | SELECT STATEMENT   |      |     2 |    14 |    12   (0)| 00:00:01 |    
|   1 |  CONCATENATION     |      |       |       |            |          |    
|*  2 |   TABLE ACCESS FULL| T1   |     1 |     7 |     6   (0)| 00:00:01 |    
|*  3 |   TABLE ACCESS FULL| T1   |     1 |     7 |     6   (0)| 00:00:01 |    
---------------------------------------------------------------------------    
                                                                               
Predicate Information (identified by operation id):                            
---------------------------------------------------                            
                                                                               
   2 - filter("C2"=TO_NUMBER(:B2))                                             
   3 - filter("C1"=TO_NUMBER(:B1) AND LNNVL("C2"=TO_NUMBER(:B2)))              

16 rows selected.

두 조건 다 Full Table Scan을 사용해버린다. 난감하다.

이럴 때 사용할 수 있는 것이 Full Hint Naming Convention이다.
Oracle 10g(아마 R2)부터는 'outline' Option을 이용하면 Full Hint Naming Convention을 볼 수 있다.

UKJA@ukja102>
UKJA@ukja102> explain plan for
  2  select /*+ use_concat full(t1) */ *
  3  from t1
  4  where c1 = :b1 or c2 = :b2
  5  ;

Explained.

Elapsed: 00:00:00.00
UKJA@ukja102>
UKJA@ukja102> select * from table(dbms_xplan.display(null,null,'outline'));

PLAN_TABLE_OUTPUT                                                              
--------------------------------------------------------------------------------
Plan hash value: 130649462                                                     
                                                                               
---------------------------------------------------------------------------    
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |    
---------------------------------------------------------------------------    
|   0 | SELECT STATEMENT   |      |     2 |    14 |    12   (0)| 00:00:01 |    
|   1 |  CONCATENATION     |      |       |       |            |          |    
|*  2 |   TABLE ACCESS FULL| T1   |     1 |     7 |     6   (0)| 00:00:01 |    
|*  3 |   TABLE ACCESS FULL| T1   |     1 |     7 |     6   (0)| 00:00:01 |    
---------------------------------------------------------------------------    
                                                                               
Outline Data                                                                   
-------------                                                                  
                                                                               
  /*+                                                                          
      BEGIN_OUTLINE_DATA                                                       
      FULL(@"SEL$1_2" "T1"@"SEL$1_2")                                          
      FULL(@"SEL$1_1" "T1"@"SEL$1")                                            
      OUTLINE(@"SEL$1")                                                        
      OUTLINE_LEAF(@"SEL$1_2")                                                 
      USE_CONCAT(@"SEL$1" 8)                                                   
      OUTLINE_LEAF(@"SEL$1_1")                                                 
      OUTLINE_LEAF(@"SEL$1")                                                   
      ALL_ROWS                                                                 
      OPTIMIZER_FEATURES_ENABLE('10.2.0.1')                                    
      IGNORE_OPTIM_EMBEDDED_HINTS                                              
      END_OUTLINE_DATA                                                         
  */                                                                           
                                                                               
Predicate Information (identified by operation id):                            
---------------------------------------------------                            
                                                                               
   2 - filter("C2"=TO_NUMBER(:B2))                                             
   3 - filter("C1"=TO_NUMBER(:B1) AND LNNVL("C2"=TO_NUMBER(:B2)))              

34 rows selected.

Outline Data가 곧 Hint의 모임을 말한다. 즉 현재 실행 계획을 재현하기 위해 필요한 Hint들이다. FULL Hint의 Full Naming Convention은 다음과 같다는 것을 확인할 수 있다.

FULL(@"SEL$1_2" "T1"@"SEL$1_2")                                          
FULL(@"SEL$1_1" "T1"@"SEL$1")   

즉 FULL Hint의 Full Naming Convention은 다음과 같다.

FULL(@query_block_name  table_alias_name)

여기서 추론할 수 있는 한가지 사실은  Concatentation Query 변환으로 인해 Query Block이 추가로 생성되었다는 것이다. 이 때문에 Hint를 사용하기가 까다로워지는 것이다.

같은 방법으로 INDEX Hint의 Full Naming Convention도 알아낼 수 있다.

UKJA@ukja102>
UKJA@ukja102>
UKJA@ukja102> explain plan for
  2  select /*+ use_concat */ *
  3  from t1
  4  where c1 = :b1 or c2 = :b2
  5  ;

Explained.

Elapsed: 00:00:00.01
UKJA@ukja102>
UKJA@ukja102> select * from table(dbms_xplan.display(null,null,'outline'));

PLAN_TABLE_OUTPUT                                                              
--------------------------------------------------------------------------------
Plan hash value: 82564388                                                      
                                                                               
--------------------------------------------------------------------------------
------                                                                         
                                                                               
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time
     |                                                                         
                                                                               
--------------------------------------------------------------------------------
------                                                                         
                                                                               
|   0 | SELECT STATEMENT             |       |     2 |    14 |     4   (0)| 00:0
0:01 |                                                                         
                                                                               
|   1 |  CONCATENATION               |       |       |       |            |    
     |                                                                         
                                                                               
|   2 |   TABLE ACCESS BY INDEX ROWID| T1    |     1 |     7 |     2   (0)| 00:0
0:01 |                                                                         
                                                                               
|*  3 |    INDEX RANGE SCAN          | T1_N2 |     1 |       |     1   (0)| 00:0
0:01 |                                                                         
                                                                               
|*  4 |   TABLE ACCESS BY INDEX ROWID| T1    |     1 |     7 |     2   (0)| 00:0
0:01 |                                                                         
                                                                               
|*  5 |    INDEX RANGE SCAN          | T1_N1 |     1 |       |     1   (0)| 00:0
0:01 |                                                                         
                                                                               
--------------------------------------------------------------------------------
------                                                                         
                                                                               
                                                                               
Outline Data                                                                   
-------------                                                                  
                                                                               
  /*+                                                                          
      BEGIN_OUTLINE_DATA                                                       
      INDEX(@"SEL$1_2" "T1"@"SEL$1_2" ("T1"."C1"))                             
      INDEX(@"SEL$1_1" "T1"@"SEL$1" ("T1"."C2"))                               
      OUTLINE(@"SEL$1")                                                        
      OUTLINE_LEAF(@"SEL$1_2")                                                 
      USE_CONCAT(@"SEL$1" 8)                                                   
      OUTLINE_LEAF(@"SEL$1_1")                                                 
      OUTLINE_LEAF(@"SEL$1")                                                   
      ALL_ROWS                                                                 
      OPTIMIZER_FEATURES_ENABLE('10.2.0.1')                                    
      IGNORE_OPTIM_EMBEDDED_HINTS                                              
      END_OUTLINE_DATA                                                         
  */                                                                           
                                                                               
Predicate Information (identified by operation id):                            
---------------------------------------------------                            
                                                                               
   3 - access("C2"=TO_NUMBER(:B2))                                             
   4 - filter(LNNVL("C2"=TO_NUMBER(:B2)))                                      
   5 - access("C1"=TO_NUMBER(:B1))                                             

37 rows selected.

이 사실을 이용하면 다음과 같이 특정 조건에서만 Full Scan을 하도록 지정할 수 있다.

UKJA@ukja102>
UKJA@ukja102> explain plan for
  2  select /*+ use_concat FULL(@"SEL$1_1" "T1"@"SEL$1") */
  3    *
  4  from t1
  5  where c1 = :b1 or c2 = :b2
  6  ;

Explained.

Elapsed: 00:00:00.00
UKJA@ukja102>
UKJA@ukja102> @plan
UKJA@ukja102> select * from table(dbms_xplan.display)
  2  /

PLAN_TABLE_OUTPUT                                                              
--------------------------------------------------------------------------------
Plan hash value: 647657254                                                     
                                                                               
--------------------------------------------------------------------------------
------                                                                         
                                                                               
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time
     |                                                                         
                                                                               
--------------------------------------------------------------------------------
------                                                                         
                                                                               
|   0 | SELECT STATEMENT             |       |     2 |    14 |     8   (0)| 00:0
0:01 |                                                                         
                                                                               
|   1 |  CONCATENATION               |       |       |       |            |    
     |                                                                         
                                                                               
|*  2 |   TABLE ACCESS FULL          | T1    |     1 |     7 |     6   (0)| 00:0
0:01 |                                                                         
                                                                               
|*  3 |   TABLE ACCESS BY INDEX ROWID| T1    |     1 |     7 |     2   (0)| 00:0
0:01 |                                                                         
                                                                               
|*  4 |    INDEX RANGE SCAN          | T1_N1 |     1 |       |     1   (0)| 00:0
0:01 |                                                                         
                                                                               
--------------------------------------------------------------------------------
------                                                                         
                                                                               
                                                                               
Predicate Information (identified by operation id):                            
---------------------------------------------------                            
                                                                               
   2 - filter("C2"=TO_NUMBER(:B2))                                             
   3 - filter(LNNVL("C2"=TO_NUMBER(:B2)))                                      
   4 - access("C1"=TO_NUMBER(:B1))                                             

18 rows selected.

성공했다!!!
더 확실한 방법은 다음과 같이 FULL과 INDEX Hint를 Full Naming Convention을 이용해 완벽하게 부여하는 것이다.


UKJA@ukja102>
UKJA@ukja102> explain plan for
  2  select /*+ use_concat FULL(@"SEL$1_1" "T1"@"SEL$1")
  3           INDEX(@"SEL$1_2" "T1"@"SEL$1_2" ("T1"."C1")) */
  4    *
  5  from t1
  6  where c1 = :b1 or c2 = :b2
  7  ;

Explained.

Elapsed: 00:00:00.00
UKJA@ukja102>
UKJA@ukja102> @plan
UKJA@ukja102> select * from table(dbms_xplan.display)
  2  /

PLAN_TABLE_OUTPUT                                                              
--------------------------------------------------------------------------------
Plan hash value: 647657254                                                     
                                                                               
--------------------------------------------------------------------------------
------                                                                         
                                                                               
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time
     |                                                                         
                                                                               
--------------------------------------------------------------------------------
------                                                                         
                                                                               
|   0 | SELECT STATEMENT             |       |     2 |    14 |     8   (0)| 00:0
0:01 |                                                                         
                                                                               
|   1 |  CONCATENATION               |       |       |       |            |    
     |                                                                         
                                                                               
|*  2 |   TABLE ACCESS FULL          | T1    |     1 |     7 |     6   (0)| 00:0
0:01 |                                                                         
                                                                               
|*  3 |   TABLE ACCESS BY INDEX ROWID| T1    |     1 |     7 |     2   (0)| 00:0
0:01 |                                                                         
                                                                               
|*  4 |   INDEX RANGE SCAN          | T1_N1 |     1 |       |     1   (0)| 00:0
0:01 |                                                                         
                                                                               
--------------------------------------------------------------------------------
------                                                                         
                                                                               
                                                                               
Predicate Information (identified by operation id):                            
---------------------------------------------------                            
                                                                               
   2 - filter("C2"=TO_NUMBER(:B2))                                             
   3 - filter(LNNVL("C2"=TO_NUMBER(:B2)))                                      
   4 - access("C1"=TO_NUMBER(:B1))                                             

18 rows selected.


Full Hint Naming Convention을 몰랐다면 해결 불가능한 것으로 남았을 것이다.
작지만 유용한 팁이라고 할 수 있겠다.


신고
Trackback 0 : Comments 13
  1. 강정식 2008.09.11 14:10 신고 Modify/Delete Reply

    욱짜님 좋은 내용 감사합니다. ^^
    힌트를 이렇게도 줄수가 있군요.. 덕분에 튜닝 스킬 하나를 더 배울 수 있었습니다.
    만약 이게 잘 먹힌다면 서브쿼리의 액세스 순서 또한 원하는데로 변경 가능할 것 같은데요.
    예를들어 [A, B, C, D] - 메인, [Z](서브) 로 구성된 SQL에서 액세스를 우리가 핸들링 하려면
    qb_name같은 힌트를 넣어서(이것도 잘 적용이 안되지만) 하거나 서브쿼리를 조인으로 불러내서
    힌트를 주어 액세스 순서를 조정해야 하는데 이게 된다면 원하는 위치에 넣을 수 있을 것 같습니다.

    테스트 한 뒤에 다시 리플 달겠습니다 ^^

  2. 욱짜 2008.09.11 14:26 신고 Modify/Delete Reply

    qb_name Hint가 동작안하는 것처럼 보이는 이유는 아마 Query Transformation에 의해 Query Block이 사라져버리기 때문일겁니다.

    Test 결과 공유해주시면 좋겠네요~

  3. 강정식 2008.09.11 15:07 신고 Modify/Delete Reply

    안녕하세요 욱짜님.
    지금 테스트 한 결과 올려 드립니다.

    1. EMP, DEPT, CHECK_TAB 테이블 생성
    -- EMP 테이블 생성 생략
    -- DEPT 테이블 생성 생략
    -- CHECK_TAB 생성
    CREATE TABLE CHECK_TAB AS
    SELECT LEVEL CHECK_NUM
    FROM DUAL
    CONNECT BY LEVEL <= 10000
    ;

    CREATE INDEX APPS.CHECK_TAB_N1 ON APPS.CHECK_TAB (CHECK_NUM);

    EXEC DBMS_STATS.GATHER_TABLE_STATS('OWNER', 'CHECK_TAB', CASCADE => TRUE);
    /


    2. 테스트 SQL
    -- 현재는 액세스 순서가 'E1 -> D1 -> E2 -> D2 -> CT'로 되어 있습니다.
    -- 이를 'E1 -> CT -> D1 -> E2 -> D2' 순서로 바꾸려고 합니다.
    EXPLAIN PLAN FOR
    SELECT E1.*
    FROM EMP E1,
    DEPT D1,
    EMP E2,
    DEPT D2
    WHERE E1.EMPNO = E2.EMPNO
    AND E1.DEPTNO = D1.DEPTNO
    AND D1.DEPTNO = D2.DEPTNO
    AND E1.EMPNO = :B1
    AND EXISTS (SELECT 1
    FROM CHECK_TAB CT
    WHERE CT.CHECK_NUM = E1.EMPNO)
    ;

    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

    ------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | 50 | 2 (0)| 00:00:01 |
    | 1 | NESTED LOOPS SEMI | | 1 | 50 | 2 (0)| 00:00:01 |
    | 2 | NESTED LOOPS | | 1 | 47 | 1 (0)| 00:00:01 |
    | 3 | NESTED LOOPS | | 1 | 44 | 1 (0)| 00:00:01 |
    | 4 | NESTED LOOPS | | 1 | 41 | 1 (0)| 00:00:01 |
    |* 5 | INDEX UNIQUE SCAN | EMP_U1 | 1 | 4 | 0 (0)| 00:00:01 |
    | 6 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 37 | 1 (0)| 00:00:01 |
    |* 7 | INDEX UNIQUE SCAN | EMP_U1 | 1 | | 0 (0)| 00:00:01 |
    |* 8 | INDEX UNIQUE SCAN | DEPT_U1 | 4 | 12 | 0 (0)| 00:00:01 |
    |* 9 | INDEX UNIQUE SCAN | DEPT_U1 | 4 | 12 | 0 (0)| 00:00:01 |
    |* 10 | INDEX RANGE SCAN | CHECK_TAB_N1 | 1 | 3 | 1 (0)| 00:00:01 |
    ------------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

    5 - access("E2"."EMPNO"=TO_NUMBER(:B1))
    7 - access("E1"."EMPNO"=TO_NUMBER(:B1))
    8 - access("E1"."DEPTNO"="D1"."DEPTNO";)
    9 - access("D1"."DEPTNO"="D2"."DEPTNO";)
    10 - access("CT"."CHECK_NUM"=TO_NUMBER(:B1))
    filter("CT"."CHECK_NUM"="E1"."EMPNO";)



    3. Stored Outline 생성하여 HINT_TEXT 값 알기
    CREATE OUTLINE XXTEST ON
    SELECT /*+ LEADING(E1 D1 E2 D2) */
    E1.*
    FROM EMP E1,
    DEPT D1,
    EMP E2,
    DEPT D2
    WHERE E1.EMPNO = E2.EMPNO
    AND E1.DEPTNO = D1.DEPTNO
    AND D1.DEPTNO = D2.DEPTNO
    AND E1.EMPNO = :B1
    AND EXISTS (SELECT 1
    FROM CHECK_TAB CT
    WHERE CT.CHECK_NUM = E1.EMPNO)
    ;

    SELECT * FROM OUTLN.OL$HINTS
    ;

    -- 결과
    /*
    USE_NL(@"SEL$5DA710D3" "CT"@"SEL$2";)
    USE_NL(@"SEL$5DA710D3" "D2"@"SEL$1";)
    USE_NL(@"SEL$5DA710D3" "E2"@"SEL$1";)
    USE_NL(@"SEL$5DA710D3" "D1"@"SEL$1";)
    -------------------------------------------------------------------------------------------
    -- 키포인트
    -- 결국 LEADING을 주었는데 서브쿼리는 힌트에 포함을 안시켜서 Internal 부분에서는 CT를 넣음
    LEADING(@"SEL$5DA710D3" "E1"@"SEL$1" "D1"@"SEL$1" "E2"@"SEL$1" "D2"@"SEL$1" "CT"@"SEL$2";)
    -------------------------------------------------------------------------------------------
    INDEX(@"SEL$5DA710D3" "CT"@"SEL$2" ("CHECK_TAB"."CHECK_NUM";))
    INDEX(@"SEL$5DA710D3" "D2"@"SEL$1" ("DEPT"."DEPTNO";))
    INDEX(@"SEL$5DA710D3" "E2"@"SEL$1" ("EMP"."EMPNO";))
    INDEX(@"SEL$5DA710D3" "D1"@"SEL$1" ("DEPT"."DEPTNO";))
    INDEX_RS_ASC(@"SEL$5DA710D3" "E1"@"SEL$1" ("EMP"."EMPNO";))
    OUTLINE(@"SEL$2";)
    OUTLINE(@"SEL$1";)
    UNNEST(@"SEL$2";)
    OUTLINE_LEAF(@"SEL$5DA710D3";)
    ALL_ROWS
    OPT_PARAM('_optim_peek_user_binds' 'false')
    OPT_PARAM('_fast_full_scan_enabled' 'false')
    OPT_PARAM('_b_tree_bitmap_plans' 'false')
    OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
    IGNORE_OPTIM_EMBEDDED_HINTS
    */




    4. 힌트 변경
    -- "CT"@"SEL$2" 값을 맨 마지막에서 두번째 위치시킴
    -- 이로 인해 실행계회에서 두번째 액세스 함
    EXPLAIN PLAN FOR
    SELECT /*+ LEADING(@"SEL$5DA710D3" "E1"@"SEL$1" "CT"@"SEL$2" "D1"@"SEL$1" "E2"@"SEL$1" "D2"@"SEL$1";) */
    E1.*
    FROM EMP E1,
    DEPT D1,
    EMP E2,
    DEPT D2
    WHERE E1.EMPNO = E2.EMPNO
    AND E1.DEPTNO = D1.DEPTNO
    AND D1.DEPTNO = D2.DEPTNO
    AND E1.EMPNO = :B1
    AND EXISTS (SELECT 1
    FROM CHECK_TAB CT
    WHERE CT.CHECK_NUM = E1.EMPNO)
    ;

    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

    ------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | 50 | 2 (0)| 00:00:01 |
    | 1 | NESTED LOOPS | | 1 | 50 | 2 (0)| 00:00:01 |
    | 2 | MERGE JOIN CARTESIAN | | 1 | 47 | 2 (0)| 00:00:01 |
    | 3 | NESTED LOOPS | | 1 | 43 | 2 (0)| 00:00:01 |
    | 4 | NESTED LOOPS SEMI | | 1 | 40 | 2 (0)| 00:00:01 |
    | 5 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 37 | 1 (0)| 00:00:01 |
    |* 6 | INDEX UNIQUE SCAN | EMP_U1 | 1 | | 0 (0)| 00:00:01 |
    |* 7 | INDEX RANGE SCAN | CHECK_TAB_N1 | 1 | 3 | 1 (0)| 00:00:01 |
    |* 8 | INDEX UNIQUE SCAN | DEPT_U1 | 1 | 3 | 0 (0)| 00:00:01 |
    | 9 | BUFFER SORT | | 1 | 4 | 2 (0)| 00:00:01 |
    |* 10 | INDEX UNIQUE SCAN | EMP_U1 | 1 | 4 | 0 (0)| 00:00:01 |
    |* 11 | INDEX UNIQUE SCAN | DEPT_U1 | 1 | 3 | 0 (0)| 00:00:01 |
    ------------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

    6 - access("E1"."EMPNO"=TO_NUMBER(:B1))
    7 - access("CT"."CHECK_NUM"=TO_NUMBER(:B1))
    filter("CT"."CHECK_NUM"="E1"."EMPNO";)
    8 - access("E1"."DEPTNO"="D1"."DEPTNO";)
    10 - access("E2"."EMPNO"=TO_NUMBER(:B1))
    11 - access("D1"."DEPTNO"="D2"."DEPTNO";)

  4. 욱짜 2008.09.11 15:16 신고 Modify/Delete Reply

    감사합니다. 댓글에서 Formatting이 되면 좋겠네요.
    티스토리의 한계인듯 합니다.

  5. 욱짜 2008.09.11 15:17 신고 Modify/Delete Reply

    몇가지 테스트를 해보시면 원칙이 있을 거 같은데 고민해보지는 않았습니다.

    Oracle 10g R2부터는 다음 명령을 사용하시면 됩니다.

    explain plan for ...;
    select * from table(dbms_xplan.display(null,null,'outline')); -- 혹은 Advanced

  6. 강정식 2008.09.11 15:18 신고 Modify/Delete Reply

    근데 한가지 의문이 드는것은 'Full Hint Naming Convention' 값을 알기 위해
    Stored Outline을 생성하여 HINT_TEXT를 조회하는 방법밖에 없는지 궁금합니다.

    만약 그렇다면 일일이 생성하여 확인해야 하는데 혹시 이 'Full Hint Naming Convention'
    값을 정할 때 내부적으로 어떤 규칙이 있다면 Outline을 생성하지 않아도 우리가
    임의대로 핸들링이 가능할 것 같아서요.

    일단 제가 몇가지를 더 테스트 해봤는데 그 내용은 아래와 같습니다.

    EXPLAIN PLAN FOR
    SELECT /*+ LEADING(E1@SEL$1 CT@SEL$2 D1@SEL$1 E2@SEL$1 D2@SEL$1) */
    E1.*
    FROM EMP E1,
    DEPT D1,
    EMP E2,
    DEPT D2
    WHERE E1.EMPNO = E2.EMPNO
    AND E1.DEPTNO = D1.DEPTNO
    AND D1.DEPTNO = D2.DEPTNO
    AND E1.EMPNO = :B1
    AND EXISTS (SELECT 1
    FROM CHECK_TAB CT
    WHERE CT.CHECK_NUM = E1.EMPNO)
    ;

    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

    ------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | 50 | 2 (0)| 00:00:01 |
    | 1 | NESTED LOOPS | | 1 | 50 | 2 (0)| 00:00:01 |
    | 2 | MERGE JOIN CARTESIAN | | 1 | 47 | 2 (0)| 00:00:01 |
    | 3 | NESTED LOOPS | | 1 | 43 | 2 (0)| 00:00:01 |
    | 4 | NESTED LOOPS SEMI | | 1 | 40 | 2 (0)| 00:00:01 |
    | 5 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 37 | 1 (0)| 00:00:01 |
    |* 6 | INDEX UNIQUE SCAN | EMP_U1 | 1 | | 0 (0)| 00:00:01 |
    |* 7 | INDEX RANGE SCAN | CHECK_TAB_N1 | 1 | 3 | 1 (0)| 00:00:01 |
    |* 8 | INDEX UNIQUE SCAN | DEPT_U1 | 1 | 3 | 0 (0)| 00:00:01 |
    | 9 | BUFFER SORT | | 1 | 4 | 2 (0)| 00:00:01 |
    |* 10 | INDEX UNIQUE SCAN | EMP_U1 | 1 | 4 | 0 (0)| 00:00:01 |
    |* 11 | INDEX UNIQUE SCAN | DEPT_U1 | 1 | 3 | 0 (0)| 00:00:01 |
    ------------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

    6 - access("E1"."EMPNO"=TO_NUMBER(:B1))
    7 - access("CT"."CHECK_NUM"=TO_NUMBER(:B1))
    filter("CT"."CHECK_NUM"="E1"."EMPNO";)
    8 - access("E1"."DEPTNO"="D1"."DEPTNO";)
    10 - access("E2"."EMPNO"=TO_NUMBER(:B1))
    11 - access("D1"."DEPTNO"="D2"."DEPTNO";)

    추가적으로 테스트 한건 힌트를 변경한 건데요.

    변경 전 : /*+ LEADING(@"SEL$5DA710D3" "E1"@"SEL$1" "CT"@"SEL$2" "D1"@"SEL$1" "E2"@"SEL$1" "D2"@"SEL$1";) */
    변경 후 : /*+ LEADING(E1@SEL$1 CT@SEL$2 D1@SEL$1 E2@SEL$1 D2@SEL$1) */

    [", @"SEL$5DA710D3"] 2개의 값을 제외하고 힌트를 주어도 원하는 실행계획이 나오더라구요.
    또 하나 '@'는 dot(.)를 표시하는 것 같고 '$'는 조인의 Depth를 표시하는 것 같습니다.

    이처럼 몇가지 규칙만 확실히 알게된다면 자유롭게 핸들링이 가능할 것 같은데...
    욱짜님께서 혹시 여기까지 내용을 아시고 계시다면 추가 공유좀 부탁드립니다. ^^

  7. 강정식 2008.09.11 15:33 신고 Modify/Delete Reply

    아 그렇군요. ^^ dbms_xplan.display에서 파라미터의 3번째 값에 outline을 주면
    hint_text가 나오는군요. 감사합니다.
    예전 포스트 올리신 글에서 봤는데 사용을 안해봐서 몰랐다가 이번에 알게 되었습니다.

    이게 된다면 그동안 튜닝으로 한계가 있었던 여러 SQL들 개선이 가능하겠는데요.
    저한테는 획기적인 발견입니다.

    다시한번 좋은 정보 감사합니다.

  8. 시연 2008.09.11 16:06 신고 Modify/Delete Reply

    아주 좋은거 배우고 갑니다. ^^

  9. extremedb 2008.09.11 16:50 신고 Modify/Delete Reply

    많은 도움이 되었네요.
    이제 CONCATTION 을 사용하면 UNION ALL 로 분리할 필요가 없게 됬네요.
    감사합니다.

  10. 강정식 2008.09.16 17:46 신고 Modify/Delete Reply

    http://blog.naver.com/xsoft/150035335488
    제 블로그에 위에 테스트 한 내용을 Formatting 해서 올렸습니다.
    향후에도 이와 관련된 테스트 내용을 만들게 되면 다시 글 올리겠습니다.

  11. 쏘심이 2008.09.18 21:23 신고 Modify/Delete Reply

    감솨합니다

  12. extremedb 2009.01.08 12:33 신고 Modify/Delete Reply

    Full Naming Convention 라는 용어보다는 Global Hint 라는 용어가 더어울리는거 같습니다.
    Full Naming Convention 라는 용어는 오라클에서 공식적으로 사용한 용어 인가요?

  13. 욱짜 2009.01.08 12:52 신고 Modify/Delete Reply

    기존 문서에 잘 설명하지 않은 형식이라서 제가 붙인 이름입니다.

    고유 명사가 아닌 보통 명사, 말 그대로 Oracle이 Hint를 적용할 때 실제로 사용하는 명명법이라는 의미입니다.

    공식적으로는 Global Hints로 분류하는 것이 맞습니다.

Write a comment

티스토리 툴바