AWR baseline enhancement in Oracle 11g R1 (1)

Bkjia.com exclusive Translation]The automatic workload warehouse was introduced from Oracle 10 Gb, but it only includes some simple baseline functions. Creating a baseline allows you to retain snapshots within a specified range for performance comparison, it has nothing to do with the AWR retention policy. You can use the DBMS_WORKLOAD_REPOSITORY package to manage this function. In 11g, it is enhanced.

Note: Most stored procedures and functions in the DBMS_WORKLOAD_REPOSITORY package accept the DBID parameter, which is the default identifier of the local database. For this reason, this parameter will be omitted in the following example.

Fixed baseline

Compared with Oracle 10 Gb, 11 GB has more fixed or static baseline functions. Initially, in Oracle 10 Gb, only one CREATE_BASELINE stored procedure is provided in the DBMS_WORKLOAD_REPOSITORY package. You can use a specific snapshot ID to define the baseline. in Oracle 11g, more baseline stored procedures and functions are included, they use the start time and end time for baseline creation, which helps to calculate the relevant snapshot ID. The function is the same as the parameter of the stored procedure, but the returned baseline ID is always saved by default, however, the new validity period parameter expiration allows you to specify those baselines that will automatically expire after a certain date.

Set serveroutput ondeclare l_return NUMBER; BEGIN -- Using procedures. DBMS_WORKLOAD_REPOSITORY.create_baseline (start_snap_id => 2490, snapshot start ID] end_snap_id => 2491, snapshot end ID] baseline_name => 'test1 _ bl', expiration => 60 ); invalid 60 days later] DBMS_WORKLOAD_REPOSITORY.create_baseline (start_time => TO_DATE ('09-JUL-2008 ', 'dd-MON-YYYY HH24: MI '), start time] end_time => TO_DATE ('09-JUL-2008 ', 'dd-MON-YYYY HH24: MI'), as of time] baseline_name => 'test2 _ bl ', expiration => NULL); never invalid] -- Using functions. l_return: = values (start_snap_id => 2492, end_snap_id => 2493, baseline_name => 'test3 _ bl', expiration => 30); DBMS_OUTPUT.put_line ('Return: '| l_return); l_return: = DBMS_WORKLOAD_REPOSITORY.create_baseline (start_time => TO_DATE ('09-JUL-2008', 'dd-MON-YYYY HH24: MI '), end_time => TO_DATE ('09-JUL-2008 20:00 ', 'dd-MON-YYYY HH24: MI'), baseline_name => 'test4 _ bl', expiration => NULL ); DBMS_OUTPUT.put_line ('Return: '| l_return); END;/Return: 8 Return: 9PL/SQL procedure successfully completed. SQL>

The new baseline is visible in the DBA_HIST_BASELINE view.

COLUMN baseline_name FORMAT A15SELECT baseline_id, baseline_name, START_SNAP_ID, TO_CHAR(start_snap_time, 'DD-MON-YYYY HH24:MI') AS start_snap_time, END_SNAP_ID, TO_CHAR(end_snap_time, 'DD-MON-YYYY HH24:MI') AS end_snap_timeFROM dba_hist_baselineWHERE baseline_type = 'STATIC'ORDER BY baseline_id;BASELINE_ID BASELINE_NAME START_SNAP_ID START_SNAP_TIME END_SNAP_ID END_SNAP_TIME----------- --------------- ------------- ----------------- ----------- ----------------- 6 test1_bl 2490 09-JUL-2008 17:00 2491 09-JUL-2008 18:00 7 test2_bl 2490 09-JUL-2008 17:00 2491 09-JUL-2008 18:00 8 test3_bl 2492 09-JUL-2008 19:00 2493 09-JUL-2008 20:00 9 test4_bl 2492 09-JUL-2008 19:00 2493 09-JUL-2008 20:004 rows selected.SQL>

Use the SELECT_BASELINE_DETAILS function of the MPs queue table to specify the BASELINE_ID parameter. You can return information about a specific baseline or use the SELECT_BASELINE_METRIC function of the MPs queue table to specify the BASELINE_NAM parameter and return the same result.

SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.select_baseline_details(6));SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.select_baseline_metric('SYSTEM_MOVING_WINDOW'));

You can use the Stored Procedure RENAME_BASELINE to rename the baseline.

BEGIN DBMS_WORKLOAD_REPOSITORY.rename_baseline( old_baseline_name => 'test4_bl', new_baseline_name => 'test5_bl');END;/

You can use the Stored Procedure DROP_BASELINE to delete baselines.

BEGIN DBMS_WORKLOAD_REPOSITORY.drop_baseline(baseline_name => 'test1_bl'); DBMS_WORKLOAD_REPOSITORY.drop_baseline(baseline_name => 'test2_bl'); DBMS_WORKLOAD_REPOSITORY.drop_baseline(baseline_name => 'test3_bl'); DBMS_WORKLOAD_REPOSITORY.drop_baseline(baseline_name => 'test5_bl');END;/