태터데스크 관리자

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

태터데스크 메시지

저장하였습니다.

dbms_xplan을 이용해 Runtime Execution Plan 확인하기

오라클 2008.03.04 16:07
성능 이상 현상과 관련해서 가장 흔하게 접하는 질문 중 하나가 이것이다.

- SQL*Plus에서 수행할 때는 빠른 쿼리가 Application에서는 느린데 왜 그렇죠?


불행하게도 이유는 분명치 않다. 아니 분명치 않은게 아니라 열거 가능한 이유가 너무 많다고 하는 것이 정답일 것이다. 그 중 가장 가능성이 높은 것이 SQL*Plus에서의 실행 계획과 Runtime(Application)에서의 실행 계획이 아마도 다를 것이라는 것이다.

그래서 위와 같은 질문을 접했을 때 가장 먼저 확인해야 하는 것이 실제 Application에서 어떤 실행 계획이 생성되었는가하는 것이다. Oracle 9i까지는 이것을 확인할 수 있는 유일한 방법은 SQL Trace를 수행하는 것이다. SQL Trace를 활성화해서 운 좋게 해당 SQL이 실행되기만을 기다리는 것이다.

다행히 10g부터는 간단한 함수 하나로 이 일을 수행할  수 있다.

select * from table(dbms_xplan.dispaly_cursor('sql_id', child_number, 'format'))


간단한 예를 통해 사용법을 알아 보자.

UKJA@ukja10> -- set serveroutput off
UKJA@ukja10> set serveroutput off
UKJA@ukja10>
UKJA@ukja10> -- create objects
UKJA@ukja10> drop table t_parse purge;

Table dropped.

Elapsed: 00:00:00.50
UKJA@ukja10> create table t_parse
    (c1 varchar2(10), dummy varchar2(1));

Table created.

Elapsed: 00:00:00.04
UKJA@ukja10> -- Skewed Data를 생성한다. Many = 1만건, Few = 10건
UKJA@ukja10> insert into t_parse
  2  select 'Many', 'x'
  3  from dual
  4  connect by level <= 10000;

10000 rows created.

Elapsed: 00:00:00.06
UKJA@ukja10>
UKJA@ukja10> insert into t_parse
  2  select 'Few', 'x'
  3  from dual
  4  connect by level <= 10;

10 rows created.

Elapsed: 00:00:00.00
UKJA@ukja10> -- Index 생성
UKJA@ukja10> create index t_parse_idx on t_parse(c1);

Index created.

Elapsed: 00:00:00.09
UKJA@ukja10>
UKJA@ukja10> -- gather statistics with histogram
UKJA@ukja10> exec dbms_stats.gather_table_stats(user, 't_parse', -
>       method_opt=>'for columns c1 size 2', -
>    cascade=>true, no_invalidate=>false);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.31
UKJA@ukja10>
UKJA@ukja10> var b1 varchar2(10);
UKJA@ukja10> -- Full Table Scan이 유리한 Query를 수행
UKJA@ukja10> exec :b1 := 'Many';

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
UKJA@ukja10> -- Bind Peeking에 의해 Full Table Scan이 선택되었을 것이다.
UKJA@ukja10> select count(dummy) from t_parse where c1 = :b1 ;

COUNT(DUMMY)
------------
       10000

Elapsed: 00:00:00.00
UKJA@ukja10>
UKJA@ukja10> -- Index Range Scan이 유리한 Query를 수행
UKJA@ukja10> exec :b1 := 'Few';

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
UKJA@ukja10>
UKJA@ukja10> select count(dummy) from t_parse where c1 = :b1 ;

COUNT(DUMMY)
------------
          10

두번 째 Query는 Index Range Scan이 유리한 Query임에도 불구하고 앞서 Bind Peeking에 의해 Full Table Scan 계획이 미리 수립되었기 때문에 항상 Full Table Scan을 따르게 된다. 만일 SQL*Plus에서 두번 째 Query만을 따로 테스트했다면 우리의 눈엔 Index Range Scan이 선택된 것으로 보였을 것이다.

위의 상황에서 "실제로" 수립된 실행 계획은 다음과 같이 간단하게 확인할 수 있다.

UKJA@ukja10> -- sql_id를 구한다.
UKJA@ukja10> var v_sql_id varchar2(1000);
UKJA@ukja10>
UKJA@ukja10> begin
  2  select sql_id into :v_sql_id
  3  from v$sql
  4  where sql_text like 'select count(dummy) from t_parse where c1 = %';
  5  end;
  6  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04
UKJA@ukja10>
UKJA@ukja10> print v_sql_id;

V_SQL_ID
------------------------------------------------------------------------------------------------------------------
3xg81x5wd4q30

UKJA@ukja10> -- 현재 수립된 실행 계획을 얻는다
UKJA@ukja10> select * from table(dbms_xplan.display_cursor(:v_sql_id));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
SQL_ID  3xg81x5wd4q30, child number 0
-------------------------------------
select count(dummy) from t_parse where c1 = :b1

Plan hash value: 329829673
------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |       |       |     6(100)
|   1 |  SORT AGGREGATE    |         |     1 |     6 |
|*  2 |   TABLE ACCESS FULL| T_PARSE | 10000 | 60000 |     6   (0)
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("C1"=:B1)


19 rows selected.

+peeked_binds 옵션을 사용하면 실행 계획이 수립될 당시에 사용된(Peek된) Bind 값을 알 수 있다. 대단히 유용한 정보다.

UKJA@ukja10>
UKJA@ukja10> select * from
  2  table(dbms_xplan.display_cursor(:v_sql_id,0,'typical +peeked_binds'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
SQL_ID  3xg81x5wd4q30, child number 0
-------------------------------------
select count(dummy) from t_parse where c1 = :b1

Plan hash value: 329829673

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)|     |------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |       |       |     6(100)|        
|   1 |  SORT AGGREGATE    |         |     1 |     6 |           |        |*  2 |   TABLE ACCESS FULL| T_PARSE | 10000 | 60000 |       6(0)|
------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------
   1 - :B1 (VARCHAR2(30), CSID=846): 'Many'

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("C1"=:B1)

24 rows selected.


ALL 옵션을 사용하면 더욱 풍부한 정보를 얻을 수 있다.

UKJA@ukja10> select * from
  2  table(dbms_xplan.display_cursor(:v_sql_id,0,'all +peeked_binds'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  3xg81x5wd4q30, child number 0
-------------------------------------
select count(dummy) from t_parse where c1 = :b1

Plan hash value: 329829673
-------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU) -------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |       |       |     6(100)
|   1 |  SORT AGGREGATE    |         |     1 |     6 |          
|*  2 |   TABLE ACCESS FULL| T_PARSE | 10000 | 60000 |     6   (0)
------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / T_PARSE@SEL$1

Peeked Binds (identified by position):
--------------------------------------
   1 - :B1 (VARCHAR2(30), CSID=846): 'Many'

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("C1"=:B1)

Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - (#keys=0) COUNT("DUMMY")[22]
   2 - "DUMMY"[VARCHAR2,1]

위의 정보들을 잘 활용하면, 번거롭게 SQL Trace를 수행하지 않아도 Runtime의 Execution Plan 정보를 완벽하게 얻을 수 있다. 더구나 SQL Trace에서 제공하지 않는 많은 종류의 Runtime 정보를 제공해주므로 어떤 면에서는 더 뛰어나다고 할 수 있다.

아마 단언하건대, Oracle 10g에서 소개된 dbms_xplan.display_cursor, gather_plan_statistics 힌트와 Oracle 11g에서 소개된 Real time SQL Monitoring기능이 보편화되면 SQL Trace의 중요성은 이전에 비해 크게 낮아지게 될 것이다. 우리로서는 더욱 작업이 편리해지는 것이니 고마울 따름이지만...
Trackback 0 : Comment 1
  1. xdba 2008.11.13 11:21 신고 Modify/Delete Reply

    9i 에서도 v$sql_plan 을 통해 runtime plan 정보를 확인할 수 있습니다.

Write a comment