baseline固定SQL執行計劃

dawn009發表於2015-04-13
SQL

在遭遇執行計劃不穩定或者執行計劃錯誤的情況下，透過baseline來固定SQL執行計劃以確保執行計劃穩定性、提高效能。baseline是oracle 11G提供的穩固sql執行計劃的功能，是spm功能的一部分。

步驟如下:

  1. SQL> set linesize 1000  
  2. SQL> col index_name for a40  
  3. SQL> col COLUMN_NAME for a20  
  4. SQL> select index_name,COLUMN_NAME from dba_ind_columns where table_name='TEST_PART' and table_owner='TEST_DBA';  
  5.   
  6. INDEX_NAME                               COLUMN_NAME  
  7. ---------------------------------------- --------------------  
  8. IND_PART_DT                              DT  
  9.   
  10. SQL>   
  11. 此表由索引，可以測試走做引以及走全表掃描  
  12. SQL> set autotrace on explain  
  13. SQL> set pagesize 300  
  14. SQL> set  linesize 300  
  15. SQL> var name1 varchar2(10);  
  16. SQL> var name2 varchar2(100);  
  17. SQL> var name1 varchar2(100);     
  18. SQL> exec :name1 :='2012-09-21 8:05:55';   
  19.   
  20. PL/SQL procedure successfully completed.  
  21.   
  22. SQL> exec :name2 :='2012-09-21 8:06:05';  
  23.   
  24. PL/SQL procedure successfully completed.  
  25. SQL> select * from TEST_DBA.TEST_PART where dt>=to_date(:name1,'yyyy-mm-dd hh24:mi:ss') and  
  26.   2  dt<to_date(:name2,'yyyy-mm-dd hh24:mi:ss') ;  
  27.   
  28.         ID STR        DT  
  29. ---------- ---------- ---------  
  30.      29155 aaaaaaaaa  21-SEP-12  
  31.      29156 aaaaaaaaa  21-SEP-12  
  32.      29157 aaaaaaaaa  21-SEP-12  
  33.      29158 aaaaaaaaa  21-SEP-12  
  34.      29159 aaaaaaaaa  21-SEP-12  
  35.      29160 aaaaaaaaa  21-SEP-12  
  36.      29161 aaaaaaaaa  21-SEP-12  
  37.      29162 aaaaaaaaa  21-SEP-12  
  38.      29163 aaaaaaaaa  21-SEP-12  
  39.      29164 aaaaaaaaa  21-SEP-12  
  40.   
  41. 10 rows selected.  
  42.   
  43.   
  44. Execution Plan  
  45. ----------------------------------------------------------  
  46. Plan hash value: 2229598636  
  47.   
  48. -------------------------------------------------------------------------------------------------------------------  
  49. | Id  | Operation                           | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |  
  50. -------------------------------------------------------------------------------------------------------------------  
  51. |   0 | SELECT STATEMENT                    |             |   250 |  5750 |     5   (0)| 00:00:01 |       |       |  
  52. |*  1 |  FILTER                             |             |       |       |            |          |       |       |  
  53. |   2 |   PARTITION RANGE ITERATOR          |             |   250 |  5750 |     5   (0)| 00:00:01 |   KEY |   KEY |  
  54. |   3 |    TABLE ACCESS BY LOCAL INDEX ROWID| TEST_PART   |   250 |  5750 |     5   (0)| 00:00:01 |   KEY |   KEY |  
  55. |*  4 |     INDEX RANGE SCAN                | IND_PART_DT |   450 |       |     3   (0)| 00:00:01 |   KEY |   KEY |  
  56. -------------------------------------------------------------------------------------------------------------------  
  57.   
  58. Predicate Information (identified by operation id):  
  59. ---------------------------------------------------  
  60.   
  61.    1 - filter(TO_DATE(:NAME1,'yyyy-mm-dd hh24:mi:ss')<TO_DATE(:NAME2,'yyyy-mm-dd hh24:mi:ss'))  
  62.    4 - access("DT">=TO_DATE(:NAME1,'yyyy-mm-dd hh24:mi:ss') AND "DT"<TO_DATE(:NAME2,'yyyy-mm-dd  
  63.               hh24:mi:ss'))  
  64. 以上生成了索引的執行sql  
  65.   
  66. SQL> select /*+FULL(TEST_PART)*/ * from TEST_DBA.TEST_PART where dt>=to_date(:name1,'yyyy-mm-dd hh24:mi:ss') and  
  67.   2  dt<to_date(:name2,'yyyy-mm-dd hh24:mi:ss') ;  
  68.   
  69.         ID STR        DT  
  70. ---------- ---------- ---------  
  71.      29155 aaaaaaaaa  21-SEP-12  
  72.      29156 aaaaaaaaa  21-SEP-12  
  73.      29157 aaaaaaaaa  21-SEP-12  
  74.      29158 aaaaaaaaa  21-SEP-12  
  75.      29159 aaaaaaaaa  21-SEP-12  
  76.      29160 aaaaaaaaa  21-SEP-12  
  77.      29161 aaaaaaaaa  21-SEP-12  
  78.      29162 aaaaaaaaa  21-SEP-12  
  79.      29163 aaaaaaaaa  21-SEP-12  
  80.      29164 aaaaaaaaa  21-SEP-12  
  81.   
  82. 10 rows selected.  
  83.   
  84.   
  85. Execution Plan  
  86. ----------------------------------------------------------  
  87. Plan hash value: 1956636844  
  88.   
  89. -------------------------------------------------------------------------------------------------------  
  90. | Id  | Operation                 | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |  
  91. -------------------------------------------------------------------------------------------------------  
  92. |   0 | SELECT STATEMENT          |           |   250 |  5750 |   161   (3)| 00:00:02 |       |       |  
  93. |*  1 |  FILTER                   |           |       |       |            |          |       |       |  
  94. |   2 |   PARTITION RANGE ITERATOR|           |   250 |  5750 |   161   (3)| 00:00:02 |   KEY |   KEY |  
  95. |*  3 |    TABLE ACCESS FULL      | TEST_PART |   250 |  5750 |   161   (3)| 00:00:02 |   KEY |   KEY |  
  96. -------------------------------------------------------------------------------------------------------  
  97.   
  98. Predicate Information (identified by operation id):  
  99. ---------------------------------------------------  
  100.   
  101.    1 - filter(TO_DATE(:NAME1,'yyyy-mm-dd hh24:mi:ss')<TO_DATE(:NAME2,'yyyy-mm-dd hh24:mi:ss'))  
  102.    3 - filter("DT">=TO_DATE(:NAME1,'yyyy-mm-dd hh24:mi:ss') AND  
  103.               "DT"<TO_DATE(:NAME2,'yyyy-mm-dd hh24:mi:ss'))  
  104. 以上是走全表掃描的執行計劃  
  105. 現在要將該語句全部變為走全表掃描，也就是用全表掃描的執行計劃來繫結該語句  
  106. SQL> select sql_id from v$sql_plan where plan_hash_value=2229598636;   
  107.   
  108. SQL_ID  
  109. -------------  
  110. day4zwzuhxjnd  
  111. day4zwzuhxjnd  
  112. day4zwzuhxjnd  
  113. day4zwzuhxjnd  
  114. day4zwzuhxjnd  
  115.   
  116. SQL> select sql_id from v$sql_plan where plan_hash_value=1956636844;   
  117.   
  118. SQL_ID  
  119. -------------  
  120. fzs1wktabj9ny  
  121. fzs1wktabj9ny  
  122. fzs1wktabj9ny  
  123. fzs1wktabj9ny  
  124. 進行繫結  
  125. SQL> declare  
  126.   2  m_clob clob;  
  127.   3  begin  
  128.   4  select sql_fulltext  
  129.   5  into m_clob  
  130.   6  from v$sql  
  131.   7  where sql_id = 'day4zwzuhxjnd'  
  132.   8  and child_number = 0;  
  133.   9  dbms_output.put_line(m_clob);  
  134.  10  dbms_output.put_line(dbms_spm.load_plans_from_cursor_cache(  
  135.  11  sql_id          => 'fzs1wktabj9ny',  
  136.  12  plan_hash_value => 1956636844,  
  137.  13  sql_text        => m_clob,  
  138.  14  fixed           => 'YES',   
  139.  15  enabled         => 'YES'));  
  140.  16  end;  
  141.  17  /  
  142. PL/SQL procedure successfully completed.  
  143. 驗證  
  144. SQL> select * from TEST_DBA.TEST_PART where dt>=to_date(:name1,'yyyy-mm-dd hh24:mi:ss') and  
  145.   2  dt<to_date(:name2,'yyyy-mm-dd hh24:mi:ss') ;  
  146.   
  147.         ID STR        DT  
  148. ---------- ---------- ---------  
  149.      29155 aaaaaaaaa  21-SEP-12  
  150.      29156 aaaaaaaaa  21-SEP-12  
  151.      29157 aaaaaaaaa  21-SEP-12  
  152.      29158 aaaaaaaaa  21-SEP-12  
  153.      29159 aaaaaaaaa  21-SEP-12  
  154.      29160 aaaaaaaaa  21-SEP-12  
  155.      29161 aaaaaaaaa  21-SEP-12  
  156.      29162 aaaaaaaaa  21-SEP-12  
  157.      29163 aaaaaaaaa  21-SEP-12  
  158.      29164 aaaaaaaaa  21-SEP-12  
  159.   
  160. 10 rows selected.  
  161.   
  162.   
  163. Execution Plan  
  164. ----------------------------------------------------------  
  165. Plan hash value: 1956636844  
  166.   
  167. -------------------------------------------------------------------------------------------------------  
  168. | Id  | Operation                 | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |  
  169. -------------------------------------------------------------------------------------------------------  
  170. |   0 | SELECT STATEMENT          |           |   250 |  5750 |   161   (3)| 00:00:02 |       |       |  
  171. |*  1 |  FILTER                   |           |       |       |            |          |       |       |  
  172. |   2 |   PARTITION RANGE ITERATOR|           |   250 |  5750 |   161   (3)| 00:00:02 |   KEY |   KEY |  
  173. |*  3 |    TABLE ACCESS FULL      | TEST_PART |   250 |  5750 |   161   (3)| 00:00:02 |   KEY |   KEY |  
  174. -------------------------------------------------------------------------------------------------------  
  175.   
  176. Predicate Information (identified by operation id):  
  177. ---------------------------------------------------  
  178.   
  179.    1 - filter(TO_DATE(:NAME1,'yyyy-mm-dd hh24:mi:ss')<TO_DATE(:NAME2,'yyyy-mm-dd hh24:mi:ss'))  
  180.    3 - filter("DT">=TO_DATE(:NAME1,'yyyy-mm-dd hh24:mi:ss') AND  
  181.               "DT"<TO_DATE(:NAME2,'yyyy-mm-dd hh24:mi:ss'))  
  182.   
  183. Note  
  184. -----  
  185.    - SQL plan baseline "SQL_PLAN_c28fvkn3sbtbg70e51298" used for this statement  
  186.   
  187. SQL>   
  188.   
  189. 檢視生成的sql baseline資訊  
  190. SQL> select signature,sql_handle,plan_name,origin,enabled,accepted,fixed,autopurge    
  191.   2     from dba_sql_plan_baselines where sql_text like '%select * from TEST_DBA.TEST_PART%';  
  192.   
  193.  SIGNATURE SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENA ACC FIX AUT  
  194. ---------- ------------------------------ ------------------------------ -------------- --- --- --- ---  
  195. 1.3917E+19 SQL_c121db950785e56f           SQL_PLAN_c28fvkn3sbtbg70e51298 MANUAL-LOAD    YES YES YES YES  
  196.   
  197. SQL>   
  198. 如果不想要這個繫結計劃了，則可以刪除它  
  199. declare  
  200. l_pls number;  
  201. begin  
  202. l_pls := DBMS_SPM.DROP_SQL_PLAN_BASELINE(  
  203. sql_handle => 'SQL_c121db950785e56f',  
  204. plan_name  => 'SQL_PLAN_c28fvkn3sbtbg70e51298'  
  205. );  
  206. end;  

對於dbms_spm.load_plans_from_cursor_cache的說明

既然是from cursor的話那麼要保證在執行過程中sql還是在記憶體裡的不能被刷出去

declare
m_clob clob;
begin
select sql_fulltext
into m_clob
from v$sql
where sql_id = 'day4zwzuhxjnd'————–原始sql的 sql_id
and child_number = 0;—————–為了讓sql只返回一行，也可以rownum=1代替
dbms_output.put_line(m_clob);
dbms_output.put_line(dbms_spm.load_plans_from_cursor_cache(
sql_id          => 'fzs1wktabj9ny',————HINT SQL_ID
plan_hash_value => 1956636844,——————-HINT PLAN_HASH_VALUE
sql_text        => m_clob,————————-原始SQL文字
fixed           => 'YES', ———————禁止演化baseline
enabled         => 'YES'));------生效
end;


出處:http://blog.csdn.net/aaaaaaaa2000/article/details/9086111

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

相關文章