Oracle-awrddrpt.sql比较两个AWR差异报告
【摘要】
概述示例
以oracle用户登录到数据库主机执行脚本查看报告
概述
awrddrpt.sql脚本位于$ORACLE_HOME/rdbms/admin目录中。
a...
概述
awrddrpt.sql脚本位于$ORACLE_HOME/rdbms/admin目录中。
awrddrpt.sql脚本比较两个时间段的AWR报告。
这个脚本的交互部分最重要的就是需要输入要进行对比的两个awr报告的begin snap_id与end snap_id
一旦收集到四个快照号码，awrddrpt.sql脚本将创建两个AWR报告，然后比较这两个报告。awrddrpt.sql脚本对于比较两个经过时间段内的性能非常有用。
示例
我们打算比对3月30号00:00~01:00 和 3月31号00:00~01:00这两个时间段的AWR
以oracle用户登录到数据库主机执行脚本
.....
Connection established.
To escape to local shell, press 'Ctrl+Alt+]'.
Last login: Sat Mar 31 09:48:24 2018 from 172.25.21.204
Oracle Corporation SunOS 5.10 Generic Patch January 2005
You have new mail.
##处于安全的考虑，以nolog的方式登录
ocsdb01:[/oracle$]sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0 Production on Sat Mar 31 11:23:08 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
## 登录特定的用户,输入密码
SQL> connect cc@PR_CC
Enter password:
Connected.
## 执行awrddrpt脚本
SQL> @?/rdbms/admin/awrddrpt
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Id DB Name Inst Num Inst Num Instance
----------- ----------- ------------ -------- -------- ------------
2143881171 2143881171 CC 1 1 cc
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'
## 默认html
Enter value for report_type:
Type Specified: html
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 2143881171 1 CC cc ocsdb01
Database Id and Instance Number for the First Pair of Snapshots
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Using 2143881171 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.
#因为今天是31号,我们只需要输入2天时间范围就可以获取到3月30号00:00~01:00的快照
Enter value for num_days: 2
Listing the last 2 days of Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
cc CC 10080 30 Mar 2018 00:00 1
10081 30 Mar 2018 01:00 1
10082 30 Mar 2018 02:00 1
10083 30 Mar 2018 03:00 1
10084 30 Mar 2018 04:00 1
10085 30 Mar 2018 05:00 1
10086 30 Mar 2018 06:00 1
10087 30 Mar 2018 07:00 1
10088 30 Mar 2018 08:00 1
10089 30 Mar 2018 09:00 1
10090 30 Mar 2018 10:00 1
10091 30 Mar 2018 11:00 1
10092 30 Mar 2018 12:00 1
10093 30 Mar 2018 13:00 1
10094 30 Mar 2018 14:00 1
10095 30 Mar 2018 15:00 1
10096 30 Mar 2018 16:00 1
10097 30 Mar 2018 17:00 1
10098 30 Mar 2018 18:00 1
10099 30 Mar 2018 19:00 1
10100 30 Mar 2018 20:00 1
10101 30 Mar 2018 21:00 1
10102 30 Mar 2018 22:00 1
10103 30 Mar 2018 23:00 1
10104 31 Mar 2018 00:00 1
10105 31 Mar 2018 01:00 1
10106 31 Mar 2018 02:00 1
10107 31 Mar 2018 03:00 1
10108 31 Mar 2018 04:00 1
10109 31 Mar 2018 05:00 1
10110 31 Mar 2018 06:00 1
10111 31 Mar 2018 07:00 1
10112 31 Mar 2018 08:00 1
10113 31 Mar 2018 09:00 1
10114 31 Mar 2018 10:00 1
10115 31 Mar 2018 11:00 1
Specify the First Pair of Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
## 选择开始的快照
Enter value for begin_snap: 10080
First Begin Snapshot Id specified: 10080
## 选择结束的快照
Enter value for end_snap: 10081
First End Snapshot Id specified: 10081
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 2143881171 1 CC cc ocsdb01
Database Id and Instance Number for the Second Pair of Snapshots
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Using 2143881171 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.
#因为今天是31号,我们只需要输入1天时间范围就可以获取到3月31号00:00~01:00
Enter value for num_days2: 1
Listing the last day's Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
cc CC 10104 31 Mar 2018 00:00 1
10105 31 Mar 2018 01:00 1
10106 31 Mar 2018 02:00 1
10107 31 Mar 2018 03:00 1
10108 31 Mar 2018 04:00 1
10109 31 Mar 2018 05:00 1
10110 31 Mar 2018 06:00 1
10111 31 Mar 2018 07:00 1
10112 31 Mar 2018 08:00 1
10113 31 Mar 2018 09:00 1
10114 31 Mar 2018 10:00 1
10115 31 Mar 2018 11:00 1
Specify the Second Pair of Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
## 选择开始的快照
Enter value for begin_snap2: 10104
Second Begin Snapshot Id specified: 10104
## 选择结束的快照
Enter value for end_snap2: 10105
Second End Snapshot Id specified: 10105
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrdiff_1_10080_1_10104.html To use this name,
press <return> to continue, otherwise enter an alternative.
## 输入awr报告名称
Enter value for report_name: artisan_awr_diff_rpt
Using the report name artisan_awr_diff_rpt
......省略输出过程
......
......
</body></html>
Report written to artisan_awr_diff_rpt
## 退出
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ocsdb01:[/oracle$]
## 当前目录下查找输出的报告
ocsdb01:[/oracle$]ls artisan_awr_diff_rpt*
artisan_awr_diff_rpt.lst
ocsdb01:[/oracle$]
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
- 123
- 124
- 125
- 126
- 127
- 128
- 129
- 130
- 131
- 132
- 133
- 134
- 135
- 136
- 137
- 138
- 139
- 140
- 141
- 142
- 143
- 144
- 145
- 146
- 147
- 148
- 149
- 150
- 151
- 152
- 153
- 154
- 155
- 156
- 157
- 158
- 159
- 160
- 161
- 162
- 163
- 164
- 165
- 166
- 167
- 168
- 169
- 170
- 171
- 172
- 173
- 174
- 175
- 176
- 177
- 178
- 179
- 180
- 181
- 182
- 183
- 184
- 185
- 186
- 187
- 188
- 189
- 190
- 191
- 192
- 193
- 194
- 195
- 196
- 197
- 198
- 199
- 200
查看报告
文章来源: artisan.blog.csdn.net，作者：小小工匠，版权归原作者所有，如需转载，请联系作者。
原文链接：artisan.blog.csdn.net/article/details/79774654
【版权声明】本文为华为云社区用户转载文章，如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论（0）