2013年（350）
分类： Oracle
2013-04-27 09:59:32
这项统计专门用来分析某条指定的SQL语句，通过awrsqrpt.sql脚本，能够生成指定sql(曾经执行过的SQL)的执行计划，消耗的资源等等信息，有助于DBA进行SQL调优。
具体操作如下，首先还是执行生成脚本：
SQL> @$ORACLE_HOME/rdbms/admin/awrsqrpt.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
3812548755 TEST08 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选择生成的报表格式，没啥说的，就默认的html格式吧。
Type Specified: html
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 3812548755 1 TEST08 test08 yans1
Using 3812548755 for database Id
Using 1 for instance number
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
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
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 7451
Begin Snapshot Id specified: 7451
Enter value for end_snap: 7475
End Snapshot Id specified: 7475指定开始和结束的快照ID：
Specify the SQL Id
~~~~~~~~~~~~~~~~~~
Enter value for sql_id: a51q9un8j1kv6注意喽，这里要指定分析的SQL_ID，你可能想问，要分析的SQL ID从何而来呢？一般来说，提出这种问题说明你还未操作过awr或看到一条SQL。为什么这么说呢，因为一般来讲，获取问题SQL的途径要么是通过V$SQL(及其它相关视图)，要么是通过AWR/STATSPACK等工具，而这些方式找到SQL语句时，只要你再稍微拿眼神的旁光这么一扫，就会发现在语句的旁边有一条SQL ID静静的矗立着:)
SQL ID specified: a51q9un8j1kv6
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrsqlrpt_1_7451_7475.html. To use this name,
press
Enter value for report_name: awr_sqlrpt_1_7451_7475.html
Using the report name awr_sqlrpt_1_7451_7475.html
............
................输入完SQL ID后，短暂等待几秒钟，报告就生成了。
本项统计报表主要针对多实例数据库，不过基本操作与上非常类似(都比较简单，需要DBA敲的字符加一块也没几个)，具体操作如下：
SQL> @$ORACLE_HOME/rdbms/admin/awrsqrpi.sql
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:
Type Specified: html
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
948405229 2 JSSDB jssdbn2 jssdbn2
* 948405229 1 JSSDB jssdbn1 jssdbn1
Enter value for dbid: 948405229
Using 948405229 for database Id
Enter value for inst_num: 1
Using 1 for instance number指定DBID以及实例的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
specifying a number lists all completed snapshots.
Enter value for num_days: 2
Listing the last 7 days of Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
jssdbn1 JSSDB 235 22 Oct 2009 00:00 1
236 22 Oct 2009 01:00 1
237 22 Oct 2009 02:00 1
238 22 Oct 2009 03:00 1
239 22 Oct 2009 04:00 1
240 22 Oct 2009 05:00 1
241 22 Oct 2009 06:00 1
242 22 Oct 2009 07:00 1
243 22 Oct 2009 08:00 1
244 22 Oct 2009 09:00 1
245 22 Oct 2009 10:00 1
246 22 Oct 2009 11:00 1
247 22 Oct 2009 12:00 1
248 22 Oct 2009 13:00 1
249 22 Oct 2009 14:00 1
250 22 Oct 2009 15:00 1
251 22 Oct 2009 16:00 1
252 22 Oct 2009 17:00 1
253 22 Oct 2009 18:00 1
254 22 Oct 2009 19:00 1
255 22 Oct 2009 20:00 1
256 22 Oct 2009 21:00 1
257 22 Oct 2009 22:00 1
258 22 Oct 2009 23:00 1
259 23 Oct 2009 00:00 1
260 23 Oct 2009 01:00 1
261 23 Oct 2009 02:00 1
262 23 Oct 2009 03:00 1
263 23 Oct 2009 04:00 1
264 23 Oct 2009 05:00 1
265 23 Oct 2009 06:00 1
266 23 Oct 2009 07:00 1
267 23 Oct 2009 08:00 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 236
Begin Snapshot Id specified: 236
Enter value for end_snap: 260
End Snapshot Id specified: 260指定开始和结束的快照ID：
Specify the SQL Id
~~~~~~~~~~~~~~~~~~
Enter value for sql_id: 7tc5u8t3mmzgf指定要分析的SQL_ID。
SQL ID specified: 7tc5u8t3mmzgf
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrsqlrpt_1_236_260.html. To use this name,
press
Enter value for report_name:
Using the report name awrsqlrpt_1_236_260.html
..........................
..........................AWR 自动生成分析报表。
查看之前的文章：
==============================================
