태터데스크 관리자

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

태터데스크 메시지

저장하였습니다.

How to trace sql exectuion - Oracle 11g의 Real-Time SQL Monitoring

오라클 2008.01.09 11:41
우리가 실행한 SQL 문장이 "실제로" 어떻게 수행되었는지 손쉽게 추적하고 모니터링하는 방법은? 여기에 몇 개의 답변을 할 수 있을까?

1) ~ Oracle 9i
Autotrace
SQL Trace

Oracle 9i까지는 Autotrace와 SQL Trace를 이용하면 SQL이 실제로 수행하면서 얼마나 많은 일을 했는지 알 수 있다. 하지만 Autotrace는 실행 단계별 일량(Row source operation 정보라고 흔히 부름)을 남기지 않기 때문에 실제로 SQL을 추적하는데는 한계가 많다.

이런 이유 때문에 대부분의 경우 SQL Trace와 Tkprof를 이용해 SQL을 추적한다. 물론 이것이 얼마나 귀찮은 작업인지는 해본 사람은 다 공감할 것이다.

2) Oracle 10g ~
GATHER_PLAN_STATISTICS 힌트

Oracle 10g에 추가된 이 놀라운 힌트는 그 동안 우리가 그토록 목말라했던 기능을 제공한다. 쿼리를 수행하면서 바로 SQL Trace와 같은 실행 정보 추적을 할 수 없을까? 다음과 같이 가능하다.

select /*+ gather_plan_statistics */ * from t_rtm where id between 1 and 100;

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
SQL_ID  bnxtw0wqkqmx5, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from t_rtm where id between 1
and 100

Plan hash value: 2986615217

---------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID| T_RTM     |      1 |     94 |     50 |00:00:00.01 |       5 |
|*  2 |   INDEX RANGE SCAN          | T_RTM_IDX |      1 |     94 |     50 |00:00:00.01 |       3 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID">=1 AND "ID"<=100)

gather_plan_statistics라는 힌트 하나만으로 SQL Trace+Tkprof를 수행한 것과 거의 동일한 효과를 얻을 수 있다. 만세~
이 기능은 한가지 큰 장점은 Estimated 값과 Actual 값을 한 눈에 비교해 볼 수 있다는 것이다. 비용 계산이 합리적인지 가늠할 수 있는 기준이 된다.

gather_plan_statistics 힌트은 아래 블로그에서 아주 상세하게 다룬바 있다.
http://ukja.tistory.com/entry/%EC%98%A4%EB%9D%BC%ED%81%B4-%ED%8C%81-GATHERPLANSTATISTICS-%ED%9E%8C%ED%8A%B8

3) Oracle 11g ~
Real-Time SQL Monitoring (v$sql_monitor, v$sql_plan_monitor)

비록 gather_plan_statistics 힌트가 획기적인 기능을 제공하지만, 임시 방편에 불과하다.
가령 실행 시간이 5초 이상되는 느린 쿼리들의 실행 이력(plan과 row source operation까지 포함한)을 볼 수 있는 방법은 없을까?

Oracle 11g에서 추가된 Real-Time Monitoring기능이 그 해답을 제공한다. Oracle은 실행 시간이 5초 이상(_sqlmon_threshold 히든 파라미터로 조정)인 모든 쿼리의 실행 이력 정보를 v$sql_monitor와 v$sql_plan_monitor 뷰에 남긴다. 그리고 dbms_sqltune.report_sql_monitor 함수를 이용해서 정보를 조회할 수 있도록 해준다. 또는 MONITOR 힌트를 사용해서 강제로 추적하게 할 수도 있다. 아래 예를 보자. (정릴이 깨지는 현상 이해 바람)

select /*+ monitor */ * from t_rtm where id between 1 and 100;

select sql_id from v$sql where sql_text like 'select /*+ monitor */ * from t_rtm%';

sql_id = bpfu171khfbg6

select dbms_sqltune.report_sql_monitor(sql_id=>'bpfu171khfbg6') from dual

SQL Monitoring Report

SQL Text
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
select /*+ monitor */ * from t_rtm where id between 1 and 100
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Global Information
 Status              :  DONE (FIRST N ROWS)
 Instance ID         :  1                  
 Session ID          :  147                
 SQL ID              :  bpfu171khfbg6      
 SQL Execution ID    :  16777218           
 Plan Hash Value     :  2986615217         
 Execution Started   :  01/09/2008 11:05:18
 First Refresh Time  :  01/09/2008 11:05:18
 Last Refresh Time   :  01/09/2008 11:05:19

---------------------------------------
| Elapsed |  Other   | Fetch | Buffer |
| Time(s) | Waits(s) | Calls |  Gets  |
---------------------------------------
|    0.00 |     0.00 |     2 |      5 |
---------------------------------------

SQL Plan Monitoring Details
==========================================================================================================================================
| Id |           Operation           |   Name    |  Rows   | Cost |   Time    | Start  | Starts |   Rows   | Activity  | Activity Detail |
|    |                               |           | (Estim) |      | Active(s) | Active |        | (Actual) | (percent) |   (sample #)    |
==========================================================================================================================================
|  0 | SELECT STATEMENT              |           |         |    3 |           |        |      1 |          |           |                 |
|  1 |   TABLE ACCESS BY INDEX ROWID | T_RTM     |      94 |    3 |           |        |      1 |          |           |                 |
|  2 |    INDEX RANGE SCAN           | T_RTM_IDX |      94 |    2 |         1 |     +0 |      1 |       50 |           |                 |
==========================================================================================================================================

기본적으로 gather_plan_statistics와 거의 비슷한 데이터를 제공하는 것을 확인할 수 있다. 이 데이터들은 v$sql_monitor와 v$sql_plan_monitor 뷰에 상주하기 때문에 필요하면 언제든지 직접 조회할 수도 있고, dbms_sqltune.report_sql_monitor 함수를 이용해 간편하게 조회할 수도 있다.

Real-Time SQL Monitoring기능은 DBA나 SQL Tuner들이 쿼리를 튜닝하는 방법을 획기적으로 개선시킬 수 있을 것으로 기대된다. 특별한 이유가 없는 한 더 이상 SQL Trace와 Tkprof를 번갈아가며 사용할 필요가 없을 것이다.


PS) 비록 SQL 실행을 추적할 수 있는 다양한 기법들이 추가되고 있지만, 여전히 SQL Trace는 가장 강력한 SQL 추적 툴이다.

                      SQL Trace여 영원하라~
신고
Trackback 0 : Comments 4
  1. 박광서 2010.04.29 15:15 신고 Modify/Delete Reply

    언제나 유익한 정보 감사히 얻고 갑니다.
    늘 건강하세요^^

  2. 이종욱 2010.06.29 10:53 신고 Modify/Delete Reply

    select dbms_sqltune.report_sql_monitor(sql_id=>'20bk5bj29ggpj') from dual;
    실행하면..

    SQL Monitoring Report 메세지와
    SQL Text 까지만 나옵니다~

    infomation 및 plan을 보려면.. 다른 세팅이 필요한건가요??

  3. 카미유 2011.05.05 10:20 Modify/Delete Reply

    관리자의 승인을 기다리고 있는 댓글입니다

Write a comment

티스토리 툴바