Shulou(Shulou.com)06/01 Report--

Optimize it through case study-- AWR BaseLine Management

BaseLine

Baseline refers to performance data for a specific period of time, which is retained for comparison with other similar workload periods when performance problems arise. Snapshots included in Baseline are excluded from the automatic AWR cleanup process and are retained indefinitely.

There are several types of baseline in Oracle Database:

Fixed Baseline:fixed baseline represents a fixed, continuous period of time that you specify. Before creating a fixed baseline, carefully consider the time period you selected as the baseline, because the baseline should represent that the system is running at good performance. You can compare this baseline with other baseline or snapshot captured during periods of poor performance in the future.

Moving Window Baseline: represents all AWR data that exists during the AWR retention period. It is useful when using adaptive thresholds because the database can use AWR data for the entire AWR retention period to calculate indicator values.

Oracle Database automatically maintains the system-defined moving window baseline. The default window size of the system-defined moving window baseline is the current AWR retention period, which defaults to 8 days. If you plan to use adaptive thresholds, consider using a longer movement window, such as 30 days, to accurately calculate the threshold. You can resize the moving window baseline to resize the moving window to be less than or equal to the number of days that the AWR is retained. Therefore, to increase the size of the mobile window, you must first increase the corresponding AWR retention period.

Baseline Template: you can use baseline template to create a baseline for a continuous period of time in the future. There are two baseline templates in Oracle: single and repeating

With single baseline template, you can create a baseline for a separate continuous period of time in the future. This technique is very useful in some cases. For example, if you want to capture AWR data during a system test scheduled for next week, you can create a single baseline template that automatically captures statistics for the period during which the test took place.

With repeating baseline template, you can create and delete baseline based on a repetitive schedule. This is useful when you want Oracle Database to capture statistics for consecutive periods automatically and continuously. For example, you might need to capture AWR data every Monday morning for up to a month. In this case, you can create a repeating baseline template to automatically create a baseline every Monday and automatically delete obsolete baseline within the specified expiration period.

Case study:

1. Create a baseline

Fixed Baseline:fixed baseline represents a fixed, continuous period of time that you specify.

Create according to snap_id: DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE (start_snap_id IN NUMBER, end_snap_id IN NUMBER, baseline_name IN VARCHAR2, dbid IN NUMBER DEFAULT NULL, expiration IN NUMBER DEFAULT NULL) Create by time: DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE (start_time IN DATE, end_time IN DATE, baseline_name IN VARCHAR2, dbid IN NUMBER DEFAULT NULL, expiration IN NUMBER DEFAULT NULL); view snapshot:14:25:31 SYS@ test1 > select snap_id,BEGIN_INTERVAL_TIME,dbid from dba_hist_snapshot SNAP_ID BEGIN_INTERVAL_TIME DBID 435 04-NOV-14 11.00.52.880 AM 1195893416 436 04-NOV-14 12.00.07.338 PM 1195893416 437 04-NOV-14 01.00.22.331 PM 1195893416 432 04-NOV-14 12.00.04.575 AM 1195893416 434 04-NOV-14 09.52.45.512 AM 1195893416 428 31-OCT -14 02.58.47.186 PM 1195893416 429 31-OCT-14 04.00.51.633 PM 1195893416 430 03-NOV-14 10.19.24.000 AM 1195893416 433 04-NOV-14 09.41.40.000 AM 1195893416 426 31-OCT-14 11.25.58.000 AM 1195893416 427 31-OCT-14 02.47.40.000 PM 1195893416 431 03-NOV-14 10.30.31.348 AM 119589341612 rows selected. Create Baseline:14:27:46 SYS@ test1 > exec DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE (start_snap_id= > 435 snapshot snapshots = > 436 snapshot sequence numbers), and PL/SQL procedure successfully completed.--435 is the starting snapshot sequence number and 436 is the end snapshot sequence number. Expiration = > 30 means that the baseline will be deleted automatically after 30 days, and expiration = > null means that it will never expire-when a baseline is created, the system will automatically assign a unique baseline ID to the newly created baseline. You can view it through the DBA_HIST_BASELINE view. Check out Baseline:14:31:10 SYS@ test1 > col baseline_name for a4014 SYS@ test1 31 SYS@ test1 > select dbid,baseline_id,baseline_name,EXPIRATION CREATION_TIME from dba_hist_baseline DBID BASELINE_ID BASELINE_NAME EXPIRATION CREATION_TIME -1195893416 1 work_bs1 30 2014-11-04 14 Baseline:14:31:30 SYS@ test1 29 Baseline:14:31:30 SYS@ test1 081195893416 0 SYSTEM_MOVING_WINDOW 2013-06-23 12:43:59 rename Baseline:14:31:30 SYS@ test1 > exec DBMS_WORKLOAD_REPOSITORY.RENAME_BASELINE (old_baseline_name = > 'work_bs1' New_baseline_name = > 'work_bl1', dbid = > 1195893416) PL/SQL procedure successfully completed.14:35:46 SYS@ test1 > select dbid,baseline_id,baseline_name,EXPIRATION,CREATION_TIME from dba_hist_baseline DBID BASELINE_ID BASELINE_NAME EXPIRATION CREATION_TIME -1195893416 1 work_bl1 30 2014-11-04 14 Baseline:14:35:54 SYS@ test1 29 Baseline:14:35:54 SYS@ test1 081195893416 0 SYSTEM_MOVING_WINDOW 2013-06-23 12:43:59 Delete Baseline:14:35:54 SYS@ test1 > exec DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE (baseline_name = > 'work_bl1' Cascade = > FALSE, dbid = > 1195893416) PL/SQL procedure successfully completed.14:38:24 SYS@ test1 > select dbid,baseline_id,baseline_name,EXPIRATION,CREATION_TIME from dba_hist_baseline DBID BASELINE_ID BASELINE_NAME EXPIRATION CREATION_TIME -1195893416 0 SYSTEM_MOVING_WINDOW 2013-06-23 12:43:59--cascade parameter is set to FALSE Specifies to delete only. Set this parameter to TRUE to specify that all snapshots associated with the baseline be deleted. Move window (Moving Window) baseline

Oracle 11g introduces the concept of mobile window (Moving Window) baseline, which is used to calculate the threshold measurement. Window is a view of AWR data within the retention period. The window size matches the default retention period of AWR for 8 days, but it can be set to a subset of this value. Before increasing the window size, you should first increase the size of the AWR retention period.

Query the RETENTION column of the DBA_HOST_WR_CONTROL view to return the current AWR retention period.

14:45:42 SYS@ test1 > SELECT retention FROM dba_hist_wr_control;RETENTION----+00008 0012 00.0

Use the stored procedure MODIFY_SNAPSHOT_SETTINGS to modify the retention period and specify the RETENTION parameter in minutes.

14:48:12 SYS@ test1 > exec DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings (retention= > 43200);-- Minutes (= 30 Days) PL/SQL procedure successfully completed.14:48:33 SYS@ test1 > SELECT retention FROM dba_hist_wr_control RETENTION----+00030 00:00:00.0

Query the DBA_HIST_BASELINE view to return the current mobile window size.

14:48:44 SYS@ test1 > SELECT moving_window_size14:50:30 2 FROM dba_hist_baseline14:50:30 3 WHERE baseline_type = 'MOVING_WINDOW';MOVING_WINDOW_SIZE- 8

Use the stored procedure MODIFY_BASELINE_WINDOW_SIZE to modify the mobile window baseline, which accepts the WINDOW_SIZE parameter in days.

14:50:45 SYS@ test1 > exec DBMS_WORKLOAD_REPOSITORY.modify_baseline_window_size (window_size = > 30); PL/SQL procedure successfully completed.14:53:25 SYS@ test1 > SELECT moving_window_size FROM dba_hist_baseline WHERE baseline_type = 'MOVING_WINDOW';MOVING_WINDOW_SIZE- 30Oracle recommends that when using the appropriate threshold, the window size should be greater than or equal to 30 days. (should be less than or equal to the retention of snapshot)

Baseline template

Baseline templates allow you to define baselines that may be captured in the future, CREATE_BASELINE_TEMPLATE stored procedures define a single baseline or repeated baseline capture, creating a single baseline template is similar to creating a time-based baseline, except for future time.

15:02:44 SYS@ test1 > exec DBMS_WORKLOAD_REPOSITORY.create_baseline_template (start_time= > TO_DATE ('09-NOV-2014 05VOG 0000mm,' DD-MON-YYYY HH24:MI'), end_time= > TO_DATE ('12-NOV-2014 05VOO 000mm,' DD-MON-YYYY HH24:MI'), baseline_name = > '09114091mm BS1 recording temptation name = > 0911409011014) PL/SQL procedure successfully completed.15:05:19 SYS@ test1 > col REPEAT_INTERVAL for a1015 SYS@ test1 05 SYS@ test1 > select DBID,TEMPLATE_ID,TEMPLATE_NAME,TEMPLATE_TYPE,DURATION,EXPIRATION REPEAT_INTERVAL from dba_hist_baseline_template DBID TEMPLATE_ID TEMPLATE_NAME TEMPLATE_ DURATION EXPIRATION REPEAT_INT -- 1195893416 1 09_11_14_TP1 SINGLE 10

The repeat baseline template is slightly different because it requires scheduling information, START_TIME and END_TIME parameters are defined when the template is activated and released, DAY_OF_WEEK,HOUR_IN_DAY and DURATION define the date, time and duration of the baseline, because the template produces multiple baselines, the baseline name starts with BASELINE_NAME_PREFIX, and the following example of the baseline template will be run in the next 6 months Baselines are collected from 00:00 to 05:00 every Monday.

15:05:35 SYS@ test1 > exec DBMS_WORKLOAD_REPOSITORY.create_baseline_template (day_of_week= > 'MONDAY',hour_in_day= > 0recoveryduration= > 5dint startkeeper time= > SYSDATE,end_time= > ADD_MONTHS (SYSDATE, 6), baseline_name_prefix = >' monday_morning_bl',template_name= > 'monday_morning_tp',expiration= > NULL); PL/SQL procedure successfully completed.15:09:20 SYS@ test1 > select DBID,TEMPLATE_ID,TEMPLATE_NAME,TEMPLATE_TYPE,DURATION,EXPIRATION,REPEAT_INTERVAL from dba_hist_baseline_template DBID TEMPLATE_ID TEMPLATE_NAME TEMPLATE_ DURATION EXPIRATION REPEAT_INT -- 1195893416 1 09_11_14_TP1 SINGLE 101195893416 2 monday_morning_tp REPEATING 5 FREQ=WEEKL Y INTERVAL = 1 * BYDAYEMM ON BYHOUR= 0 * * BYMINUTE = 0 BYSECON Duan 0 15:12:14 SYS@ test1 > col BASELINE_NAME_PREFIX for a201512 SYS@ test1 33 SYS@ test1 > r 1 SELECT template_name, 2 template_type, 3 baseline_name_prefix 4 start_time, 5 end_time, 6 day_of_week, 7 hour_in_day, 8 duration 9 expiration 10 * FROM dba_hist_baseline_templateTEMPLATE_NAME TEMPLATE_ BASELINE_NAME_PREFIX START_TIME END_TIME DAY_OF_WE HOUR_IN_DAY-- -DURATION EXPIRATION--09_11_14_TP1 SINGLE 09_11_14_BS1 2014-11-09 00:00:00 2014-11-12 05:00:00 10monday_morning_tp REPEATING monday_morning_bl 2014-11-04 15:09:20 2015-05-04 15:09:20 MONDAY 05 Note that the BASELINE_NAME_PREFIX column may retain the baseline prefix or the full baseline name It mainly depends on the type of capture baseline.

DROP_BASELINE_TEMPLATE stored procedure deletes the baseline template

15:17:46 SYS@ test1 > exec DBMS_WORKLOAD_REPOSITORY.drop_baseline_template (template_name = >'09 / 11 / 14 / TP1'); PL/SQL procedure successfully completed.15:18:26 SYS@ test1 > exec DBMS_WORKLOAD_REPOSITORY.drop_baseline_template (template_name = > 'monday_morning_tp'); PL/SQL procedure successfully completed.15:18:52 SYS@ test1 > select DBID,TEMPLATE_ID,TEMPLATE_NAME,TEMPLATE_TYPE,DURATION,EXPIRATION,REPEAT_INTERVAL from dba_hist_baseline_template;no rows selected

Baseline view

DBA_HIST_BASELINE: displays information about the baselines obtained in the system. For each baseline, the view shows the full time range and whether the baseline is the default. Other information includes the date of creation, the time of the last statistics calculation, and the baseline type.

DBA_HIST_BASELINE_DETAILS: displays information that can be used to determine the validity of a given baseline, such as whether there is a shutdown during the baseline period and the percentage covered by snapshot data during the baseline period.

DBA_HIST_BASELINE_TEMPLATE: the baseline template is saved. This view provides the information needed by MMON to determine when the baseline is created from the template and when the baseline should be deleted.

DBA_HIST_BASELINE_METADATA: displays metadata information for the baseline, including name, type, creation time, template, and expiration time.

If you want to create a baseline at some point in the past, use the CREATE_BASELINE process; if any part of the period is in the future, use the CREATE_BASELINE_TEMPLATE process.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.