Monday, February 17, 2014

Extract AWR data to build Response Time graphs (awrrtsys.sql)

This SQL script extract AWR data required to perform Response Time analysis for Oracle Databases. All information about it will be presented in the IOUG Collaborate 2014 session “553: Oracle Database Performance: Are Database Users Telling Me The Truth?”

--#********************************************************************
--#-- Filename           : awrrtsys.sql
--#-- Author             : Alfredo Krieg
--#-- Original           : 17-Oct-12
--#-- Last Update        : 21-Ago-13
--#-- Description        : awrrtsys.sql- System Response time per snapshot in the last x days
--#                        per desired unit of work -
--#-- Usage              : start awrrtsys.sql <unit of work> <last x days>
--#-- This script use AWR data (licensing is required)
--#********************************************************************

set termout on
set feedback off
set heading on
set linesize 150
set pagesize 100
set feedback off verify off

col snap_id           format  999999     heading "Snapshot Id"
col instance_number   format  99         heading "Instance Number"
col snap_time         format  a25        heading "Snap Begin Time"
col non_idle_wait     format  9999999.99 heading "Qt (s)"
col cpu_time          format  9999999.99 heading "St (s)"
col lio               format  9999999.99 heading "LIO"
col pio               format  9999999.99 heading "PIO"
col uw_variable       format  9999999.99 heading "UC"
col rt_ms_per_lio     format  9999999.99 heading "RT (ms/lio)"
col rt_ms_per_pio     format  9999999.99 heading "RT (ms/pio)"
col rt_ms_per_uw      format  9999999.99 heading "RT (ms/uc)"



SELECT a.SNAP_ID,
         b.instance_number,
         TO_CHAR (END_INTERVAL_TIME, 'mon/dd/yyyy HH24:mi') AS snap_time,
         non_idle_wait,
         background_cpu+
         db_cpu as cpu_time,
         lio,
         pio,
         uwvariable AS UW_VARIABLE,
         ROUND (
            ( ( (background_cpu + db_cpu) / lio) + (non_idle_wait / lio))
            * 1000,
            4)
            AS RT_ms_per_lio,
         ROUND (
            ( ( (background_cpu + db_cpu) / pio) + (non_idle_wait / pio))
            * 1000,
            4)
            AS RT_ms_per_pio,
         ROUND (
            ( ( (background_cpu + db_cpu) / (uwvariable))
             + (non_idle_wait / (uwvariable)))
            * 1000,
            4)
            AS RT_ms_per_uw
    FROM ( 
    SELECT SNAP_ID,
                   SUM (non_idle_wait) AS non_idle_wait,
                   ROUND (SUM (background_cpu), 0) AS background_cpu,
                   ROUND (SUM (db_cpu), 0) AS db_cpu,
                   SUM (lio) AS lio,
                   SUM (pio) AS pio,
                   SUM (uwvariable) AS uwvariable
              FROM (
              SELECT SNAP_ID,
                           ROUND (time_secs, 0) non_idle_wait,
                           0 AS background_cpu,
                           0 AS db_cpu,
                           0 AS lio,
                           0 AS pio,
                           0 AS uwvariable
                      FROM (  SELECT snap_id,
                                     'WAIT' AS wait_class,
                                     'Non Idle' AS name,
                                     (SUM (VALUE) - SUM (value1)) / 1000000
                                        AS time_secs
                                FROM (  SELECT snap_id,
                                               0 AS snap2,
                                               SUM (time_waited_micro) AS VALUE,
                                               0 AS value1
                                          FROM dba_hist_system_event
                                         WHERE wait_class <> 'Idle'
                                      GROUP BY snap_id
                                      UNION
                                        SELECT snap_id + 1,
                                               snap_id,
                                               0,
                                               SUM (time_waited_micro) AS value1
                                          FROM dba_hist_system_event
                                         WHERE wait_class <> 'Idle'
                                      GROUP BY snap_id)
                            GROUP BY snap_id)
                    UNION
                      SELECT snap_id,
                             0,
                             (SUM (VALUE) - SUM (value1)) / 1000000
                                AS background_cpu,
                             0,
                             0,
                             0,
                             0                       
                        FROM (SELECT snap_id,
                                     0 AS snap2,
                                     stat_name,
                                     VALUE,
                                     0 AS value1
                                FROM dba_hist_sys_time_model
                               WHERE stat_name IN ('background cpu time')
                              UNION
                              SELECT snap_id + 1,
                                     snap_id,
                                     stat_name,
                                     0,
                                     VALUE AS value1
                                FROM dba_hist_sys_time_model
                               WHERE stat_name IN ('background cpu time'))
                    GROUP BY snap_id, stat_name
                    UNION
                      SELECT snap_id,
                             0,
                             0,
                             (SUM (VALUE) - SUM (value1)) / 1000000 AS db_cpu,
                             0,
                             0,
                             0                          
                        FROM (SELECT snap_id,
                                     0 AS snap2,
                                     stat_name,
                                     VALUE,
                                     0 AS value1
                                FROM dba_hist_sys_time_model
                               WHERE stat_name IN ('DB CPU')
                              UNION
                              SELECT snap_id + 1,
                                     snap_id,
                                     stat_name,
                                     0,
                                     VALUE AS value1
                                FROM dba_hist_sys_time_model
                               WHERE stat_name IN ('DB CPU'))
                    GROUP BY snap_id, stat_name
                    UNION
                      SELECT snap_id,
                             0,
                             0,
                             0,
                             SUM (VALUE) - SUM (value1) AS lio,
                             0,
                             0                           
                        FROM (SELECT snap_id,
                                     0 AS snap2,
                                     stat_name,
                                     VALUE,
                                     0 AS value1
                                FROM DBA_HIST_SYSSTAT
                               WHERE stat_name IN ('session logical reads')
                              UNION
                              SELECT snap_id + 1,
                                     snap_id,
                                     stat_name,
                                     0,
                                     VALUE AS value1
                                FROM DBA_HIST_SYSSTAT
                               WHERE stat_name IN ('session logical reads'))
                    GROUP BY snap_id, stat_name
                    UNION
                      SELECT snap_id,
                             0,
                             0,
                             0,
                             0,
                             SUM (VALUE) - SUM (value1) AS pio,
                             0                           
                        FROM (SELECT snap_id,
                                     0 AS snap2,
                                     stat_name,
                                     VALUE,
                                     0 AS value1
                                FROM DBA_HIST_SYSSTAT
                               WHERE stat_name IN ('physical reads')
                              UNION
                              SELECT snap_id + 1,
                                     snap_id,
                                     stat_name,
                                     0,
                                     VALUE AS value1
                                FROM DBA_HIST_SYSSTAT
                               WHERE stat_name IN ('physical reads'))
                    GROUP BY snap_id, stat_name
                    UNION
                      SELECT snap_id,
                             0,
                             0,
                             0,
                             0,
                             0,
                             SUM (VALUE) - SUM (value1) AS uwvariable                          
                        FROM (SELECT snap_id,
                                     0 AS snap2,
                                     stat_name,
                                     VALUE,
                                     0 AS value1
                                FROM DBA_HIST_SYSSTAT
                               WHERE stat_name IN ('&1')
                              UNION
                              SELECT snap_id + 1,
                                     snap_id,
                                     stat_name,
                                     0,
                                     VALUE AS value1
                                FROM DBA_HIST_SYSSTAT
                               WHERE stat_name IN ('&1'))
                    GROUP BY snap_id, stat_name
                    )
          GROUP BY snap_id
            HAVING SUM (background_cpu) >= 0
          ORDER BY snap_id DESC) a, DBA_HIST_SNAPSHOT B
   WHERE a.SNAP_ID = b.SNAP_ID AND BEGIN_INTERVAL_TIME > SYSDATE - &2

ORDER BY 1;