The AWR Report is only generated using snapshots from period that instance was Started. If any shutdown occurrs it break stats and AWR can’t generate a report comparing a period where stats belong a old Instance Startup.
This occurrs because Instance stats is not persistent accross reboots, (as the name says is a Instance), so all stats get reseted in every reboot.
When generating reports between hours is easy identify when instance was started, but when generating awr reports between many days this become a painfull task if instance was restarted multiples times during a desired period.
How to find the best Interval to Generate your AWR Reports?
To Single Instance this can be done using bellow query:
SET LINESIZE 200 SET PAGESIZE 200 UNDEF num_days COL startup_time FOR a30 COL db_name FOR a10 COL snap_start FOR 9999999 COL snap_end FOR 9999999 COL start_interval FOR a25 COL end_interval FOR a25 COL range_interval FOR a40 COL qtd_snaps FOR 999 SELECT s.startup_time, di.instance_name, MIN(snap_id) snap_start, MAX(snap_id) snap_end, MIN(end_interval_time) start_interval, MAX(end_interval_time) end_interval, EXTRACT(DAY FROM(MAX(end_interval_time) ) - MIN(end_interval_time) ) || ' Days(s) ' || EXTRACT(HOUR FROM(MAX(end_interval_time) ) - MIN(end_interval_time) ) || ' Hour(s) ' || EXTRACT(MINUTE FROM(MAX(end_interval_time) ) - MIN(end_interval_time) ) || ' Minute(s) ' range_interval, MAX(snap_id) - MIN(snap_id) qtd_snaps FROM dba_hist_snapshot s, dba_hist_database_instance di WHERE di.dbid = s.dbid AND di.instance_number = s.instance_number AND end_interval_time > DECODE(&&num_days,0,TO_DATE('31-JAN-9999','DD-MON-YYYY'),3.14,s.end_interval_time,TO_DATE(SYSDATE,'dd/mm/yyyy' ) - (&num_days - 1) ) GROUP BY s.startup_time, di.instance_name ORDER BY startup_time ASC;
In above output is easy identify what SNAP_ID to use without keep trying and getting ORA-20200 or by reading a huge list of snaps.
The above query is NOT valid to get SNAP_ID to generate AWR Global RAC Report.
Soon as possible I will post a Query to generate same output to easy our life when generating AWR Global RAC Report.
Hope its helps.
This demonstration compares the use of parallel and serial processing for the same SQL query.
This demonstration is associated with lessons 2 and 3 of the Oracle University seminar: Parallel Processing in Oracle 11g.