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

This article will explain in detail how to create a baseline for oracle 11g. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

There are generally two ways to evaluate performance optimization: if you want to know that the performance metric indicates that the server is close to the capacity limit, you should set the absolute value But if you want to know the difference between today's performance and that at the same time last week (or last month), the current performance must be compared with the baseline, which is a set of snapshots generated over a certain period of time. These snapshots are grouped statistically in order to obtain a set of baseline values that vary over time, which can be used as a baseline with snapshot samples for any period of time. It's just that in general, most of us will choose the snapshot of the normal time of the system as the baseline.

The baseline appears in oracle 10g, while in oracle database 11g the baseline of the automatic workload data repository is further enhanced:

1 ready-to-use mobile window baseline through which adaptive thresholds can be specified

2 use the baseline template to schedule the creation of the baseline

3 rename the baseline

4 set the expiration date of the baseline

AWR 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:

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

Moving window baseline: represents all war data that exists during the war retention period

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

Create a fixed baseline:

SQL > begin

2 dbms_workload_repository.create_baseline (

3 start_snap_id= > 1510

4 end_snap_id= > 1511

5 baseline_name= > 'test_baseline')

6 end

7 /

PL/SQL procedure successfully completed.

SQL > select dbid,baseline_name,start_snap_id,end_snap_id,MOVING_WINDOW_SIZE,EXPIRATION from dba_hist_baseline

DBID BASELINE_NAME START_SNAP_ID END_SNAP_ID MOVING_WINDOW_SIZE EXPIRATION

-

4257181262 test_baseline 1510 1511

4257181262 SYSTEM_MOVING_WINDOW 1515 1591 8

Create procedure and function for baseline

Created according to snap_id:

PROCEDURE CREATE_BASELINE

Argument Name Type In/Out Default?

START_SNAP_ID NUMBER IN

END_SNAP_ID NUMBER IN

BASELINE_NAME VARCHAR2 IN

DBID NUMBER IN DEFAULT

EXPIRATION NUMBER IN DEFAULT

FUNCTION CREATE_BASELINE RETURNS NUMBER

Argument Name Type In/Out Default?

START_SNAP_ID NUMBER IN

END_SNAP_ID NUMBER IN

BASELINE_NAME VARCHAR2 IN

DBID NUMBER IN DEFAULT

EXPIRATION NUMBER IN DEFAULT

Created based on time:

PROCEDURE CREATE_BASELINE

Argument Name Type In/Out Default?

START_TIME DATE IN

END_TIME DATE IN

BASELINE_NAME VARCHAR2 IN

DBID NUMBER IN DEFAULT

EXPIRATION NUMBER IN DEFAULT

FUNCTION CREATE_BASELINE RETURNS NUMBER

Argument Name Type In/Out Default?

START_TIME DATE IN

END_TIME DATE IN

BASELINE_NAME VARCHAR2 IN

DBID NUMBER IN DEFAULT

EXPIRATION NUMBER IN DEFAULT

You can also delete and rename baseline:

SQL > begin

2 dbms_workload_repository.drop_baseline (

3 baseline_name= > 'test_baseline'

4 cascade= > true)

5 end

6 /

PL/SQL procedure successfully completed.

In oracle 11g, the system has created moving window baseline by default:

SQL > select dbid,baseline_name,start_snap_id,end_snap_id,MOVING_WINDOW_SIZE,EXPIRATION from dba_hist_baseline

DBID BASELINE_NAME START_SNAP_ID END_SNAP_ID MOVING_WINDOW_SIZE EXPIRATION

-

4257181262 SYSTEM_MOVING_WINDOW 1515 1591 8

If cascade= > true is specified in drop baseline, the snap corresponding to baseline will be cascaded and deleted.

Oracle database automatically maintains system-defined moving window baseline, and the default window size for system-defined moving window baseline is the current AWR retention period. If you plan to use adaptive thresholds, consider using a longer moving window, such as 30 days, to accurately calculate the threshold. The mobile window is resized to be less than or equal to the number of AWR retention days, because to increase the size of the mobile window, you must first increase the corresponding AWR retention period.

Adjust the retention cycle of awr:

SQL > exec dbms_workload_repository.modify_snapshot_settings (retention= > 43200)

PL/SQL procedure successfully completed.

SQL > select retention from dba_hist_wr_control

RETENTION

+ 00030 0000 purl 00.0

SQL > select baseline_id,baseline_name,moving_window_size from dba_hist_baseline

BASELINE_ID BASELINE_NAME MOVING_WINDOW_SIZE

-

0 SYSTEM_MOVING_WINDOW 8

SQL > exec dbms_workload_repository.modify_baseline_window_size (window_size= > 30)

PL/SQL procedure successfully completed.

SQL > select baseline_id,baseline_name,moving_window_size from dba_hist_baseline

BASELINE_ID BASELINE_NAME MOVING_WINDOW_SIZE

-

0 SYSTEM_MOVING_WINDOW 30

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

Single baseline template:

PROCEDURE CREATE_BASELINE_TEMPLATE

Argument Name Type In/Out Default?

START_TIME DATE IN

END_TIME DATE IN

BASELINE_NAME VARCHAR2 IN

TEMPLATE_NAME VARCHAR2 IN

EXPIRATION NUMBER IN DEFAULT

DBID NUMBER IN DEFAULT

SQL > exec DBMS_WORKLOAD_REPOSITORY.create_baseline_template (start_time= > TO_DATE ('09-MAR-2016 05VON0000,' DD-MON-YYYY HH24:MI'), end_time= > TO_DATE ('16-MAR-2016 05VOOOOOOO,' DD-MON-YYYY HH24:MI'), baseline_name= > '091140911)

PL/SQL procedure successfully completed.

SQL > select dbid,template_name,template_type,baseline_name_prefix,start_time,end_time,duration,expiration from dba_hist_baseline_template

DBID TEMPLATE_NAME TEMPLATE_ BASELINE_NAME_PREFIX START_TIME END_TIME DURATION EXPIRATION

4257181262 09_11_14_TP1 SINGLE 09_11_14_BS1 2016-03-09 00:00:00 2016-03-16 05:00:00 10

Repeating the baseline template is slightly different because it requires scheduling information, the start_time and end_time parameters are defined when the template is activated and released, and day_of_week, hour_in_day, and duration define the date, time, and duration of the baseline, because the template produces multiple baselines, and the baseline name starts with baseline_name_prefix.

Repeat baseline template:

PROCEDURE CREATE_BASELINE_TEMPLATE

Argument Name Type In/Out Default?

DAY_OF_WEEK VARCHAR2 IN

HOUR_IN_DAY NUMBER IN

DURATION NUMBER IN

START_TIME DATE IN

END_TIME DATE IN

BASELINE_NAME_PREFIX VARCHAR2 IN

TEMPLATE_NAME VARCHAR2 IN

EXPIRATION NUMBER IN DEFAULT

DBID NUMBER IN DEFAULT

Exec DBMS_WORKLOAD_REPOSITORY.create_baseline_template (day_of_week= > 'MONDAY',hour_in_day= > 0DurationDurance = > 5Jing startkeeper time= > SYSDATE,end_time= > ADD_MONTHS (SYSDATE, 6), baseline_name_prefix = >' monday_morning_bl',template_name= > 'monday_morning_tp',expiration= > NULL)

SQL > select dbid,TEMPLATE_NAME,TEMPLATE_TYPE,BASELINE_NAME_PREFIX,START_TIME,END_TIME,DAY_OF_WEEK,HOUR_IN_DAY,DURATION,EXPIRATION,REPEAT_INTERVAL from dba_hist_baseline_template

DBID TEMPLATE_NAME TEMPLATE_ BASELINE_NAME_PREFIX START_TIME END_TIME DAY_OF_WE HOUR_IN_DAY DURATION EXPIRATION

REPEAT_INTERVAL

4257181262 09_11_14_TP1 SINGLE 09_11_14_BS1 2016-03-09 00:00:00 2016-03-16 05:00:00 10

4257181262 monday_morning_tp REPEATING monday_morning_bl 2016-03-15 02:54:55 2016-09-15 02:54:55 MONDAY 0 5

FREQ=WEEKLY;INTERVAL=1;BYDAY=MON;BYHOUR=0;BYMINUTE=0;BYSECOND=0

The baseline is described above, so how to use the baseline to compare awr data with snapshots for a specified period of time. In fact, Oracle provides us with AWR Compare Period Report to facilitate the comparison of awr data for a specified period of time.

Since the baseline can be kept in the awr snapshot all the time, DBA can use the awr of the baseline and the fault period for performance analysis to further analyze the performance trend of the database. Here is a simple comparison of AWR data periods:

SQL > select dbid,min (snap_id), max (snap_id) from dba_hist_snapshot group by dbid

DBID MIN (SNAP_ID) MAX (SNAP_ID)

4257181262 1508 1669

Create a baseline for snap_id 1656 to 1657 here

SQL > begin

2 dbms_workload_repository.create_baseline (

3 start_snap_id= > 1656

4 end_snap_id= > 1657

5 baseline_name= > 'test_baseline')

6 end

7 /

PL/SQL procedure successfully completed.

Delete most snapshot

SQL > exec dbms_workload_repository.drop_snapshot_range (1508, 1667, 4257181262)

PL/SQL procedure successfully completed.

SQL > select snap_id,begin_interval_time,end_interval_time from dba_hist_snapshot where dbid=4257181262

SNAP_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME

-

1669 15-MAR-16 10.23.42.387 PM 15-MAR-16 11.10.41.380 PM

1656 11-MAR-16 10.00.33.158 PM 14-MAR-16 08.40.27.499 PM

1657 14-MAR-16 08.40.27.499 PM 14-MAR-16 10.00.35.439 PM

1668 15-MAR-16 08.41.44.009 PM 15-MAR-16 10.23.42.387 PM

It is also verified that the corresponding snapshot of the previous awr baseline will not be deleted by the awr retention policy or manually deleting the awr.

Next, use the awrddrpt.sql script to generate comparison data for the two awr:

SQL > @ awrddrpt.sql

Current Instance

~

DB Id DB Id DB Name Inst Num Inst Num Instance

--

4257181262 4257181262 ORA11G 11 ora11g

Specify the Report Type

~ ~ ~

Would you like an HTML report, or a plain text report?

Enter 'html' for an HTML report, or' text' for plain text

Defaults to 'html'

Enter value for report_type: html

Type Specified: html

Instances in this Workload Repository schema

~

DB Id Inst Num DB Name Instance Host

--

2350429211 1 TLINK tlink ylqz_s

* 4257181262 1 ORA11G ora11g redhat-ora

Database Id and Instance Number for the First Pair of Snapshots

~ ~ ~

Using 4257181262 for Database Id for the first pair of snapshots

Using 1 for Instance Number for the first pair of snapshots

Specify the number of days of snapshots to choose from

~ ~ ~

Entering the number of days (n) will result in the most recent

(n) days of snapshots being listed. Pressing

Without

Specifying a number lists all completed snapshots.

Enter value for num_days: 3

Listing the last 3 days of Completed Snapshots

Snap

Instance DB Name Snap Id Snap Started Level

--

Ora11g ORA11G 1656 14 Mar 2016 20:40 1

1657 14 Mar 2016 22:00 1

1668 15 Mar 2016 22:23 1

1669 15 Mar 2016 23:10 1

Specify the First Pair of Begin and End Snapshot Ids

~ ~ ~

Enter value for begin_snap: 1656

First Begin Snapshot Id specified: 1656

Enter value for end_snap: 1657

First End Snapshot Id specified: 1657

Instances in this Workload Repository schema

~

DB Id Inst Num DB Name Instance Host

--

2350429211 1 TLINK tlink ylqz_s

* 4257181262 1 ORA11G ora11g redhat-ora

Database Id and Instance Number for the Second Pair of Snapshots

~ ~ ~

Using 4257181262 for Database Id for the second pair of snapshots

Using 1 for Instance Number for the second pair of snapshots

Specify the number of days of snapshots to choose from

~ ~ ~

Entering the number of days (n) will result in the most recent

(n) days of snapshots being listed. Pressing

Without

Specifying a number lists all completed snapshots.

Enter value for num_days2: 3

Listing the last 3 days of Completed Snapshots

Snap

Instance DB Name Snap Id Snap Started Level

--

Ora11g ORA11G 1656 14 Mar 2016 20:40 1

1657 14 Mar 2016 22:00 1

1668 15 Mar 2016 22:23 1

1669 15 Mar 2016 23:10 1

Specify the Second Pair of Begin and End Snapshot Ids

~ ~ ~

Enter value for begin_snap2: 1668

Second Begin Snapshot Id specified: 1668

Enter value for end_snap2: 1669

Second End Snapshot Id specified: 1669

Specify the Report Name

~ ~ ~

The default report file name is awrdiff_1_1656_1_1668.html To use this name

Press

To continue, otherwise enter an alternative.

Enter value for report_name: / home/oracle/awr0316.hmtl

This is the end of the article on "how to create a baseline for oracle 11g". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

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.