-- [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')
;

-- Host CPU Utilization (%)

SELECT
 TO_CHAR(begin_time,'yyyymmdd') begin_date,
 COUNT(*) counts,
 ROUND(AVG(average),3)  "Avg CPU %"
FROM dba_hist_sysmetric_summary
WHERE  INSTR(metric_name,'Host CPU Utilization') >0   
GROUP BY  TO_CHAR(begin_time,'yyyymmdd')
ORDER BY  TO_CHAR(begin_time,'yyyymmdd')
;