태터데스크 관리자

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

태터데스크 메시지

저장하였습니다.

QB_NAME Hint의 편리함 - Oracle 10g

오라클 2008.01.17 14:51
Oracle 10g에서 QB_NAME 이라는 유용한 힌트가 추가되었다. QB_NAME은 Query Block에 사용자가 직접 별명(이름)을 부여하는 것을 가능하게 해주는 힌트이다.

오라클은 SQL Text를 여러 개의 Query Block 으로 나누어 관리한다. 가령 하나의 SQL Text에 Inline View가 하나 포함되어 있다면 내부적으로 두 개의 Query Block이 존재한다.

아주 간단한 예제만으로도 이 힌트가 얼마나 유용한지 알 수 있다.

아래와 같은 쿼리의 실행 계획을 보자.

explain plan for select t1.id1, t2.name2, x.id4, x.name5,
    (select count(*) from t1 s where s.id1 = t1.id1) as id1_1
from t1, t2, t3, t5,
    (select t4.id4, t5.name5
        from t4, t5
        where t4.id4 = t5.id5 and t5.name5 like '%c%') x
where t1.id1 = t2.id2
    and t2.id2 in (select id3 from t3 where name3 like '%b%')
    and t2.id2 = x.id4
    and t3.id3 = t1.id1
    and t5.name5 = t1.name1;
   
select * from table(dbms_xplan.display(null,null));


----------------------------------------------------------------------------------
| Id  | Operation               | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |        |     1 |   113 |    14  (15)| 00:00:01 |
|   1 |  SORT AGGREGATE         |        |     1 |    13 |            |          |
|*  2 |   INDEX RANGE SCAN      | T1_IDX |     1 |    13 |     1   (0)| 00:00:01 |
|*  3 |  HASH JOIN              |        |     1 |   113 |    14  (15)| 00:00:01 |
|*  4 |   HASH JOIN SEMI        |        |     1 |   106 |    12  (17)| 00:00:01 |
|   5 |    NESTED LOOPS         |        |     1 |    86 |     9  (12)| 00:00:01 |
|*  6 |     HASH JOIN           |        |     1 |    73 |     8  (13)| 00:00:01 |
|*  7 |      HASH JOIN          |        |     1 |    53 |     6  (17)| 00:00:01 |
|   8 |       NESTED LOOPS      |        |     1 |    33 |     3   (0)| 00:00:01 |
|*  9 |        TABLE ACCESS FULL| T5     |     1 |    20 |     2   (0)| 00:00:01 |
|* 10 |        INDEX RANGE SCAN | T4_IDX |     1 |    13 |     1   (0)| 00:00:01 |
|  11 |       TABLE ACCESS FULL | T2     |     1 |    20 |     2   (0)| 00:00:01 |
|  12 |      TABLE ACCESS FULL  | T1     |     1 |    20 |     2   (0)| 00:00:01 |
|* 13 |     INDEX RANGE SCAN    | T3_IDX |     1 |    13 |     1   (0)| 00:00:01 |
|* 14 |    TABLE ACCESS FULL    | T3     |     1 |    20 |     2   (0)| 00:00:01 |
|  15 |   TABLE ACCESS FULL     | T5     |     1 |     7 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------

실행 계획을 해석하는데 숙달된 전문가가 아니라면 아마 지레 겁이 날 것이다. 서너 개의 인라인 뷰와 서브 쿼리만으로 실행 계획이 금방 매우 복잡해진다. 가장 큰 문제는 실행 계획의 특정 오퍼레이션(1,2,...,15)이 SQL Text의 어느 부분과 매칭되는지 직관적으로 파악하기가 어렵다는 것이다.

QB_NAME 힌트는 이런 복잡한 쿼리의 실행 계획을 해석하는데 아주 유용한 도구가 된다. QB_NAME 힌트를 이용한 예는 다음과 같다.

-- Statistics Level 을 All로 변경한다.
alter session set statistics_level = all;

-- QB_NAME 힌트 부여
explain plan for select /*+ qb_name(main) */ t1.id1, t2.name2, x.id4, x.name5,
    (select /*+ qb_name(scalar) */ count(*) from t1 s where s.id1 = t1.id1) as id1_1
from t1, t2, t3, t5,
    (select /*+ qb_name(inline) */ t4.id4, t5.name5
        from t4, t5
        where t4.id4 = t5.id5 and t5.name5 like '%c%') x
where t1.id1 = t2.id2
    and t2.id2 in (select /*+ qb_name(subquery) */ id3 from t3 where name3 like '%b%')
    and t2.id2 = x.id4
    and t3.id3 = t1.id1
    and t5.name5 = t1.name1;

-- Cursor에서 ALL stats를 조회한다.
select * from table(dbms_xplan.display(null,null, 'ALL'));

----------------------------------------------------------------------------------
| Id  | Operation               | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |        |     1 |   113 |    14  (15)| 00:00:01 |
|   1 |  SORT AGGREGATE         |        |     1 |    13 |            |          |
|*  2 |   INDEX RANGE SCAN      | T1_IDX |     1 |    13 |     1   (0)| 00:00:01 |
|*  3 |  HASH JOIN              |        |     1 |   113 |    14  (15)| 00:00:01 |
|*  4 |   HASH JOIN SEMI        |        |     1 |   106 |    12  (17)| 00:00:01 |
|   5 |    NESTED LOOPS         |        |     1 |    86 |     9  (12)| 00:00:01 |
|*  6 |     HASH JOIN           |        |     1 |    73 |     8  (13)| 00:00:01 |
|*  7 |      HASH JOIN          |        |     1 |    53 |     6  (17)| 00:00:01 |
|   8 |       NESTED LOOPS      |        |     1 |    33 |     3   (0)| 00:00:01 |
|*  9 |        TABLE ACCESS FULL| T5     |     1 |    20 |     2   (0)| 00:00:01 |
|* 10 |        INDEX RANGE SCAN | T4_IDX |     1 |    13 |     1   (0)| 00:00:01 |
|  11 |       TABLE ACCESS FULL | T2     |     1 |    20 |     2   (0)| 00:00:01 |
|  12 |      TABLE ACCESS FULL  | T1     |     1 |    20 |     2   (0)| 00:00:01 |
|* 13 |     INDEX RANGE SCAN    | T3_IDX |     1 |    13 |     1   (0)| 00:00:01 |
|* 14 |    TABLE ACCESS FULL    | T3     |     1 |    20 |     2   (0)| 00:00:01 |
|  15 |   TABLE ACCESS FULL     | T5     |     1 |     7 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------

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

   1 - SCALAR
   2 - SCALAR       / S@SCALAR
   3 - SEL$EA1A1EE6
   9 - SEL$EA1A1EE6 / T5@INLINE
  10 - SEL$EA1A1EE6 / T4@INLINE
  11 - SEL$EA1A1EE6 / T2@MAIN
  12 - SEL$EA1A1EE6 / T1@MAIN
  13 - SEL$EA1A1EE6 / T3@MAIN
  14 - SEL$EA1A1EE6 / T3@SUBQUERY
  15 - SEL$EA1A1EE6 / T5@MAIN

QB_NAME 힌트를 통해 부여한 별명을 통해 정확하게 어떤 Operation이 SQL Text의 어느 부분과 연결되어 있는지 매우 직관적으로 파악할 수 있다. 이 기능을 이용하면 매우 길고 복잡한 쿼리에서도 문제가 되는 부분을 좀 더 쉽게 파악할 수 있다.

가령 매우 복잡한 쿼리에서 특정 인라인 뷰가 실행 계획에서 어느 부분에 해당하는지 파악하려면 다음과 같이 QB_NAME 힌트를 부여하면 된다.

select
 ... { very complex query here } ...
from ( ... ( select /*+ QB_NAME(problematic_query) */ ... ) ) a, ( ... ( ... ) ) b,
    c, d, ...


QB_NAME 힌트의 또 하나의 위대한 점은 우리가 부여한 별칭을 조회 뿐만 아니라 "사용"도 가능하다는 것이다. 아래 예를 보면...

-- 우리가 부여한 subquery 이름을 이용해 no_unnest 힌트를 부여한다.
explain plan for select /*+ qb_name(main) no_unnest(@subquery) */ t1.id1, t2.name2, x.id4, x.name5,
    (select /*+ qb_name(scalar) */ count(*) from t1 s where s.id1 = t1.id1) as id1_1
from t1, t2, t3, t5,
    (select /*+ qb_name(inline) */ t4.id4, t5.name5
        from t4, t5
        where t4.id4 = t5.id5 and t5.name5 like '%c%') x
where t1.id1 = t2.id2
    and t2.id2 in (select /*+ qb_name(subquery) */ id3 from t3 where name3 like '%b%')
    and t2.id2 = x.id4
    and t3.id3 = t1.id1
    and t5.name5 = t1.name1;

select * from table(dbms_xplan.display(null,null, 'ALL'));

-- Subquery Unnesting이 Disable 됨으로써 Filter 조건이 사용되었다.
---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |     1 |    93 |    13  (16)| 00:00:01 |
|   1 |  SORT AGGREGATE              |        |     1 |    13 |            |          |
|*  2 |   INDEX RANGE SCAN           | T1_IDX |     1 |    13 |     1   (0)| 00:00:01 |
|*  3 |  FILTER                      |        |       |       |            |          |
|*  4 |   HASH JOIN                  |        |     1 |    93 |    12  (17)| 00:00:01 |
|   5 |    NESTED LOOPS              |        |     1 |    86 |     9  (12)| 00:00:01 |
|*  6 |     HASH JOIN                |        |     1 |    73 |     8  (13)| 00:00:01 |
|*  7 |      HASH JOIN               |        |     1 |    53 |     6  (17)| 00:00:01 |
|   8 |       NESTED LOOPS           |        |     1 |    33 |     3   (0)| 00:00:01 |
|*  9 |        TABLE ACCESS FULL     | T5     |     1 |    20 |     2   (0)| 00:00:01 |
|* 10 |        INDEX RANGE SCAN      | T4_IDX |     1 |    13 |     1   (0)| 00:00:01 |
|  11 |       TABLE ACCESS FULL      | T2     |     1 |    20 |     2   (0)| 00:00:01 |
|  12 |      TABLE ACCESS FULL       | T1     |     1 |    20 |     2   (0)| 00:00:01 |
|* 13 |     INDEX RANGE SCAN         | T3_IDX |     1 |    13 |     1   (0)| 00:00:01 |
|  14 |    TABLE ACCESS FULL         | T5     |     1 |     7 |     2   (0)| 00:00:01 |
|* 15 |   TABLE ACCESS BY INDEX ROWID| T3     |     1 |    20 |     1   (0)| 00:00:01 |
|* 16 |    INDEX RANGE SCAN          | T3_IDX |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

이 유용한 힌트를 복잡한 쿼리에서 활용할 수 있는 기회를 갖기를 바래본다.


신고
Trackback 0 : Comment 0

Write a comment

티스토리 툴바