利用statspack來獲取生成環境中top SQL及其執行計劃

作者：

來源：

在一些特殊情況下，生產環境中top SQL會讓DBA捉摸不定。比如一個由複雜分支條件所生成的動態語句；第三方軟體或者程式碼本身對會話環境進行了修改，導致無法正確重現問題語句的查詢計劃；語句過長導致無法獲取正確的全部語句，等等。這時，我們可以考慮設定statspack為更高階別（預設級別為5），以獲取相關語句的詳細資訊。具體過程如下： 獲取級別6的statspack的快照：   SQL> exec statspack.SNAP(i_snap_level => 6);   N分鐘後….   SQL> exec statspack.SNAP(i_snap_level => 6);   獲取statspack報告   SQL> @?/rdbms/admin/spreport.sql   ... … 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.       Listing all Completed Snapshots                                                          Snap Instance     DB Name        Snap Id   Snap Started    Level Comment ------------ ------------ --------- ----------------- ----- -------------------- edgar        EDGAR                1 28 Dec 2005 15:32     5                                    11 03 Jan 2006 09:54     5                                  12 03 Jan 2006 09:54     5                                    21 11 May 2007 13:33     6                                  31 11 May 2007 13:34     6   在選取快照時，可以看到我們剛才生成的兩個快照級別為6。   檢視報告，可以看到報告內容比我們普通的statspack報告更加詳細（這些不是本文重點，不做具體解釋）。仔細觀察top SQL部分，你會發現報告中多出了一列“Old Hash Value”，找到你需要的語句，記下它的這個hash值。         CPU                  CPU per             Elapsd                   Old   Time (s)   Executions  Exec (s)  %Total   Time (s)    Buffer Gets  Hash Value ---------- ------------ ---------- ------ ---------- --------------- ----------      13.51           55       0.25   64.2      13.69          13,528 2547576380 Module: SQL*Plus select a, b from ttt where b like 'aaa%'   … …   我們用Oracle提供的另一個statspack 報告指令碼來生成這條SQL的報告：   SQL> @?/rdbms/admin/sprepsql.sql   … … Listing all Completed Snapshots                                  Snap                    Snap Instance     DB Name             Id   Snap Started    Level Comment ------------ ------------ --------- ----------------- ----- -------------------- edgar        EDGAR                1 28 Dec 2005 15:32     5                                    11 03 Jan 2006 09:54     5                                  12 03 Jan 2006 09:54     5                                    21 11 May 2007 13:33     6                                  31 11 May 2007 13:34     6   … …   還是選擇剛才的兩個快照：   Specify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter value for begin_snap: 21 Begin Snapshot Id specified: 21   Enter value for end_snap: 31 End   Snapshot Id specified: 31   這兒要求輸入hash值，把剛才得到的hash值輸入：   Specify the old (i.e. pre-10g) Hash Value ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter value for hash_value: 2547576380     生成了SQL報告，看下報告，裡面有SQL的完整語句和它的查詢計劃。     SQL Text ~~~~~~~~ select a, b from ttt where b like 'aaa%'   Plans in shared pool between Begin and End Snap Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Shows the Execution Plans found in the shared pool between the begin and end snapshots specified.  The values for Rows, Bytes and Cost shown below are those which existed at the time the first-ever snapshot captured this plan - these values often change over time, and so may not be indicative of current values -> Rows indicates Cardinality, PHV is Plan Hash Value -> ordered by Plan Hash Value   -------------------------------------------------------------------------------- | Operation                      | PHV/Object Name     |  Rows | Bytes|   Cost | -------------------------------------------------------------------------------- |SELECT STATEMENT                |     2 |   60    |      |     56 | |  TABLE ACCESS FULL            |                     |     2 |   60 |       56 | --------------------------------------------------------------------------------