2018.11.06 / 10:24
How to trace sql exectuion - Oracle 11g의 Real-Time SQL Monitoring
hanulbit
추천 수 206
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여 영원하라~
'오라클' 카테고리의 다른 글
| 2008.01.13 |
| 2008.01.11 |
| 2008.01.09 |
| 2008.01.07 |
| 2008.01.02 |
tags : dbms_sqltune.report_sql_monitor, gather_plan_statistics, Orcle 11g, real time sql monitoring, sql trace, tkprof, v$sql_monitor, v$sql_plan_monitor
출처: http://ukja.tistory.com/88 [오라클 성능 문제에 대한 통찰 - 조동욱]