【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 --
這裡給大家介紹另外一種方法。這種方法可以很方便和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
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
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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 根據SQL Id獲得SQL語句的執行計劃SQL
- 通過分析SQL語句的執行計劃優化SQL語句SQL優化
- 獲得目標SQL語句執行計劃的方法SQL
- 通過分析SQL語句的執行計劃優化SQL 二SQL優化
- 通過shell指令碼抓取awr報告中的問題sql指令碼SQL
- 通過分析SQL語句的執行計劃優化SQL(總結)SQL優化
- 產生AWR及單個sql的執行計劃SQL
- _awr_sql_child_limit是否能控制awr記錄sql執行次數的問題SQLMIT
- 通過等待事件來獲得查詢SQl的執行計劃事件SQL
- 使用dbms_xplan包來獲得sql語句的執行計劃SQL
- AWR報告的收集和分析執行計劃的方式
- 如何使用awr_set_report_thresholds控制AWR報告裡的sql語句數量SQL
- zt_導回awr報告中的歷史執行計劃
- 清除SQL語句的執行計劃SQL
- 【指令碼】通過hash_value直接獲得sql語句的執行計劃（9i-->10g過度）指令碼SQL
- 透過shell指令碼抓取awr報告中的問題sql指令碼SQL
- AWR中的SQL StatisticsSQL
- AWR 中 top sql 的資訊獲取 - 分析SQL
- 【檢視】使用V$SQL_PLAN檢視獲取曾經執行過的SQL語句執行計劃SQL
- 透過分析SQL語句的執行計劃最佳化SQL(總結)SQL
- Oracle AWR中常用到的幾個SQL語句OracleSQL
- Oracle AWR報告分析之–SQL ordered byOracleSQL
- 獲取oracle sql語句詳細些執行計劃OracleSQL
- 指令碼：獲得現有語句的執行計劃指令碼
- 查詢orcale執行的SQL語句記錄SQL
- 手工生成AWR執行期對比報告記錄
- [轉]透過分析SQL語句的執行計劃最佳化SQL(總結)SQL
- 獲取oracle正在處於等待狀態的sql語句的執行計劃的語句OracleSQL
- unix 系統捕獲sql語句和手工調整AWR的設定SQL
- Laravel 獲取執行的sql語句LaravelSQL
- MySQL——通過EXPLAIN分析SQL的執行計劃MySqlAI
- CoreData執行過程的sql語句SQL
- 剖析SQL語句的執行過程SQL
- Oracle SQL 語句的執行過程OracleSQL
- 一條SQL語句的執行計劃變化探究SQL
- 使用 EXPLAIN PLAN 獲取SQL語句執行計劃 （R0.1）AISQL
- 【最佳化】檢視歷史的執行計劃之檢視AWR報告
- MySQL 記錄所有執行了的 sql 語句MySql