Here are few silver bullets to use AWR for diagnosing performance issues …..

AWR – Automatic Workload Repository

  • Detailed & comprehensive performance history
  • Based on “snapshot” (time interval)
  • Automatic Database Diagnostic Monitor (ADDM)
  • About 79 DBA_HIST_* “tables”
  • Defaults: Hourly snapshots
  • Weekly retention (month is better)

Overview of important AWR tables

DBA_HIST_SNAPSHOT

  • PK:
    • SNAP_ID
    • DBID, INSTANCE_NUMBER
  • Basic interval characteristics:
    • BEGIN_INTERVAL_TIME
    • END_INTERVAL_TIME

DBA_HIST_SQLSTAT

  • PK:
    • SNAP_ID
    • SQL_ID, PLAN_HASH_VALUE (DBID, INSTANCE_NUMBER)
  • Basic stats:
    • Executions, Gets, Reads
  • Time:
    • CPU, Elapsed
  • Wait time:
    • IO, Concurrency, App
  • Use “deltas” rather than “totals”
  • Time in microseconds

DBA_HIST_SYSSTAT

  • PK:
    • SNAP_ID
    • STAT_ID (DBID, INSTANCE_NUMBER)
  • AWR snapshots of V$SYSSTAT
  • Includes STAT_NAME (almost 400!)
  • Does NOT include deltas, you need to calculate these yourself
  • Watch out for stat counter resets (bounces)
  • “DB time” in units of centiseconds
  • Use as basis of comparison

DBA_HIST_SQLTEXT

  • PK:
    • SQL_ID
    • DBID
  • No SNAP_ID, INSTANCE_NUMBER
  • Preserves SQL_TEXT even when found nowhere else

DBA_HIST_SYSTEM_EVENT

  • PK:
    • SNAP_ID
    • EVENT_ID (DBID, INSTANCE_NUMBER)
  • AWR snapshots of V$SYSTEM_EVENT
  • Includes EVENT_NAME (almost 900!)
  • Does NOT equal sum of ASH sampling
  • Time is clearly in units of microseconds

DBA_HIST_SEG_STAT_OBJ

  • PK:
    • OBJ#, DATAOBJ#  (DBID)
  • Object details (name, owner, type, tablespace)
  • Several segment types:
    • Table, Table Partition
    • Index, Index Partition
    • LOB

Here are few more ….

  • DBA_HIST_ACTIVE_SESS_HISTORY
  • DBA_HIST_SNAPSHOT
  • DBA_HIST_SQLSTAT
  • DBA_HIST_SYSSTAT
  • DBA_HIST_SQLTEXT
  • DBA_HIST_SYSTEM_EVENT
  • DBA_HIST_SEG_STAT
  • DBA_HIST_SEG_STAT_OBJ
  • DBA_HIST_WR_CONTROL

AWR settings

Defaults:

one hour snapshots,
one week retention (month is better)

To change:

DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
retention   IN  NUMBER    DEFAULT NULL,
interval    IN  NUMBER    DEFAULT NULL,
topnsql     IN  NUMBER    DEFAULT NULL,
dbid        IN  NUMBER    DEFAULT NULL);

Now we have got a outline for AWR. Next we will see some practical scenarios where we can use AWR.

PrintFriendly

Viewers of this post also viewed...