Create Manual Snapshots and Generate AWR Report for Database

Create Manual Snapshot

Login to the database using SYS user.

Then, execute the following command to create a manual snapshot.

EXECUTE dbms_workload_repository.create_snapshot();

Then wait for 30 min ( or required time period ) and create another manual snapshot by following command.

EXECUTE dbms_workload_repository.create_snapshot();

 

Then generate the AWR between those two manual snapshots.

Generating an AWR Report

The awrrpt.sql SQL script generates an HTML or text report that displays statistics for a range of snapshot Ids.

To generate an AWR report for a range of snapshots:

    At the SQL prompt, enter:

    @$ORACLE_HOME/rdbms/admin/awrrpt.sql

    Specify whether you want an HTML or a text report:
    Enter value for report_type: text

    Specify the number of days for which you want to list snapshot Ids.

    Enter value for num_days: 2

    A list of existing snapshots for the specified time range is displayed. In this example, snapshots captured in the last 2 days are displayed.

    Specify a beginning and ending snapshot ID for the workload repository report:

    Enter value for begin_snap: 150
    Enter value for end_snap: 160

    In this example, the snapshot with a snapshot ID of 150 is selected as the beginning snapshot, and the snapshot with a snapshot ID of 160 is selected as the ending snapshot.

    Enter a report name, or accept the default report name:

    Enter value for report_name:
    Using the report name awrrpt_1_150_160

    In this example, the default name is accepted and an AWR report named awrrpt_1_150_160 is generated.