꿈꾸는 개발자, DBA 커뮤니티 구루비

DBMS_XPLAN

이 패키지를 사용하면 실행계획과 관련된 정보를 빠르고 정확하게 얻을 수 있다.
테스트를 하기 위한 데이터는 다음과 같이 작업 한다.

SQL >create table t1 as select level as c1, decode(level, 10000, 'one','many') as c2
  2  from dual connect by level <= 10000;
Table created.

SQL >create table t2 as select level as c1, 'x' as c2 from dual
  2  connect by level <= 10000;
Table created.

SQL >create index t1_n1 on t1(c1);
Index created.

SQL >create index t1_n2 on t1(c2);
Index created.

SQL >create index t2_n1 on t2(c1);
Index created.


SQL >exec dbms_stats.gather_table_stats('oracle','t1',method_opt=>'for all columns size skewonly');
PL/SQL procedure successfully completed.

SQL >exec dbms_stats.gather_table_stats('oracle','t2');
PL/SQL procedure successfully completed.

예상 실행계획 얻기

예상 실행 계획은 DBMS_XPLAN.DISPLAY 함수를 통해 얻을 수 있다.

SQL> explain plan for
select * from t1, t2 where t1.c1 = t2.c1 and t1.c2 = :b1;
SQL>select * from table(dbms_xplan.display);

실제 테스트 하면 다음과 같이 출력된다.

ORACLE@CX3WAS1 >select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1838229974

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  5000 | 75000 |     8  (13)| 00:00:01 |
|*  1 |  HASH JOIN         |      |  5000 | 75000 |     8  (13)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| T1   |  5000 | 45000 |     4   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   | 10000 | 60000 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - access("T1"."C1"="T2"."C1")
   2 - filter("T1"."C2"=:B1)

16 rows selected

실제 실행계획 얻기

실제 실행 계획을 얻는 방법은 DBMS_XPLAN.DISPLAY_CURSOR 함수를 사용하는 것이다.

SQL> select * from t1, t2 where t1.c1 = t2.c1 and t1.c2 = :b1;
SQL> select * from table(dbms_xplan.display_cursor);

dbms_xplan.display_cursor는 v$session에 대한 접근 권한을 가지고 있어야 한다. 테스트는 다음과 같다.

SQL >var b1 varchar2(10);
SQL >exec :b1 := 'one';
PL/SQL procedure successfully completed.

SQL >select * from oracle.t1 t1, oracle.t2 t2 where t1.c1 = t2.c1 and t1.c2 = :b1;
        C1 C2           C1 C
---------- ---- ---------- -
     10000 one       10000 x

SQL >select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  0mxyvwd9348p9, child number 0
-------------------------------------
select * from oracle.t1 t1, oracle.t2 t2 where t1.c1 = t2.c1 and t1.c2
= :b1

Plan hash value: 1838229974

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     8 (100)|          |
|*  1 |  HASH JOIN         |      |  5000 | 75000 |     8  (13)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| T1   |  5000 | 45000 |     4   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   | 10000 | 60000 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."C1"="T2"."C1")
   2 - filter("T1"."C2"=:B1)

22 rows selected.

DBMS_XPLAN.DISPLAY_CURSOR 함수는 V$SESSION 의 PREV_SQL_ID컬럼과 PREV_CHILD_NUMBER컬럼 값을 이용하여 방금 전에 실행한 쿼리를 인식한다. 그러므로, 방금전 실행한 쿼리의 정보를 가져올 필요가 없다.
즉, 실제로는 아래와 같은 방법을 사용한다.

SQL> select * from t1, t2 where t1.c1 = t2.c1 and t1.c2 = :b1;
SQL> col prev_sql_id new_value prev_sql_id
SQL> col prev_child_number new_value prev_child_number
SQL> select prev_sql_id, prev_child_number from v$session where sid = userenv('sid');
SQL> select * from table( dbms_xplan.display_cursor ('&prev_sql_id' , '&prev_child_number'));

하지만, 이런 이유로 SET SERVEROUTPUT ON 명령을 수행하면 DBMS_XPLAN.DISPLAY_CURSOR 함수가 정상적으로 동작하지 않는다.

SQL> set serveroutput on
SQL> select * from t1, t2 where t1.c1 = t2.c1 and t1.c2 = :b1;
SQL> select * from table(dbms_xplan.display_cursor);

실제 테스트 해보면 다음과 같이 출력됨을 확인 할 수 있다.

SQL >set serveroutput on
SQL >select * from oracle.t1 t1, oracle.t2 t2 where t1.c1 = t2.c1 and t1.c2 = :b1;

        C1 C2           C1 C
---------- ---- ---------- -
     10000 one       10000 x

SQL >select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  9babjv8yq8ru3, child number 0

BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;

NOTE: cannot fetch plan for SQL_ID: 9babjv8yq8ru3, CHILD_NUMBER: 0
      Please verify value of SQL_ID and CHILD_NUMBER;
      It could also be that the plan is no longer in cursor cache (check v$sql_plan)

8 rows selected

DBMS_XPLAN.DISPLAY_CURSOR 함수의 가장 큰 장점은 실행 계획 뿐아니라 일량 정보 (Plan Statistics)도 같이 보여준다는 것이다.
쿼리 수행 시 GATHER_PLAN_STATISTICS 힌트를 부여하고 DBMS_XPLAN.DISPLAY_CURSOR 함수 호출 시 FORMAT 옵션에 "ALLSTATS LAST"값을 부여하면 된다.
예제에서 SQL_ID와 CHILD_NUMBER값이 null인 것은 현재 세션에서 방금 실행한 쿼리라는 의미이다.

SQL> var b1 varchar2(10);
SQL> exec :b1 := 'one';
SQL> select /*+ gather_plan_statistics */ * from t1, t2
where t1.c1 = t2.c1 and t1.c2 = :b1;
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

테스트하면 다음과 같이 출력됨을 확인할 수 있다.

SQL >var b1 varchar2(10);
SQL >exec :b1 := 'one';

PL/SQL procedure successfully completed.

SQL >select /*+ gather_plan_statistics */ * from oracle.t1 t1, oracle.t2 t2
  2  where t1.c1 = t2.c1 and t1.c2 = :b1;

        C1 C2           C1 C
---------- ---- ---------- -
     10000 one       10000 x

SQL >select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  cnp2wr3gmdvgx, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from oracle.t1 t1, oracle.t2 t2
where t1.c1 = t2.c1 and t1.c2 = :b1

Plan hash value: 1838229974

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.01 |      22 |       |       |          |
|*  1 |  HASH JOIN         |      |      1 |   5000 |      1 |00:00:00.01 |      22 |  1088K|  1088K|  416K (0)|
|*  2 |   TABLE ACCESS FULL| T1   |      1 |   5000 |      1 |00:00:00.01 |      12 |       |       |          |
|   3 |   TABLE ACCESS FULL| T2   |      1 |  10000 |  10000 |00:00:00.02 |      10 |       |       |          |
----------------------------------------------------------------------------------------------------------------

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

   1 - access("T1"."C1"="T2"."C1")
   2 - filter("T1"."C2"=:B1)


22 rows selected.

SQLID와 CHILD_NUMBER값을 지정하면 현재 라이브러리 캐시에 등록된 특정 쿼리를 지정할 수 있다.

여기서 두가지 유의 사항이 있다.

SQL_ID만으로 특정 세션이 수행 중인 쿼리를 지정할 수 없다.
동일한 SQL 문장이 여러 개의 차일드 커서를 가질 수 있고, 차일드 커서마다 실행계획이 다를 수 있기 때문에 SQL_ID와 CHILD_NUMBER를 합쳐야 한다.
GATHER_PLAN_STATISTICS 힌트를 부여하고 쿼리를 실행하면 Plan Statistics가 계산된다.
단, 그 결과는 쿼리가 끝난 이후에 저장된다.
즉, 쿼리가 실행 중이라면 DBMS_XPLAN.DISPLAY_CURSOR호출시 Plan Statistics정보를 얻을 수 없다.

Plan Statistics 얻기

Plan Statistics가 어떤 정보를 포함하는지 보자.
정보를 정확히 확인하기 위해 다음과 같이 정렬 작업이 발생하게 정렬 작업의 크기를 최소화하고 작업을 실행계획을 만들어 보자.

SQL >alter session set workarea_size_policy=manual;

Session altered.

SQL >alter session set sort_area_size = 0;

Session altered.

SQL >select /*+ gather_plan_statistics leading(oracle.t1) use_hash(oracle.t2) */
  2  t1.c1, t2.c2 from oracle.t1 t1, oracle.t2 t2
  3  where t1.c1 = t2.c1
  4  and t1.c2 = 'many'
  5  order by t1.c1, t1.c2;
...
      9994 x
      9995 x
      9996 x
      9997 x
      9998 x
      9999 x

9999 rows selected.

SQL >select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  5yp2kdm32y34k, child number 0
-------------------------------------
select /*+ gather_plan_statistics leading(oracle.t1)
use_hash(oracle.t2) */ t1.c1, t2.c2 from oracle.t1 t1, oracle.t2 t2
where t1.c1 = t2.c1 and t1.c2 = 'many' order by t1.c1, t1.c2

Plan hash value: 2097371403

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

| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|

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

|   0 | SELECT STATEMENT    |      |      1 |        |   9999 |00:00:00.05 |      25 |     29 |     29 |       |       |          |         |

|   1 |  SORT ORDER BY      |      |      1 |   9999 |   9999 |00:00:00.05 |      25 |     29 |     29 |   234K|   234K|  193K (1)|    1024 |

|*  2 |   HASH JOIN         |      |      1 |   9999 |   9999 |00:00:00.03 |      22 |     16 |     16 |  1156K|  1156K|  492K (1)|    1024 |

|   3 |    TABLE ACCESS FULL| T2   |      1 |  10000 |  10000 |00:00:00.02 |      10 |      0 |      0 |       |       |          |         |

|*  4 |    TABLE ACCESS FULL| T1   |      1 |   9999 |   9999 |00:00:00.02 |      12 |      0 |      0 |       |       |          |         |

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


Predicate Information (identified by operation id):

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

   2 - access("T1"."C1"="T2"."C1")
   4 - filter("T1"."C2"='many')


24 rows selected.

SQL >

항목의 의미를 알아보자

Starts : 현재 단계의 오퍼레이션이 호출된 회수, Nested Loops Join인 경우 후행테이블을 접근하는 만큼 Start값이 증가한다.
E-Rows : 옵티마이져가 실행 계획을 만들때 예상한 row 건수
A-Rows : 실제 실행한 후 계산한 row 건수 E-Rows와 값이 비슷할 수록 옵티마이져의 예상이 정확하다는 것을 의미한다.
Buffer : Logical Reads를 의미한다.
Reads : Physical Reads를 의미한다.
Writes : Physical Writes를 의미한다.

Buffer와 Reads와 Writes는 트리 구조에서 하위 레벨의 오퍼레이션에서 발생한 값을 합친 누적치 이므로 분석시 참고해야 한다. 부가 정보에 대한 설명은 다음과 같다.
0Mem : Optimal 소트에 필요할 것으로 예상되는 정렬 작업 영역의 크기
1Mem : One Pass 소트에 필요할 것으로 예상되는 정렬 작업 영역의 크기
Used-Mem : 실제로 사용된 정렬 작업 영역의 크기 (0) Optimal 소트 (1) Onepass (2) Multipass

[참고]

Optimal 소트 : 메모리에서 정렬이 완료되는 것을 의미한다.
One Pass 소트 : 읽은 데이터가 너무 커서 메모리에 더 이상 수용할 수 없을때 나타나는 것으로 데이터를 읽어 들이면서 정렬을 수행하다가 가용메모리가 차게 되면 정렬된 데이터집합을 디스크로 덤프하게되는데 이러한 과정은 모든 입력 데이터를 처리할 때까지 반복하여 run(집합)을 만든다. 결국, 몇 개의 정렬된 run(집합)이 남게 되고 이것을 단일 데이터 집합으로 머지하는데, 정렬된 모든 run을 전부 읽어 들일 정도로 메모리가 충분하여 한번에 로딩하면 이것을 One Pass라고 한다.
Multi Pass 소트 : onepass가 동작원리는 같은데 run이 한번에 메모리에 로딩하지 못하는 결국 반복하는 정렬 작업을 말한다. 이때 정렬된 정보를 반복해서 읽어야 하는 횟수를 merge Pass라고 한다.

위에서 설명한 정렬 작업 영역의 크기에 대한 정보는 엄격하게 말하면 Plan Statistics 정보가 아니다. Plan Statistics정보는 - V$SQL_PLAN_STATISTICS 에서 얻는 정보를 의미한다.

SQL> desc v$sql_plan_statistics

부가정보의 정렬 작업에 사용된 정보는 V$SQL_WORKAREA 에서 얻은 정보이다.

SQL> desc v$sql_workarea

실제로 위에서 실행한 쿼리에 대한 정보를 V$SQL_WORKAREA에서 검색해보면 DBMS_XPLAN.DISPLAY_CURSOR함수와 동일한 결과를 얻을 수 있다.

SQL> select * from v$sql_workarea where sql_id = 'XXX';