AWR Snapshots/Reports, Baselines, Top SQL, Load Profile, and Licensing Awareness

Learning outcomes

The ServiceHub outage happened yesterday from 14:00–14:12, so the live sessions/cursors are gone. Automatic Workload Repository (AWR) persists periodic performance snapshots and produces delta-based reports containing load profile, time model, waits, top SQL, I/O and other diagnostic sections. Historical power creates a strict licensing obligation: AWR belongs to Oracle Diagnostics Pack.

1. Licensing preflight is step zero

The current 26ai licensing matrix lists Oracle Diagnostics Pack = included in Free and extra-cost on EE/EE-ES. The pack includes AWR, ADDM, ASH and related performance history. On Enterprise Edition, do not query DBA_HIST_*, V$ACTIVE_SESSION_HISTORY, DBMS_WORKLOAD_REPOSITORY or ADDM APIs until entitlement is confirmed.

SELECT banner_fullFROM v$versionWHERE banner_full LIKE 'Oracle%';SELECT name,value,isdefault,issys_modifiable,ispdb_modifiableFROM v$parameterWHERE name IN (  'control_management_pack_access',  'statistics_level')ORDER BY name;

Current defaults are DIAGNOSTIC+TUNING for Free and Enterprise Edition and TYPICAL for statistics. An unlicensed EE database can therefore be functionally enabled; contracts/licensing records decide legal use.

2. Use CDB-root snapshots for the mandatory Free lab

AWR can operate at CDB/PDB levels with PDB-specific snapshot behavior/settings. To avoid depending on a PDB automatic-flush configuration, the mandatory lab creates manual snapshots from CDB$ROOT while ServiceHub workload runs in FREEPDB1.

ALTER SESSION SET CONTAINER=CDB$ROOT;VARIABLE begin_snap NUMBERBEGIN  :begin_snap :=    DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT('TYPICAL');END;/PRINT begin_snap

3. Run a controlled workload in FREEPDB1

BEGIN  EXECUTE IMMEDIATE 'DROP TABLE sh22_awr_work PURGE';EXCEPTION WHEN OTHERS THEN  IF SQLCODE != -942 THEN RAISE; END IF;END;/CREATE TABLE sh22_awr_work ASSELECT  LEVEL AS id,  MOD(LEVEL,500) AS technician_id,  MOD(LEVEL*13,10000)/10 AS amount,  RPAD('awr-lab',120,'x') AS payloadFROM dualCONNECT BY LEVEL <= 150000;CREATE INDEX sh22_awr_tech_ixON sh22_awr_work(technician_id);BEGIN  DBMS_STATS.GATHER_TABLE_STATS(USER,'SH22_AWR_WORK',cascade=>TRUE);END;/BEGIN  FOR i IN 1..12 LOOP    FOR r IN (      SELECT technician_id,SUM(amount) total_amount      FROM sh22_awr_work      WHERE technician_id BETWEEN 50 AND 420      GROUP BY technician_id    ) LOOP      NULL;    END LOOP;  END LOOP;END;/

The loop intentionally creates repeatable SQL/CPU/logical-read activity; it is not a benchmark and should stay disposable.

4. Take the ending snapshot

VARIABLE end_snap NUMBERBEGIN  :end_snap :=    DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT('TYPICAL');END;/PRINT end_snapSELECT  snap_id,  begin_interval_time,  end_interval_time,  startup_timeFROM dba_hist_snapshotWHERE snap_id BETWEEN :begin_snap AND :end_snapORDER BY snap_id;

Both snapshots must belong to a meaningful compatible interval. An instance restart between snapshots changes what a report can validly compare.

5. Generate an AWR text report directly from SQLcl/SQL*Plus

VARIABLE dbid NUMBERVARIABLE instance_number NUMBERBEGIN  SELECT dbid INTO :dbid FROM v$database;  SELECT instance_number INTO :instance_number FROM v$instance;END;/PRINT dbidPRINT instance_number

SET PAGESIZE 0SET LINESIZE 200SET LONG 10000000SET LONGCHUNKSIZE 10000000SET TRIMSPOOL ONSPOOL sh22_awr_report.txtSELECT outputFROM TABLE(  DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_TEXT(    :dbid,    :instance_number,    :begin_snap,    :end_snap  ));SPOOL OFF

Oracle also supplies awrrpt.sql in a server Oracle home. Direct package output is useful when standalone SQLcl does not have the server's rdbms/admin script directory.

6. Read the report in a disciplined order

1. Elapsed/DB Time/DB CPU: how much foreground work occurred?
2. Load Profile: commits, executions, logical/physical reads and redo per second/transaction—what workload volume?
3. Top Timed Events / wait classes: where did foreground time accumulate?
4. Top SQL by elapsed/CPU/gets/reads/executions: which SQL accounts for the resource/time?
5. Instance/file/segment/advisory sections: supporting or contradicting evidence.

“Top SQL” means top among statements captured for that snapshot/report dimension. AWR sampling/top-SQL capture thresholds mean absence is not proof a statement never ran.

7. Baselines preserve a meaningful comparison interval

VARIABLE baseline_id NUMBERBEGIN  :baseline_id :=    DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE(      start_snap_id => :begin_snap,      end_snap_id   => :end_snap,      baseline_name => 'SH22_KNOWN_GOOD',      expiration    => 30    );END;/SELECT baseline_id,baseline_name,start_snap_id,end_snap_id,expirationFROM dba_hist_baselineWHERE baseline_name='SH22_KNOWN_GOOD';

A baseline pins/labels a snapshot range for comparison/retention behavior; it does not automatically prove the interval is healthy. Choose it from application/SLO evidence.

BEGIN  DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE(    baseline_name => 'SH22_KNOWN_GOOD',    cascade       => FALSE  );END;/

8. Deliberately wrong: “CONTROL_MANAGEMENT_PACK_ACCESS = DIAGNOSTIC+TUNING, so our EE license includes the packs”

That inference is false. Current Oracle documentation explicitly sets that value as the default on Enterprise Edition, while Diagnostics/Tuning remain extra-cost packs on EE/EE-ES. The safe repair is entitlement verification first; if not licensed, set/operate according to the organization's approved no-pack configuration and use the live/trace workflow from Lessons 1–2 and Lesson 5.

9. Cleanup

DROP TABLE sh22_awr_work PURGE;

The mandatory lab leaves the manual snapshots in AWR to avoid unexpectedly deleting repository history. They age out according to AWR retention unless explicitly managed by an authorized DBA.

10. Production judgment

AWR is best for historical interval comparison, load profile and durable high-load SQL/wait evidence. Use comparable business windows and record application releases/configuration changes around the snapshots. Do not tune from one report section in isolation.

On Free, Diagnostics Pack and Tuning Pack are included and CONTROL_MANAGEMENT_PACK_ACCESS defaults to DIAGNOSTIC+TUNING. On EE/EE-ES both packs are extra-cost. CONTROL_MANAGEMENT_PACK_ACCESS is dynamic but not PDB-modifiable; STATISTICS_LEVEL=BASIC disables many diagnostics and is strongly discouraged. No COMPATIBLE change or restart is required for this lab. Lesson 4 zooms from interval aggregates to sampled session timelines through ASH.

Authoritative references

• Gathering Statistics Using AWR — AWR snapshots/statistics
• DBMS_WORKLOAD_REPOSITORY — snapshot/report/baseline APIs
• Generating AWR Reports — report interpretation/generation
• CONTROL_MANAGEMENT_PACK_ACCESS — pack functional enablement/defaults
• Licensing Information — Diagnostics/Tuning pack entitlements