Performance drilldown

well, every dba might now these kind of phone calls, where a user is complaining about poor performance of the database… but if you ask questions like when or what part of the application is slow, you don’t get useful answers.
so you have to play “detective” and find the guy who murdered the performance 😉
But where to start? You might generated some AWR reports and try to find something nasty – but in my opinion this is like finding a needle in a haystack, you need something covering a metric over time… my advise: use DBA_HIST_SYSMETRIC_SUMMARY (as long as you have the proper license!)

if you are in a hurry and need something handy now, you may use this:

set linesize 200
col "CPU USAGE %" for a12 heading "CPU|USAGE %"
col "LOGICAL READ IO/s" for a12 heading "LOGICAL|READ IO/s"
col "PHYSICAL READ IO/s" for a12 heading "PHYSICAL|READ IO/s"
col "PHYSICAL READ MB/s" for a12 heading "PHYSICAL|READ MB/s"
col "PHYSICAL WRITE IO/s" for a12 heading "PHYSICAL|WRITE IO/s"
col "PHYSICAL WRITE MB/s" for a12 heading "PHYSICAL|WRITE MB/s"
col "REDO MB/s" for a12 heading "REDO|MB/s"
col "BUFFER HIT RATIO" for a12 heading "BUFFER|HIT RATIO"
select 
    snap_id
   ,to_char(begin_interval_time, 'YYYY-MM-DD HH24:MI') "DATE"
   ,round(cpu_value,2) "CPU USAGE %"
   ,round(logical_read_value) "LOGICAL READ IO/s"
   ,round(physical_read_value) "PHYSICAL READ IO/s"
   ,round(physical_read_byte_value/1024/1024) "PHYSICAL READ MB/s"
   ,round(physical_write_value) "PHYSICAL WRITE IO/s"
   ,round(physical_write_byte_value/1024/1024) "PHYSICAL WRITE MB/s"
   ,round(redo_byte_value/1024/1024,2) "REDO MB/s"
   ,round(100-(physical_read_value/logical_read_value*100),2) "BUFFER HIT RATIO"
from (
   select 
       a.snap_id
      ,b.begin_interval_time
      ,a.metric_name
      ,a.average 
   from dba_hist_sysmetric_summary a
   inner join dba_hist_snapshot b on a.snap_id = b.snap_id
   )
pivot (sum(average) as value for (metric_name) in (
          'Logical Reads Per Sec' as logical_read
         ,'Physical Reads Per Sec' as physical_read
         ,'Physical Writes Per Sec' as physical_write
         ,'Physical Read Total Bytes Per Sec' as physical_read_byte
         ,'Physical Write Total Bytes Per Sec' as physical_write_byte
         ,'Redo Generated Per Sec' as redo_byte
         ,'Host CPU Utilization (%)' as cpu))
order by snap_id;

Keep in Mind: this SQL gives you the average values between two snapshots, the dba_hist_sysmetric_summary view is also able to provide you the peak values…

so far
Benjamin

This entry was posted in Uncategorized and tagged , . Bookmark the permalink.

Leave a Reply