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;
Labels: AWR, Database, Oracle, Response Time
posted by Alfredo Krieg @ 6:04 PM
0 Comments
0 Comments:
Post a Comment
Subscribe to Post Comments [Atom]
<< Home