Search code examples
databaseoracle-databaseoracle11g

Oracle Database CPU, Memory utilization history

I need to determine the workload of our database instance each week, AWR report provides many details but its very hard to break down the data.

I need a query that produces a data set that represents the snap-id with the following value: CPU utilization Memory Utilization Read/Write operations

Using this set I will be able to create a histogram shows the CPU, memory, and read/write utilization during the week by each hour.

Solution

  • You can try querying the DBA_HIST_SYSMETRIC_SUMMARY view to get the CPU utilization Memory Utilization Read/Write operations at the SNAP_ID level.

    Sample query provided below:

    select * 
from DBA_HIST_SYSMETRIC_SUMMARY
where snap_id=<snap_id>
  and metric_name in ('Host CPU Utilization (%)','I/O Megabytes per Second','I/O Requests per Second','Total PGA Allocated');