Load Profile in AWR reports
=====
col short_name format a20 heading 'Load Profile'
col per_sec format 999,999,999.9 heading 'Per Second'
col per_tx format 999,999,999.9 heading 'Per Transaction'
set colsep ' '
select lpad(short_name, 20, ' ') short_name
, per_sec
, per_tx from
(select short_name
, max(decode(typ, 1, value)) per_sec
, max(decode(typ, 2, value)) per_tx
, max(m_rank) m_rank
from
(select /*+ use_hash(s) */
m.short_name
, s.value * coeff value
, typ
, m_rank
from v$sysmetric s,
(select 'Database Time Per Sec' metric_name, 'DB Time' short_name, .01 coeff, 1 typ, 1 m_rank from dual union all
select 'CPU Usage Per Sec' metric_name, 'DB CPU' short_name, .01 coeff, 1 typ, 2 m_rank from dual union all
select 'Redo Generated Per Sec' metric_name, 'Redo size' short_name, 1 coeff, 1 typ, 3 m_rank from dual union all
select 'Logical Reads Per Sec' metric_name, 'Logical reads' short_name, 1 coeff, 1 typ, 4 m_rank from dual union all
select 'DB Block Changes Per Sec' metric_name, 'Block changes' short_name, 1 coeff, 1 typ, 5 m_rank from dual union all
select 'Physical Reads Per Sec' metric_name, 'Physical reads' short_name, 1 coeff, 1 typ, 6 m_rank from dual union all
select 'Physical Writes Per Sec' metric_name, 'Physical writes' short_name, 1 coeff, 1 typ, 7 m_rank from dual union all
select 'User Calls Per Sec' metric_name, 'User calls' short_name, 1 coeff, 1 typ, 8 m_rank from dual union all
select 'Total Parse Count Per Sec' metric_name, 'Parses' short_name, 1 coeff, 1 typ, 9 m_rank from dual union all
select 'Hard Parse Count Per Sec' metric_name, 'Hard Parses' short_name, 1 coeff, 1 typ, 10 m_rank from dual union all
select 'Logons Per Sec' metric_name, 'Logons' short_name, 1 coeff, 1 typ, 11 m_rank from dual union all
select 'Executions Per Sec' metric_name, 'Executes' short_name, 1 coeff, 1 typ, 12 m_rank from dual union all
select 'User Rollbacks Per Sec' metric_name, 'Rollbacks' short_name, 1 coeff, 1 typ, 13 m_rank from dual union all
select 'User Transaction Per Sec' metric_name, 'Transactions' short_name, 1 coeff, 1 typ, 14 m_rank from dual union all
select 'User Rollback UndoRec Applied Per Sec' metric_name, 'Applied urec' short_name, 1 coeff, 1 typ, 15 m_rank from dual union all
select 'Redo Generated Per Txn' metric_name, 'Redo size' short_name, 1 coeff, 2 typ, 3 m_rank from dual union all
select 'Logical Reads Per Txn' metric_name, 'Logical reads' short_name, 1 coeff, 2 typ, 4 m_rank from dual union all
select 'DB Block Changes Per Txn' metric_name, 'Block changes' short_name, 1 coeff, 2 typ, 5 m_rank from dual union all
select 'Physical Reads Per Txn' metric_name, 'Physical reads' short_name, 1 coeff, 2 typ, 6 m_rank from dual union all
select 'Physical Writes Per Txn' metric_name, 'Physical writes' short_name, 1 coeff, 2 typ, 7 m_rank from dual union all
select 'User Calls Per Txn' metric_name, 'User calls' short_name, 1 coeff, 2 typ, 8 m_rank from dual union all
select 'Total Parse Count Per Txn' metric_name, 'Parses' short_name, 1 coeff, 2 typ, 9 m_rank from dual union all
select 'Hard Parse Count Per Txn' metric_name, 'Hard Parses' short_name, 1 coeff, 2 typ, 10 m_rank from dual union all
select 'Logons Per Txn' metric_name, 'Logons' short_name, 1 coeff, 2 typ, 11 m_rank from dual union all
select 'Executions Per Txn' metric_name, 'Executes' short_name, 1 coeff, 2 typ, 12 m_rank from dual union all
select 'User Rollbacks Per Txn' metric_name, 'Rollbacks' short_name, 1 coeff, 2 typ, 13 m_rank from dual union all
select 'User Transaction Per Txn' metric_name, 'Transactions' short_name, 1 coeff, 2 typ, 14 m_rank from dual union all
select 'User Rollback Undo Records Applied Per Txn' metric_name, 'Applied urec' short_name, 1 coeff, 2 typ, 15 m_rank from dual) m
where m.metric_name = s.metric_name
and s.intsize_csec > 5000
and s.intsize_csec < 7000
--sysdate - interval '5' minute
and END_TIME > sysdate - interval '5' minute
)
group by short_name)
order by m_rank;
DB Time 4.6
DB CPU 2.0
Redo size 255,839.5 7,734.4
Logical reads 596,220.2 18,024.6
Block changes 1,221.1 36.9
Physical reads 2,155.1 65.2
Physical writes 23.4 .7
User calls 754.5 22.8
Parses 874.5 26.4
Hard Parses 6.7 .2
Logons 20.8 .6
Executes 1,003.1 30.3
Rollbacks .0
Transactions 33.1
Applied urec .0 .0
--- =====
set head off pages 0 lines 300 echo off feedback off
VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBER number
VARIABLE x VARCHAR2(30)
exec select max(snap_id) -1 into :BgnSnap from dba_hist_snapshot ;
exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;
exec select DBID into :DID from v$database;
exec select INSTANCE_NUMBER into :INST_NUMBER from v$instance ;
WITH snaps
AS (SELECT :DID db_id,
:INST_NUMBER instance_number,
:EndSnap e_snap_id,
:BgnSnap b_snap_id
FROM DUAL), e_u_val
AS (SELECT SUM (VALUE) end_val
FROM dba_hist_sysstat e, snaps sn
WHERE 1 = 1
AND e.snap_id = sn.e_snap_id
AND e.dbid = sn.db_id
AND e.instance_number = sn.instance_number
AND e.stat_name IN ('user rollbacks', 'user commits')),
b_u_val
AS (SELECT SUM (VALUE) bgn_val
FROM dba_hist_sysstat b, snaps sn
WHERE 1 = 1
AND b.snap_id = sn.b_snap_id
AND b.dbid = sn.db_id
AND b.instance_number = sn.instance_number
AND b.stat_name IN ('user rollbacks', 'user commits')),
d_u_val
AS (SELECT end_val - bgn_val usr_val
FROM e_u_val, b_u_val
WHERE 1 = 1),
db_tme
AS (SELECT EXTRACT (
DAY FROM e.end_interval_time - b.end_interval_time)
* 86400
+ EXTRACT (
HOUR FROM e.end_interval_time - b.end_interval_time)
* 3600
+ EXTRACT (
MINUTE FROM e.end_interval_time - b.end_interval_time)
* 60
+ EXTRACT (
SECOND FROM e.end_interval_time - b.end_interval_time)
d_db_tme
FROM dba_hist_snapshot b, dba_hist_snapshot e, snaps sn
WHERE e.snap_id = sn.e_snap_id
AND b.snap_id = sn.b_snap_id
AND b.dbid = sn.db_id
AND b.instance_number = sn.instance_number
AND e.dbid = sn.db_id
AND e.instance_number = sn.instance_number),
trn_val
AS (SELECT 'Transactions:' st_name,
ROUND (usr_val / d_db_tme, 2) per_sec,
NULL per_txn,
12 m_rank
FROM db_tme, d_u_val
WHERE 1 = 1),
bgn_val
AS (SELECT /*+ use_hash(s) */
m.st_name, b.VALUE VALUE, m_rank
FROM dba_hist_sysstat b,
(SELECT 'redo size' stat_name, 'Redo size:' st_name, 1 m_rank
FROM DUAL
UNION ALL
SELECT 'session logical reads' stat_name,
'Logical reads:' st_name,
2 m_rank
FROM DUAL
UNION ALL
SELECT 'db block changes' metric_name,
'Block changes:' st_name,
3 m_rank
FROM DUAL
UNION ALL
SELECT 'physical reads' metric_name,
'Physical reads' st_name,
4 m_rank
FROM DUAL
UNION ALL
SELECT 'physical writes' metric_name,
'Physical writes:' st_name,
5 m_rank
FROM DUAL
UNION ALL
SELECT 'user calls' metric_name,
'User calls:' st_name,
6 m_rank
FROM DUAL
UNION ALL
SELECT 'parse count (total)' metric_name,
'Parses:' st_name,
7 m_rank
FROM DUAL
UNION ALL
SELECT 'parse count (hard)' metric_name,
'Hard Parses:' st_name,
8 m_rank
FROM DUAL
UNION ALL
SELECT 'logons cumulative' metric_name,
'Logons:' st_name,
9 m_rank
FROM DUAL
UNION ALL
SELECT 'execute count' metric_name,
'Executes:' st_name,
10 m_rank
FROM DUAL
UNION ALL
SELECT 'user rollbacks' metric_name,
'Rollbacks:' st_name,
11 m_rank
FROM DUAL) m,
snaps sn
WHERE 1 = 1
AND m.stat_name = b.stat_name
AND b.snap_id = sn.b_snap_id
AND b.dbid = sn.db_id
AND b.instance_number = sn.instance_number),
end_val
AS (SELECT /*+ use_hash(s) */
m.st_name, b.VALUE VALUE, m_rank
FROM dba_hist_sysstat b,
(SELECT 'redo size' stat_name, 'Redo size:' st_name, 1 m_rank
FROM DUAL
UNION ALL
SELECT 'session logical reads' stat_name,
'Logical reads:' st_name,
2 m_rank
FROM DUAL
UNION ALL
SELECT 'db block changes' metric_name,
'Block changes:' st_name,
3 m_rank
FROM DUAL
UNION ALL
SELECT 'physical reads' metric_name,
'Physical reads' st_name,
4 m_rank
FROM DUAL
UNION ALL
SELECT 'physical writes' metric_name,
'Physical writes:' st_name,
5 m_rank
FROM DUAL
UNION ALL
SELECT 'user calls' metric_name,
'User calls:' st_name,
6 m_rank
FROM DUAL
UNION ALL
SELECT 'parse count (total)' metric_name,
'Parses:' st_name,
7 m_rank
FROM DUAL
UNION ALL
SELECT 'parse count (hard)' metric_name,
'Hard Parses:' st_name,
8 m_rank
FROM DUAL
UNION ALL
SELECT 'logons cumulative' metric_name,
'Logons:' st_name,
9 m_rank
FROM DUAL
UNION ALL
SELECT 'execute count' metric_name,
'Executes:' st_name,
10 m_rank
FROM DUAL
UNION ALL
SELECT 'user rollbacks' metric_name,
'Rollbacks:' st_name,
11 m_rank
FROM DUAL) m,
snaps sn
WHERE 1 = 1
AND m.stat_name = b.stat_name
AND b.snap_id = sn.e_snap_id
AND b.dbid = sn.db_id
AND b.instance_number = sn.instance_number)
SELECT st_name, per_sec, per_txn
FROM (SELECT e.st_name,
ROUND ( (e.VALUE - b.VALUE) / (SELECT d_db_tme FROM db_tme),
1)
per_sec,
ROUND ( (e.VALUE - b.VALUE) / (SELECT usr_val FROM d_u_val), 1)
per_txn,
e.m_rank
FROM end_val e, bgn_val b
WHERE e.st_name = b.st_name AND e.m_rank = b.m_rank
UNION ALL
SELECT st_name, round(per_sec,1), per_txn, m_rank FROM trn_val)
ORDER BY m_rank
/
Redo size: 5,382,951.2 79709.2
Logical reads: 640,660.8 9486.7
Block changes: 23,180.7 343.3
Physical reads 10,727.6 158.9
Physical writes: 586.8 8.7
User calls: 796.8 11.8
Parses: 884.7 13.1
Hard Parses: 6.4 .1
Logons: 21.0 .3
Executes: 1,150.6 17
Rollbacks: .0 0
Transactions: 67.5
