在没有awr 之前，如果希望对不同时间段时，数据库的整体影响进行对比，只能依靠DBA 手工查询相关视图，并通过时间条件来获取差异(还有些统计已经无法对比)，而在 AWR 中， 直接就提供了，对不同时间段时，数据库的性能统计做差异对比的功能。
执行脚本如下：
SQL> @$ORACLE_HOME/rdbms/admin/awrddrpt.sql
Current Instance
~~~~~~~~~~~~~~~~
| DB Id | DB Id | DB Name | Inst Num Inst Num Instance | |
| ----------- ----------- ------------ -------- -------- ------------ |
| |||
| 3812548755 | 3812548755 TEST08 | 1 | 1 test08 | |
|
|
|
|
|
|
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'
Enter value for report_type: html
Type Specified: HTML
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| DB Id | Inst Num DB Name | Instance | Host |
| ------------ -------- ------------ ------------ ------------ |
| ||
| * 3812548755 | 1 TEST08 | test08 | yans1 |
Database Id and Instance Number for the First Pair of Snapshots
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Using 3812548755 for Database Id for the first pair of snapshots
Using 1 for Instance Number for the first pair of snapshots
注意，下面紧接着，是选择第一份报表的相关参数，包括快照的区间，以及开始和结束 的快照 ID：
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.
Enter value for num_days: 2
Listing the last 2 days of Completed Snapshots
|
|
|
|
|
| Snap |
| Instance | DB Name | Snap Id | Snap Started | Level | |
| ------------ ------------ --------- ------------------ ----- |
|
| |||
| test08 | TEST08 | 7450 25 10 月 2009 00:00 | 1 | ||
|
|
| 7451 25 10 月 2009 01:00 | 1 | ||
|
|
| 7452 25 10 月 2009 02:00 | 1 | ||
|
|
| 7453 25 10 月 2009 03:00 | 1 | ||
|
|
| 7454 25 10 月 2009 04:00 | 1 | ||
|
|
| 7455 25 10 月 2009 05:00 | 1 | ||
|
|
| 7456 25 10 月 2009 06:00 | 1 | ||
|
|
| 7457 25 10 月 2009 07:00 | 1 | ||
|
|
| 7458 25 10 月 2009 08:00 | 1 | ||
|
|
| 7459 25 10 月 2009 09:00 | 1 | ||
|
|
| 7460 25 10 月 2009 10:00 | 1 | ||
|
|
| 7461 25 10 月 2009 11:00 | 1 | ||
|
|
| 7462 25 10 月 2009 12:00 | 1 | ||
|
|
| 7463 25 10 月 2009 13:00 | 1 | ||
|
|
| 7464 25 10 月 2009 14:00 | 1 | ||
|
|
| 7465 25 10 月 2009 15:00 | 1 | ||
|
|
| 7466 25 10 月 2009 16:00 | 1 | ||
|
|
| 7467 25 10 月 2009 17:00 | 1 | ||
|
|
| 7468 25 10 月 2009 18:00 | 1 | ||
|
|
| 7469 25 10 月 2009 19:00 | 1 | ||
| 1 | |
| 7471 25 10 月 2009 21:00 | 1 |
| 7472 25 10 月 2009 22:00 | 1 |
| 7473 25 10 月 2009 23:00 | 1 |
| 7474 26 10 月 2009 00:00 | 1 |
| 7475 26 10 月 2009 01:00 | 1 |
| 7476 26 10 月 2009 02:00 | 1 |
| 7477 26 10 月 2009 03:00 | 1 |
| 7478 26 10 月 2009 04:00 | 1 |
| 7479 26 10 月 2009 05:00 | 1 |
| 7480 26 10 月 2009 06:00 | 1 |
| 7481 26 10 月 2009 07:00 | 1 |
| 7482 26 10 月 2009 08:00 | 1 |
| 7483 26 10 月 2009 09:00 | 1 |
| 7484 26 10 月 2009 10:00 | 1 |
| 7485 26 10 月 2009 11:00 | 1 |
| 7486 26 10 月 2009 12:00 | 1 |
| 7487 26 10 月 2009 13:00 | 1 |
| 7488 26 10 月 2009 14:00 | 1 |
| 7489 26 10 月 2009 15:00 | 1 |
|
|
|
Specify the First Pair of Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 7459
First Begin Snapshot Id specified: 7459
Enter value for end_snap: 7462
First End Snapshot Id specified: 7462
然后，是选择要对比的报表相关参数，包括快照的区间，以及开始和结束的快照 ID：
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| DB Id | Inst Num DB Name | Instance | Host |
| ------------ -------- ------------ ------------ ------------ |
| ||
| * 3812548755 | 1 TEST08 | test08 | yans1 |
|
|
|
|
|
Database Id and Instance Number for the Second Pair of Snapshots
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Using 3812548755 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.
Enter value for num_days2: 2
Listing the last 2 days of Completed Snapshots
|
|
|
|
|
| Snap |
| Instance | DB Name | Snap Id | Snap Started | Level | |
| ------------ ------------ --------- ------------------ ----- |
|
| |||
| test08 | TEST08 | 7450 25 10 月 2009 00:00 | 1 | ||
|
|
| 7451 25 10 月 2009 01:00 | 1 | ||
|
|
| 7452 25 10 月 2009 02:00 | 1 | ||
|
|
| 7453 25 10 月 2009 03:00 | 1 | ||
|
|
| 7454 25 10 月 2009 04:00 | 1 | ||
|
|
| 7455 25 10 月 2009 05:00 | 1 | ||
|
|
| 7456 25 10 月 2009 06:00 | 1 | ||
|
|
| 7457 25 10 月 2009 07:00 | 1 | ||
|
|
| 7458 25 10 月 2009 08:00 | 1 | ||
|
|
| 7459 25 10 月 2009 09:00 | 1 | ||
|
|
| 7460 25 10 月 2009 10:00 | 1 | ||
|
|
| 7461 25 10 月 2009 11:00 | 1 | ||
|
|
| 7462 25 10 月 2009 12:00 | 1 | ||
|
|
| 7463 25 10 月 2009 13:00 | 1 | ||
|
|
| 7464 25 10 月 2009 14:00 | 1 | ||
|
|
| 7465 25 10 月 2009 15:00 | 1 | ||
|
|
| 7466 25 10 月 2009 16:00 | 1 | ||
|
|
| 7467 25 10 月 2009 17:00 | 1 | ||
|
|
| 7468 25 10 月 2009 18:00 | 1 | ||
|
|
| 7469 25 10 月 2009 19:00 | 1 | ||
|
|
| 7470 25 10 月 2009 20:00 | 1 | ||
|
|
| 7471 25 10 月 2009 21:00 | 1 | ||
|
|
| 7472 25 10 月 2009 22:00 | 1 | ||
| 1 | |
| 7474 26 10 月 2009 00:00 | 1 |
| 7475 26 10 月 2009 01:00 | 1 |
| 7476 26 10 月 2009 02:00 | 1 |
| 7477 26 10 月 2009 03:00 | 1 |
| 7478 26 10 月 2009 04:00 | 1 |
| 7479 26 10 月 2009 05:00 | 1 |
| 7480 26 10 月 2009 06:00 | 1 |
| 7481 26 10 月 2009 07:00 | 1 |
| 7482 26 10 月 2009 08:00 | 1 |
| 7483 26 10 月 2009 09:00 | 1 |
| 7484 26 10 月 2009 10:00 | 1 |
| 7485 26 10 月 2009 11:00 | 1 |
| 7486 26 10 月 2009 12:00 | 1 |
| 7487 26 10 月 2009 13:00 | 1 |
| 7488 26 10 月 2009 14:00 | 1 |
| 7489 26 10 月 2009 15:00 | 1 |
|
|
|
Specify the Second Pair of Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap2: 7483
Second Begin Snapshot Id specified: 7483
Enter value for end_snap2: 7486
Second End Snapshot Id specified: 7486
最后，为要生成的报表命令：
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrdiff_1_7459_1_7483.html To use this name, press <return> to continue, otherwise enter an alternative.
Enter value for report_name: awr_diff_1_7459_1_7483.html
Using the report name awr_diff_1_7459_1_7483.html
<HTML><HEAD><TITLE>Workload Repository Compare Period Report</TITLE>
...............
...............
报表生成以后，在显示时将以并列的形式，直观的显示出两个不同时间段里，数据库各 项参数的差异，摘要如图：
生成指定 SQL 语句的统计报表
前例的对比是在单实例环境下进行的，如果希望对多实例的数据库做对比，那就要使用 $ORACLE_HOME/rdbms/admin/awrddrpi.sql 脚本了。该脚本的操作基本与前例相同，这里 不再演示，感兴趣的朋友不妨自行测试。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/1700919/viewspace-2120584/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/1700919/viewspace-2120584/
169
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
