How can I add another section for Database Monitor charts inside the AWR Browser. How do I create a Custom Chart in AWR Browser?
The AWR Browser can only show data from the AWR. The data dictionary views all start with DBA_HIST_. A lot of the data in those AWR views comes from views like v$sysstat, where the database monitor gets its data.
The database monitor has this as one of its queries (see comments to see where each of these shows up):
select sum(decode(name,'db block changes',value,0)) block_changes, -- logical IO chart
sum(decode(name,'db block gets',value,0)) current_reads, -- logical IO chart
sum(decode(name,'consistent gets',value,0)) consistent_reads, -- logical IO chart
sum(decode(name,'physical reads',value,0)) datafile_reads, -- physical IO chart
sum(decode(name,'physical writes',value,0)) datafile_writes, -- physical IO chart
sum(decode(name,'redo writes',value,0)) redo_writes, -- physical IO chart
sum(decode(name,'parse count (total)',value,0)) parse_count, -- call rates chart
sum(decode(name,'execute count',value,0)) execute_count, -- call rates chart
sum(decode(name,'user commits',value,0)) u_commit, -- call rates chart
sum(decode(name,'user rollbacks',value,0)) u_rollback -- call rates chart
from v$sysstat
Here's a sample Custom Chart for Logical I/O:
1) In the AWR Browser, right-click "Custom", then click "New Chart/Grid"
2) Type: "Logical IO" under "Note Name:" and select "A multi-series chart"
3) Click Next and enter this for the query:
Select sn.snap_id, sn.BEGIN_INTERVAL_TIME,
sum(decode(ss2.stat_name,'db block changes',ss2.value,0)) -
sum(decode(ss1.stat_name,'db block changes',ss1.value,0)) "Block Changes",
sum(decode(ss2.stat_name,'db block gets',ss2.value,0)) -
sum(decode(ss1.stat_name,'db block gets',ss1.value,0)) "Current Reads",
sum(decode(ss2.stat_name,'consistent gets',ss2.value,0)) -
sum(decode(ss1.stat_name,'consistent gets',ss1.value,0)) "Consistent Gets"
From SYS.DBA_HIST_sysstat ss1,
SYS.DBA_HIST_sysstat ss2,
SYS.DBA_HIST_snapshot sn
where ss1.snap_id = (select max(s.snap_id)
from SYS.DBA_HIST_snapshot s
where s.snap_id < sn.snap_id
and s.instance_number = sn.instance_number
and s.dbid = sn.dbid
and s.startup_time = sn.startup_time)
and ss2.snap_id = sn.snap_id
and ss1.dbid = sn.dbid
and ss1.stat_id = ss2.stat_id
and ss1.stat_name in ('db block changes', 'db block gets', 'consistent gets')
and ss1.instance_number = sn.instance_number
and ss2.dbid = sn.dbid
and ss2.instance_number = sn.instance_number
and sn.snap_id in (&Snapshotlist)
group by sn.snap_id, sn.BEGIN_INTERVAL_TIME
order by sn.snap_id
4) The Logical IO chart will now appear under Custom Charts. Simila process could be done for the Physical IO Chart and the Call Rates chart as well as most of the other charts.
© 2021 Quest Software Inc. ALL RIGHTS RESERVED. Feedback Terms of Use Privacy