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;
/
