DBA AWR System Metrics Summary
Description
Categories: Enginatics, Kcapps
Repository: Github
Repository: Github
Historic system statistics from the automated workload repository showing CPU load, wait time percentage, logical (buffer/RAM) and physical read and write IO figures, summarized in snapshot time intervals. All IO figures are measured in MB/s.
-CPU%: Total CPU usage percentage. If this is low, the hardware is underused and performance could potentially get improved by using it better e.g. by ... more
-CPU%: Total CPU usage percentage. If this is low, the hardware is underused and performance could potentially get improved by using it better e.g. by ... more
Run
DBA AWR System Metrics Summary and other Oracle EBS reports with Blitz Report™ on our demo environment
select * from ( select x.* &day_avg1 from ( select to_char(xxen_util.client_time(gs.end_time),'Day') day_of_week, xxen_util.client_time(gs.end_time) end_interval_time, gs.inst_id instance_number, gs.metric_name, gs.unit_factor*gs.value average_, null maxval_ from ( select gs.*, case when gs.metric_unit like 'Blocks %' or gs.metric_unit like 'Reads %' or gs.metric_unit like 'Writes %' then vp.value/1000000 when gs.metric_unit like '%Bytes%' then 1/1000000 else 1 end unit_factor from (select vp.value from v$parameter vp where vp.name='db_block_size') vp, (select x.* from (select max(gs.intsize_csec) over (partition by gs.inst_id, gs.metric_id) max_intsize_csec, gs.* from &sysmetric_view) x where x.intsize_csec=x.max_intsize_csec) gs where gs.metric_name in ( 'Host CPU Utilization (%)', 'Database Wait Time Ratio', 'Average Active Sessions', 'Logical Reads Per Sec', 'Physical Read Total Bytes Per Sec', 'Physical Write Total Bytes Per Sec', 'Redo Generated Per Sec', 'Redo Writes Per Sec', 'User Commits Per Sec', 'Hard Parse Count Per Sec', 'Network Traffic Volume Per Sec' ) ) gs union all select to_char(xxen_util.client_time(dhs.end_interval_time),'Day') day_of_week, xxen_util.client_time(dhs.end_interval_time) end_interval_time, dhss.instance_number, dhss.metric_name, dhss.average_, dhss.maxval_ from dba_hist_snapshot dhs, ( select dhss.unit_factor*dhss.average average_, dhss.unit_factor*dhss.maxval maxval_, dhss.* from ( select case when dhss.metric_unit like 'Blocks %' or dhss.metric_unit like 'Reads %' or dhss.metric_unit like 'Writes %' then vp.value/1000000 when dhss.metric_unit like '%Bytes%' then 1/1000000 else 1 end unit_factor, dhss.* from v$parameter vp, &sysmetric_symmary_view where vp.name='db_block_size' and dhss.metric_name in ( 'Host CPU Utilization (%)', 'Database Wait Time Ratio', 'Average Active Sessions', 'Logical Reads Per Sec', 'Physical Read Total Bytes Per Sec', 'Physical Write Total Bytes Per Sec', 'Redo Generated Per Sec', 'Redo Writes Per Sec', 'User Commits Per Sec', 'Hard Parse Count Per Sec', 'Network Traffic Volume Per Sec' ) ) dhss ) dhss where 1=1 and dhs.snap_id=dhss.snap_id and dhs.dbid=dhss.dbid and dhs.instance_number=dhss.instance_number ) x ) pivot ( sum(average_) avg, sum(maxval_) max &day_avg2 for metric_name in ( 'Host CPU Utilization (%)' "CPU%", 'Database Wait Time Ratio' "WAIT%", 'Average Active Sessions' act_sess, 'Logical Reads Per Sec' buff_read, 'Physical Read Total Bytes Per Sec' phys_read, 'Physical Write Total Bytes Per Sec' phys_write, 'Redo Generated Per Sec' redo_rate, 'Redo Writes Per Sec' redo_writes, 'User Commits Per Sec' commits, 'Hard Parse Count Per Sec' hard_parses, 'Network Traffic Volume Per Sec' network_traffic ) ) order by instance_number, cast(end_interval_time as date) desc |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Date From |
|
LOV | |
Date To |
|
LOV | |
Show Daily Averages |
|
LOV | |
Diagnostic Pack enabled |
|
LOV | |
Container Data |
|
LOV |