Oracle生成不同时间段时的统计对比报表
在没有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 |
||
|
7470 25 10 月 2009 20:00 |
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 |
||
|
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 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博客 ” ，链接：https://blog.itpub.net/1700919/viewspace-2120584/ ，如需转载，请注明出处，否则将追究法律责任。
