System Topology
SELECT snap_id,TO_CHAR(begin_interval_time, 'DD-MON-YYYY HH24:MI'),TO_CHAR(end_interval_time, 'DD-MON-YYYY HH24:MI'),d.name||instance_number,
ROUND(MAX(ela),2) "Elapse Time (s)",
ROUND(MAX(case when stat_name='logons current' then (value) else 0 end),3) "Sess# Start",
ROUND(MAX(case when stat_name='total PGA allocated' then (value)/1024/1024 else 0 end),3) "pga (mb)",
ROUND(MAX(case when stat_name='total PGA allocated' then (value)/1024/1024 else 0 end)
/MAX(case when stat_name='logons current' then (value) else 0 end),3) "pga (mb/sess)",
ROUND(SUM(case when stat_name='NUM_VCPUS' then value else 0 end),2) "CPU Cores",
ROUND(MAX(case when stat_name='NUM_CPUS' then value else 0 end),2) "CPU Threads",
ROUND(MAX(case when stat_name='DB time' then diff_val else 0 end),2) "Active Sessions/Sec",
ROUND(MAX(case when stat_name='NUM_VCPUS' then value*ela else 0 end),2) "Total Core Time",
ROUND(SUM(case when stat_name='BUSY_TIME' then diff_val else 0 end)/max(ela),2) "Host Core Util",
ROUND(SUM(case when stat_name='USER_TIME' then diff_val else 0 end)/max(ela),2) "Host User Util",
ROUND(SUM(case when stat_name='SYS_TIME' then diff_val else 0 end)/max(ela),2) "Host SYS Util",
ROUND(SUM(case when stat_name='LOAD' then value else 0 end),2) "LOAD(# Active Process)",
ROUND(SUM(case when stat_name='DB CPU' or stat_name='background cpu time' then diff_val else 0 end),2) "DB+BG Time (s)",
ROUND(SUM(case when stat_name='DB CPU' or stat_name='background cpu time' then diff_val else 0 end)/max(ela),2) "DB Cores Util",
ROUND(MAX(case when stat_name='background cpu time' then diff_val else 0 end)/max(ela),2) "BG Time Core",
ROUND(MAX(case when stat_name='RMAN cpu time (backup/restore)' then diff_val else 0 end)/max(ela),2) "RMAN Core",
ROUND(MAX(case when stat_name='physical read IO requests' then diff_val/ela else 0 end),3) "IOPs r",
ROUND(MAX(case when stat_name='physical read total IO requests' then (diff_val)/ela else 0 end),3) "IOPs r total",
ROUND(MAX(case when stat_name='physical read total multi block requests' then (diff_val)/ela else 0 end),3) "IOPs r Sequential",
ROUND(SUM(case when stat_name='physical read total IO requests' then (diff_val)/ela
when stat_name='physical read total multi block requests' then -1*(diff_val)/ela else 0 end),3) "IOPs r Random(single)",
ROUND(MAX(case when stat_name='redo blocks read for recovery' then (diff_val)/ela else 0 end),3) "redo recovery blocks/s",
ROUND(MAX(case when stat_name='physical write IO requests' then diff_val/ela else 0 end),3) "IOPs w",
ROUND(MAX(case when stat_name='physical write total IO requests' then (diff_val)/ela else 0 end),3) "IOPs w total",
ROUND(MAX(case when stat_name='physical write total multi block requests' then (diff_val)/ela else 0 end),3) "IOPs w Sequential",
ROUND(SUM(case when stat_name='physical write total IO requests' then (diff_val)/ela
when stat_name='physical write total multi block requests' then -1*(diff_val)/ela else 0 end),3) "IOPs w Random(single)",
ROUND(MAX(case when stat_name='DBWR undo block writes' then (diff_val)/ela else 0 end),3) "Undo Blocks/s",
ROUND(SUM(case when stat_name='redo blocks written' then diff_val/ela else 0 end),5) "redo blocks written/s",
ROUND(SUM(case when stat_name='redo writes' then diff_val/ela else 0 end),5) "redo write/s",
ROUND(SUM(case when stat_name='redo blocks written' then diff_val else 0 end)/
SUM(case when stat_name='redo writes' then diff_val else 1 end),5) "Redo Blocks/w",
ROUND(MAX(case when stat_name='physical read bytes' then (diff_val)/ela else 0 end),3) "IO r (bytes)/s",
ROUND(MAX(case when stat_name='physical read total bytes' then (diff_val)/ela else 0 end),3) "IO_Tot r (bytes)/s",
ROUND(MAX(case when stat_name='physical write bytes' then (diff_val)/ela else 0 end),3) "IO w (bytes)/s",
ROUND(MAX(case when stat_name='physical write total bytes' then (diff_val)/ela else 0 end),3) "IO_Tot w (bytes)/s",
ROUND(MAX(case when stat_name='redo size' then (diff_val)/ela else 0 end),3) "Redo (bytes)/s",
ROUND(MAX(case when stat_name='bytes sent via SQL*Net to client' then (diff_val)/ela else 0 end),3) "SQL*Net Sent (bytes)/s",
ROUND(MAX(case when stat_name='bytes received via SQL*Net from client' then (diff_val)/ela else 0 end),3) "SQL*Net received (bytes)/s",
ROUND (((SUM (CASE WHEN stat_name = 'gc cr blocks received' OR stat_name = 'gc current blocks received'
OR stat_name = 'gc cr blocks served' OR stat_name = 'gc current blocks served'
THEN diff_val ELSE 0 END) * (SELECT TO_NUMBER (VALUE) FROM v$parameter WHERE name = 'db_block_size'))
+ (SUM (CASE WHEN stat_name = 'gcs messages sent' OR stat_name = 'ges messages sent'
OR stat_name = 'gcs msgs received' OR stat_name = 'ges msgs received' THEN diff_val ELSE 0 END) * 200)
) / 1024 / MAX (ela), 3) "Estd Interconnect traffic (KB)"
FROM (
SELECT s.instance_number, s.snap_id, s.begin_interval_time begin_interval_time, s.end_interval_time end_interval_time,
round((CAST (s.end_interval_time AS DATE) - CAST (s.begin_interval_time AS DATE)) * 24 * 3600) ela, sy.stat_name stat_name,
(CASE WHEN s.begin_interval_time = s.startup_time THEN sy.VALUE
ELSE sy.VALUE - LAG (sy.VALUE, 1) OVER
(PARTITION BY sy.stat_id, s.dbid, s.instance_number, s.startup_time ORDER BY s.snap_id)
END) / 1000000 diff_val, sy.VALUE value
FROM dba_hist_snapshot s, dba_hist_sys_time_model sy
WHERE s.dbid = sy.dbid (+)
AND s.instance_number = sy.instance_number (+)
AND s.snap_id = sy.snap_id (+)
AND sy.stat_name in ('DB CPU','DB time','background cpu time','RMAN cpu time (backup/restore)')
union all
SELECT s.instance_number, s.snap_id, s.begin_interval_time begin_interval_time, s.end_interval_time end_interval_time,
round((CAST (s.end_interval_time AS DATE) - CAST (s.begin_interval_time AS DATE)) * 24 * 3600) ela, os.stat_name stat_name,
(CASE WHEN s.begin_interval_time = s.startup_time THEN os.VALUE
ELSE os.VALUE - LAG (os.VALUE, 1) OVER
(PARTITION BY os.stat_id, s.dbid, s.instance_number, s.startup_time ORDER BY s.snap_id)
END) / 100 diff_val, os.VALUE value
FROM dba_hist_snapshot s, dba_hist_osstat os
WHERE s.dbid = os.dbid (+)
AND s.instance_number = os.instance_number (+)
AND s.snap_id = os.snap_id (+)
AND os.stat_name in ('BUSY_TIME','USER_TIME','SYS_TIME','NUM_CPUS','NUM_VCPUS','LOAD')
union all
SELECT s.instance_number, s.snap_id, s.begin_interval_time begin_interval_time, s.end_interval_time end_interval_time,
round((CAST (s.end_interval_time AS DATE) - CAST (s.begin_interval_time AS DATE)) * 24 * 3600) ela, ps.name stat_name,
(CASE WHEN s.begin_interval_time = s.startup_time THEN ps.VALUE
ELSE ps.VALUE - LAG (ps.VALUE, 1) OVER
(PARTITION BY ps.name, s.dbid, s.instance_number, s.startup_time ORDER BY s.snap_id)
END) diff_val, ps.VALUE value
FROM dba_hist_snapshot s, dba_hist_pgastat ps
WHERE s.dbid = ps.dbid (+)
AND s.instance_number = ps.instance_number (+)
AND s.snap_id = ps.snap_id (+)
AND ps.name in ('total PGA allocated')
union all
SELECT s.instance_number, s.snap_id, s.begin_interval_time begin_interval_time, s.end_interval_time end_interval_time,
round((CAST (s.end_interval_time AS DATE) - CAST (s.begin_interval_time AS DATE)) * 24 * 3600) ela, ss.stat_name stat_name,
(CASE WHEN s.begin_interval_time = s.startup_time THEN ss.VALUE
ELSE ss.VALUE - LAG (ss.VALUE, 1) OVER
(PARTITION BY ss.stat_id, s.dbid, s.instance_number, s.startup_time ORDER BY s.snap_id)
END) diff_val, ss.VALUE value
FROM dba_hist_snapshot s, dba_hist_sysstat ss
WHERE s.dbid = ss.dbid (+)
AND s.instance_number = ss.instance_number (+)
AND s.snap_id = ss.snap_id (+)
AND ss.stat_name in ('logons current','execute count',
'physical reads','physical writes',
'physical read total bytes','physical read bytes',
'physical write total bytes','physical write bytes',
'redo writes','redo size','redo blocks written',
'physical read IO requests','physical read total IO requests','physical read total multi block requests',
'physical read total IO multi block requests',
'physical write IO requests','physical write total IO requests','physical write total multi block requests',
'physical write total IO multi block requests',
'DBWR undo block writes','redo blocks read for recovery',
'bytes sent via SQL*Net to client','bytes received via SQL*Net from client','Estd Interconnect traffic',
'session logical reads',
'gc cr blocks received','gc current blocks received','gc cr blocks served','gc current blocks served',
'gcs messages sent','ges messages sent')
union all
SELECT s.instance_number, s.snap_id, s.begin_interval_time begin_interval_time, s.end_interval_time end_interval_time,
round((CAST (s.end_interval_time AS DATE) - CAST (s.begin_interval_time AS DATE)) * 24 * 3600) ela, dm.name stat_name,
(CASE WHEN s.begin_interval_time = s.startup_time THEN dm.VALUE
ELSE dm.VALUE - LAG (dm.VALUE, 1) OVER
(PARTITION BY dm.name, s.dbid, s.instance_number, s.startup_time ORDER BY s.snap_id)
END) diff_val, dm.VALUE value
FROM dba_hist_snapshot s, dba_hist_dlm_misc dm
WHERE s.dbid = dm.dbid (+)
AND s.instance_number = dm.instance_number (+)
AND s.snap_id = dm.snap_id (+)
AND dm.name in ('gcs msgs received','ges msgs received')
order by instance_number,snap_id desc,stat_name
), v$database d
Where TO_CHAR(end_interval_time, 'DD-MON-YYYY HH24:MI') > '2014-08-14 00:00:00'
GROUP BY snap_id,TO_CHAR(begin_interval_time, 'DD-MON-YYYY HH24:MI'),TO_CHAR(end_interval_time, 'DD-MON-YYYY HH24:MI'),d.name||instance_number
order by d.name||instance_number , snap_id
Download Speedway to graphically plot the above script results.
