Oracle 10g 까지는 NOT IN 서브쿼리를 사용할 때 NULL을 허용하는 컬럼으로 메인쿼리와 조인하면 Anti Join을 사용할 수 없었고 Filter 서브쿼리로 실행되었기 때문에 성능이 저하되었다. 마찬가지로 메인쿼리쪽의 조인컬럼이 NULL 허용이라도 Filter로 처리된다. 하지만 11g부터는 Anti Join Null Aware를 사용하여 Null인 데이터가 한 건이라도 발견되면 Scan을 중단하므로 성능이 향상된다. (The Logical Optimizer)에서도 이런 사실을 언급하고 있다. 하지만 Anti Join Null Aware로 인해 변환된 SQL의 모습은 책에서 언급되지 않았으므로 이 글을 통하여 알아보자.

 

먼저 가장 기본적인 예제를 실행해보자.

실행환경: Oracle 11.2.0.1

 

--Anti Join Null Aware를 활성화 시킨다. Default True 이므로 실행하지 않아도 됨.

ALTER SESSION SET "_optimizer_null_aware_antijoin" = TRUE;

 

SELECT d.department_id, d.department_name, location_id

  FROM department d

 WHERE d.department_id NOT IN (SELECT e.department_id

                                 FROM employee e)

   AND d.location_id = 1700;

 

NOT IN 서브쿼리는 두 가지 뜻이 있다

위의 SQL을 해석할 때 단순히 location_id = 1700인 부서 중에서 사원이 한 명도 없는 건을 출력한다고 생각하면 한가지를 놓친 것이다. 만약 이런 요건이라면 NOT IN 대신에 NOT EXISTS 서브쿼리를 사용해야 한다. 다시 말해 NOT IN 서브쿼리를 사용하면 employee 테이블의 department_id 값 중에 한 건이라도 Null이 있으면 결과집합이 출력되지 않는다. 실제로도 결과건수가 없다. 이제 위의 SQL에 해당하는 Plan을 보자.

 

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

| Id  | Operation                     | Name              | A-Rows |   A-Time   | Buffers |

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

|   0 | SELECT STATEMENT              |                   |      0 |00:00:00.01 |       7 |

|*  1 |  FILTER                       |                   |      0 |00:00:00.01 |       7 |

|   2 |   NESTED LOOPS ANTI SNA       |                   |      0 |00:00:00.01 |       0 |

|   3 |    TABLE ACCESS BY INDEX ROWID| DEPARTMENT        |      0 |00:00:00.01 |       0 |

|*  4 |     INDEX RANGE SCAN          | DEPT_LOCATION_IX  |      0 |00:00:00.01 |       0 |

|*  5 |    INDEX RANGE SCAN           | EMP_DEPARTMENT_IX |      0 |00:00:00.01 |       0 |

|*  6 |   TABLE ACCESS FULL           | EMPLOYEE          |      1 |00:00:00.01 |       7 |

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

Predicate Information (identified by operation id):

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

   1 - filter( IS NULL)

   4 - access("D"."LOCATION_ID"=1700)

   5 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")

   6 - filter("E"."DEPARTMENT_ID" IS NULL)

 

NULL을 발견하면 멈춘다

NESTED LOOPS ANTI NA라는 기능은 Null 데이터를 찾자마자 Scan을 멈추는 것이다. ID 기준으로 6번의 Predicate Information을 보면 NULL인 데이터를 단 한 건(A-Rows 참조)만 찾아내고 Scan을 멈추었다. 이제 NESTED LOOPS ANTI SNA가 어떻게 수행되는지 10053 Trace를 통하여 살펴보자.

 

FPD: Considering simple filter push in query block SEL$526A7031 (#1)

"D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID" AND "D"."LOCATION_ID"=1700 AND  NOT EXISTS (SELECT /*+ QB_NAME ("SUB") */ 0 FROM "EMPLOYEE" "E")

FPD: Considering simple filter push in query block SUB (#2)

"E"."DEPARTMENT_ID" IS NULL

try to generate transitive predicate from check constraints for query block SUB (#2)

finally: "E"."DEPARTMENT_ID" IS NULL

 

FPD(Filter Push Down) 기능으로 인하여 쿼리블럭명이 SUB Not Exists 서브쿼리가 추가 되었고 그 서브쿼리에 DEPARTMENT_ID IS NULL 조건이 추가되었다.

 

SQL 어떻게 바뀌었나?

위의 10053 Trace 결과에 따르면 Logical Optimizer SQL을 아래처럼 바꾼 것이다.

 

SELECT d.department_id, d.department_name, d.location_id

  FROM department d

 WHERE NOT EXISTS (SELECT 0           

                     FROM employee e

                    WHERE e.department_id IS NULL) –-NULL 을 체크하는 서브쿼리

   AND NOT EXISTS (SELECT 0           

                     FROM employee e

                    WHERE e.department_id  = d.department_id)                     

   AND d.location_id = 1700 ;

 

SQL을 보면 NOT IN 서브쿼리가 NOT EXIST 서브쿼리로 바뀌었고 NULL을 체크하는 서브쿼리가 추가되었다. 또한 NULL을 체크하는 서브쿼리의 결과가 한 건이라도 존재하면 SQL은 더 이상 실행되지 않는다는 것을 알 수 있다. NESTED LOOPS ANTI SNA의 비밀이 풀리는 순간이다. ORACLE 9i 10g 에서도 위와 같이 SQL을 작성하면 NESTED LOOPS ANTI SNA의 효과를 볼 수 있다. 하지만 위의 SQL처럼 수동으로 작성하는경우 NULL 한건을 체크 하는데 오래 걸리며 부하가 있다면 이렇게 사용하면 안 된다. 이제 Plan을 보자.

 

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

| Id  | Operation                     | Name              | A-Rows |   A-Time   | Buffers |

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

|   0 | SELECT STATEMENT              |                   |      0 |00:00:00.01 |       7 |

|*  1 |  FILTER                       |                   |      0 |00:00:00.01 |       7 |

|   2 |   NESTED LOOPS ANTI           |                   |      0 |00:00:00.01 |       0 |

|   3 |    TABLE ACCESS BY INDEX ROWID| DEPARTMENT        |      0 |00:00:00.01 |       0 |

|*  4 |     INDEX RANGE SCAN          | DEPT_LOCATION_IX  |      0 |00:00:00.01 |       0 |

|*  5 |    INDEX RANGE SCAN           | EMP_DEPARTMENT_IX |      0 |00:00:00.01 |       0 |

|*  6 |   TABLE ACCESS FULL           | EMPLOYEE          |      1 |00:00:00.01 |       7 |

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

Predicate Information (identified by operation id):

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

   1 - filter( IS NULL)

   4 - access("D"."LOCATION_ID"=1700)

   5 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

   6 - filter("E"."DEPARTMENT_ID" IS NULL)

 

Operation의 순서에 유의하라

위의 Plan을 과 원본 Plan을 비교해보면 원본이 ANTI SNA라는 것만 제외하면 실행계획과 일량까지 같음을 알 수 있다. 헷갈리지 말아야 할 것은 ID 기준으로 6(NULL 체크 서브쿼리)이 가장 먼저 실행된다는 것이다. 왜냐하면 서브쿼리 내부에 메인쿼리와 조인조건이 없기 때문에 서브쿼리가 먼저 실행될 수 있기 때문이다. 반대로 Filter 서브쿼리내부에 메인쿼리와 조인 조건이 있다면 메인쿼리의 컬럼이 먼저 상수화 되기 때문에 항상 서브쿼리쪽 집합이 후행이 된다. 이런 사실을 모르고 보면 PLAN상으로만 보면 NULL 체크 서브쿼리가 가장 마지막에 실행되는 것으로 착각 할 수 있다.

 

결론

Anti Join Null Aware를 사용하여 Null인 데이터가 한 건이라도 발견되면 Scan을 중단하므로 성능이 향상된다. NULL을 체크하는 Filter 서브쿼리가 추가되기 때문이다. 하지만 그런 서브쿼리가 항상 추가되는 것은 아니다. 추가되는 기준이 따로 있는데 다음 글에서 이 부분을 다루려고 한다.

 

PS

책에 위의 SQL이 빠져있다. SQL PLAN을 출력하여 끼워 넣기 바란다.

Posted by extremedb

댓글을 달아 주세요

  1. 혈기린 2010.08.02 10:19  댓글주소  수정/삭제  댓글쓰기

    좋은 내용감사 드립니다
    보통 흔히 아는 실행계획대로 읽는다면 6 - filter("E"."DEPARTMENT_ID" IS NULL) 이부분이 제일 마지막에 필터로 풀린느데 여기서는 이부분이 젤일 먼저 실행되는군요
    이런건 어떻게 판단하는건가요? 트레이스 내용을 보고 판단하는지요? 아니면 SQL을 보고 판단하는건가요?

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2010.08.02 16:41 신고  댓글주소  수정/삭제

      안녕하세요. 기린님
      이런 경우는 예외에 속하기 때문에 Plan상의 Operation 부분을 보고 판단할 수 없습니다.
      하지만 SQL을 보면 Uncorreated Subquery(비상관서브쿼리)인지 아닌지 판단할 수 있으므로 어려움은 없을것 입니다..

DBMS_XPLAN 패키지의 장점은 포맷을 자유로이 설정한다는것
  작년 겨울에 About DBMS_XPLAN - 1.실행계획 이라는 글에서 실행계획의 세부항목을 소개한바 있다.
이 패키지의가장 뛰어난 특징은 사용자가 출력 포맷을 설정하여 원하는 정보만 얻을수 있다는 것이다.
따라서 이번에는 DBMS_XPLAN 패키지 사용시 포맷설정을 자유롭게 하기 위한 Format Controller를 소개하려 한다.

Format Controller는 아래와 같이 3가지 종류가 있다.

1) 기본 Format Controller : 반드시 적용되어야 하는 기본적인 Controller 이다.
                                   적용하지 않더라도 자동으로 기본값으로 적용된다.

2)세부 Format Controller: 기본 포맷정보에 의해서 표시되거나 생략되는 되는 세부적인 포맷을 Control 한다.
                                   이 Control은  + 표시로 추가하거나 - 표시로 생략이 가능하다.

3)실행통계 Format Controller: 이 Control을 적용하면 실행시의 PGA 통계를 출력한다.

이제 한가지씩 상세히 살펴보자.

1)  기본 Format Controller
1.basic
     : 가장 기본적인 포맷으로서 id, Operation, Object Name을 출력한다.
2.typical     : basic 옵션에서 한발더 나아가서 옵티마이져가 에상할수 있는 모든것들을 보여준다.
                  출력되는 정보로는 예상 row, 예상 bytes, 예상 temporary space 사용량, cost, 예상시간,
                  Predicate Information(Operation 별로 access 및 filter 정보) 이다.
3.serial      : typical 과 같으나 parallel 쿼리사용시 관련 정보가 나오지 않는다.
4.all           : plan 정보는 typical 과 같으나 plan 이외의 정보중에서 Outline Data 정보를 제외하고 전부 출력한다.
5.advanced : all 과 같지만 Peeked Binds, Outline Data, note 등을 더보여준다.

2)  세부 Format Controller

1.alias  :Operation 별로 쿼리블럭명과 object alias 를 control 한다.
             plan 의 하단에 위치하며 쿼리변형이 발생하거나 복잡한 쿼리 튜닝의 경우 유용하다.        
2.bytes : plan 상의 E-Bytes 정보를 control 한다.
3.cost  :plan 상의 Cost (%CPU)를 control 한다.
4.note  : 결과중 가장 마지막에 위치하며 여러가지 유용한 정보를 보여준다.
            예를 들면 dynamic sampling 이 사용되었는지의 혹은 plan_table 이 old 버젼이므로 새로만들어야
            한다는 등의 유용한 정보를 나타낸다.     
5.outline : Outline Data를 control 한다. USER 가 작성한 힌트와 옵티마이져가 추가한 내부적인 힌트들이
               포함된다. 쿼리변형이 발생하거나 복잡한 쿼리 튜닝의 경우 유용하다.   
6.parallel : PARALLEL 쿼리인경우 TQ, IN-OUT, and PQ Distrib 등의 정보를 control 한다.
7.partition :파티션 ACCESS 가 포함된경우 Pstart(시작 파티션) and Pstop(종료 파티션) 등의 정보를 control 한다.
8.peeked_binds : BIND 변수의 값을 control 한다. 단 _optim_peek_user_binds 파라미터의 값이 TRUE 로
                          되어있는 경우만 해당되며  파라미터는 세션단위로 수정이 가능하다. EXPLAIN PLAN 을
                          사용한 경우에는 나타나지 않는다.
9.predicate : Predicate Information을 control 한다. Operation 별로 access 및 filter 정보를 나타낸다.
                   일반적인 튜닝시 가장 눈여겨 보아야할 정보이다.
10.projection : projection information을 control 한다. Operation 별로 select 되는 컬럼정보를 나타낸다.
11.remote : DBLINK 를 사용힐때 REMOTE 쿼리의 수행정보를 control 한다.
12.rows : plan 상의 E-Rows수를 control 한다.

3)  실행통계 Format Controller

  이정보들은 DBMS_XPLAN.DISPLAY 함수에는 적용되지 않는다. 왜냐하면 explain plan 은 쿼리가 실제 수행되는것이 아니므로 실행통계정보가 없기 때문이다.
또한 DBMS_XPLAN.DISPLAY_CURSOR 나 DBMS_XPLAN.DISPLAY_AWR 등의 함수 수행시에도 GATHER_PLAN_STATISTICS 힌트를 주거나 아니면 파라미터 STATISTICS_LEVEL = ALL 로 되어 있어야 출력이 가능하다.

1.allstats : I/O 통계정보(Buffers, Reads, Writes)와 PGA 통계정보(OMem, 1Mem , Used-Mem, Used-Tmp,
                Max-Tmp 등)를 동시에 control 한다.
2.iostats : I/O 통계정보(Buffers, Reads, Writes)를 control 한다.
3.last : 실행통계 출력시 이 control을 명시하면 가장마지막에 수행된 실행통계를 출력한다.
          이 control을 명시하지 않으면 실행통계의 누적치를 출력하므로 주의가 필요하다.
4.memstats :PGA 통계정보(OMem, 1Mem , Used-Mem, Used-Tmp, Max-Tmp 등)를 control 한다.
5.runstats_last : iostats control 과 last control 을 합친것과 같다.
                       이 control은 Oracle 10g Release 1 에서만 사용할수 있다.
6.runstats_tot : iostats control과 동일하다.  이 control은 Oracle 10g Release 1 에서만 사용할수 있다.

주의사항 : runstats_last 와 runstats_tot 를 제외한 4가지의 control은 Oracle 10g Release 2 에서만 사용할수 있다.


그럼 이제 적용해볼까?
  위에서 설명한 Controller 를 이용하여 Format 을적용해보자.

SElECT  /*+ gather_plan_statistics */  *
FROM EMP E
WHERE E.DEPTNO = :B1
  AND ROWNUM <= 100
ORDER BY EMPNO;

이후로는 위의 SQL 은 동일하므로 생략된다.

SELECT *
   FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL, 'advanced allstats last' ) );


아래의 plan 은 지면관계상 잘려서 2줄로 나타내었음을 이해해주기 바란다.

----------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Starts | E-Rows |E-Bytes|E-Temp |
----------------------------------------------------------------------------------
|   1 |  SORT ORDER BY                |        |      1 |    100 |  2600 |   153M|
|*  2 |   COUNT STOPKEY               |        |      1 |        |       |       |
|   3 |    TABLE ACCESS BY INDEX ROWID| EMP    |      1 |   2002K|    49M|       |
|*  4 |     INDEX RANGE SCAN          | EMP_N1 |      1 |   2003K|       |       |
----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
 Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------
 19034   (1)| 00:03:49 |      9 |00:00:00.01 |       4 |  2048 |  2048 | 2048  (0)|
            |          |      9 |00:00:00.01 |       4 |       |       |          |
  4126   (1)| 00:00:50 |      9 |00:00:00.01 |       4 |       |       |          |
   989   (1)| 00:00:12 |      9 |00:00:00.01 |       3 |       |       |          |
-----------------------------------------------------------------------------------

 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   3 - SEL$1 / E@SEL$1
   4 - SEL$1 / E@SEL$1
 
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
      OPT_PARAM('_optim_peek_user_binds' 'false')
      OPT_PARAM('_bloom_filter_enabled' 'false')
      OPT_PARAM('_optimizer_connect_by_cost_based' 'false')
      OPT_PARAM('optimizer_index_cost_adj' 25)
      OPT_PARAM('optimizer_index_caching' 90)
      FIRST_ROWS(1)
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "E"@"SEL$1" ("EMP"."DEPTNO"))
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(ROWNUM<=100)
   4 - access("E"."DEPTNO"=:B1)
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - (#keys=1) "E"."EMPNO"[NUMBER,22], "E"."DEPTNO"[NUMBER,22], "E"."EMPNO_VARCHAR"[VARCHAR2,40], "E"."JOB"[VARCHAR2,2], "E"."HIREDATE"[DATE,7]
   2 - "E"."EMPNO"[NUMBER,22], "E"."EMPNO_VARCHAR"[VARCHAR2,40], "E"."JOB"[VARCHAR2,2], "E"."HIREDATE"[DATE,7], "E"."DEPTNO"[NUMBER,22]
   3 - "E"."EMPNO"[NUMBER,22], "E"."EMPNO_VARCHAR"[VARCHAR2,40], "E"."JOB"[VARCHAR2,2], "E"."HIREDATE"[DATE,7], "E"."DEPTNO"[NUMBER,22]
   4 - "E".ROWID[ROWID,10], "E"."DEPTNO"[NUMBER,22]
 

 'advanced allstats last' 포맷은 출력되는 정보가 너무많아
   'advanced allstats last' 포맷을 적용하였으므로 DBMS_XPLAN.DISPLAY_CURSOR 가 보여줄수 있는 모든
정보를 출력 하였다. 단 지면 관계상 가장 처음에 나오는 SQL TEXT 와 sql_id, child number, plan_hash_value 등은 생략하였다. 많은정보를 생략하였음에도 불구하고 일반적인 튜닝시 필요가 없는 정보가 모두 출력되고 말았다. 

 이제 위에서 정의된 각 Controller 를 이용하여 여러분만의 Format 을 만들어보자.
필자의 경우 가장 선호하는 포맷은 아래의 두가지 이다.
 
권장되는 포맷유형 2가지

1.쿼리변형이 없는 단순 쿼리 튜닝의 경우:

SELECT *
   FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL, 'allstats last -rows +predicate'));

 포맷을 'allstats last -rows +predicate' 로 주었으므로 예측 row 수(E-row) 가 생략되고 실행통계와
Predicate Information 만을 출력한다.
 아래의 plan 또한 너무길어 지면관계상 2줄로 나타내었다.

-------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Starts | A-Rows |   A-Time   |
-------------------------------------------------------------------------------
|   1 |  SORT ORDER BY                |        |      1 |      9 |00:00:00.01 |
|*  2 |   COUNT STOPKEY               |        |      1 |      9 |00:00:00.01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| EMP    |      1 |      9 |00:00:00.01 |
|*  4 |     INDEX RANGE SCAN          | EMP_N1 |      1 |      9 |00:00:00.01 |
-------------------------------------------------------------------------------

      -------------------------------------
       Buffers |  OMem |  1Mem | Used-Mem |
      -------------------------------------
             4 |  2048 |  2048 | 2048  (0)|
             4 |       |       |          |
             4 |       |       |          |
             3 |       |       |          |
      -------------------------------------

 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(ROWNUM<=100)
   4 - access("E"."DEPTNO"=:B1)
 
 
깔끔하게 꼭필요한 정보만 출력 되었다.


2.쿼리변형이 발생하거나 복잡한 쿼리 튜닝의 경우.

SELECT * FROM
  TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL, 'allstats last -rows +alias +outline +predicate'));


 'allstats last -rows +alias +outline +predicate' 포맷을 사용하면 Query Block Name / Object Alias 정보와 Outline Data 정보가 추가로 출력된다.
아래의 plan 도 지면관계상 2줄로 나타내었다.

-------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Starts | A-Rows |   A-Time   |
-------------------------------------------------------------------------------
|   1 |  SORT ORDER BY                |        |      1 |      9 |00:00:00.01 |
|*  2 |   COUNT STOPKEY               |        |      1 |      9 |00:00:00.01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| EMP    |      1 |      9 |00:00:00.01 |
|*  4 |     INDEX RANGE SCAN          | EMP_N1 |      1 |      9 |00:00:00.01 |
-------------------------------------------------------------------------------

      -------------------------------------
       Buffers |  OMem |  1Mem | Used-Mem |
      -------------------------------------
             4 |  2048 |  2048 | 2048  (0)|
             4 |       |       |          |
             4 |       |       |          |
             3 |       |       |          |
      -------------------------------------

 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   3 - SEL$1 / E@SEL$1
   4 - SEL$1 / E@SEL$1
 
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
      OPT_PARAM('_optim_peek_user_binds' 'false')
      OPT_PARAM('_bloom_filter_enabled' 'false')
      OPT_PARAM('_optimizer_connect_by_cost_based' 'false')
      OPT_PARAM('optimizer_index_cost_adj' 25)
      OPT_PARAM('optimizer_index_caching' 90)
      FIRST_ROWS(1)
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "E"@"SEL$1" ("EMP"."DEPTNO"))
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(ROWNUM<=100)
   4 - access("E"."DEPTNO"=:B1)
 
  쿼리변형이 발생한경우나 뷰(혹은 인라인뷰) 등을 튜닝할경우 아주 적합한 옵션이다. 하지만 특이한경우
Column Projection Information 이 필요한경우도 있다. 이경우는 +projection 포맷을 추가해주면 된다.

  자신만의 적절한 포맷이 필요해
 출력되는 정보의 양이 너무 많으면 소화 하기가 힘들고 너무 적으면 튜닝하기가 어려워진다. SQL이 아무리 복잡하고 다양한 경우가 있더라도 2~3 가지의 Format 조합으로도 충분하다. 여러분 각자의 입맛에 맞는 Format 을 개발해보길 바란다. 물론 그러기 위해서는 각각의 Controller 들과 친해질 필요가 있다.

Posted by extremedb

댓글을 달아 주세요

최근의 많은수의 사람들이 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 만 구하면 언제든지 실행될수 있다.

select * from table(dbms_xplan.display_awr(:v_sql_id,null,null,'advanced allstats last'));



사용자 삽입 이미지





위 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

Posted by extremedb

댓글을 달아 주세요

  1. Favicon of http://xsoft.tistory.com BlogIcon 강정식 2009.04.08 15:02  댓글주소  수정/삭제  댓글쓰기

    안녕하세요 동규님 ^^ 강정식입니다.
    XPLAN에서 여쭤볼것이 있어서 이렇게 질문을 남깁니다.

    제가 'XPLAN과 10046 event를 이용한 튜닝방법' 세미나를 준비중이라
    DBMS_XPLAN.DISPLAY(이하 DISPLAY), DBMS_XPLAN.DISPLAY_CURSOR(이하 CURSOR)
    2개를 테스트 해 보고 있습니다.

    그런데 ADVANCED Format을 통해 나온 Query Block Name(이하 블럭)을 이용하여
    인덱스를 변경하는 건을 테스트 하다가 생각지도 못한 Case가 발견되어 질문을 드립니다.

    이야기로 풀어 정리를 하자면 DISPLAY를 통해 나온 블럭을 이용하여 힌트를 교정한 뒤
    CURSOR로 수행을 해보니 그 힌트가 적용되지 않았습니다.

    하여 그 원인을 확인해본 결과 Predicate Information에서 바인드 변수가 TO_NUMBER(:B1)로
    바뀌어서 들어가는데 이로 인해 DISPLAY 할 때와 CURSOR 할 때가 PLAN이 바뀌어져 버렸고
    이로 인해 블럭또한 바뀌어서 적용이 안되었습니다.

    하여 DISPLAY시 바인드 변수의 데이터타입을 지정한 뒤 PLAN을 확인해보니 여전히
    TO_NUMBER(:B1)로 적용이 되고 있었습니다. 결국 PLAN을 확인할 때는 바인드 변수의
    데이터타입을 핸들링 할 수 없었는데 혹시 동규님께서 이를 핸들링하는 방법에 대해
    아시고 계신게 있으신지요?

    아래에 간단하게 데이터타입에 대한 내용을 확인할 수 있는 스크립트를 올려드립니다.

    -- create table
    DROP TABLE TEST_X PURGE;

    CREATE TABLE TEST_X AS
    SELECT LEVEL EMPNO
    FROM DUAL
    CONNECT BY LEVEL <= 100
    ;

    -- create index
    CREATE UNIQUE INDEX EMP_U1 ON TEST_X (EMPNO) COMPUTE STATISTICS;

    -- gather statistics
    EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'TEST_X', CASCADE => TRUE);

    -- 테스트
    VAR B1 NUMBER;
    :B1 := 1;

    EXPLAIN PLAN FOR
    SELECT *
    FROM TEST_X
    WHERE EMPNO = :B1
    ;

    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

    --------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    --------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | 16 | 1 (0)| 00:00:01 |
    | 1 | TABLE ACCESS BY INDEX ROWID| TEST_X | 1 | 16 | 1 (0)| 00:00:01 |
    |* 2 | INDEX UNIQUE SCAN | EMP_U1 | 1 | | 0 (0)| 00:00:01 |
    --------------------------------------------------------------------------------------

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

    2 - access("EMPNO"=TO_NUMBER(:B1))
    ;

    SET SERVEROUTPUT OFF;

    VAR B1 NUMBER;
    :B1 := 1;

    SELECT /*+ GATHER_PLAN_STATISTICS */
    *
    FROM TEST_X
    WHERE EMPNO = :B1
    ;

    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);

    --------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    --------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | | | 1 (100)| |
    | 1 | TABLE ACCESS BY INDEX ROWID| TEST_X | 1 | 16 | 1 (0)| 00:00:01 |
    |* 2 | INDEX UNIQUE SCAN | EMP_U1 | 1 | | 0 (0)| |
    --------------------------------------------------------------------------------------

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

    2 - access("EMPNO"=:B1)

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2009.04.14 17:41 신고  댓글주소  수정/삭제

      :+ 변수명을 사용하면 implicit 형변환을 피할수 없습니다.
      explin plan 에서 : + 변수명을 사용할경우 값은 제대로 binding 되지만 변수자체는 항상 varchar2 로 인식되기 때문입니다.
      컬럼이 varchar2 형이 아닌 경우 항상 형변환이 발생 하겠죠.
      형변환을 피할수 있는 방법은 :+ 변수명 대신에 & + 변수명을 사용하시기 바랍니다.
      또한 accept 명령어를 사용해도 같은 효과를 볼수 있습니다.
      하지만 & + 변수명을 사용하면 binding 을 하지않고 변수를 상수로 replace 하는 개념입니다.
      하지만 explain plan 이라는 것은 어짜피 adhoc query 이므로 latch 걱정은 안하셔도 됩니다.

      아래의 스크립트를 참고 하시기 바랍니다.

      SQL> EXPLAIN PLAN FOR
      2 SELECT *
      3 FROM (SELECT E1.*
      4 FROM EMP E1
      5 UNION ALL
      6 SELECT E1.*
      7 FROM EMP E1
      8 UNION ALL
      9 SELECT E1.*
      10 FROM EMP E1
      11 UNION ALL
      12 SELECT E1.*
      13 FROM EMP E1) EMP_V,
      14 DEPT D
      15 WHERE EMP_V.DEPTNO = D.DEPTNO
      16 AND D.DEPTNO = &v_deptno
      17 ;
      v_deptno의 값을 입력하십시오: 10
      구 16: AND D.DEPTNO = &v_deptno
      신 16: AND D.DEPTNO = 10

      해석되었습니다.

      SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL,NULL,'ADVANCED'));

      PLAN_TABLE_OUTPUT
      --------------------------------------------------------------------------------

      -------------------------------------------------------------------------------
      | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
      -------------------------------------------------------------------------------
      | 0 | SELECT STATEMENT | | 1 | 100 | 2 (0)|
      | 1 | NESTED LOOPS | | 1 | 100 | 2 (0)|
      | 2 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 62 | 1 (0)|
      |* 3 | INDEX UNIQUE SCAN | DEPT_U1 | 1 | | 1 (0)|
      | 4 | VIEW | | 1 | 38 | 1 (0)|
      | 5 | UNION-ALL | | | | |
      | 6 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 18 | 1 (0)|
      |* 7 | INDEX RANGE SCAN | EMP_N1 | 1 | | 1 (0)|
      | 8 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 18 | 1 (0)|
      |* 9 | INDEX RANGE SCAN | EMP_N1 | 1 | | 1 (0)|
      | 10 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 18 | 1 (0)|
      |* 11 | INDEX RANGE SCAN | EMP_N1 | 1 | | 1 (0)|
      | 12 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 18 | 1 (0)|
      |* 13 | INDEX RANGE SCAN | EMP_N1 | 1 | | 1 (0)|
      -------------------------------------------------------------------------------

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

      PLAN_TABLE_OUTPUT
      --------------------------------------------------------------------------------

      3 - access("D"."DEPTNO"=10)
      7 - access("E1"."DEPTNO"=10)
      9 - access("E1"."DEPTNO"=10)
      11 - access("E1"."DEPTNO"=10)
      13 - access("E1"."DEPTNO"=10)
      ... 이하생략
      즐거운 하루 되세요.

  2. Favicon of http://xsoft.tistory.com BlogIcon 강정식 2009.04.15 16:33  댓글주소  수정/삭제  댓글쓰기

    답변 감사드립니다.
    제가 이런 질문을 드리는 이유는 bind 변수가 explain plan과 execution plan에서 틀린 경우가 있어서 그랬구요...
    이와 관련되어 테스트 한 내용을 링크걸어 드리니 한번 봐주시기 바랍니다 ^^

    http://blog.naver.com/xsoft/150045721858

    감사합니다.

  3. Favicon of http://xsoft.tistory.com BlogIcon 강정식 2009.04.16 09:12  댓글주소  수정/삭제  댓글쓰기

    메일 보내 드렸습니다. 확인 부탁 드립니다 ^^

  4. Favicon of http://xsoft.tistory.com BlogIcon 강정식 2009.04.16 15:00  댓글주소  수정/삭제  댓글쓰기

    동규님. 상세한 답변 감사드립니다. ^^
    FPD가 JPPD보다 COST가 낮아 옵티마이저가 먼저 고려되는 것인지 덕분에 처음 알았습니다.
    정말 동규님의 깊은 내공을 잠시나마 뵐 수 있었습니다.

  5. Favicon of http://blog.naver.com/xsoft BlogIcon 강정식 2009.04.24 11:45  댓글주소  수정/삭제  댓글쓰기

    안녕하세요. 동규님

    'DBMS_XPLAN.DISPLAY_CURSOR'의 A-Time에 대해 궁금한 점이 있어서 질문을 드립니다.
    제가 알고 있기로는 A-Time이 실제 수행시간을 표현하는 것으로 알고 있는데 이 값이 Trace와
    차이가 나는 부분이 확인되어 이에 대해 질문을 드리고자 합니다.

    아래는 이 내용을 재현할 수 있는 스크립트와 해당 내용에 대해 나열한 것입니다.


    1. 수행 스크립트
    -- create table
    DROP TABLE EMP PURGE;
    DROP TABLE DEPT PURGE;

    CREATE TABLE EMP AS
    SELECT LEVEL EMPNO,
    TO_CHAR(LEVEL) EMPNO_VARCHAR,
    CHR(65 + CEIL(LEVEL / 500000) - 1) JOB,
    TO_DATE('00010101', 'YYYYMMDD') + CEIL(LEVEL / 10) - 1 HIREDATE,
    LENGTH(LEVEL) * 10 DEPTNO
    FROM DUAL
    CONNECT BY LEVEL <= 10000000
    ;

    CREATE TABLE DEPT AS
    SELECT LEVEL * 10 DEPTNO,
    'SALES_' || LEVEL DNAME,
    'ZONE_' || LEVEL LOC
    FROM DUAL
    CONNECT BY LEVEL <= 9
    ;

    -- create index
    CREATE UNIQUE INDEX USER.EMP_U1 ON APPS.EMP (EMPNO) COMPUTE STATISTICS PARALLEL 8;
    ALTER INDEX USER.EMP_U1 NOPARALLEL;

    CREATE INDEX USER.EMP_N1 ON APPS.EMP (DEPTNO) COMPUTE STATISTICS PARALLEL 8;
    ALTER INDEX USER.EMP_N1 NOPARALLEL;

    CREATE INDEX USER.EMP_N2 ON APPS.EMP (HIREDATE) COMPUTE STATISTICS PARALLEL 8;
    ALTER INDEX USER.EMP_N2 NOPARALLEL;

    CREATE INDEX USER.EMP_N3 ON APPS.EMP (EMPNO_VARCHAR) COMPUTE STATISTICS PARALLEL 8;
    ALTER INDEX USER.EMP_N3 NOPARALLEL;

    CREATE UNIQUE INDEX USER.DEPT_U1 ON APPS.DEPT (DEPTNO);

    -- gather statistics
    EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'EMP', CASCADE => TRUE, DEGREE => 8);

    EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'DEPT', CASCADE => TRUE);




    2. 테스트
    1) Trace 내용
    SElECT *
    FROM EMP E,
    DEPT D
    WHERE E.DEPTNO = D.DEPTNO
    AND (E.DEPTNO = :B1 -- 10
    OR
    E.HIREDATE BETWEEN TO_DATE(:B2, 'YYYYMMDD') -- 2009/04/01
    AND TO_DATE(:B3, 'YYYYMMDD') -- 2009/04/02
    )
    ;

    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse 1 0.00 0.00 0 0 0 0
    Execute 1 0.00 0.00 0 0 0 0
    Fetch 3 9.25 9.06 0 44245 0 29
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 5 9.25 9.06 0 44245 0 29

    Misses in library cache during parse: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: 44

    Rows Row Source Operation
    ------- ---------------------------------------------------
    29 HASH JOIN (cr=44245 pr=0 pw=0 time=9059820 us)
    9 TABLE ACCESS FULL DEPT (cr=3 pr=0 pw=0 time=180 us)
    29 TABLE ACCESS FULL EMP (cr=44242 pr=0 pw=0 time=9057805 us)


    Elapsed times include waiting on following events:
    Event waited on Times Max. Wait Total Waited
    ---------------------------------------- Waited ---------- ------------
    SQL*Net message to client 3 0.00 0.00
    SQL*Net message from client 3 0.31 0.57






    2) DBMS_XPLAN.DISPLAY_CURSOR 내용
    TEST >
    1 SElECT /*+ GATHER_PLAN_STATISTICS */
    2 *
    3 FROM EMP E,
    4 DEPT D
    5 WHERE E.DEPTNO = D.DEPTNO
    6 AND (E.DEPTNO = :B1 -- 10
    7 OR
    8 E.HIREDATE BETWEEN TO_DATE(:B2, 'YYYYMMDD') -- 2009/04/01
    9 AND TO_DATE(:B3, 'YYYYMMDD') -- 2009/04/02
    10 )
    11 ;

    EMPNO EMPNO_VARCHAR JOB HIREDATE DEPTNO DEPTNO DNAME
    ---------- ------------- ------ ------------ ---------- ---------- --------
    1 1 A 01-JAN-01 10 10 SALES_1
    2 2 A 01-JAN-01 10 10 SALES_1
    3 3 A 01-JAN-01 10 10 SALES_1
    4 4 A 01-JAN-01 10 10 SALES_1
    5 5 A 01-JAN-01 10 10 SALES_1
    6 6 A 01-JAN-01 10 10 SALES_1
    7 7 A 01-JAN-01 10 10 SALES_1
    8 8 A 01-JAN-01 10 10 SALES_1
    9 9 A 01-JAN-01 10 10 SALES_1
    7334991 7334991 O 01-APR-09 70 70 SALES_7
    7334992 7334992 O 01-APR-09 70 70 SALES_7
    7334993 7334993 O 01-APR-09 70 70 SALES_7
    7334994 7334994 O 01-APR-09 70 70 SALES_7
    7334995 7334995 O 01-APR-09 70 70 SALES_7
    7334996 7334996 O 01-APR-09 70 70 SALES_7
    7334997 7334997 O 01-APR-09 70 70 SALES_7
    7334998 7334998 O 01-APR-09 70 70 SALES_7
    7334999 7334999 O 01-APR-09 70 70 SALES_7
    7335000 7335000 O 01-APR-09 70 70 SALES_7
    7335001 7335001 O 02-APR-09 70 70 SALES_7
    7335002 7335002 O 02-APR-09 70 70 SALES_7
    7335003 7335003 O 02-APR-09 70 70 SALES_7
    7335004 7335004 O 02-APR-09 70 70 SALES_7
    7335005 7335005 O 02-APR-09 70 70 SALES_7
    7335006 7335006 O 02-APR-09 70 70 SALES_7
    7335007 7335007 O 02-APR-09 70 70 SALES_7
    7335008 7335008 O 02-APR-09 70 70 SALES_7
    7335009 7335009 O 02-APR-09 70 70 SALES_7
    7335010 7335010 O 02-APR-09 70 70 SALES_7

    29 rows selected.

    Elapsed: 00:00:09.67
    TEST >
    1 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------------------------------------------------
    SQL_ID gczuawz61s195, child number 0
    -------------------------------------
    SElECT /*+ GATHER_PLAN_STATISTICS */ * FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO AND
    (E.DEPTNO = :B1 -- 10 OR E.HIREDATE BETWEEN TO_DATE(:B2, 'YYYYMMDD') -- 2009/04/01
    AND TO_DATE(:B3, 'YYYYMMDD') -- 2009/04/02 )

    Plan hash value: 1093152308

    -------------------------------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
    -------------------------------------------------------------------------------------------------------------------------
    |* 1 | HASH JOIN | | 1 | 2021K| 29 |00:00:00.01 | 44245 | 2 | 1023K| 1023K| 811K (0)|
    | 2 | TABLE ACCESS FULL| DEPT | 1 | 9 | 9 |00:00:00.01 | 3 | 0 | | | |
    |* 3 | TABLE ACCESS FULL| EMP | 1 | 2021K| 29 |00:00:00.01 | 44242 | 2 | | | |
    -------------------------------------------------------------------------------------------------------------------------

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

    1 - access("E"."DEPTNO"="D"."DEPTNO";)
    3 - filter(("E"."DEPTNO"=:B1 OR ("E"."HIREDATE">=TO_DATE(:B2,'YYYYMMDD') AND
    "E"."HIREDATE"<=TO_DATE(:B3,'YYYYMMDD'))))




    내용을 보시면 아시겠지만 Trace에서는 수행속도가 9초 정도 걸렸고 이 내용이 row operation에서도
    정확히 표현이 되고 있습니다.
    하지만 'DBMS_XPLAN.DISPLAY_CURSOR'에서는 수행 시 'Elapsed: 00:00:09.67'가 걸렸지만 A-Time에서는
    '00.01'초만 걸린 것으로 확인이 되고 있습니다.

    왜 이런 현상이 나오는지 알 수 있을까요?

  6. Favicon of https://scidb.tistory.com BlogIcon extremedb 2009.04.24 15:14 신고  댓글주소  수정/삭제  댓글쓰기

    오라클 버그 입니다.
    이런 경우에는
    alter session set STATISTICS_LEVEL = ALL ;
    을 설정 하시고 다시한번 SQL 과 DBMS_XPLAN.DISPLAY_CURSOR 를 실행 해보시기 바랍니다.
    감사합니다.

  7. Favicon of http://blog.naver.com/xsoft BlogIcon 강정식 2009.04.24 18:43  댓글주소  수정/삭제  댓글쓰기

    답변 감사드립니다 ^^