[Oracle] Oracle Real-Time SQL Monitoring(쿼리 모니터링)

Oracle 11g 이상부터 Real-Time SQL Monitoring(v$sql_monitor, v$sql_plan_monitor) 기능을 사용할 수 있습니다. 

Oracle 11g에서 추가된 Real-Time SQL Monitoring 기능은 5초 이상인 모든 쿼리의 실행 이력 정보를 v$sql_monitor와 v$sql_plan_monitor 뷰에 남깁니다. 그리고 DBMS_SQLTUNE.REPORT_SQL_MONITOR 함수를 이용해서 정보를 조회 가능하도록 지원 됩니다. 

1. Pack 항목 확인

Oracle Diagnostic Pack

 - AWR

 - ADDM

 - ASH

 - Performance Tuning

 - Event notifications

 - Event history and metric history

 - Blackouts

 - Dynamic metric baselines

 - Monitoring templates

 - Memory-access based performance montoring

 

Tuning Pack

 - SQL Access Advisor

 - SQL Tuning Advisor

 - Automatic SQL Tuning

 - SQL Tuning Sets

 - SQL Monitoring

 - Reorganize objects

control_management_pack_access 파라미터는 3가지가 존재합니다. NONE은 Pack 두 가지 모두 사용하지 않겠다는 의미이며, DIAGNOSTIC은 oracle diagnostic pack만 사용 가능하며, DIAGOSTC+TUNING은 두 가지 Pack를 모두 사용 하게 됩니다.

 

SQL> SHOW PARAMETER statistics_level

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
statistics_level                     string      TYPICAL
SQL> SHOW PARAMETER control_management_pack_access

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_management_pack_access       string      DIAGNOSTIC+TUNING

2. HR 유저로 쿼리 수행

 SQL> show user;
USER은 "HR"입니다

SQL>
SELECT    /*+ MONITOR */
          A.DEPARTMENT_ID, ROUND(AVG(A.SALARY),2) AS AVG_SALARY
FROM      EMPLOYEES A
         ,DEPARTMENTS B
WHERE     A.DEPARTMENT_ID = B.DEPARTMENT_ID
AND       B.LOCATION_ID = 1700
GROUP BY  A.DEPARTMENT_ID
;

DEPARTMENT_ID AVG_SALARY
------------- ----------
           10       4400
           30       4150
           90   19333.33
          100    8601.33
          110      10154

3. SYSTEM 유저로 HR 유저가 수행한 쿼리 모니터링

SQL> SELECT    SQL_ID, STATUS, SQL_TEXT
FROM      V$SQL_MONITOR
WHERE     USERNAME = 'HR'
 /

SQL_ID        STATUS
------------- -------------------
SQL_TEXT
--------------------------------------------------------------------------------
g0md6v4p73kda DONE (ALL ROWS)
SELECT    /*+ MONITOR */
          A.DEPARTMENT_ID, ROUND(AVG(A.SALARY),2) AS AVG_SALARY
FROM      EMPLOYEES A
         ,DEPARTMENTS B
WHERE     A.DEPARTMENT_ID = B.DEPARTMENT_ID
AND       B.LOCATION_ID = 1700
GROUP BY  A.DEPARTMENT_ID

4. DBMS_SQLTUNE.REPORT_SQL_MONITOR 함수를 이용해서 HTML 리포트 파일 만들기

- 3번에서 조회 된 "g0md6v4p73kda" 값을 함수에 적용

SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SET PAGESIZE 0
SET TRIM ON
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF

SPOOL report_sql_monitor.htm
SELECT DBMS_SQLTUNE.report_sql_monitor(
  sql_id       => 'g0md6v4p73kda',
  type         => 'HTML',
  report_level => 'ALL') AS report
FROM dual
/
SPOOL OFF

5. HTML 튜닝 리포트 확인