Oracle : AWR Customization

Share via:

Post Views: 0

Dear Readers,

In this article, we will see AWR Customization .

AWR Customization:
Oracle init parameter “statistics_level” enables AWR to collect and maintain performance data.
Values for parameter are ::
BASIC : Doesn’t collect AWR or any metric stats
TYPICAL: (Default), Collects typical advisories and excludes timed OS statistics
ALL : Enables to gather Max amount of performance data, can cause overhead. Recommended to use in case of specific performance issue troubleshooting.

AWR Retention period & Snapshot Interval:
AWR Retention Period & Snapshot Interval could be adjusted using below
Eg : If you want Snapshot for every 30 Min interval, and retention period 30 days (in Mins)
exec dbms_workload_repository.modify_snapshot_settings (
interval => 30, retention => 43200);

SYSAUX Space consumption:
Estimate SYSAUX space consumption for AWR dba_hist tables per database size
@$ORACLE_HOME/rdbms/admin/utlsyxsz.sql

AWR report per thresholds:
You can customize the report to view top n events or top n sqls or more by using executing below before running the AWR report.
Eg : For top 40 events & top 50 sqls
exec dbms_workload_repository.awr_set_report_thresholds(top_n_events=>40, top_n_sql=>50);
@$ORACLE_HOME /rdbms/admin/awrrpt.sql

AWR report Performance:
If AWR report or snapshot creation is running longer. It is possible the SQLs on fixed objects or data dictionary used by AWR chose inefficient execution plan. It requires Dictionary stats and / or fixed object stats gathered
exec DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
exec DBMS_STATS.GATHER_DICTIONARY_STATS(estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, options => ‘GATHER AUTO’);

AWR report comparison:
You can compare 2 AWR reports using below:
@$ORACLE_HOME /rdbms/admin/awrddrpt.sql
NOTE: This requires you to specify the first pair of snap IDs (Begin, end) & Second pair of snap IDs (Begin, end) for comparison

AWR Information Report:
Detailed AWR health report: Includes sysaux occupants, space usage by awr components, AWR snapshot info (with/without errors), AWR settings, ASH details etc…
@$ORACLE_HOME/rdbms/admin/awrinfo.sql

AWR Flushing:
MMON is background process which removes old historic data from AWR
Manual AWR data purge can be done by:
Exec dbms_workload_repository.drop_snapshot_range (low_snap_id => :B1,high_snap_id => :B2);

Exclude specific tables from purge
alter system set “_awr_disabled_flush_tables”=’ WRH$_SQLSTAT_BL’;
NOTE : This is an undocumented parameter so need to be careful using it, might want to get acknowledgement from oracle.

Thank you for giving your valuable time to read the above information.
Follow us on
Website : www.ktexperts.com
Facebook Page : KTexperts
Linkedin Page : KT EXPERTS