19 | 10 | 2017
Latest Articles
Popular Articles

Performance Tuning

AWR Baselines

User Rating:  / 0
PoorBest 

AWR Baselines

 

AWR baselines were introduced in Oracle 10G with some additional functionalities and possibilities in 11G.

AWR baselines are created between 2 AWR snapshots. 

 

STATIC BASELINES ( FIXED BASELINES )

 

A fixed static baseline corresponds to a period between 2 snapshots / timestamps in the PAST

 

08:59:27 SILVER 27/10/2013 07:23 > column begin_interval_time format a30
08:59:50 SILVER 27/10/2013 07:23 > column end_interval_time format a30
08:59:56 SILVER 27/10/2013 07:23 > select snap_id,begin_interval_time,end_interval_time from dba_hist_snapshot order by 1 ;

15527 27-OCT-13 08.00.56.212 PM 27-OCT-13 09.00.09.402 PM
15528 27-OCT-13 09.00.09.402 PM 27-OCT-13 10.00.22.744 PM
15529 27-OCT-13 10.00.22.744 PM 27-OCT-13 11.00.36.273 PM
15530 27-OCT-13 11.00.36.273 PM 28-OCT-13 12.00.49.974 AM
15531 28-OCT-13 12.00.49.974 AM 28-OCT-13 01.00.02.569 AM
15532 28-OCT-13 01.00.02.569 AM 28-OCT-13 02.00.16.457 AM
15533 28-OCT-13 02.00.16.457 AM 28-OCT-13 03.00.29.456 AM
15534 28-OCT-13 03.00.29.456 AM 28-OCT-13 04.00.43.125 AM
15535 28-OCT-13 04.00.43.125 AM 28-OCT-13 05.00.56.559 AM
15536 28-OCT-13 05.00.56.559 AM 28-OCT-13 06.00.09.799 AM
15537 28-OCT-13 06.00.09.799 AM 28-OCT-13 07.00.23.297 AM
15538 28-OCT-13 07.00.23.297 AM 28-OCT-13 08.00.36.733 AM
15539 28-OCT-13 08.00.36.733 AM 28-OCT-13 09.00.49.279 AM
15540 28-OCT-13 09.00.49.279 AM 28-OCT-13 10.00.02.109 AM
15541 28-OCT-13 10.00.02.109 AM 28-OCT-13 11.00.14.655 AM
15542 28-OCT-13 11.00.14.655 AM 28-OCT-13 12.00.27.810 PM
15543 28-OCT-13 12.00.27.810 PM 28-OCT-13 01.00.40.481 PM
15544 28-OCT-13 01.00.40.481 PM 28-OCT-13 02.00.53.296 PM
15545 28-OCT-13 02.00.53.296 PM 28-OCT-13 03.00.32.154 PM
15546 28-OCT-13 03.00.32.154 PM 28-OCT-13 04.00.45.122 PM
15547 28-OCT-13 04.00.45.122 PM 28-OCT-13 05.00.58.027 PM
15548 28-OCT-13 05.00.58.027 PM 28-OCT-13 06.00.10.185 PM
15549 28-OCT-13 06.00.10.185 PM 28-OCT-13 07.00.23.667 PM
15550 28-OCT-13 07.00.23.667 PM 28-OCT-13 08.00.37.025 PM
15551 28-OCT-13 08.00.37.025 PM 28-OCT-13 09.00.50.648 PM
15552 28-OCT-13 09.00.50.648 PM 28-OCT-13 10.00.04.257 PM
15553 28-OCT-13 10.00.04.257 PM 28-OCT-13 11.00.19.416 PM
15554 28-OCT-13 11.00.19.416 PM 29-OCT-13 12.00.32.121 AM
15555 29-OCT-13 12.00.32.121 AM 29-OCT-13 01.00.45.541 AM
15556 29-OCT-13 01.00.45.541 AM 29-OCT-13 02.00.58.695 AM
15557 29-OCT-13 02.00.58.695 AM 29-OCT-13 03.00.12.195 AM
15558 29-OCT-13 03.00.12.195 AM 29-OCT-13 04.00.25.397 AM

 

Create a static baseline referencing some snapshot id' s.


09:00:47 SILVER 27/10/2013 07:23 > begin
09:01:37 2 dbms_workload_repository.create_baseline(
09:01:37 3 start_snap_id => 15537,
09:01:37 4 end_snap_id => 15543,
09:01:37 5 baseline_name => 'SILVER_AFTER_GO_LIVE',
09:01:37 6 expiration => null );
09:01:37 7 end;
09:01:37 8 /

PL/SQL procedure successfully completed.

 

Create a static baseline between 2 timestamps



10:17:55 SILVER 27/10/2013 07:23 > begin
10:17:55 2 dbms_workload_repository.create_baseline(
10:17:55 3 start_time => TO_DATE('29-OCT-13 02.00','DD-MON-YY HH24.Mi'),
10:17:55 4 end_time => TO_DATE('29-OCT-13 03.00','DD-MON-YY HH24.Mi'),
10:17:55 5 baseline_name => 'SILVER_AFTER_GO_LIVE_2',
10:17:55 6 expiration => 30 );
10:17:55 7 end;
10:17:55 8 /

PL/SQL procedure successfully completed.


10:17:56 SILVER 27/10/2013 07:23 > select instance_number,baseline_id,baseline_name,baseline_type from dba_hist_baseline_details;

INSTANCE_NUMBER  BASELINE_ID      BASELINE_NAME                  BASELINE_TYPE
---------------- ---------------- ------------------------------ -------------
1                1                SILVER_AFTER_GO_LIVE           STATIC
1                4                SILVER_AFTER_GO_LIVE_2         STATIC
1                0                SYSTEM_MOVING_WINDOW           MOVING_WINDOW

 

Drop a baseline

 

10:18:04 SILVER 27/10/2013 07:23 > begin
10:18:13 2 dbms_workload_repository.drop_baseline('SILVER_AFTER_GO_LIVE_2');
10:18:44 3 end;
10:18:47 4 /

PL/SQL procedure successfully completed.

10:07:11 SILVER 27/10/2013 07:23 > column baseline_name format a30
10:07:21 SILVER 27/10/2013 07:23 > select instance_number,baseline_id,baseline_name,baseline_type from dba_hist_baseline_details;

INSTANCE_NUMBER  BASELINE_ID      BASELINE_NAME                  BASELINE_TYPE
---------------- ---------------- ------------------------------ -------------
1                1                SILVER_AFTER_GO_LIVE           STATIC
1                0                SYSTEM_MOVING_WINDOW           MOVING_WINDOW

 

Gather baseline statistics ( this is often done using the EM GUI )

 

09:50:31 SILVER 27/10/2013 07:23 > begin
09:50:39 2 dbsnmp.bsln_internal.compute_statistics(1,1);
09:51:24 3 end;
09:51:25 4 /

PL/SQL procedure successfully completed.

 

Once we have a static baseline with statistics we can easily reference this in comparison reports using the EM GUI. For those who like the command line are the functions

 

1. dbms_workload_repository.awr_diff_report_html

2. dbms_workload_repository.awr_global_diff_report_html

 

 

 The System Moving Window Baseline

 

This baseline cannot be dropped and is new in 11G. In stead of being static the baselines MOVES IN THE TIME. It cannot span a "larger" window than the AWR retention period. Remember we can alter the default AWR retention of 8 days in Oracle 11 to "enlarge" the system moving window.

With the below example I "enlarge" the AWR retention to 30 days. 

 

12:12:44 SILVER 29/10/2013 10:09 > select retention from dba_hist_wr_control;

RETENTION
---------------------------------------------------------------------------
+00008 00:00:00.0

12:12:58 SILVER 29/10/2013 10:09 > begin
12:13:01 2 DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings( retention => 30*1440);
12:13:16 3 end;
12:13:17 4 /

PL/SQL procedure successfully completed.

12:13:17 SILVER 29/10/2013 10:09 > select retention from dba_hist_wr_control;

RETENTION
---------------------------------------------------------------------------
+00030 00:00:00.0

 

With the below example I "enlarge" the system moving window size to 20 days. 

 

12:13:29 SILVER 29/10/2013 10:09 > select moving_window_size from dba_hist_baseline where baseline_type = 'MOVING_WINDOW';

MOVING_WINDOW_SIZE
------------------
8

 

12:20:46 SILVER 29/10/2013 10:09 > begin dbms_workload_repository.modifyify_baseline_window_size( window_size => 20 ); END;
/

PL/SQL procedure successfully completed.

 

12:21:11 SILVER 29/10/2013 10:09 > select moving_window_size from dba_hist_baseline where baseline_type = 'MOVING_WINDOW';

MOVING_WINDOW_SIZE

------------------
20

 

Note that one cannot compute baseline statistics for the system moving window baseline

 

12:27:22 SILVER 29/10/2013 10:09 > 12:27:22 SILVER 29/10/2013 10:09 > begin
12:27:47 2 dbsnmp.bsln_internal.compute_statistics(1,0);
12:27:59 3 end;
12:28:00 4 /
begin
*
ERROR at line 1:
ORA-20101: baseline (id:1) is not a STATIC baseline for instance 0
ORA-06512: at "DBSNMP.BSLN_INTERNAL", line 979
ORA-06512: at line 2

 

 Baseline Templates

 

A baseline template is a template for a baselines yet to be created IN THE FUTURE

 

We can differrentiate

 

1. A single baseline template for one period in the future ( example next Saturday between 8 AM CET and 5 PM CET )

2. A repeating baseline template for multiple periods in the future ( example every Sunday between November and February ) 

 

Single Baseline template

 

14:10:43 SILVER 29/10/2013 10:09 > begin
14:10:47 2 dbms_workload_repository.create_baseline_template( START_TIME    => TO_DATE('02-NOV-13 08.00','DD-MON-YY HH24.Mi'),
14:17:10 3                                                    END_TIME      => TO_DATE('02-NOV-13 17.00','DD-MON-YY HH24.Mi'),
14:17:44 4                                                    BASELINE_NAME => 'BASELINE_LOAD_DATAWAREHOUSE',
14:18:34 5                                                    TEMPLATE_NAME => 'TEMPLATE_LOAD_DATAWAREHOUSE');
14:18:58 6 end;
14:19:01 7 /

PL/SQL procedure successfully completed.

 

Repeating Baseline template

 

With the below exampe we have every Monday a baseline created starting at 9 AM, which lasts 8 hours, start time is November 4, end time is March 4 and we keep these baselines for a period of 60 days, baselines created with this template older than 60 days will automatically be purged

 

14:33:22 SILVER 29/10/2013 10:09 > begin
14:34:03 2 dbms_workload_repository.create_baseline_template( DAY_OF_WEEK          => 'MONDAY',
14:34:12 3                                                    HOUR_IN_DAY          => 9,
14:34:20 4                                                    DURATION             => 8,
14:34:29 5                                                    START_TIME           => TO_DATE('04-NOV-13','DD-MON-YY'),
14:34:43 6                                                    END_TIME             => TO_DATE('04-MAR-14','DD-MON-YY'),
14:34:57 7                                                    BASELINE_NAME_PREFIX => 'BASELINE_MONDAY_',
14:35:07 8                                                    TEMPLATE_NAME        => 'TEMPLATE_MONDAY',
14:35:18 9                                                    EXPIRATION           =>  60);
14:35:26 10 end;
14:35:29 11 /

PL/SQL procedure successfully completed.

 

14:43:55 SILVER 29/10/2013 10:09 > select template_name,template_type,start_time, end_time, day_of_week, hour_in_day, duration, expiration                                                          from dba_hist_baseline_template where template_name in ('TEMPLATE_LOAD_DATAWAREHOUSE','TEMPLATE_MONDAY');

 

TEMPLATE_NAME               TEMPLATE_TYPE START_TIME       END_TIME         DAY_OF_WEEK HOUR_IN_DAY DURATION EXPIRATION
--------------------------- ------------- ---------------- ---------------- ----------- ----------- -------- ----------
TEMPLATE_LOAD_DATAWAREHOUSE SINGLE        02/11/2013 08:00 02/11/2013 17:00
TEMPLATE_MONDAY             REPEATING     04/11/2013 00:00 04/03/2014 00:00 MONDAY       9          8        60