oracle awrsqrpt.sql 指令碼使用方法

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


以下是個例子

 

WORKLOAD REPOSITORY SQL Report

Snapshot Period Summary

DB Name         DB Id    Instance     Inst Num Startup Time    Release     RAC
------------ ----------- ------------ -------- --------------- ----------- ---
test          45999999989  test           1 18-May-14 00:45 11.2.0.3.0  NO

              Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------
Begin Snap:     11707 09-Jul-14 10:00:47     1,266      38.1
  End Snap:     11708 09-Jul-14 11:00:02     1,496      40.0
   Elapsed:               59.25 (mins)
   DB Time:            4,576.15 (mins)

SQL Summary                     DB/Inst: test/test  Snaps: 11707-11708

                Elapsed
   SQL Id      Time (ms)
------------- ----------
1agdrb19sf1f2 8.5506E+07
Module: JDBC Thin Client
select * from table where id='123456'

          -------------------------------------------------------------

SQL ID: 1agdrb19sf1f2           DB/Inst:test/test  Snaps: 11707-11708
-> 1st Capture and Last Capture Snap IDs
   refer to Snapshot IDs witin the snapshot range
-> select vrycjsqlis0_.SQ_ID as SQ1_478_, vrycjsqlis0_.RYXH as RYXH478_, ...

    Plan Hash           Total Elapsed                 1st Capture   Last Capture
#   Value                    Time(ms)    Executions       Snap ID        Snap ID
--- ---------------- ---------------- ------------- ------------- --------------
1   1091025147             85,505,996        2,3286         11708          11708
          -------------------------------------------------------------

Plan 1(PHV: 1091025147)
-----------------------

Plan Statistics                 DB/Inst: test/test  Snaps: 11707-11708
-> % Total DB Time is the Elapsed Time of the SQL statement divided
   into the Total Database Time multiplied by 100

Stat Name                                Statement   Per Execution % Snap
---------------------------------------- ---------- -------------- -------
Elapsed Time (ms)                        8.5506E+07        3,672.0    31.1
CPU Time (ms)                            3.0166E+07        1,295.5    37.6
Executions                                   23,286            N/A     N/A
Buffer Gets                                       0            0.0     0.0
Disk Reads                                        0            0.0     0.0
Parse Calls                                   5,470            0.2     0.1
Rows                                        124,801            5.4     N/A
User I/O Wait Time (ms)                           0            N/A     N/A
Cluster Wait Time (ms)                            0            N/A     N/A
Application Wait Time (ms)                        0            N/A     N/A
Concurrency Wait Time (ms)                   17,775            N/A     N/A
Invalidations                                     0            N/A     N/A
Version Count                                     3            N/A     N/A
Sharable Mem(KB)                                113            N/A     N/A
          -------------------------------------------------------------

Execution Plan
------------------------------------------------------------------------------------
| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                |       |       |    35 (100)|          |
|   1 |  TABLE ACCESS FULL| test |     1 |  3282 |    35   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)

 

Full SQL Text

SQL ID       SQL Text
------------ -----------------------------------------------------------------
1agdrb19sf1f select * from test