오라클 Plan, Trace 쉽게 보는 - Realtime SQL Monitoring

Oracle DBMS 11g 부터 사용할 수 있는 아주 유용한 기능중에 하나가 Realtime SQL Monitoring 이라는 기능입니다.

이 기능은 EM (Enterprise Manager) 이라는 Oracle 유료 툴의 일부 기능입니다.

따라서 정식으로 사용하기 위해서는 EM Tuning Pack 라이센스가 필요합니다.

하지만, 따로 제한이 걸려있거나 하지 않습니다. 개인적으로, 또는 연습목적으로 사용하는 데는 아무런 제약이 없습니다.

 

Realtime SQL Monitoring 을 사용하는 구문은 아래와 같습니다.

 

SQL> 
set long 1000000
set longchunksize 1000000
set linesize 1000
select DBMS_SQLTUNE.report_sql_monitor(sql_id => '&sql_id', type=>'TEXT' , report_level => 'ALL') from dual;

 

그닥, 복잡하지 않습니다. DBMS_SQLTUNE 패키지의 report_sql_monitor() 함수를 호출하면 됩니다.

이때, 결과를 보기좋게 만들기 위해 set long, set linesize 같은 구문을 실행해 줍니다.

 

실행결과는 아래와 같이 나옵니다.

 

 

맨 윗부분에 실행한 SQL문장이 표시되고,

다음에 정보(Information) 부분이 표시됩니다. 실행중인지, 실행이 끝났는지, 어떤 유저에서 실행한 건지 등등...

다음으로 통계정보(Stats) 가 표시됩니다.

    - Elapsed Time : 경과시간

    - Cpu Time(s)   : CPU 사용시간 (초단위)

    - IO Waits(s)    : Disk I/O에 걸린 시간 (초단위)

    - Fetch Calls    : Fetch 횟수 (조회결과 건수 아님)

    - Buffer Gets   : 가져온 Block 갯수

    - Read Reqs    : Disk I/O 요청 횟수

    - Read Bytes   : Disk I/O 한 크기 (MB)

 

마지막으로 Plan 이 표시됩니다. 정확히 말하면 Plan + Trace 입니다.

단순히 실행계획만 나타나는 것이 아니고, 실제로 실행한 정보가 표시됩니다.

아래 일반 Plan 과 비교해보면 차이점을 확연히 알 수 있습니다.

 

 

위 Plan 뜬 걸 보면 Rows 가 8192건으로 나옵니다. 이건 실제로 조회를 해보지 않고, 통계정보만을 바탕으로 Oracle Optimizer 가 예측한 정보입니다.

 

 

Realtime SQL Report 에서 보면 Rows(Actual) 이라는 값을 확인할 수 있습니다.

통계정보를 기반으로 Plan을 봤을때는 Rows(Estim) 값이 8192 건으로 예측되었지만,

실제로 실행해보니, Row(Actual) 값이 65536 건이 조회되었다는 의미입니다.

 

이렇게 실제 실행한 정보를 확인할 수 있기 때문에 과거 Trace 뜨는 것과 같은 효과를 발휘합니다.

이 경우는 통계정보가 실제와 많이 다른 것을 확인할 수 있고, 따라서 통계정보 갱신작업을 해줄 필요가 있습니다.

 

위에서 report_sql_monitor() 함수를 호출하면, &sql_id 에 의해 sql_id 값을 입력하라고 합니다.

즉, sql_id 값을 알아야 Realtime SQL Report 를 확인할 수 있습니다.

 

SQL> 
set linesize 1000
select username, module, sql_id, substr(sql_text,1,60) sql_text
from v$sql_monitor
where sql_exec_start >= sysdate - 1/24
order by sql_exec_start;

 

sql_id 값을 얻기 위해 위와 같이 딕셔너리 뷰에서 조회할 수 있습니다.

 

 

v$sql_monitor 딕셔너리뷰를 조회하면 위에서처럼 SQL 문장들을 확인할 수 있고, 내 SQL문을 찾으면, sql_id 값을 확인할 수 있습니다.

sql_id 는 다른 toad 나 orange 같은 툴에서도 쉽게 확인할 수 있습니다. 툴에서 나오는 sql_id 와 같은 것이기 때문에 그걸 사용해도 됩니다.

 

Realtime SQL Monitoring 은 디폴트로 5초 이상 수행된 SQL에 대해 위와같은 정보를 가지고 있습니다.

따라서 5초 이내로 수행된 SQL 들은 위와같이 조회해도 sql_id 값을 찾을 수 없습니다.

5초 이내의 SQL 이라도 특정 SQL 에 대해 모니터링하고자 하는 경우는 SQL에 /*+ monitor */ 힌트를 주면 됩니다.

 

또, sys 나 system 유저가 아닌 일반 DB유저 (예, scott) 에서 report_sql_monitor() 함수를 실행하고자 하는 경우는 에러가 발생합니다. 이때는 해당 유저에 권한부여가 필요합니다.   ( 참조 => 모니터링 권한부여 )

 

 

▶ 참고) Realtime SQL Monitoring 으로 Plan 그래픽컬하게 보기