Performance Tuning
AWR Baselines
User Rating: / 1
- Details
-
Parent Category: Articles
-
Created on Friday, 12 March 2010 21:25
-
Last Updated on Tuesday, 29 October 2013 16:40
-
Published on Monday, 12 April 2010 22:25
-
Written by t
-
Hits: 7567
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