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 튜닝 리포트 확인

출처: http://estenpark.tistory.com/362 [전문가로가는길]