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
SnapInstance 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 1Specify 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
ElapsedSQL 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 CaptureValue 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.
secooler10.09.14
— The End —