DBAtlas · Database operations atlas

AWR diff (compare period) report between two periods

OraclePerformance

Command

-- Interactive: @?/rdbms/admin/awrddrpt.sql   (RAC: awrgdrpt.sql)

-- From a script: period 1 (good) = <bid1>-<eid1>, period 2 (bad) = <bid2>-<eid2>
SET LINESIZE 8000 PAGESIZE 0 TRIMSPOOL ON HEADING OFF FEEDBACK OFF
SPOOL /tmp/awr_diff.txt
SELECT output
FROM   TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_DIFF_REPORT_TEXT(
         dbid1 => (SELECT dbid FROM v$database), inst_num1 => 1,
         bid1  => <bid1>, eid1 => <eid1>,
         dbid2 => (SELECT dbid FROM v$database), inst_num2 => 1,
         bid2  => <bid2>, eid2 => <eid2>));
SPOOL OFF
-- HTML: DBMS_WORKLOAD_REPOSITORY.AWR_DIFF_REPORT_HTML

What it does

Puts two snapshot ranges side by side and shows percentage differences in DB time, wait events, load profile and per-SQL statistics. Directly answers "it was fine yesterday, slow today".

When to use it

Regression analysis after an upgrade/patch, parameter change, deployment or statistics gathering; comparing the same time slot on two days.

Caution

Requires the Diagnostics Pack license. Compared periods should have the same length and similar workload, otherwise the deltas are misleading. To compare different databases (e.g. prod vs test), move AWR data with awrextr.sql/awrload.sql and pass the matching dbid2.

Open in DBAtlas →

Related