理解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@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7839206/viewspace-1007358/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7839206/viewspace-1007358/