Tag: awr

Posted on

How to Read an AWR Report in Oracle

grand canyon

When an Oracle database slows down, the first place a DBA should look is the AWR report — a detailed snapshot of what’s happening inside the system.

It reveals where time is spent, which queries are consuming resources, and whether the database is healthy or struggling.

In this post, we’ll walk through the key sections to check so you can quickly tell if your database is performing well — and where to focus your tuning efforts.

Below is the order I (and most senior DBAs) follow when analyzing an AWR report:

Continue reading “How to Read an AWR Report in Oracle”
Posted on

How to Change AWR Retention Period in Oracle Database

water drop photo

You can first check the AWR Retention Period in Oracle with this

set lines 200
col SNAP_INTERVAL format a20
col RETENTION format a20
col SRC_DBNAME format a20

select * from dba_hist_wr_control;

      DBID SNAP_INTERVAL        RETENTION            TOPNSQL        CON_ID   SRC_DBID SRC_DBNAME
---------- -------------------- -------------------- ---------- ---------- ---------- --------------------
3847XX0535 +00000 01:00:00.0    +00008 00:00:00.0    DEFAULT             0 3847XX0535

You can see that the interval is every hour and the retention is just 8 days.

You can change the retention to 30 days with this (30*24*60)

execute dbms_workload_repository.modify_snapshot_settings(retention => 43200);

If you also want to change the SNAP_INTERVAL, you can change it like that for taking snaps every 30 mins

execute dbms_workload_repository.modify_snapshot_settings(interval => 30);