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
–#– 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;