Automatic Workload Repository

Collected performance statistics

The collected statistics include:

Access and usage statistics of segments
Activity usages («time model statistics»), as found in v$sys_time_model and v$sess_time_model.
System and session related statistics as found in v$sysstat and v$sesstat
The SQL statements that use lots of system resource or take long to finish
Active Session History

AWR Snapshot

The instance stores performance data in the SGA.

The MMON background process regularly (by default: hourly) processes the collected data and creates AWR Snapshots. Thus, an AWR snapshot contains the performance metrics for a time period. A snapshot corresponds to the minimum time granularity for reporting purposes.

These snapshots are then written into the SYSAUX tablespace.

dbms_workload_repository.modify_snapshot_settings allows to specify settings such as

snapshot interval
retention period
number of SQL statements for each top criteria

See also the init parameter awr_pdb_autoflush_enabled.

Use $ORACLE_HOME/rdbms/admin/awrrpti.sql to report the differences between two snapshots.

Find snasphots

dba_hist_snapshot allows to find snapshots (taken in a given period)

--
--     Find Snap IDs between «one day ago» and «now».
--

select
  snap_id,
  begin_interval_time,
  end_interval_time
from
  dba_hist_snapshot
where
  end_interval_time   > sysdate - 1 and
  begin_interval_time < sysdate
order by
  begin_interval_time;

Github repository oracle_scriptlets, path: /ash-awr/awr/find_snap_ids.sql

AWR baseline

An AWR baseline is a set of performance metric values. Usually, these are determined when a system performs without problems. They can then be used to compare the metrics in times when a System does not seem to perform very well.

Automatic Database Monitor (ADDM)

The Automatic Database Monitor uses the captured statistics to automatically and proactively find possible performance problems.

ADDM can be run manually, if needed.

Query difference between two periods

with
stat_1 as       (-- {
   select /*+ materialize */
            stat.sql_id sql_id,
       sum (stat.executions_delta    )        executions,
       sum (stat.elapsed_time_delta  ) / 1e6 elapsed_time,
       sum (stat.cpu_time_delta      )        cpu_time,
       sum (stat.iowait_delta        )        iowait_time,
       sum (stat.disk_reads_delta    )        disk_reads,
       sum (stat.buffer_gets_delta   )        buffer_gets,
       sum (stat.direct_writes_delta )        writes,
       sum (stat.parse_calls_delta   )        parses,
       sum (stat.sorts_delta         )        sorts
   from
       dba_hist_sqlstat stat  join
       dba_hist_snapshot snap using (snap_id)
   where
       trunc(snap.end_interval_time) = trunc(sysdate-2)
   group by
       stat.sql_id
   having sum(stat.executions_delta) > 0
),  -- }
stat_2 as       (-- {
   select /*+ materialize */
            stat.sql_id sql_id,
       sum (stat.executions_delta    )       executions,
       sum (stat.elapsed_time_delta  ) / 1e6 elapsed_time ,
       sum (stat.cpu_time_delta      )       cpu_time,
       sum (stat.iowait_delta        )       iowait_time,
       sum (stat.disk_reads_delta    )       disk_reads,
       sum (stat.buffer_gets_delta   )       buffer_gets,
       sum (stat.direct_writes_delta )       writes,
       sum (stat.parse_calls_delta   )       parses,
       sum (stat.sorts_delta         )       sorts
   from
       dba_hist_sqlstat stat  join
       dba_hist_snapshot snap using (snap_id)
   where
       trunc(snap.end_interval_time) = trunc(sysdate-1)
   group by
       stat.sql_id
   having sum(stat.executions_delta) > 0
)  -- }
select           -- {
   sql_id,
   replace (cast (dbms_lob.substr (text.sql_text, 250) as varchar (250)), chr (10), '')   sql_text,
   --
   stat_2.elapsed_time - stat_1.elapsed_time elapsed_time_d, stat_1.elapsed_time elapsed_time_1, stat_2.elapsed_time elapsed_time_2,
   stat_2.executions   - stat_1.executions   executions_d  , stat_1.executions     executions_1, stat_2.executions     executions_2
from
  stat_1                    full outer join
  stat_2                using (sql_id) join
  dba_hist_sqltext text using (sql_id)
order by
  nvl(stat_2.elapsed_time, 0) - nvl(stat_1.elapsed_time, 0) desc -- }
;