Navigation Bar

Saturday, August 28, 2021

Introduction to AWRs

An awr is a report extracted from the statistical data stored by oracle which gives an indication of the health and performance of the database. This data can then be used for problem detection and performance tuning purpose. 
This statistical data is collected and stored as snapshots at specific time intervals (generally 1 hour) and stored in tables owned by the SYS user and stored in the SYSAUX tablespace. 
A snapshot is a set of statistical data gathered for a specific time period. 
The AWR report takes the cumulative data from 2 snapshots and subtracts the earlier snapshot cumulative data from the later snapshot and generates a delta report showing the statistics and information relevant for the time period requested. The awr reports are run internally every hour and the findings are reported to the OEM interface. The user can use OEM or can manually generate the awr report in text or HTML format. 

The following workload repository views are available through which this statisical data that is collected can be viewed and analysed. 
V$ACTIVE_SESSION_HISTORY - Displays the active session history (ASH) sampled every second.
V$METRIC - Displays metric information.
V$METRICNAME - Displays the metrics associated with each metric group.
V$METRIC_HISTORY - Displays historical metrics.
V$METRICGROUP - Displays all metrics groups.
DBA_HIST_ACTIVE_SESS_HISTORY - Displays the history contents of the active session history.
DBA_HIST_BASELINE - Displays baseline information.
DBA_HIST_DATABASE_INSTANCE - Displays database environment information.
DBA_HIST_SNAPSHOT - Displays snapshot information.
DBA_HIST_SQL_PLAN - Displays SQL execution plans.
DBA_HIST_WR_CONTROL - Displays AWR settings.
This statistical data includes 
  • Wait events used to identify performance problems. 
  • Time model statistics indicating the amount of DB time associated with a process from the V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views. 
  • Active Session History (ASH) statistics from the V$ACTIVE_SESSION_HISTORY view. 
  • Some system and session statistics from the V$SYSSTAT and V$SESSTAT views. 
  • Object usage statistics. 
  • Resource intensive SQL statements. 
  • I/O, memory and CPU information 
The report has evolved over time with newer versions of oracle 
from the original utlbstat/utlestat scripts in 7i to 
statspack report in 8i and 9i to 
AWR from Oracle 10g onwards. 

The STATISTICS_LEVEL initialization parameter 

Gathering database statistics using the AWR is enabled by default and is controlled by the STATISTICS_LEVEL initialization parameter. 
The STATISTICS_LEVEL parameter should be set to the TYPICAL or ALL to enable statistics gathering by the AWR. The default setting is TYPICAL. 
Setting STATISTICS_LEVEL to BASIC disables many Oracle Database features, including the AWR, and is not recommended.
Generating an AWR report At the SQL prompt, enter 
@$ORACLE_HOME/rdbms/admin/awrrpt.sql
Enter value for report_type: text or HTML
Enter value for num_days: 2
Enter value for begin_snap: 150
Enter value for end_snap: 160
Enter value for report_name:
Using the report name awrrpt_1_150_160
The script for generating an awr report for a RAC database is 
@$ORACLE_HOME/rdbms/admin/awrgrpt.sql
The script for generating an awr report for a particular instance is 
@$ORACLE_HOME/rdbms/admin/awrrpti.sql
The script for generating an awr report for a particular RAC instance or instances is 
@$ORACLE_HOME/rdbms/admin/awrgrpti.sql
To generate an awr report for a particular SQL statement In this the sql_id of the concerned SQL statement needs to be given in input 
@$ORACLE_HOME/rdbms/admin/awrsqrpt.sql
....
....
Enter value for sql_id: 2b064ybzkwf1y
...
...
To generate an awr report for a particular SQL statement on a particular RAC instance In this the instance number and sql_id of the concerned SQL statement needs to be given in input 
@$ORACLE_HOME/rdbms/admin/awrsqrpi.sql

To compare awrs of 2 snapshot intervals 

This can be done by the awr compare periods report. 
While an awr report shows the AWR data of 2 snapshots, the AWR compare periods report helps to identify and compare performance attributes and settings between 2 time periods. This is useful to compare the data for 2 periods, 1 for a period where performance was good with 1 for a period where there is a performance issue. 
For example, if the awr is know to be normal between 10 am to 11 am from monday to thursday and on friday a performance issue or database slowness is reported for the same time interval we can do a comparative analysis of configuration settings, workload profile and statisitics to get an idea of the differences between these 2 time periods. Based on these differences it becomes easier to diagnose and pin point the cause of the performance degradation.

To generate an awr compare periods report 
@$ORACLE_HOME/rdbms/admin/awrddrpt.sql
...
Specify a beginning and ending snapshot ID for the first time period:
 -- here we can give the snap ids for the period where the database performance was good.
...
...
Specify a beginning and ending snapshot ID for the second time period:
 -- here we can give the snap ids for the period where the database performance has degraded
To generate an awr RAC compare periods report 
@$ORACLE_HOME/rdbms/admin/awrgdrpt.sql
To generate an awr compare periods report on a specific database instance 
@$ORACLE_HOME/rdbms/admin/awrddrpi.sql
To generate an AWR RAC compare periods report on a specific database instance -- here we can specify the specific RAC instance for which the data of the snap intervals to be compared. 
$ORACLE_HOME/rdbms/admin/awrgdrpi.sql
To create a snapshot at a time differenct from that of the automatically generated snapshot 
BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();
END;
/
Similarly you can drop a snapshot or a range of snapshots with the following command 
BEGIN
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (low_snap_id => 313, high_snap_id => 313);
END;
/

Modify snapshot settings 

This is used to adjust the interval, retention and the Top N SQL settings for a given database id. 
The INTERVAL setting affects how often the database automatically generates snapshots. 
The RETENTION setting affects how long the database stores snapshots in the workload repository. The 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). 

If we want to modify the retention period to 30 days, generate the awr snapshot every 30 mins and change the topN parameter to 100 
BEGIN
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( retention => 43200, interval => 30, topnsql => 100, dbid => 1597873919);
END;
/
These modified settings can be viewed from dba_hist_wr_control view 

Baselines 

Baselines are snapshots that are saved and retained irrespective of the AWR retention policy. 
They are used as a benchmark for comparison purpose with awr taken from another time interval. Baselines are basically fixed and moving window baselines. A fixed baseline is created between 2 snapshot ids or 2 time periods. If an expiry date is not mentioned this baseline will remain indefinitely. 
SET SERVEROUTPUT ON
DECLARE
  l_return  NUMBER;
BEGIN
  -- Using procedures.
  DBMS_WORKLOAD_REPOSITORY.create_baseline(
    start_snap_id => 308,
    end_snap_id   => 310,
    baseline_name => 'snap_baseline',
    expiration    => 60); -- optional

  DBMS_WORKLOAD_REPOSITORY.create_baseline(
    start_time    => TO_DATE('21-AUG-2021 11:00', 'DD-MON-YYYY HH24:MI'),
    end_time      => TO_DATE('21-AUG-2021 13:00', 'DD-MON-YYYY HH24:MI'),
    baseline_name => 'time_baseline',
    expiration    => NULL); -- optional
END;
/
These baselines that are created can be viewed in DBA_HIST_BASELINE view. 
More details of these baselines can be viewed from the pipelined table functions in DBMS_WORKLOAD_REPOSITORY package 
select_baseline_details (baseline id as input) 
select_baseline_metric (baseline name as input) 
SELECT baseline_id, baseline_name, START_SNAP_ID, 
       TO_CHAR(start_snap_time, 'DD-MON-YYYY HH24:MI') AS start_snap_time,       
       END_SNAP_ID,            
       TO_CHAR(end_snap_time, 'DD-MON-YYYY HH24:MI') AS end_snap_time
FROM   dba_hist_baseline
WHERE  baseline_type = 'STATIC'
ORDER BY baseline_id;


SELECT *
FROM   TABLE(DBMS_WORKLOAD_REPOSITORY.select_baseline_details(1));

SELECT *
FROM   TABLE(DBMS_WORKLOAD_REPOSITORY.select_baseline_metric('snap_baseline'));
Baselines can be renamed with the rename_baseline procedure 
BEGIN
  DBMS_WORKLOAD_REPOSITORY.rename_baseline(
    old_baseline_name => 'snap_baseline',
    new_baseline_name => 'snapid_baseline');
END;
/
Baselines can be dropped dropped using the DROP_BASELINE procedure. 
BEGIN
  DBMS_WORKLOAD_REPOSITORY.drop_baseline(baseline_name => 'time_baseline');
END;
/
Baselines can also be created and managed from OEM. 

MOVING WINDOW BASELINES 

A moving window baseline contains all AWR data within the AWR retention period. The default window size of the moving window baseline is the same as the default AWR retention period which is 8 days. 
The size of the moving window baseline is altered using the MODIFY_BASELINE_WINDOW_SIZE procedure, which accepts a WINDOW_SIZE parameter in days. 
SELECT moving_window_size
FROM   dba_hist_baseline
WHERE  baseline_type = 'MOVING_WINDOW'; -- 8 

BEGIN
  DBMS_WORKLOAD_REPOSITORY.modify_baseline_window_size(
    window_size => 5);
END;
/

SELECT moving_window_size
FROM   dba_hist_baseline
WHERE  baseline_type = 'MOVING_WINDOW'; -- 5
Other features included in AWR are baseline templates which allow you to define baselines which you would like to capture in the future.

References

https://expertoracle.com/2018/01/23/automatic-workload-repository-awr-basics/
https://oracle-base.com/articles/10g/automatic-workload-repository-10g#awr_features
https://oracle-base.com/articles/11g/awr-baseline-enhancements-11gr1

at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)