I/O STAT 예시

2011년쯤에 작성했었는데. (그이후로 오라클을 못만진 주륵ㅜ... 내 커리어 어쩔...)

0원으로 AWR 모니터링을 할 수 있게끔 그럴 싸 한 걸 만들었지만 클라우드 사용요금 압박때문에.. (기존에는 워드프레스 다시 한다면 메타베이스로 ㅎㅎ)

여튼 이런식으로 쿼리로도 출력이 가능하므로 특정 snap_id 델타 값을 갖는 데이터들은 시계열 분석도 가능하다. 

단순 시계열 통계로 차트(구글차트등을 이용하여)보기는 당연 가능 할 것이고.

분석쪽으로 좀 더 배우신분들은 해당 데이터로 머신러닝등을 활용해서 더 좋은 인사이트를 찾지 않을까 싶다.

with base as ( 

select snap_id

     , instance_number

     , function_name

     , sum(small_read_megabytes) small_read_megabytes

     , sum(large_read_megabytes) large_read_megabytes  

     , sum(small_read_reqs) small_read_reqs  

     , sum(large_read_reqs) large_read_reqs  

     , sum(small_write_megabytes) small_write_megabytes  

     , sum(large_write_megabytes) large_write_megabytes  

     , sum(small_write_reqs) small_write_reqs 

     , sum(large_write_reqs) large_write_reqs  

     , sum(number_of_waits) number_of_waits 

     , sum(wait_time) wait_time     

  from dba_hist_iostat_function b

 where dbid    = 1068263450  --dbid

   and instance_number = 1   --instance number

 group by snap_id,instance_number,function_name  

 )

SELECT 

       to_char(begin_interval_time,'yyyy-mm-dd hh24:mi') begin_interval_time

   --  , snap_id

   --  , instance_number

     , function_name

     , rds

     , rrq

     , wrs

     , wrq

     , wts

     , wttm

  FROM  

 (

select begin_interval_time

     , a.snap_id

     , a.instance_number

     , a.function_name

     , (small_read_megabytes - lag(small_read_megabytes) over(partition by a.instance_number,function_name order by a.snap_id)) 

     + (large_read_megabytes - lag(large_read_megabytes) over(partition by a.instance_number,function_name order by a.snap_id)) rds

     , (small_read_reqs - lag(small_read_reqs) over(partition by a.instance_number,function_name order by a.snap_id)) 

     + (large_read_reqs - lag(large_read_reqs) over(partition by a.instance_number,function_name order by a.snap_id)) rrq

     , (small_write_megabytes - lag(small_write_megabytes) over(partition by a.instance_number,function_name order by a.snap_id)) 

     + (large_write_megabytes - lag(large_write_megabytes) over(partition by a.instance_number,function_name order by a.snap_id)) wrs

     , (small_write_reqs - lag(small_write_reqs) over(partition by a.instance_number,function_name order by a.snap_id)) 

     + (large_write_reqs - lag(large_write_reqs) over(partition by a.instance_number,function_name order by a.snap_id)) wrq

     , number_of_waits - lag(number_of_waits) over(partition by a.instance_number,function_name order by a.snap_id) wts

     , wait_time - lag(wait_time) over(partition by a.instance_number,function_name order by a.snap_id) wttm

 from base a inner join dba_hist_snapshot b on a.snap_id = b.snap_id and a.instance_number = b.instance_number

 where b.dbid    = $dbid

 ) b

   where to_char(begin_interval_time,'yyyy-mm-dd hh24') BETWEEN '$min_date' AND '$max_date' --스냅구간 조건  년월일시분

     and function_name in (-- 'ARCH'                 

                             'Buffer Cache Reads'   

                       --      ,'DBWR'                 

                       --      ,'Data Pump'            

                       --      ,'Direct Reads'         

                       --      ,'Direct Writes'        

                       --      ,'LGWR'                 

                       --      ,'Others'               

                       --      ,'RMAN'                 

                       --      ,'Recovery'             

                       --      ,'Streams AQ'           

                       --      ,'XDB'                  

                             )

;

select instance_number
          , total_rds/:ela     total_rds
          , bc_rds/:ela           bc_rds
          , dr_rds/:ela           dr_rds
          , total_wrs/:ela     total_wrs
          , dbwr_wrs/:ela       dbwr_wrs
          , dw_wrs/:ela           dw_wrs
          , lgwr_wrs/:ela       lgwr_wrs
          , total_rrq/:ela     total_rrq
          , bc_rrq/:ela           bc_rrq
          , dr_rrq/:ela           dr_rrq
          , total_wrq/:ela     total_wrq
          , dbwr_wrq/:ela       dbwr_wrq
          , dw_wrq/:ela           dw_wrq
          , lgwr_wrq/:ela       lgwr_wrq     
        from (select instance_number
             , bc_rds + dr_rds
               + arch_rds + dbwr_rds + dp_rds + dw_rds + lgwr_rds 
               + oth_rds + rman_rds + reco_rds + saq_rds + xdb_rds  total_rds
             , bc_rds      bc_rds
             , dr_rds      dr_rds
             , dbwr_wrs + dw_wrs + lgwr_wrs 
               + arch_wrs + bc_wrs + dp_wrs + dr_wrs + oth_wrs
               + rman_wrs + reco_wrs + saq_wrs + xdb_wrs            total_wrs
             , dbwr_wrs   dbwr_wrs
             , dw_wrs       dw_wrs
             , lgwr_wrs   lgwr_wrs
             , bc_rrq + dr_rrq
               + arch_rrq + dbwr_rrq  + dp_rrq + dw_rrq + lgwr_rrq
               + oth_rrq + rman_rrq + reco_rrq + saq_rrq + xdb_rrq  total_rrq
             , bc_rrq       bc_rrq
             , dr_rrq       dr_rrq
             , dbwr_wrq + dw_wrq+ lgwr_wrq
               + arch_wrq + bc_wrq + dp_wrq + dr_wrq + oth_wrq 
               + rman_wrq + reco_wrq + saq_wrq + xdb_wrq            total_wrq
             , dbwr_wrq   dbwr_wrq
             , dw_wrq       dw_wrq
             , lgwr_wrq   lgwr_wrq
          from 
          (select e.instance_number
                , e.function_name
                , sum((e.small_read_megabytes - b.small_read_megabytes)
                  + (e.large_read_megabytes - b.large_read_megabytes))   rds
                , sum((e.small_read_reqs - b.small_read_reqs)
                  + (e.large_read_reqs - b.large_read_reqs))             rrq
                , sum((e.small_write_megabytes - b.small_write_megabytes)
                  + (e.large_write_megabytes - b.large_write_megabytes)) wrs
                , sum((e.small_write_reqs - b.small_write_reqs)
                  + (e.large_write_reqs - b.large_write_reqs))           wrq
                , sum((e.number_of_waits - b.number_of_waits))           wts
                , sum((e.wait_time       - b.wait_time))                 wttm
             from dba_hist_iostat_function b
                , dba_hist_iostat_function e
            where b.snap_id = :bid
              and e.snap_id = :eid
              and e.dbid    = :dbid
              and e.dbid    = b.dbid
              and e.instance_number member of :inst_num
              and e.instance_number = b.instance_number
              and e.function_id     = b.function_id
              and e.function_name   = b.function_name
            group by e.instance_number,e.function_name)
         pivot (sum(rds) rds, sum(rrq) rrq, sum(wrs) wrs, sum(wrq) wrq,
                sum(wts) wts, sum(wttm) wttm
                for function_name in (''ARCH''                 arch
                                     ,''Buffer Cache Reads''   bc
                                     ,''DBWR''                 dbwr
                                     ,''Data Pump''            dp
                                     ,''Direct Reads''         dr
                                     ,''Direct Writes''        dw
                                     ,''LGWR''                 lgwr
                                     ,''Others''               oth
                                     ,''RMAN''                 rman
                                     ,''Recovery''             reco
                                     ,''Streams AQ''           saq
                                     ,''XDB''                  xdb
                                     ))) 
      order by instance_number
;