How To Create AWR Snapshot Manually
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.
For example if 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:
SQL> set linesize 1000
SQL> set pagesize 1000
SQL> 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
---------- --------------------------------------------------------------------------- ---------------------------------------------------------------------------
11 21-AUG-20 12.30.11.310 AM 21-AUG-20 01.20.40.714 AM
10 20-AUG-20 11.30.26.603 PM 21-AUG-20 12.30.11.310 AM
9 20-AUG-20 10.30.05.138 PM 20-AUG-20 11.30.26.603 PM
8 20-AUG-20 09.30.48.535 PM 20-AUG-20 10.30.05.138 PM
7 20-AUG-20 08.30.35.698 PM 20-AUG-20 09.30.48.535 PM
6 20-AUG-20 07.52.26.815 PM 20-AUG-20 08.30.35.698 PM
5 20-AUG-20 07.41.25.000 PM 20-AUG-20 07.52.26.815 PM
4 20-AUG-20 05.30.50.857 PM 20-AUG-20 06.30.07.232 PM
3 20-AUG-20 04.30.31.670 PM 20-AUG-20 05.30.50.857 PM
2 20-AUG-20 03.30.09.329 PM 20-AUG-20 04.30.31.670 PM
1 20-AUG-20 02.36.53.000 PM 20-AUG-20 03.30.09.329 PM
11 rows selected.
2.Generate a new snapshot:
SQL> EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;
PL/SQL procedure successfully completed.
3. Check the newly created snapshots
SQL> 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
---------- --------------------------------------------------------------------------- ---------------------------------------------------------------------------
12 21-AUG-20 01.20.40.714 AM 21-AUG-20 01.30.42.900 AM ------> newly generated snapshot
11 21-AUG-20 12.30.11.310 AM 21-AUG-20 01.20.40.714 AM
10 20-AUG-20 11.30.26.603 PM 21-AUG-20 12.30.11.310 AM
9 20-AUG-20 10.30.05.138 PM 20-AUG-20 11.30.26.603 PM
8 20-AUG-20 09.30.48.535 PM 20-AUG-20 10.30.05.138 PM
7 20-AUG-20 08.30.35.698 PM 20-AUG-20 09.30.48.535 PM
6 20-AUG-20 07.52.26.815 PM 20-AUG-20 08.30.35.698 PM
5 20-AUG-20 07.41.25.000 PM 20-AUG-20 07.52.26.815 PM
4 20-AUG-20 05.30.50.857 PM 20-AUG-20 06.30.07.232 PM
3 20-AUG-20 04.30.31.670 PM 20-AUG-20 05.30.50.857 PM
2 20-AUG-20 03.30.09.329 PM 20-AUG-20 04.30.31.670 PM
1 20-AUG-20 02.36.53.000 PM 20-AUG-20 03.30.09.329 PM
12 rows selected.
SQL> !date
Fri Aug 21 01:31:07 IST 2020
In our example the snap 12 snap_id has been generated.
No comments:
Post a Comment