使用spm繫結執行計劃來線上優化資料庫

客戶的一個資料庫mytest出現非常緩慢，cpu使用相當高，資料庫wait event顯示大量read by other session、latch: cache buffers chains和read by other session事件
查詢等待事件，有幾個比較主要的等待事件如下：
SQL> select sql_id,event,count(1) counts from v$session group by sql_id,event order by 3 desc;
SQL_ID            EVENT                      COUNTS
-------------     -------------------------- ------
353205q5fk492     read by other session          25
353205q5fk492     latch: cache buffers chains    16
353205q5fk492     db file sequential read        14

問題出在353205q5fk492語句上。仔細檢視這條語句，有兩個執行計劃。
檢視資料庫sql使用情況，發現採用了一條效率並不高的執行計劃，如下：
Plan hash value: 3282783069
--------------------------------------------------------------------------------
| Id  | Operation                     | Name          | Rows  | Bytes | Cost (%C
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |               |       |       |  1154 (1
|   1 |  COUNT STOPKEY                |               |       |       |
|   2 |   INLIST ITERATOR             |               |       |       |
|   3 |    TABLE ACCESS BY INDEX ROWID| xxxxxxxxx     |     1 |   537 |  1153
|   4 |     INDEX RANGE SCAN          | xxxxxxxxx~id1 | 27479 |       |    14
--------------------------------------------------------------------------------
檢視語句的情況如下，當前採用的是這一條執行計劃。
SQL> select address,hash_value from v$sqlarea where sql_id='353205q5fk492';
ADDRESS          HASH_VALUE
---------------- ----------
000000235FFBE9B0  3282783069

所以為了緊急處理這個問題，我們採用如下方法進行處理
1、首先把這個執行計劃踢出shared_pool
SQL> exec dbms_shared_pool.purge('000000235FFBE9B0,3282783069','C')
2、然後徵求客戶意見後，採用這個執行計劃的語句進行查殺。
3、針對這條語句中where條件進行了重新統計分析

經過上面三步處理後，迫使該sql重新解析，解析為好的執行計劃，然後使用spm成功固定其執行計劃，固定的方法如下：
SQL> var plans_loaded number
SQL> exec :plans_loaded:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'353205q5fk492',plan_hash_value=>3566569303)

最後後檢查新最後採取的執行計劃
SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines
SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENA ACC SQL_TEXT
------------------------------ ------------------------------ -------------- --- --- ------------------------------------
SQL_e96f1f0834698c71           SQL_PLAN_fkvsz10u6m33j21b3b9d1 MANUAL-LOAD    YES YES SELECT xxxxxxxxx

Plan hash value: 3566569303
----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |       |       |     1 (100)|          |
|   1 |  COUNT STOPKEY               |               |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| xxxxxxxxx     |     1 |   537 |     1   (0)| 00:00:01 |
|   3 |    INDEX RANGE SCAN          | xxxxxxxxx~id2 |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

最後檢查，發現已經使用繫結的執行計了
SQL> select hash_value, sql_id,child_number,plan_hash_value,executions,round(buffer_gets/executions,0) buffergets_per,SQL_PLAN_BASELINE ,last_load_time,last_active_time from v$sql where sql_id='353205q5fk492';
HASH_VALUE SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BUFFERGETS_PER SQL_PLAN_BASELINE              LAST_LOAD_TIME                         LAST_ACTIVE_TIME
---------- ------------- ------------ --------------- ---------- -------------- ------------------------------ -------------------------------------- -------------------
 3566569303 353205q5fk492           12      1263503027        284            573 SQL_PLAN_fkvsz10u6m33j21b3b9d1 2015-03-06/17:21:20                   2015-03-06/17:21:20  --表明用上spm固定了好的執行計劃。

通過這樣處理後，發現效率提升非常明顯，cpu空閒率直接上升到80%以上了。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29371470/viewspace-2121422/，如需轉載，請註明出處，否則將追究法律責任。