理解awr中的基準線(baseline)

先看看baseline的解釋

A baseline is created with the CREATE_BASELINE procedure. A baseline is simply
performance data for a set of snapshots that is preserved and used for comparisons
with other similar workload periods when performance problems occur. You can
review the existing snapshots in the DBA_HIST_SNAPSHOT view to determine the
range of snapshots that you want to use

大意是baseline使用dbms_workload_repository.create_baseline儲存過程建立，baseline生成兩次快照統計資訊值的對比資料，用以確定效能問題或實施效能調整後觀察調整效果。
可以查詢DBA_HIST_SNAPSHOT檢視用於確定要保留那些baseline.

實際建立baseline時，作了如下的操作

INSERT INTO wrh$_sysstat_bl
SELECT * FROM wrh$_sysstat tab
WHERE :beg_snap = tab.snap_id
AND tab.snap_id <= :end_snap
AND tab.dbid = :dbid
AND EXISTS
(
SELECT 1 FROM wrm$_snapshot s
WHERE s.dbid = tab.dbid
AND s.snap_id = tab.snap_id
AND s.instance_number = tab.instance_number
AND s.status = 0
AND s.bl_moved = 0
);

可以看到，只是簡單把相關的資料複製到別一張系統表中。

我們看一個建立baseline的過程：

SQL> Begin
2 dbms_workload_repository.create_baseline(
3 start_snap_id => 685,
4 end_snap_id => 687,
5 baseline_name => 'HIS_SLOWLY');
6
7 End;
8 /

PL/SQL 過程已成功完成。

可以透過dba_hist_baseline檢視baseline資訊:

SQL> Select baseline_id,baseline_name From dba_hist_baseline;

BASELINE_ID BASELINE_NAME
----------- ----------------------------------------------------------------
3 HIS_SLOWLY
0 SYSTEM_MOVING_WINDOW

透過dbms_workload_repository.select_baseline_details函式也能返回相關資訊

SQL> Select baseline_id,start_snap_time,end_snap_id
2 From Table((Select dbms_workload_repository.select_baseline_details(l_baseline_id => 3) From dual ));

BASELINE_ID START_SNAP_TIME END_SNAP_ID
----------- --------------------------------------------------------------------------- -----------
3 14-7月 -08 11.00.51.899 上午 687

SQL>

可以使用dbms_workload_repository.select_baseline_metric函式檢視兩次快照統計資訊的差異對比：

SQL> Select baseline_name,metric_name,num_interval,interval_size
2 From Table((Select
3 dbms_workload_repository.select_baseline_metric(l_baseline_name => 'HIS_SLOWLY') From dual ));

BASELINE_NAME METRIC_NAME NUM_INTERVAL INTERVAL_SIZE
--------------------------------- ---------------------------------------------- ------------ -------------
HIS_SLOWLY Physical Writes Direct Per Sec 85 365991
HIS_SLOWLY User Rollbacks Percentage 85 365991
HIS_SLOWLY Recursive Calls Per Sec 85 365991
..............

[@more@]