AWR report generation,settings and baseline

1) AWR report manually snapshot command 

EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;

Note: By default snapshots of the relevant data are taken every hour and retained for 7 days.

2) The default value of AWR report can alter by below procedure.

BEGIN

  DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(

    retention => 43200,        -- Minutes (= 30 Days). Current value retained if NULL.

    interval  => 60);          -- Minutes. Current value retained if NULL.

   END;

   /

3) To Describe or understand DBMS_WORKLOAD_REPOSITORY you need to run below command which will help to check AWR settings.

desc dbms_workload_repository;

Note: The MMON Oracle background process is responsible for periodically flushing the oldest AWR tables, using a LIFO queue method. 

The only parameter listed in the procedures is the flush_level , which can have either the default value of TYPICAL or a value of ALL.  When the statistics level is set to ALL, the AWR gathers the maximum amount of performance data.

If we need to disable flushing the run time statistics for an AWR workload table, you can get the underlying WRH tables with this query:

 select

    table_id_kewrtb,

    table_name_kewrtb

from

    x$kewrtb

order by

    table_id_kewrtb;

Once you identify a specific table to disable flushing, you can use an ALTER SYSTEM command:

 alter system set “_awr_disabled_flush_tables”=’ WRH$_IC_CLIENT_STATS’; 

4) AWR setting query through it we can know AWR snapshot settings

select

       extract( day from snap_interval) *24*60+

       extract( hour from snap_interval) *60+

       extract( minute from snap_interval ) "Snapshot Interval",

       extract( day from retention) *24*60+

       extract( hour from retention) *60+

       extract( minute from retention ) "Retention Interval"

from dba_hist_wr_control;

We can know all AWR setting through 

$ORACLE_HOME/rdbms/admin/awrinfo.sql;

which can help you keep track of the AWR repository sizing. 

5) To drop Snapshot

BEGIN

DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range (

low_snap_id  => 22, 

high_snap_id => 32);

END;

/

6) Snapshot information can be viewd by 

Select * from dba_hist_snapshot;

7) Baseline

A baseline is a pair of snapshots that represents a specific period of usage.   Once baselines are defined they can be used to compare current performance against similar periods in the past. You may wish to create baseline to represent a period of batch processing.

BEGIN

 DBMS_WORKLOAD_REPOSITORY.create_baseline (

   start_snap_id => 210, 

   end_snap_id   => 220,

   baseline_name => 'batch baseline');

END;

/

Drop base line:

BEGIN

 DBMS_WORKLOAD_REPOSITORY.drop_baseline (

   baseline_name => 'batch baseline',

   cascade       => FALSE); -- Deletes associated snapshots if TRUE.

END;

/