基线(Baseline):
在创建数据库比较报告时,通常选择两个时段的采样进行比较,而如果我们能够创建基于正常运行的数据库快照,在发生性能问题时,就可以将异常与正常情况进行比较,获得差异对比信息。有了基线之后,AWR报告之间的对比就会更加明确和有效。
SQL> exec dbms_workload_repository.create_baseline(start_snap_id=>90,end_snap_id=>95,baseline_name=>'baseline01');
PL/SQL 过程已成功完成。
SQL> select * from dba_hist_baseline;
DBID BASELINE_ID BASELINE_NAM START_SNAP_ID START_SNAP_TIME END_SNAP_ID END_SNAP_TIME
---------- ----------- ------------ ------------- ------------------------------ ----------- ------------------------------
1293815896 1 baseline01 90 25-11月-11 02.17.22.241 上午 95 25-11月-11 07.00.39.847 上午
SQL> exec dbms_workload_repository.drop_baseline(baseline_name=>'baseline01',cascade=>true);
PL/SQL 过程已成功完成。
SQL> select * from dba_hist_baseline;
未选定行
在删除的时候指定了cascade=>true,那么快照信息也会级联的被删除!就是说再次生成AWR报告的时候将会看不见90~95的快照信息了。
awrddrpt.sql脚本可以对同一个数据库两个时段进行比较,生成详细的对比报告,提供比较分析!
awrddrpi.sql脚本可以对不同的数据库两个时段进行比较,生成详细的对比报告,提供比较分析!
[oracle@linux awr]$ sqlplus / as sysdba @?/rdbms/admin/awrddrpt.sql --生成AWR对比报告。
SQL*Plus: Release 10.2.0.4.0 - Production on 星期六 11月 26 01:02:30 2011
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Id DB Name Inst Num Inst Num Instance
----------- ----------- ------------ -------- -------- ------------
1293815896 1293815896 ORCL 1 1 orcl
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
输入 report_type 的值: --选择html默认值。
Type Specified: html
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 1293815896 1 ORCL orcl linux
Database Id and Instance Number for the First Pair of Snapshots
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Using 1293815896 for Database Id for the first pair of snapshots
Using 1 for Instance Number for the first pair of snapshots
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing <return> without
specifying a number lists all completed snapshots.
输入 num_days 的值: 2 --最近2天。
Listing the last 2 days of Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
orcl ORCL 90 25 11月 2011 02:17 1
91 25 11月 2011 03:00 1
92 25 11月 2011 04:00 1
93 25 11月 2011 05:00 1
94 25 11月 2011 06:00 1
95 25 11月 2011 07:00 1
96 25 11月 2011 08:00 1
97 25 11月 2011 23:59 1
98 26 11月 2011 01:00 1
Specify the First Pair of Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
输入 begin_snap 的值: 91
First Begin Snapshot Id specified: 91 --第一个开始采样值。
输入 end_snap 的值: 92
First End Snapshot Id specified: 92 --第一个结束采样值。
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 1293815896 1 ORCL orcl linux
Database Id and Instance Number for the Second Pair of Snapshots
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Using 1293815896 for Database Id for the second pair of snapshots
Using 1 for Instance Number for the second pair of snapshots
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing <return> without
specifying a number lists all completed snapshots.
输入 num_days2 的值: 2 --还是最近2天。
Listing the last 2 days of Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
orcl ORCL 90 25 11月 2011 02:17 1
91 25 11月 2011 03:00 1
92 25 11月 2011 04:00 1
93 25 11月 2011 05:00 1
94 25 11月 2011 06:00 1
95 25 11月 2011 07:00 1
96 25 11月 2011 08:00 1
97 25 11月 2011 23:59 1
98 26 11月 2011 01:00 1
Specify the Second Pair of Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
输入 begin_snap2 的值: 92 --第二个开始采样值。
Second Begin Snapshot Id specified: 92
输入 end_snap2 的值: 93 --第二个结束采样值。
Second End Snapshot Id specified: 93
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrdiff_1_91_1_92.html To use this name,
press <return> to continue, otherwise enter an alternative.
输入 report_name 的值: --默认值。
Using the report name awrdiff_1_91_1_92.html
... ...
... ...
... ...
P>
<P>
<P>
<P>
<P>
</BODY></HTML>
Report written to awrdiff_1_91_1_92.html