【AWR】通过AWR报告中记录的 SQL Id获得SQL语句的执行计划

获得SQL的执行计划的方法很多，例如explain plan for和sql trace等。
这里给大家介绍另外一种方法。这种方法可以很方便和AWR报告相结合。对于在生成的AWR报告中被发现的消耗资源较多的SQL语句，我们可以使用AWR提供的awrsqrpt.sql脚本达到获取SQL语句执行计划的目的。

1.下面是以获得SQL ID为“8p23kcbgfqnk4”的SQL语句的执行计划为例，展示一下这个过程。
SQL> @?/rdbms/admin/awrsqrpt.sql

Current Instance
~~~~~~~~~~~~~~~~

   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
 1922648773 HSW                 1 hsw


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: text

Type Specified:  text


Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name      Instance     Host
------------ -------- ------------ ------------ ------------
* 1922648773        1 HSW          hsw          HOUSW-LT

Using 1922648773 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 without
specifying a number lists all completed snapshots.


Enter value for num_days: 3

Listing the last 3 days of Completed Snapshots

                                                        Snap
Instance     DB Name        Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
hsw          HSW                  1 14 Sep 2010 12:00      1
                                  2 14 Sep 2010 13:00      1
                                  3 14 Sep 2010 14:00      1
                                  4 14 Sep 2010 15:00      1

                                  5 14 Sep 2010 16:04      1
                                  6 14 Sep 2010 17:00      1
                                  7 14 Sep 2010 22:50      1



Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 6
Begin Snapshot Id specified: 6

Enter value for end_snap: 7
End   Snapshot Id specified: 7




Specify the SQL Id
~~~~~~~~~~~~~~~~~~
Enter value for sql_id: 8p23kcbgfqnk4
SQL ID specified:  8p23kcbgfqnk4

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrsqlrpt_1_6_7.txt.  To use this name,
press to continue, otherwise enter an alternative.

Enter value for report_name: 8p23kcbgfqnk4

Using the report name 8p23kcbgfqnk4


WORKLOAD REPOSITORY SQL Report

Snapshot Period Summary

DB Name         DB Id    Instance     Inst Num Release     RAC Host
------------ ----------- ------------ -------- ----------- --- ------------
HSW           1922648773 hsw                 1 10.2.0.3.0  NO  HOUSW-LT

              Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------
Begin Snap:         6 14-Sep-10 17:00:12        15       2.3
  End Snap:         7 14-Sep-10 22:50:29        17       2.3
   Elapsed:              350.29 (mins)
   DB Time:                0.02 (mins)

SQL Summary                                       DB/Inst: HSW/hsw  Snaps: 6-7

                Elapsed
   SQL Id      Time (ms)
------------- ----------
8p23kcbgfqnk4     19,672
select file#, block#, ts# from seg$ where type# = 3

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

SQL ID: 8p23kcbgfqnk4                             DB/Inst: HSW/hsw  Snaps: 6-7
-> 1st Capture and Last Capture Snap IDs
   refer to Snapshot IDs witin the snapshot range
-> select file#, block#, ts# from seg$ where type# = 3

    Plan Hash           Total Elapsed                 1st Capture   Last Capture
#   Value                    Time(ms)    Executions       Snap ID        Snap ID
--- ---------------- ---------------- ------------- ------------- --------------
1   1605285479                 19,672             1             7              7
          -------------------------------------------------------------


Plan 1(PHV: 1605285479)
-----------------------

Plan Statistics                                   DB/Inst: HSW/hsw  Snaps: 6-7
-> % 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)                            19,672       19,672.2  1351.7
CPU Time (ms)                                    32           32.3    79.6
Executions                                        1            N/A     N/A
Buffer Gets                                     141          141.0     0.6
Disk Reads                                        0            0.0     0.0
Parse Calls                                       1            1.0     0.1
Rows                                              0            0.0     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)                        0            N/A     N/A
Invalidations                                     0            N/A     N/A
Version Count                                     1            N/A     N/A
Sharable Mem(KB)                                 12            N/A     N/A
          -------------------------------------------------------------

Execution Plan
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |    32 (100)|          |
|   1 |  TABLE ACCESS FULL| SEG$ |     2 |    28 |    32   (0)| 00:00:01 |
--------------------------------------------------------------------------



Full SQL Text

SQL ID       SQL Text
------------ -----------------------------------------------------------------
8p23kcbgfqnk select file#, block#, ts# from seg$ where type# = 3


Report written to 8p23kcbgfqnk4

2.小结
在知道SQL Id的前提下，我们可以使用Oracle自带的awrsqrpt.sql脚本快速的获得SQL语句的执行计划信息（在上述报告中显示的信息不局限于此）。
有兴趣的朋友可以调出awrsqrpt.sql脚本，研究一下这个功能的实现过程。

Good luck.

secooler
10.09.14

-- The End --

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/519536/viewspace-673950/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/519536/viewspace-673950/