ORACLE
HOME > DB > ORACLE
2018.11.06 / 10:24

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

hanulbit
추천 수 179


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

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

    • 이종욱 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을 보려면.. 다른 세팅이 필요한건가요??

    • 카미유 2011.05.05 10:20 Modify/Delete Reply

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



    출처: http://ukja.tistory.com/88 [오라클 성능 문제에 대한 통찰 - 조동욱]