获得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 <return> 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 <return> 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脚本，研究一下这个功能的实现过程.
这里给大家介绍另外一种方法。这种方法可以很方便和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 <return> 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 <return> 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脚本，研究一下这个功能的实现过程.
