????? 有些时候，我们需要分析占用资源比较大的sql的执行计划，也需要将sql的执行计划以报告的形式反馈给客户，由于AWR报告里的SQL通常都是些变量，因此以命令行方式生成sql的执行计划就很麻烦，而且也不美观，利用awrsqrpt.sql脚本就很方便。
????? 生成HTML的执行计划很简单，如果是生成本地数据库的sql执行计划，执行awrsqrpt.sql就可以，但是如果需要生成由AWR迁移到本地数据库的分析数据，就需要使用awrsqrpi.sql。
SQL> @?/rdbms/admin/awrsqrpi
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
------------ -------- ------------ ------------ ------------
* 1520519778??????? 1 STREAM?????? stream?????? STREAM
? 2400249746??????? 1 CNDERPDB???? cnderpdb1??? p5a1
? 2400249746??????? 2 CNDERPDB???? cnderpdb2??? p5b1
输入 dbid 的值:? 2400249746?????? --输入要生成执行计划的数据库ID
Using 2400249746 for database Id
输入 inst_num 的值:? 1???????????????? --输入节点号
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 <return> without
specifying a number lists all completed snapshots.
输入 num_days 的值:? 7
Listing the last 7 days of Completed Snapshots
??????????????????????????????????????????????????????? Snap
Instance???? DB Name??????? Snap Id??? Snap Started??? Level
------------ ------------ --------- ------------------ -----
cnderpdb1??? CNDERPDB???????? 50063 16 6月? 2011 08:00???? 1
????????????????????????????? 50064 16 6月? 2011 09:00???? 1
????????????????????????????? 50065 16 6月? 2011 10:00???? 1
????????????????????????????? 50066 16 6月? 2011 11:00???? 1
????????????????????????????? 50067 16 6月? 2011 12:00???? 1
... ...
????????????????????????????? 50206 22 6月? 2011 07:00???? 1
????????????????????????????? 50207 22 6月? 2011 08:00???? 1
????????????????????????????? 50208 22 6月? 2011 09:00???? 1
????????????????????????????? 50209 22 6月? 2011 10:00???? 1
?
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
输入 begin_snap 的值:? 50063?????????????? --输入开始快照号
Begin Snapshot Id specified: 50063
输入 end_snap 的值:? 50209??????????????????--输入结束快照号
End?? Snapshot Id specified: 50209
?
Specify the SQL Id
~~~~~~~~~~~~~~~~~~
输入 sql_id 的值:? 8hm5s0k011450????? --在AWR报告中看到的占用资源较大的SQL?ID
SQL ID specified:? 8hm5s0k011450
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrsqlrpt_1_50063_50209.html.? To use this name,
press <return> to continue, otherwise enter an alternative.
输入 report_name 的值:? d:\stream.html?? --保存路径和名字
Using the report name d:\stream.html
Report written to d:\stream.html
SQL>
?
之后打开D盘下的stream.html就可以很直观的看到SQL_ID为8hm5s0k011450的执行计划
| Elapsed Time (ms) | 18,121,198 | 4.89 | 3.20 |
| CPU Time (ms) | 17,874,450 | 4.82 | 3.33 |
| Executions | 3,707,839 | ? | ? |
| Buffer Gets | 404,447,392 | 109.08 | 3.85 |
| Disk Reads | 0 | 0.00 | 0.00 |
| Parse Calls | 6 | 0.00 | 0.00 |
| Rows | 9,831,284 | 2.65 | ? |
| User I/O Wait Time (ms) | 0 | ? | ? |
| Cluster Wait Time (ms) | 0 | ? | ? |
| Application Wait Time (ms) | 0 | ? | ? |
| Concurrency Wait Time (ms) | 0 | ? | ? |
| Invalidations | 0 | ? | ? |
| Version Count | 38 | ? | ? |
| Sharable Mem(KB) | 713 | ? | ? |
?
| 0 | SELECT STATEMENT | ? | ? | ? | 3 (100) | ? |
| 1 | ?? FOR UPDATE | ? | ? | ? | ? | ? |
| 2 | ???? SORT ORDER BY | ? | 1 | 32 | 3 (34) | 00:00:01 |
| 3 | ?????? TABLE ACCESS FULL | TEMPSK | 1 | 32 | 2 (0) | 00:00:01 |