install sql server

How to Generate an AWR Report Using SQL Developer

Introduction

Oracle’s Automatic Workload Repository (AWR) is a powerful tool for monitoring database performance. It collects and stores performance statistics, allowing DBAs to analyze database health and optimize queries. In this guide, we’ll walk you through the steps to generate an AWR report using SQL Developer.

Step 1: Connect to SQL Developer

Open SQL Developer.
Connect to your database using an account with DBA privileges (e.g., SYSDBA or a user with SELECT_CATALOG_ROLE).

Step 2: Identify Available AWR Snapshots

AWR reports are generated using snapshots that capture database performance data at specific intervals. To check available snapshots, run the following SQL query:


  WITH SNAPSHOT_FILTERED AS (
    SELECT SNAP_ID, BEGIN_INTERVAL_TIME, END_INTERVAL_TIME  
    FROM DBA_HIST_SNAPSHOT  
    WHERE TRUNC(BEGIN_INTERVAL_TIME) BETWEEN TO_DATE('13-JAN-2025', 'DD-MON-YYYY')  
                                        AND TO_DATE('19-JAN-2025', 'DD-MON-YYYY')
)
SELECT SNAP_ID, BEGIN_INTERVAL_TIME, END_INTERVAL_TIME
FROM SNAPSHOT_FILTERED
WHERE (TO_CHAR(BEGIN_INTERVAL_TIME, 'HH24:MI:SS') BETWEEN '08:00:00' AND '10:00:00')
   OR (TO_CHAR(BEGIN_INTERVAL_TIME, 'HH24:MI:SS') BETWEEN '10:00:00' AND '14:00:00')
   OR (TO_CHAR(BEGIN_INTERVAL_TIME, 'HH24:MI:SS') BETWEEN '14:00:00' AND '18:00:00')
ORDER BY BEGIN_INTERVAL_TIME;

WITH SNAPSHOT_FILTERED AS (

SELECT SNAP_ID, BEGIN_INTERVAL_TIME, END_INTERVAL_TIME

FROM DBA_HIST_SNAPSHOT

WHERE TRUNC(BEGIN_INTERVAL_TIME) BETWEEN TO_DATE('13-JAN-2025', 'DD-MON-YYYY')

AND TO_DATE('19-JAN-2025', 'DD-MON-YYYY')

)

SELECT SNAP_ID, BEGIN_INTERVAL_TIME, END_INTERVAL_TIME

FROM SNAPSHOT_FILTERED

WHERE (TO_CHAR(BEGIN_INTERVAL_TIME, 'HH24:MI:SS') BETWEEN '08:00:00' AND '10:00:00')

OR (TO_CHAR(BEGIN_INTERVAL_TIME, 'HH24:MI:SS') BETWEEN '10:00:00' AND '14:00:00')

OR (TO_CHAR(BEGIN_INTERVAL_TIME, 'HH24:MI:SS') BETWEEN '14:00:00' AND '18:00:00')

ORDER BY BEGIN_INTERVAL_TIME;

This will display a list of snapshots, showing their IDs and timestamps. Note down the SNAP_ID values for the start and end intervals of the period you wish to analyze.

Step 3: Generate the AWR Report

Option 1: HTML Format


SET LONG 1000000;
SET PAGESIZE 0;
SET LINESIZE 1000;
SET TRIMOUT ON;
SET TRIMSPOOL ON;

SPOOL awr_report_08_10.html
SELECT output FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
    (SELECT DBID FROM V$DATABASE),  
    1,                              -- Instance Number
    <START_SNAP_ID_SLOT_1>,         -- Replace with correct SNAP_ID
    <END_SNAP_ID_SLOT_1>            -- Replace with correct SNAP_ID
));
SPOOL OFF;

SET LONG 1000000;

SET PAGESIZE 0;

SET LINESIZE 1000;

SET TRIMOUT ON;

SET TRIMSPOOL ON;

SPOOL awr_report_08_10.html

SELECT output FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(

(SELECT DBID FROM V$DATABASE),

1, -- Instance Number

<START_SNAP_ID_SLOT_1>, -- Replace with correct SNAP_ID

<END_SNAP_ID_SLOT_1> -- Replace with correct SNAP_ID

));

SPOOL OFF;

Option 2: Text Format


    SELECT output 
    FROM TABLE(
        DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_TEXT(
            (SELECT dbid FROM v$database), 
            1, 
            &lt;begin_snap_id&gt;, 
            &lt;end_snap_id&gt;
        )
    );

SELECT output

FROM TABLE(

DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_TEXT(

(SELECT dbid FROM v$database),

<begin_snap_id>,

<end_snap_id>

)

);

Tip: Replace <begin_snap_id> and <end_snap_id> with the snapshot IDs you identified earlier.

Step 4: Save the AWR Report

Once the query executes successfully, SQL Developer will return the report output. To save it:

For HTML format: Copy the output and save it as a .html file. Open it in a browser for better readability.
For Text format: Copy the output and save it as a .txt file.

Alternative: Generate AWR Report via SQL Developer GUI

Open SQL Developer and connect to your database.
Click View → Reports.
Navigate to:
Data Dictionary Reports → All Reports → AWR → AWR Report
Select your DBID, Instance Number, and Snapshot Range.
Click Run Report and save the output.

Bonus: Generating AWR Reports Using SQL*Plus

If you prefer SQL*Plus, you can run the built-in script:


    @?/rdbms/admin/awrrpt.sql

@?/rdbms/admin/awrrpt.sql

This will prompt you to enter:

Report format (HTML/TEXT)
Snapshot range

Ensuring AWR is Enabled

To check if AWR is enabled, run:


    SELECT * FROM DBA_HIST_WR_CONTROL;

SELECT * FROM DBA_HIST_WR_CONTROL;

If AWR is disabled, enable it using:


    EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(INTERVAL=>60, RETENTION=>43200);

EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(INTERVAL=>60, RETENTION=>43200);

(Interval in minutes, Retention in minutes)

Conclusion

AWR reports provide valuable insights into Oracle database performance. Using SQL Developer, you can quickly generate and analyze these reports to troubleshoot performance issues, optimize queries, and enhance database efficiency.

Do you use AWR reports frequently? Let us know your experience in the comments below! 🚀

Tags: #OracleDBA #SQLDeveloper #AWR #PerformanceTuning #DatabaseMonitoring