1

I want to fetch the logical reads per second directly from database table. I want the same value as shown in AWR report. Seems like dba_hist_seg_stat table has logical read count, but I still can't relate it to time. Can anyone please let me know how can I exactly calculate the logical reads per second in for every hour as AWR report shows it?

pseudocode
  • 341
  • 1
  • 6
  • 15

1 Answers1

1

This assumes you have stats pack license:

select * from V$SYSMETRIC where metric_name = 'Logical Reads Per Sec';

select * from V$SYSMETRIC_SUMMARY where metric_name = 'Logical Reads Per Sec';

select * from V$SYSMETRIC_HISTORY where metric_name = 'Logical Reads Per Sec';

select * from DBA_HIST_SYSMETRIC_SUMMARY where metric_name = 'Logical Reads Per Sec';

The choice of the view depends on the detail and time span that you want to look in. The last one reads all awr snapshots. The first one shows the last minute and last 15 seconds values.

Also take a look at Metrics vs Statistics

  • Thanks ik_zelf I want the count per hour for last 24 hours. I suppose I have to go with the last view. I also need other information like physical read/writes per second and response time per transaction and I think all the information is in the last view. This is a great help. – pseudocode Sep 05 '13 at 06:07
  • Hi I also want to capture the information about lgwr lag. This is present in the lgwr trace files as warnings. Since it corresponds to log file sync in AWR reports, so I was hoping to get this information as well from the same table (DBA_HIST_SYSMETRIC_SUMMARY) but could not find it. Could you please let me know if I can fetch this information from some other table. Or I am looking for the wrong information altogether in AWR report. – pseudocode Sep 13 '13 at 19:49