10gR2中awrsqrpt.sql介紹

 在RDBMS/admin/awrsqrpt.sql中，這個指令碼可以方便地取出某個sql在某兩個快照間隔內，它總的消耗的cpu時間，執行次數，邏輯讀，物理讀，sql的執行計劃以及sql的full sql text等

 

生成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 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 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的執行計劃

Stat Name Statement Total Per Execution % Snap Total

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

 

Id Operation Name Rows Bytes Cost (%CPU) Time

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

 

 

source:http://streamsong.iteye.com/blog/1109377