Oracle IOPS Analysis And AWR Report
-- [1] Query to get span_id for specific time period (7 days used below)

SELECT dhs.snap_id
FROM  dba_hist_snapshot dhs,
    v$instance i
WHERE  dhs.instance_number = i.instance_number
AND    begin_interval_time 
       BETWEEN TRUNC(SYSDATE)-7 AND TRUNC(SYSDATE);

-- The input values shown are for reference.  Actual database_id (dbid1,dbid2), 
-- instance_number and other IDs to be used from output of above query [1] to 
-- create a diff report.

SELECT *
FROM TABLE(dbms_workload_repository.awr_diff_report_html(
         dbid1     => 100001,
         inst_num1 => 1,
         bid1      => 1000,
         eid1      => 1001,
         dbid2     => 100001,
         inst_num2 => 1,
         bid2      => 1002,
         eid2      => 1003
       )
);

-- The actual database_id (l_dbid) and instance_number to be used from output 
-- of query [1] to create a ash (Active Session History) report in last 7 days.

SELECT *
FROM TABLE(dbms_workload_repository.ash_report_html(
         l_dbid     => 100001,
         l_inst_num => 1,
         l_btime    => TRUNC(SYSDATE)-7,
         l_etime    => SYSDATE
       )
);

-- List of metrics (CPU, IOPS etc.) and their IDs

SELECT  metric_id, metric_name, metric_unit
FROM  dba_hist_sysmetric_summary
ORDER BY metric_id;

-- IOPS from dba_his_sysmetric_summary table

SELECT
 TO_CHAR(begin_time,'yyyymmdd') begin_date,
 COUNT(*) counts,
 SUM(average)  avg_iops
FROM dba_hist_sysmetric_summary
WHERE  INSTR(metric_name,'Total IO') >0
GROUP BY  TO_CHAR(begin_time,'yyyymmdd')
ORDER BY  TO_CHAR(begin_time,'yyyymmdd');


1. Oracle and Server CPU analysis

2. Oracle Database CPU Analysis Queries

3. Oracle Database CPU Analysis Queries

Oracle PGA and SGA Analysis

Oracle registered trademark of Oracle Corporation.

Last Revised On: May 31st, 2014

  273