V$OSSTAT, V$SYS_TIME_MODEL 및 V$SYSTEM_EVENT 뷰를 이용한 시스템 레벨 모니터링 방안

이번 시간에는 시스템 레벨의 모니터링에 유용한 3개의 뷰를 이용해서 작성한 모니터링 스크립트를 소개합니다. (꼬박 하루동안 이것만 🙂) 3개의 뷰는 V$OSSTAT, V$SYS_TIME_MODELV$SYSTEM_EVENT 이며 뷰들의 용도는 다음과 같습니다.

  • V$OSSTAT : OS CPU, Run-Queue, Memory, Swap 등의 정보를 제공하기 위해 사용합니다.
  • V$SYS_TIME_MODEL
    1. CPU(%)를 DB CPU(%)과 NON DB CPU(%)로 구분해서 제공하기 위해 사용합니다. 또한 DB CPU(%)는 Foreground CPU(%) 와 Background CPU(%)로 구분합니다. (이를 위해 V$OSSTAT 뷰의 일부 데이터를 이용합니다)
    2. V$SYSTEM_EVENT 뷰와 조인해서 DB CPU, DB Elapsed Time, Wait Class, Wait Event 발생 현황을 트리 구조로 제공합니다.

1. 화면 출력 결과


스크립트 수행 결과는 크게 3부분으로 구분됩니다. (그림-1 참조)

  • OS Info 영역
  • DB CPU(%) 및 NON DB CPU(%) 영역
  • 응답시간 분석 트리 영역
그림-1. 수행 결과 예시
%ec%84%b1%eb%8a%a54-1

2. OS Info 영역 설명


OS Info 영역은 V$OSSTAT 뷰의 정보를 이용합니다. 일반적으로 OS 정보를 모니터링할 때는 Top과 같은 시스템 유틸리티를 사용하는 것이 일반적이고 가장 편리한 방법입니다. 하지만 V$OSSTAT 뷰를 이용해서 OS 정보를 모니터링하는 것은 OS 유틸리티 대비 몇가지 장점을 제공합니다.

  1. 다른 뷰들과 조인해서 유용한 데이터를 추출하기가 용이합니다.
  2. 제품 개발 시에 OS API나 시스템 유틸리티 대용으로 사용할 수 있습니다.

2-1. 칼럼 설명

칼럼 명 설명
NUM_CPUS V$OSSTAT 뷰의 NUM_CPUS 칼럼 값
RUN-QUEUE V$OSSTAT 뷰의 LOAD 칼럼 값
TOTAL_CPU V$OSSTAT 뷰의 BUSY_TIME delta 값을 계산한 후 백분율(%) 데이터 제공
USER_CPU V$OSSTAT 뷰의 USER_TIME delta 값을 계산한 후 백분율(%) 데이터 제공
SYS_CPU V$OSSTAT 뷰의 SYS_TIME delta 값을 계산한 후 백분율(%) 데이터 제공
IOWAIT_CPU V$OSSTAT 뷰의 IOWAIT_TIME delta 값을 계산한 후 백분율(%) 데이터 제공
NICE_CPU V$OSSTAT 뷰의 NICE_TIME 값을 계산한 후 백분율(%) 데이터 제공
TOTAL_MEM V$OSSTAT 뷰의 PHYSICAL_MEMORY_BYTES 칼럼 값을 Gb 단위로 제공
FREE_MEM V$OSSTAT 뷰의 FREE_MEMORY_BYTES 칼럼 값을 Gb 단위로 제공
MEM_USAGE (PHYSICAL_MEMORY_BYTES-FREE_MEMORY_BYTES)

/ PHYSICAL_MEMORY_BYTES*100 값 제공

FREE_SWAP V$OSSTAT 뷰의 SWAP_FREE_BYTES 칼럼 값을 Mb 단위로 제공

2-2. 소스 설명
CPU(%)란 CPU가 사용한 시간을 이용해서 만든 비율(%) 지표입니다. 따라서 V$OSSTAT 뷰에서 제공하는 TIME 유형의 데이터들을 이용해서 사용률(%) 지표를 만들 수 있습니다. 이를 위해 2개의 SNAP (mon_sys_begin, mon_sys_end Global Temporary Table 이용) 데이터를 입력한 후, V$OSSTAT 뷰의 TIME 데이터들의 delta 값을 인터벌 및 NUM_CPUS로 나눕니다. 참고로 V$OSSTAT 뷰의 TIME 유형의 데이터는 1/100 (Centiseconds) 단위입니다.

SELECT
       num_cpus ,
       ROUND( load,1)                                          "RUN-QUEUE"     ,
       ROUND( busy_time_diff/interval/num_cpus , 1 )||'%'      "TOTAL_CPU"     ,
       ROUND( user_time_diff/interval/num_cpus , 1 )||'%'      "USER_CPU"      ,
       ROUND( sys_time_diff/interval/num_cpus , 1 )||'%'       "SYS_CPU"       ,
       ROUND( iowait_time_diff/interval/num_cpus , 1 )||'%'    "IOWAIT_CPU"    ,
       ROUND( nice_time_diff/interval/num_cpus , 1 )||'%'      "NICE_CPU"      ,
       ROUND( idle_time_diff/interval/num_cpus , 1 )||'%'      "IDLE_CPU"      ,
       ROUND( total_mem/1024/1024/1024 , 1 )||'(Gb)'           "TOTAL_MEM"     ,
       ROUND( free_mem/1024/1024/1024 , 1 )||'(Gb)'            "FREE_MEM"      ,
       ROUND(( total_mem- free_mem ) /total_mem*100 , 1 )||'%' "MEM_USAGE"     ,
       ROUND( free_swap/1024/1024/1024 , 1 )||'(Mb)'           "FREE_SWAP"     ,
       ROUND( interval,1)||'(Sec)'                             "INTERVAL"
FROM   (
        SELECT MAX( decode( b.name , 'NUM_CPUS' ,    b.value ) ) num_cpus ,
               MAX( decode( b.name , 'BUSY_TIME' ,   b.value - a.value ) ) busy_time_diff   ,
               MAX( decode( b.name , 'USER_TIME' ,   b.value - a.value ) ) user_time_diff   ,
               MAX( decode( b.name , 'SYS_TIME' ,    b.value - a.value ) ) sys_time_diff    ,
               MAX( decode( b.name , 'IOWAIT_TIME' , b.value - a.value ) ) iowait_time_diff ,
               MAX( decode( b.name , 'NICE_TIME' ,   b.value - a.value ) ) nice_time_diff   ,
               MAX( decode( b.name , 'IDLE_TIME' ,   b.value - a.value ) ) idle_time_diff   ,
               MAX( decode( b.name , 'LOAD' ,        b.value ) ) load ,
               MAX( decode( b.name , 'PHYSICAL_MEMORY_BYTES' , b.value ) ) total_mem ,
               MAX( decode( b.name , 'FREE_MEMORY_BYTES' ,     b.value ) ) free_mem  ,
               MAX( decode( b.name , 'SWAP_FREE_BYTES' ,       b.value ) ) free_swap ,
               MAX( TO_NUMBER( SUBSTR( TO_CHAR( b.log_time- a.log_time , 'SSFF' ) , 18 , 6 ) ) ) interval
        FROM   mon_sys_begin a ,
               mon_sys_end b
        WHERE  a.flag='[OSSTAT]'
        and    a.name=b.name
       );

3. DB CPU(%) and NON DB CPU(%) 영역 설명


OS 레벨의 CPU(%)를 DB가 사용하는 CPU(%)와 NON DB가 사용하는 CPU(%)로 구분해서 제공합니다.

3-1. 칼럼 설명

칼럼 명 설명
TOTAL_CPU V$OSSTAT 뷰의 BUSY_TIME delta 값을 계산한 후 백분율(%) 데이터 제공
DB_FG_CPU V$SYS_TIME_MODEL 뷰의 DB time delta 값을 계산한 후 백분율(%) 데이터 제공
DB_BG_CPU V$SYS_TIME_MODEL 뷰의 background cpu time delta 값을 계산한 후 백분율(%)

데이터 제공

NON_DB_CPU TOTAL_CPU – (DB time + background cpu time) 값을 계산 한 후 백분율(%) 데이터 제공

3-2. 소스 설명
전체 CPU 사용률(%) 데이터는 V$OSSTAT 뷰를 이용하고, DB CPU 사용률(%) 데이터는 V$SYS_TIME_MODEL 뷰를 이용합니다. 참고로 V$SYS_TIME_MODEL 뷰의 단위는 1/1000000 (microseconds) 이므로 ‘10000’으로 더 나눠서 백분율을 구합니다.

SELECT
       ROUND( busy_time_diff/interval/num_cpus , 1 ) ||'%'            "TOTAL_CPU" ,
       ROUND( db_fg_cpu_time_diff/interval/num_cpus/10000 , 1 ) ||'%' "DB_FG_CPU" ,
       ROUND( db_bg_cpu_time_diff/interval/num_cpus/10000 , 1 ) ||'%' "DB_BG_CPU" ,
       ROUND((( busy_time_diff*10000 ) - db_fg_cpu_time_diff- db_bg_cpu_time_diff )
                                 /interval/num_cpus/10000 , 1 ) ||'%' "NON_DB_CPU"
FROM   (
        SELECT MAX( decode( b.name , 'NUM_CPUS' ,  b.value ) ) num_cpus ,
               MAX( decode( b.name , 'BUSY_TIME' , b.value - a.value ) ) busy_time_diff ,
               MAX( decode( b.name , 'DB CPU' ,    b.value - a.value ) ) db_fg_cpu_time_diff ,
               MAX( decode( b.name , 'background cpu time' , b.value - a.value ) ) db_bg_cpu_time_diff ,
               MAX( TO_NUMBER( SUBSTR( TO_CHAR( b.log_time- a.log_time , 'SSFF' ) , 18 , 6 ) ) ) interval
        FROM   mon_sys_begin a ,
               mon_sys_end b
        WHERE  a.name IN ( 'NUM_CPUS' , 'BUSY_TIME' , 'DB CPU' , 'background cpu time')
        AND    a.name=b.name
       ) ;


4. 응답시간 분석 트리 영역 설명


시스템 레벨의 ‘전체 Elapsed Time’을 DB Time (Foreground 프로세스가 사용한 elapsed time)과 background elapsed time (Background 프로세스가 사용한 elapsed time)으로 구분하고, DB time을 DB CPU 및 WAIT CLASS, WAIT EVENT로 세분화해서 모니터링할 수 있다면, 현재 발생하는 성능 문제가 어디서 발생하는지 매우 직관적으로 파악할 수 있습니다. V$SYS_TIME_MODEL 뷰는 DB Time, Elapsed Time과 관련한 유용한 데이터를 제공하며 V$SYSTEM_EVENT 뷰는 대기 이벤트에 대한 자세한 정보를 제공하므로, 이 두개의 뷰를 이용해서 ‘응답시간 분석 트리’를 만들 수 있습니다.

4-1. 칼럼 및 수행 결과 설명

칼럼 명 설명
NAME 수행 결과를 계층 구조로 제공하며 비중도(%)를 표시합니다.
PCT_GRAPH 10%당 1개의 ‘*’을 표시합니다.
DIFF_TIME(Sec) 2개의 스냅 구간 사이에 해당 지표가 사용한 시간을 표시합니다.

수행 결과를 분석할 때 몇 가지 유의해야할 사항이 있습니다.

  • TOTAL elapsed time = DB time + background elapsed time 이므로 두개의 비중도(%)를 합하면 100%가 됩니다.
  • DB time = DB CPU + WAIT TIME 입니다. 예제 데이터를 보면 DB time(90%)은 DB CPU (5%) + Concurrency Class (2%) + Configuration Class (1%) + Other Class (83%)와 유사한 것을 알 수 있습니다. 그리고 Other 대기 클래스 중에서 enq : SV – contention 대기 이벤트가 가장 문제인 것을 직관적으로 파악할 수 있습니다.
  • DB Time은 Foreground 프로세스가 수행한 elapsed time의 합이며 elapsed time 기준으로 세분화한 데이터들을 보면 지표 명 내에 ‘elapsed time’이 존재합니다. 예제의 데이터를 보면 DB time(90%)는 PL/SQL execution elapsed time(1%) + sql execute elapsed time(90%)과 유사한 것을 알 수 있습니다.

4-2. 소스 설명
비율(%) 계산을 위해서는 전체 응답시간을 알아야하므로 WITH 절을 이용해서 해당 값을 구해 둡니다. 그리고 두 개의 스냅 데이터를 이용해서 delta 값들을 계산하고, 트리 구성을 위해 자신의 ‘parent’를 설정합니다.

WITH v_total_ela AS (
                    SELECT SUM( b.value- a.value ) VALUE
                    FROM   mon_sys_begin a ,
                           mon_sys_end b
                    WHERE  a.name IN ( 'DB time' , 'background elapsed time' )
                    AND    a.name=b.name
     )
SELECT
     LPAD( ' ' , 2*( level- 1 ) )||name||' ('||ROUND( diff_ela/total_ela*100 , 0 )||'%)' "NAME",
     LPAD( ' ' , ROUND( diff_ela/total_ela*10,0), '*') "PCT_GRAPH",
     ROUND( diff_ela/1000000 , 3 ) "DIFF_TIME(Sec)"
FROM (
      SELECT b.name ,
      CASE
          WHEN b.name='DB time'                         THEN 'TOTAL elapsed time'
          WHEN b.name='background elapsed time'         THEN 'TOTAL elapsed time'
          WHEN b.name='background cpu time'             THEN 'background elapsed time'
          WHEN b.name LIKE 'RMAN cpu time%'             THEN 'background cpu time'
          WHEN b.name='DB CPU'                          THEN 'DB time'
          WHEN b.name='connection management call elapsed time'           THEN 'DB time'
          WHEN b.name='sequence load elapsed time'      THEN 'DB time'
          WHEN b.name='sql execute elapsed time'        THEN 'DB time'
          WHEN b.name='parse time elapsed'              THEN 'DB time'
          WHEN b.name='hard parse elapsed time'         THEN 'parse time elapsed'
          WHEN b.name='hard parse (sharing criteria) elapsed time'        THEN 'hard parse elapsed time'
          WHEN b.name='hard parse (bind mismatch) elapsed time'           THEN 'hard parse (sharing criteria) elapsed time'
          WHEN b.name='failed parse elapsed time'       THEN 'parse time elapsed'
          WHEN b.name='failed parse (out of shared memory) elapsed time'  THEN 'failed parse elapsed time'
          WHEN b.name='PL/SQL execution elapsed time'   THEN 'DB time'
          WHEN b.name='inbound PL/SQL rpc elapsed time' THEN 'DB time'
          WHEN b.name='PL/SQL compilation elapsed time' THEN 'DB time'
          WHEN b.name='Java execution elapsed time'     THEN 'DB time'
          WHEN b.name='repeated bind elapsed time'      THEN 'DB time'
      END parent ,
             b.value- a.value diff_ela ,
             c.value total_ela
      FROM   mon_sys_begin a ,
             mon_sys_end b ,
             v_total_ela c
      WHERE  a.flag='[TIME_MODEL]'
      AND    a.name=b.name
      AND    b.value >0
      UNION ALL
      SELECT '[WAIT] '||b.name,
             'DB time' ,
             b.value- a.value diff_ela,
             c.value
      FROM   mon_sys_begin a ,
             mon_sys_end b ,
             v_total_ela c
      WHERE  a.flag='[WAIT_CLASS]'
      AND    a.name=b.name
      AND    round((b.value-a.value)/1000000,1) > 0
      UNION ALL
      SELECT '[EVENT] '||b.name,
             '[WAIT] '||substr(b.flag, 14),
             b.value- a.value diff_ela,
             c.value
      FROM   mon_sys_begin a ,
             mon_sys_end b ,
             v_total_ela c
      WHERE  a.flag like '[WAIT_EVENT]%'
      AND    a.name=b.name
      AND    round((b.value-a.value)/1000000,1) > 0
      UNION ALL
      SELECT 'TOTAL elapsed time',
              NULL,
              value,
              value
      FROM    v_total_ela
     ) START WITH parent IS NULL
CONNECT BY PRIOR name=parent
AND  diff_ela > 0
AND  ROUND( diff_ela/total_ela*100 , 0 ) > 0;

5. 수행 방법 및 스크립트 소스


5.1 수행 방법

SQLPLUS> @mon_sys <interval> 

-- Global Temporary 테이블 생성 스크립트
drop table mon_sys_begin;

create global temporary table mon_sys_begin
(
  log_time   timestamp,
  flag       varchar2(30),
  name       varchar2(64),
  value      number
) on commit delete rows;;

drop table mon_sys_end;

create global temporary table mon_sys_end
(
  log_time   timestamp,
  flag       varchar2(30),
  name       varchar2(64),
  value      number
) on commit delete rows;

5.2 모니터링 스크립트 소스 (mon_sys.sql)

!clear

set feedback off
set verify   off
set pages 1000

define __SLEEP_TIME=&1;

INSERT
INTO   mon_sys_begin
SELECT systimestamp ,
       '[OSSTAT]' ,
       stat_name ,
       value
FROM   v$osstat
UNION  ALL
SELECT systimestamp ,
       '[TIME_MODEL]' ,
       stat_name ,
       value
FROM   v$sys_time_model
UNION  ALL
SELECT systimestamp ,
       '[WAIT_CLASS]' ,
       wait_class ,
       SUM( time_waited_micro ) VALUE
FROM   v$system_event
WHERE  wait_class NOT IN ( 'Idle' )
GROUP BY wait_class
UNION ALL
SELECT systimestamp ,
       '[WAIT_EVENT]'||wait_class,
       event ,
       time_waited_micro  VALUE
FROM   v$system_event
WHERE  wait_class NOT IN ( 'Idle' );

exec sys.dbms_lock.sleep(&__SLEEP_TIME);

INSERT
INTO   mon_sys_end
SELECT systimestamp ,
       '[OSSTAT]' ,
       stat_name ,
       value
FROM   v$osstat
UNION  ALL
SELECT systimestamp ,
       '[TIME_MODEL]' ,
       stat_name ,
       value
FROM   v$sys_time_model
UNION  ALL
SELECT systimestamp ,
       '[WAIT_CLASS]' ,
       wait_class ,
       SUM( time_waited_micro ) VALUE
FROM   v$system_event
WHERE  wait_class NOT IN ( 'Idle' )
GROUP  BY wait_class
UNION ALL
SELECT systimestamp ,
       '[WAIT_EVENT] '||wait_class,
       event,
       time_waited_micro  VALUE
FROM   v$system_event
WHERE  wait_class NOT IN ( 'Idle' );

col num_cpus      for 999
col "RUN-QUEUE"   for 999.9
col "TOTAL_CPU"   for a9
col "USER_CPU"    for a8
col "SYS_CPU"     for a7
col "IOWAIT_CPU"  for a10
col "NICE_CPU"    for a8
col "IDLE_CPU"    for a8
col "TOTAL_MEM"   for a9
col "FREE_MEM"    for a8
col "MEM_USAGE"   for a9
col "FREE_SWAP"   for a9
col "INTERVAL"    for a8

TTITLE LEFT '[---------------------------------CPU ------------------------------------][------------MEMORY---------][--SWAP--]'

SELECT
       num_cpus ,
       ROUND( load,1)                                          "RUN-QUEUE"     ,
       ROUND( busy_time_diff/interval/num_cpus , 1 )||'%'      "TOTAL_CPU"     ,
       ROUND( user_time_diff/interval/num_cpus , 1 )||'%'      "USER_CPU"      ,
       ROUND( sys_time_diff/interval/num_cpus , 1 )||'%'       "SYS_CPU"       ,
       ROUND( iowait_time_diff/interval/num_cpus , 1 )||'%'    "IOWAIT_CPU"    ,
       ROUND( nice_time_diff/interval/num_cpus , 1 )||'%'      "NICE_CPU"      ,
       ROUND( idle_time_diff/interval/num_cpus , 1 )||'%'      "IDLE_CPU"      ,
       ROUND( total_mem/1024/1024/1024 , 1 )||'(Gb)'           "TOTAL_MEM"     ,
       ROUND( free_mem/1024/1024/1024 , 1 )||'(Gb)'            "FREE_MEM"      ,
       ROUND(( total_mem- free_mem ) /total_mem*100 , 1 )||'%' "MEM_USAGE"     ,
       ROUND( free_swap/1024/1024/1024 , 1 )||'(Mb)'           "FREE_SWAP"     ,
       ROUND( interval,1)||'(Sec)'                             "INTERVAL"
FROM   (
        SELECT MAX( decode( b.name , 'NUM_CPUS' ,    b.value ) ) num_cpus ,
               MAX( decode( b.name , 'BUSY_TIME' ,   b.value - a.value ) ) busy_time_diff   ,
               MAX( decode( b.name , 'USER_TIME' ,   b.value - a.value ) ) user_time_diff   ,
               MAX( decode( b.name , 'SYS_TIME' ,    b.value - a.value ) ) sys_time_diff    ,
               MAX( decode( b.name , 'IOWAIT_TIME' , b.value - a.value ) ) iowait_time_diff ,
               MAX( decode( b.name , 'NICE_TIME' ,   b.value - a.value ) ) nice_time_diff   ,
               MAX( decode( b.name , 'IDLE_TIME' ,   b.value - a.value ) ) idle_time_diff   ,
               MAX( decode( b.name , 'LOAD' ,        b.value ) ) load ,
               MAX( decode( b.name , 'PHYSICAL_MEMORY_BYTES' , b.value ) ) total_mem ,
               MAX( decode( b.name , 'FREE_MEMORY_BYTES' ,     b.value ) ) free_mem  ,
               MAX( decode( b.name , 'SWAP_FREE_BYTES' ,       b.value ) ) free_swap ,
               MAX( TO_NUMBER( SUBSTR( TO_CHAR( b.log_time- a.log_time , 'SSFF' ) , 18 , 6 ) ) ) interval
        FROM   mon_sys_begin a ,
               mon_sys_end b
        WHERE  a.flag='[OSSTAT]'
        and    a.name=b.name
       );

TTITLE OFF

col "TOTAL_CPU_TIME"  for a14
col "DB_FG_CPU_TIME"  for a14
col "DB_BG_CPU_TIME"  for a14
col "TOTAL_CPU"       for a9
col "DB_FG_CPU"       for a9
col "DB_BG_CPU"       for a9
col "NON_DB_CPU"      for a10

SELECT
       ROUND( busy_time_diff/interval/num_cpus , 1 ) ||'%'            "TOTAL_CPU" ,
       ROUND( db_fg_cpu_time_diff/interval/num_cpus/10000 , 1 ) ||'%' "DB_FG_CPU" ,
       ROUND( db_bg_cpu_time_diff/interval/num_cpus/10000 , 1 ) ||'%' "DB_BG_CPU" ,
       ROUND((( busy_time_diff*10000 ) - db_fg_cpu_time_diff- db_bg_cpu_time_diff )
                                 /interval/num_cpus/10000 , 1 ) ||'%' "NON_DB_CPU"
FROM   (
        SELECT MAX( decode( b.name , 'NUM_CPUS' ,  b.value ) ) num_cpus ,
               MAX( decode( b.name , 'BUSY_TIME' , b.value - a.value ) ) busy_time_diff ,
               MAX( decode( b.name , 'DB CPU' ,    b.value - a.value ) ) db_fg_cpu_time_diff ,
               MAX( decode( b.name , 'background cpu time' , b.value - a.value ) ) db_bg_cpu_time_diff ,
               MAX( TO_NUMBER( SUBSTR( TO_CHAR( b.log_time- a.log_time , 'SSFF' ) , 18 , 6 ) ) ) interval
        FROM   mon_sys_begin a ,
               mon_sys_end b
        WHERE  a.name IN ( 'NUM_CPUS' , 'BUSY_TIME' , 'DB CPU' , 'background cpu time')
        AND    a.name=b.name
       ) ;

col "NAME"           for a80
col "DIFF_TIME(Sec)" for 99999.99
col "PCT(%)"         for a15
col "PCT_GRAPH"      for a21

WITH v_total_ela AS (
                    SELECT SUM( b.value- a.value ) VALUE
                    FROM   mon_sys_begin a ,
                           mon_sys_end b
                    WHERE  a.name IN ( 'DB time' , 'background elapsed time' )
                    AND    a.name=b.name
     )
SELECT
     LPAD( ' ' , 2*( level- 1 ) )||name||' ('||ROUND( diff_ela/total_ela*100 , 0 )||'%)' "NAME",
     LPAD( ' ' , ROUND( diff_ela/total_ela*10,0), '*') "PCT_GRAPH",
     ROUND( diff_ela/1000000 , 3 ) "DIFF_TIME(Sec)"
FROM (
      SELECT b.name ,
      CASE
          WHEN b.name='DB time'                         THEN 'TOTAL elapsed time'
          WHEN b.name='background elapsed time'         THEN 'TOTAL elapsed time'
          WHEN b.name='background cpu time'             THEN 'background elapsed time'
          WHEN b.name LIKE 'RMAN cpu time%'             THEN 'background cpu time'
          WHEN b.name='DB CPU'                          THEN 'DB time'
          WHEN b.name='connection management call elapsed time'           THEN 'DB time'
          WHEN b.name='sequence load elapsed time'      THEN 'DB time'
          WHEN b.name='sql execute elapsed time'        THEN 'DB time'
          WHEN b.name='parse time elapsed'              THEN 'DB time'
          WHEN b.name='hard parse elapsed time'         THEN 'parse time elapsed'
          WHEN b.name='hard parse (sharing criteria) elapsed time'        THEN 'hard parse elapsed time'
          WHEN b.name='hard parse (bind mismatch) elapsed time'           THEN 'hard parse (sharing criteria) elapsed time'
          WHEN b.name='failed parse elapsed time'       THEN 'parse time elapsed'
          WHEN b.name='failed parse (out of shared memory) elapsed time'  THEN 'failed parse elapsed time'
          WHEN b.name='PL/SQL execution elapsed time'   THEN 'DB time'
          WHEN b.name='inbound PL/SQL rpc elapsed time' THEN 'DB time'
          WHEN b.name='PL/SQL compilation elapsed time' THEN 'DB time'
          WHEN b.name='Java execution elapsed time'     THEN 'DB time'
          WHEN b.name='repeated bind elapsed time'      THEN 'DB time'
      END parent ,
             b.value- a.value diff_ela ,
             c.value total_ela
      FROM   mon_sys_begin a ,
             mon_sys_end b ,
             v_total_ela c
      WHERE  a.flag='[TIME_MODEL]'
      AND    a.name=b.name
      AND    b.value >0
      UNION ALL
      SELECT '[WAIT] '||b.name,
             'DB time' ,
             b.value- a.value diff_ela,
             c.value
      FROM   mon_sys_begin a ,
             mon_sys_end b ,
             v_total_ela c
      WHERE  a.flag='[WAIT_CLASS]'
      AND    a.name=b.name
      AND    round((b.value-a.value)/1000000,1) > 0
      UNION ALL
      SELECT '[EVENT] '||b.name,
             '[WAIT] '||substr(b.flag, 14),
             b.value- a.value diff_ela,
             c.value
      FROM   mon_sys_begin a ,
             mon_sys_end b ,
             v_total_ela c
      WHERE  a.flag like '[WAIT_EVENT]%'
      AND    a.name=b.name
      AND    round((b.value-a.value)/1000000,1) > 0
      UNION ALL
      SELECT 'TOTAL elapsed time',
              NULL,
              value,
              value
      FROM    v_total_ela
     ) START WITH parent IS NULL
CONNECT BY PRIOR name=parent
AND  diff_ela > 0
AND  ROUND( diff_ela/total_ela*100 , 0 ) > 0;

commit;

5.3 수행 결과 예시

[---------------------------------CPU ------------------------------------][------------MEMORY---------][--SWAP--]
NUM_CPUS RUN-QUEUE TOTAL_CPU USER_CPU SYS_CPU IOWAIT_CPU NICE_CPU IDLE_CPU TOTAL_MEM FREE_MEM MEM_USAGE FREE_SWAP INTERVAL
-------- --------- --------- -------- ------- ---------- -------- -------- --------- -------- --------- --------- --------
       1       3.1 97.3%     5.6%     91.3%   0%         0%       .5%      2.3(Gb)   .1(Gb)   97.4%     2(Mb)     2.2(Sec)

TOTAL_CPU DB_FG_CPU DB_BG_CPU NON_DB_CPU
--------- --------- --------- ----------
97.3%     54.1%     17.1%     26%

NAME                                                                             PCT_GRATH              DIFF_TIME(Sec)
-------------------------------------------------------------------------------- --------------------- --------------
TOTAL elapsed time (100%)                                                        *********                      22.38
  DB time (90%)                                                                  ********                       20.21
    DB CPU (5%)                                                                                                  1.17
    PL/SQL execution elapsed time (1%)                                                                            .19
    [WAIT] Concurrency (2%)                                                                                       .47
      [EVENT] buffer busy waits (1%)                                                                              .14
      [EVENT] enq: TX - index contention (1%)                                                                     .33
    [WAIT] Configuration (1%)                                                                                     .23
      [EVENT] log file switch completion (1%)                                                                     .23
    [WAIT] Other (83%)                                                           *******                        18.64
      [EVENT] enq: SV -  contention (80%)                                        *******                        18.01
      [EVENT] latch: ges resource hash list (2%)                                                                  .35
    sql execute elapsed time (90%)                                               ********                       20.18
  background elapsed time (10%)                                                                                  2.17
    background cpu time (2%)

글을 마치며


다음 시간에는 V$SESS_TIME_MODEL 뷰를 이용한 세션 레벨 모니터링 스크립트를 작성할 예정입니다. 이러한 모니터링 스크립트는 작성 그 자체도 중요하겠지만 해당 뷰에서 제공하는 데이터의 성격을 명확하게 이해하는 것 또한 중요합니다. 따라서, 해당 스크립트의 내용을 잘 이해한 후 각자의 모니터링 환경에 적합하게 적절히 수정해서 사용하시면 좋을것 같습니다.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s