Automatic Workload Repository (AWR) is a collection of database statistics owned by the SYS user. By default snapshot are generated once every 60min .

But In case we wish to generate awr snapshot manually, then we can run the below script.  This is usually useful, when we need to generate an awr report for a non-standard window with smaller interval.

Lets say, we want to generate a report for next 5 minutes. (7.10 – 7.15) . So we will generate a snapshot at 7.10 and another at 7.15 . And awr can be generated using this begin_snap_id and end_snap_id.

1. Current available snapshots in database:

select snap_id,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME from dba_hist_snapshot where BEGIN_INTERVAL_TIME > systimestamp -1 order by BEGIN_INTERVAL_TIME desc;



   SNAP_ID BEGIN_INTERVAL_TIME                                                         END_INTERVAL_TIME
---------- --------------------------------------------------------------------------- ---------------------------------------------------------------------------
     19279 04-FEB-18 08.00.37.329 AM                                                   04-FEB-18 09.00.28.712 AM 
     19278 04-FEB-18 07.00.46.301 AM                                                   04-FEB-18 08.00.37.329 AM
     19277 04-FEB-18 06.00.56.088 AM                                                   04-FEB-18 07.00.46.301 AM
     19276 04-FEB-18 05.00.02.943 AM                                                   04-FEB-18 06.00.56.088 AM
     19275 04-FEB-18 04.00.11.668 AM                                                   04-FEB-18 05.00.02.943 AM
     19274 04-FEB-18 03.00.20.483 AM                                                   04-FEB-18 04.00.11.668 AM
     19273 04-FEB-18 02.00.29.134 AM                                                   04-FEB-18 03.00.20.483 AM
     19272 04-FEB-18 01.00.38.004 AM                                                   04-FEB-18 02.00.29.134 AM

2.Generate a new snapshot:

EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;

PL/SQL procedure successfully completed.

3. Check the snapshots(whether new one is created or not )

select snap_id,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME from dba_hist_snapshot where BEGIN_INTERVAL_TIME > systimestamp -1 order by BEGIN_INTERVAL_TIME desc;

   SNAP_ID BEGIN_INTERVAL_TIME                                                         END_INTERVAL_TIME
---------- --------------------------------------------------------------------------- ---------------------------------------------------------------------------
     19280 04-FEB-18 09.00.28.712 AM                                                   04-FEB-18 09.53.43.968 AM ------>>>> Newly created snap
     19279 04-FEB-18 08.00.37.329 AM                                                   04-FEB-18 09.00.28.712 AM
     19278 04-FEB-18 07.00.46.301 AM                                                   04-FEB-18 08.00.37.329 AM
     19277 04-FEB-18 06.00.56.088 AM                                                   04-FEB-18 07.00.46.301 AM
     19276 04-FEB-18 05.00.02.943 AM                                                   04-FEB-18 06.00.56.088 AM
     19275 04-FEB-18 04.00.11.668 AM                                                   04-FEB-18 05.00.02.943 AM
     19274 04-FEB-18 03.00.20.483 AM                                                   04-FEB-18 04.00.11.668 AM
     19273 04-FEB-18 02.00.29.134 AM                                                   04-FEB-18 03.00.20.483 AM
     19272 04-FEB-18 01.00.38.004 AM                                                   04-FEB-18 02.00.29.134 AM

 

Here the snap 19280 snap_id has been generated.