Oracle Database - AWR (Advanced|Automatic) - Workload Repository - Performance Statistics Gathering Tool - DBA_HIST views

Interval

Snapshots are made once every hour (snapshot interval) by default.

Retention period

Snapshots are retained by default for 8 days (retention period) as you have normally two different workloads between the week and the weekend due to batch job.

The retention period must be large enough to capture at least one complete workload cycle. If there is a monthly workload peak, a retention period of one month may be necessary.

TOPNSQL

TOPNSQL setting affects the number of Top SQL to flush for each SQL criteria (Elapsed Time, CPU Time, Parse Calls, sharable Memory, and Version Count).

Container Id

From 12g.

The ID of the container to which the data pertains. Possible values include:

• 0: This value is used for rows containing data that pertain to the entire CDB. This value is also used for rows in non-CDBs.
• 1: This value is used for rows containing data that pertain to only the root
• n: Where n is the applicable container ID for the rows containing data

Window

The default window baseline value is the current AWR retention_period.

see the snapshot parameters values

select * from DBA_HIST_WR_CONTROL;

see the snapshots

select * from DBA_HIST_SNAPSHOT

More details: DBA_HIST_SNAPSHOT

see baseline informations

• baseline

select * from DBA_HIST_BASELINE 

• Baseline Metrics

DBMS_WORKLOAD_REPOSITORY.SELECT_BASELINE_METRICS (
    baseline_name IN VARCHAR2,
             dbid IN NUMBER DEFAULT NULL,
     instance_num IN NUMBER DEFAULT NULL)
RETURN awr_baseline_metric_type_table PIPELINED;

• Baseline template

See the table DBA_HIST_BASELINE_TEMPLATE

Snapshot

• Modify the snapshots parameters

BEGIN
  DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( 
    retention => 11520,
    interval => 60, 
    topnsql => 100, 
    dbid => 3310949047,
    CON_ID =>   ); -- From 12c
END;
/

where:

• retention: is the retention period specified as minutes (8 days)
• interval: is the interval between each snapshot specified as minutes (1 hour)
• topnsql is the number of Top SQL to flush for each SQL criteria
• dbid: is the database identifier
• con_id: is the container id

To disable automatic snapshot collection, the snapshot interval must be set to 0.

• create snapshots manually

BEGIN
  DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();
END;
/

-- sqlplus
exec dbms_workload_repository.create_snapshot;

• delete snapshots:

BEGIN
  DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (
                           low_snap_id => 22, 
                           high_snap_id => 32, 
                           dbid => 3310949047);
END;
/

Baseline

• Create Now

BEGIN
    DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE (
                   start_snap_id => 270, 
                   end_snap_id => 280,
                   baseline_name => 'peak baseline', 
                   dbid => 3310949047,
                   expiration => 30);
END;
/

• Create a baseline in the future with a baseline template (Once, no schedule)

BEGIN
    DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE (
                   start_time => '2012-04-02 17:00:00 PST', 
                   end_time => '2012-04-02 20:00:00 PST', 
                   baseline_name => 'baseline_120402', 
                   template_name => 'template_120402', 
                   expiration => 30, 
                   dbid => 3310949047);
END;
/
-- a baseline template named template_120402 is created that will generate a baseline named baseline_120402 
-- for the time period from 5:00 p.m. to 8:00 p.m. on April 2, 2012 
-- on the database with a database ID of 3310949047. The baseline will expire after 30 days.

• Create baseline in the future with a scheduled baseline template (repeating)

BEGIN
    DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE (
                   day_of_week => 'monday', 
                   hour_in_day => 17,
                   duration => 3, 
                   expiration => 30,
                   start_time => '2012-04-02 17:00:00 PST', 
                   end_time => '2012-12-31 20:00:00 PST', 
                   baseline_name_prefix => 'baseline_2012_mondays_', 
                   template_name => 'template_2012_mondays',
                   dbid => 3310949047);
END;
/

• Drop a baseline

BEGIN
  DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE (
                  baseline_name => 'peak baseline',
                  cascade => FALSE, 
                  dbid => 3310949047);
END;
/
-- To drop the associated snapshots along with the baseline, set the cascade parameter to TRUE

• Drop a baseline template

BEGIN
  DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE_TEMPLATE (
                   template_name => 'template_2012_mondays',
                   dbid => 3310949047);
END;
/

• Renaming

BEGIN
    DBMS_WORKLOAD_REPOSITORY.RENAME_BASELINE (
                   old_baseline_name => 'peak baseline', 
                   new_baseline_name => 'peak mondays', 
                   dbid => 3310949047);
END;
/

• Modify windows settings (The window size must be set to a value that is equal to or less than the value of the AWR retention setting.)

BEGIN
    DBMS_WORKLOAD_REPOSITORY.MODIFY_BASELINE_WINDOW_SIZE (
                   window_size => 30, 
                   dbid => 3310949047);
END;
/