Shulou(Shulou.com)05/31 Report--

This article mainly explains "what are the knowledge points related to Baseline". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Next, let the editor take you to learn "what are the knowledge points related to Baseline"?

In Oracle Database 11g, AWR baselines provide powerful capabilities for defining dynamic and future baselines and greatly simplify the process of creating and managing performance data for comparison.

Oracle Database 11g has a system-defined Moving Window Baseline by default, and this baseline corresponds to all AWR data in the AWR retention period. Only one Moving Window Baseline can exist. The system-defined Moving Window Baseline size is the current AWR retention period, that is, the default is eight days.

If you want to increase the Moving Window Baseline, you first need to increase the AWR retention period accordingly. The AWR retention period and the size of the system-defined Moving Window Baseline are two independent parameters. However, the AWR retention period must be greater than or equal to the system-defined Moving Window Baseline size.

Oracle Database 11g provides the ability to collect two baselines: static baselines and Moving Window Baseline. Static baselines can be single or repetitive. A single AWR baseline is collected in a single period of time. Repeat baselines are collected during repetitive periods (for example, every Monday in June).

In Oracle Database 11g, if STATISTICS_LEVEL=TYPICAL or ALL, baselines are enabled by default.

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.

Single AWR baseline

CREATE_BASELINE

Syntax:

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); 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) RETURN NUMBER 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); 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); RETURN NUMBER

Example:

Create a baseline called TEST39 that starts 10:00:00 on 2013-05-25 and ends 11:00:00 on 2013-05-29 and never expires

BEGIN DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE (start_time = > '2013-05-25 10 end_time, baseline_name = >' 2013-05-29 11), baseline_name = > 'TEST39', expiration = > NULL); END

See which baseline has been created

SQL > set line 1000SQL > col TEMPLATE_NAME for a20SQL > col BASELINE_NAME for a20SQL > select DBID,BASELINE_ID,BASELINE_NAME,BASELINE_TYPE,EXPIRATION,TEMPLATE_NAME from dba_hist_baseline

-

Delete baseline

First determine the existing baseline name, such as TEST3

BEGIN DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE (baseline_name = > 'TEST3', cascade = > FALSE, dbid = > null); END;/

Select FALSE for the parameter cascade. If you select TRUE, snapshots related to the baseline will be deleted.

-

Rename the baseline

View existing baselines and rename them

BEGIN DBMS_WORKLOAD_REPOSITORY.RENAME_BASELINE (old_baseline_name = > 'TEST3', new_baseline_name = >' NTEST3',); END;/

=

Baseline template

CREATE_BASELINE_TEMPLATE

Syntax:

Specifies a template for generating a baseline for a single time period in the future.DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE (start_time IN DATE, end_time IN DATE, baseline_name IN VARCHAR2, template_name IN VARCHAR2, expiration IN NUMBER, dbid IN NUMBER DEFAULT NULL) Specifies a template for creating and dropping baseline based on repeating time periods:DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE (day_of_week IN VARCHAR2, hour_in_day IN NUMBER, duration IN NUMBER, start_time IN DATE, end_time IN DATE, baseline_name_prefix IN VARCHAR2, template_name IN VARCHAR2, expiration IN NUMBER Dbid IN NUMBER DEFAULT NULL)

Example:

Single baseline template

Query the current time first:

SQL > select sysdate from dual;SYSDATE-2013-05-29 15:47:13

BASELINE_TEMPLATE requires any part to contain time that has not occurred before it can be used.

To this end, let's prove it in particular:

SQL > BEGIN 2 DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE (3 start_time = > '2013-05-25 10 NULL, 4 end_time = >' 2013-05-29 15 start_time, 5 baseline_name = > 'FALL29', 6 template_name = >' FALL29', 7 expiration = > NULL); 8 END 9 / BEGIN* Line 1 error: ORA-13537: invalid input for creating baseline template (end_time, end_time is less than SYSDATE) ORA-06512: in "SYS.DBMS_WORKLOAD_REPOSITORY", line 768ORA-06512: in "SYS.DBMS_WORKLOAD_REPOSITORY", line 818ORA-06512: in line 2

Sure enough, verify that CREATE_BASELINE must be used at some point in the past, while the CREATE_BASELINE_TEMPLATE process must be used for any period of time in the future.

SQL > BEGIN 2 DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE (3 start_time = > '2013-05-25 10 NULL, 4 end_time = >' 2013-05-29 16 SQL, 5 baseline_name = > 'FALL29', 6 template_name = >' FALL29', 7 expiration = > NULL), 8 END; 9 / PL/SQL process has been completed successfully.

Repeat baseline template

Create a baseline template named template_2013_mondays: every Monday (day_of_week) at 8 a.m. (hour_in_day) lasts 12 hours (duration) and will be retained for 30 days (expiration), starting at 10:00:00 on 2013-05-25 and ending at 17:00:00 on 2013-05-30.

SQL > BEGIN 2 DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE (3 day_of_week = > 'monday', hour_in_day = > 8, 4 duration = > 12, expiration = > 30, 5 start_time = >' 2013-05-25 10 DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE 00L, 6 end_time = > '2013-05-30 17 DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE 0000' 7 baseline_name_prefix = > 'baseline_2013_mondays_', 8 template_name = >' template_2013_mondays', 9 dbid = > NULL) 10 END; 11 / PL/SQL process completed successfully.

Query what baseline templates are available

SQL > col REPEAT_INTERVAL for a60SQL > 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_INTERVAL -- 2197530720 2 FALL08 SINGLE2197530720 3 FALL09 SINGLE2197530720 4 FALL19 SINGLE2197530720 5 FALL29 SINGLE2197530720 6 template_2013_monday REPEATING 12 30 FREQ=WEEKLY INTERVAL=1;BYDAY=MON;BYHOUR=8;BYMINUTE=0;BYSECON s dating 0

-

Delete baseline template

Query existing baseline templates to determine which baseline names need to be deleted, such as template_2013_monday

BEGIN DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE_TEMPLATE (template_name = > 'template_2013_mondays', dbid = > null); END;/ modifies the default Moving Window Baselin size

The default configuration of AWR in 11gR2 is that the retention period is 8 days and snapshots are generated every hour.

SQL > col SNAP_INTERVAL for a20SQL > col RETENTION for a20SQL > select * from dba_hist_wr_control; DBID SNAP_INTERVAL RETENTION TOPNSQL--2197530720 + 00000 01col RETENTION for a20SQL 00.0 + 00008 00lv 00.0 DEFAULT

Now want to change the AWR configuration to meet the needs of the system

SQL > BEGIN 2 DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS (retention = > 7200, 3 interval = > 30, topnsql = > 100); 4 error in line 5 / BEGIN*: ORA-13541: system mobile window baseline size (691200) greater than retention time (345600) ORA-06512: in "SYS.DBMS_WORKLOAD_REPOSITORY", line 174ORA-06512: in "SYS.DBMS_WORKLOAD_REPOSITORY", line 222ORA-06512: in line 2

An ORA-13541: system moving window baseline size (691200) greater than retention (518400) error occurred, and according to the error prompt, the system Mobile window baseline (Moving Window Baseline) is 691200, that is, 8 days. That is, to modify the value of the AWR retention period, which is related to this value, it must be greater than this value.

View the current Moving Window Baseline value

SQL > col BASELINE_NAME for a30SQL > select dbid, baseline_name, baseline_type, moving_window_size from dba_hist_baseline DBID BASELINE_NAME BASELINE_TYPE MOVING_WINDOW_SIZE--2197530720 SYSTEM_MOVING_WINDOW MOVING_WINDOW 8

Now that we know why, it's convenient to solve it, Modifying the Window Size of the Default Moving Window Baseline.

BEGIN DBMS_WORKLOAD_REPOSITORY.MODIFY_BASELINE_WINDOW_SIZE (window_size = > 5, dbid = > 3310949047); END;/

Note that window size must be set to a retention value that is less than or equal to the AWR setting.

In this example, the optional dbid parameter specifies the database identifier is 3310949047. If you do not specify a value for dbid, then the local database identifier is used as the default value.

Check the value of the current Moving Window Baseline to see if it is modified as you wish

SQL > select dbid, baseline_name, baseline_type, moving_window_size from dba_hist_baseline DBID BASELINE_NAME BASELINE_TYPE MOVING_WINDOW_SIZE--2197530720 SYSTEM_MOVING_WINDOW MOVING_WINDOW 5

Continue with previous changes

SQL > BEGIN 2 DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS (retention = > 7200, 3 interval = > 30, topnsql = > 100), 4 END; 5 / PL/SQL process has been completed successfully.

View the current AWR configuration after modification

SQL > select * from dba_hist_wr_control; DBID SNAP_INTERVAL RETENTION TOPNSQL--2197530720 + 00000 00VOR 30mm 00.0 + 00005 00VOR 00RO 00.0 100

By now the problem has been resolved.

At this point, I believe you have a deeper understanding of "what are the relevant knowledge points of Baseline?" you might as well come to the actual operation! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.