Problem
You want to establish baseline statistics that represent a normal view of database operations
Solution
You can create AWR baselines in order to establish a saved workload view for your database, which can be used later for comparison to other AWR snapshots. The purpose of a baseline is to establish a normal workload view of your database for a predefined time period. Performance statistics for an AWR baseline are saved in your database, and are not purged automatically. There are two types of baselines—fixed and moving.
Fixed Baselines
The most common type of baseline is called a fixed baseline. This is a single, static view that is meant to represent a normal system workload. To manually create an AWR baseline, you can use the CREATE_BASELINE procedure of the DBMS_WORKLOAD_REPOSITORY PL/SQL package. The following example illustrates how to create a baseline based on a known begin and end date and time for which the
baseline will be created:
SQL> exec dbms_workload_repository.create_baseline -
(to_date(‘2011-06-01:00:00:00’,’yyyy-mm-dd:hh24:mi:ss’), -
to_date(‘2011-06-01:06:00:00’,’yyyy-mm-dd:hh24:mi:ss’),’Batch Baseline #1’);
PL/SQL procedure successfully completed.
For the foregoing baseline, we want to establish a normal workload for a data warehouse batch window, which is between midnight and 6 a.m. This baseline will be held indefinitely unless explicitly dropped (see Recipe 4-7 for managing AWR baselines). Any fixed baseline you create stays in effect until a new baseline is created. If you want to have a set expiration for a baseline, you can simply specify the retention period for a baseline when creating it by using the EXPIRATION parameter, which is specified in days:
exec dbms_workload_repository.create_baseline( -
start_time=>to_date(‘2011-06-01:00:00:00’,’yyyy-mm-dd:hh24:mi:ss’), -
end_time=>to_date(‘2011-06-01:06:00:00’,’yyyy-mm-dd:hh24:mi:ss’), -
baseline_name=>’Batch Baseline #1’, -
expiration=>30);
You can also create a baseline based on already created AWR snapshot IDs. In order to do this, you could run the CREATE_BASELINE procedure as follows:
exec dbms_workload_repository.create_baseline( -
start_snap_id=>258,end_snap_id=>268,baseline_name=>’Batch Baseline #1’, expiration=>30);
Moving Baselines
Like the fixed baseline, the moving baseline is used to capture metrics over a period of time. The big difference is the metrics for moving baselines are captured based on the entire AWR retention period.
For instance, the default AWR retention is eight days (see Recipe 4-2 on changing the AWR retention period). These metrics, also called adaptive thresholds, are captured based on the entire eight-day
window. Furthermore, the baseline changes with each passing day, as the AWR window for a given database moves day by day. Because of this, the metrics over a given period of time can change as a database evolves and performance loads change over time. A default moving baseline is automatically created—the SYSTEM_MOVING_BASELINE. It is recommended to increase the default AWR retention period, as this may give a more complete set of metrics on which to accurately analyze performance. The maximum size of the moving window is the AWR retention period. To modify the moving window baseline, use the MODIFY_BASELINE_WINDOW_SIZE procedure of the DBMS_WORKLOAD_REPOSITORY package, as in the following example:
SQL> exec dbms_workload_repository.modify_baseline_window_size(30);
PL/SQL procedure successfully completed.
How It Works
Setting the AWR retention period is probably the most important thing to configure when utilizing the moving baseline, as all adaptive threshold metrics are based on information from the entire retention period. When setting the retention period for the moving baseline, remember again that it cannot exceed the AWR retention period, else you may get the following error:
SQL> exec dbms_workload_repository.modify_baseline_window_size(45);
BEGIN dbms_workload_repository.modify_baseline_window_size(45); END;
*
ERROR at line 1:
ORA-13541: system moving window baseline size (3888000) greater than retention (2592000)
ORA-06512: at “SYS.DBMS_WORKLOAD_REPOSITORY”, line 686
ORA-06512: at line 1
If you set your AWR retention to an unlimited value, there still is an upper bound to the moving baseline retention period, and you could receive the following error if you set your moving baseline retention period too high, and your AWR retention period is set to unlimited:
exec dbms_workload_repository.modify_baseline_window_size(92);
BEGIN dbms_workload_repository.modify_baseline_window_size(92); END;
*
ERROR at line 1:
ORA-13539: invalid input for modify baseline window size (window_size, 92) ORA-06512: at “SYS.DBMS_WORKLOAD_REPOSITORY”, line 686
ORA-06512: at line 1
For fixed baselines, the AWR retention isn’t a factor, and is a consideration only based on how far back in time you want to compare a snapshot to your baseline. After you have set up any baselines, you can get information on baselines from the data dictionary. To get information on the baselines in your database, you can use a query such as the following one, which would show you any fixed baselines you have configured, as well as the automatically configured moving baseline:
SELECT baseline_name, start_snap_id start_id,
TO_CHAR(start_snap_time, ‘yyyy-mm-dd:hh24:mi’) start_time,
end_snap_id end_id, TO_CHAR(end_snap_time, ‘yyyy-mm-dd:hh24:mi’) end_time, expiration FROM dba_hist_baseline ORDER BY baseline_id;
BASELINE_NAME START_ID START_TIME END_ID END_TIME EXPIRATION
SYSTEM_MOVING_WINDOW 255 2011-05-27:22:00 358 2011-06-08:22:00
Batch Baseline #1 258 2011-05-28:13:39 268 2011-05-29:00:00 30
From the foregoing results, the moving baseline includes the entire range of snapshots based on the AWR retention period; therefore the expiration is shown as NULL. You can get similar information by using the SELECT_BASELINE_DETAILS function of the DBMS_WORKLOAD_REPOSITORY package. You do need the baseline_id number to pass into the function to get the desired results:
SELECT start_snap_id, start_snap_time, end_snap_id, end_snap_time, pct_total_time pct FROM (SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.select_baseline_details(12)));
START_SNAP_ID START_SNAP_TIME END_SNAP_ID END_SNAP_TIME PCT
258 28-MAY-11 01.39.19.296 PM 268 29-MAY-11 12.00.45.211 AM 100
To get more specific information on the moving baseline in the database, you are drilling down into the statistics for the adaptive metrics. For instance, to see an average and maximum for each metric related to reads based on the moving window, you could use the following query:
column metric_name format a50
SELECT metric_name, average, maximum FROM (SELECT * FROM TABLE (DBMS_WORKLOAD_REPOSITORY.select_baseline_metric(‘SYSTEM_MOVING_WINDOW’))) where lower(metric_name) like ‘%read%’ order by metric_name;
METRIC_NAME AVERAGE MAXIMUM
Average Synchronous Single-Block Read Latency .159658155 53.8876404
Consistent Read Changes Per Sec 2.99232446 3984.11246
Consistent Read Changes Per Txn 117.812978 239485
Consistent Read Gets Per Sec 202.570936 64677.436
Consistent Read Gets Per Txn 3930.41373 372602.889
Logical Reads Per Sec 224.984307 64690.6884
Logical Reads Per Txn 4512.34119 840030
Logical Reads Per User Call 276.745756 135804
Physical Read Bytes Per Sec 1249601.48 528672777
Physical Read IO Requests Per Sec 6.44664078 2040.73828
Physical Read Total Bytes Per Sec 1272159.18 528699475
Physical Read Total IO Requests Per Sec 7.82238122 2042.31792
Physical Reads Direct Lobs Per Sec .006030572 4.6953047
Physical Reads Direct Lobs Per Txn .231642268 141
Physical Reads Direct Per Sec 59.3280451 64535.1513
Physical Reads Direct Per Txn 602.336945 371825.222
Physical Reads Per Sec 152.539244 64535.2511
Physical Reads Per Txn 2966.04803 371831.889
Excellent explanation
ReplyDelete