to overcome this issue we don't have worry we can generate the AWR-COMPARE report by providing the snap value as we do for normal AWR report but in this we have to give two range 1st pair of 1st day and 2nd pair of 2nd day snapshots after that oracle will take care to generate the COMPARE awr report ...use below mentioned step
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
SQL>--call to awrddrpti.sq sql from $ORACLE_HOME/rdbms/admin/awrddrpti.sql
SQL> @C:\app\sqladmin\product\12.1.0\dbhome_1\RDBMS\ADMIN\awrddrpt.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Id DB Name Inst Num Inst Num Instance
----------- ----------- ------------ -------- -------- ------------
1409205695 1409205695 ORCL 1 1 orcl
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: html ======>type the HTML to get the report in HTML
Type Specified: html
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 1409205695 1 ORCL orcl ALWAYSON1
* 1409205695 1 ORCL orcl NODE4
Database Id and Instance Number for the First Pair of Snapshots
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Using 1409205695 for Database Id for the first pair of snapshots
Using 1 for Instance Number for the first pair of snapshots
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing <return> without
specifying a number lists all completed snapshots.
Enter value for num_days: 5 ==> give the number days to display the snap range
Listing the last 5 days of Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
orcl ORCL 1 03 Jun 2015 17:00 1
2 04 Jun 2015 14:34 1
3 04 Jun 2015 16:00 1
4 04 Jun 2015 17:00 1
5 04 Jun 2015 18:00 1
6 04 Jun 2015 19:00 1
Specify the First Pair of Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 3 ===>Enter the begin snap of 1 day since when started issue
First Begin Snapshot Id specified: 3
Enter value for end_snap: 4 ===>Enter the end snap of 1 day when perf was acceptable
First End Snapshot Id specified: 4
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 1409205695 1 ORCL orcl ALWAYSON1
* 1409205695 1 ORCL orcl NODE4
Database Id and Instance Number for the Second Pair of Snapshots
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Using 1409205695 for Database Id for the second pair of snapshots
Using 1 for Instance Number for the second pair of snapshots
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing <return> without
specifying a number lists all completed snapshots.
Enter value for num_days2: 5 ====> Enter the days number to display the snap value if suppose before 5th days issue was ok then in 1st pair give 5 to get the 1st day value and in 2nd pair you have to give 5 gain to get the value of 5th day.
Listing the last 5 days of Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
orcl ORCL 1 03 Jun 2015 17:00 1
2 04 Jun 2015 14:34 1
3 04 Jun 2015 16:00 1
4 04 Jun 2015 17:00 1
5 04 Jun 2015 18:00 1
6 04 Jun 2015 19:00 1
Specify the Second Pair of Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap2: 5 ===>give the begin snap of 5th day when issue started
Second Begin Snapshot Id specified: 5
Enter value for end_snap2: 6
Second End Snapshot Id specified: 6 ===>give the end snap of 5th day when issue was OK
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrdiff_1_3_1_5.html To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name: awr_dif.html ===>Specify the report name with full path eg. c:\myreport.html(windows) or /u01/oracle/myreport.html(unix)
Same you can generate from SQL statement.
select snap_id, end_interval_time ====> use this query to get the snap vallue
from dba_hist_snapshot
where end_interval_time > trunc(sysdate-1)
order by snap_id;
======> Use this query to generate the report 1st value is DBid and 2nd instance number and 3rd is begin snap and 4th is end snap of 1st pair and 5th is dbid and 6th is inst number 7th being snap of 1st pair and 8th is end snap...
select * from TABLE(DBMS_WORKLOAD_REPOSITORY.awr_diff_report_html(4034329550,1,8947,8951,
4034329550,1,8971,8975));
Enjoy with Oracle.
