ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > baseline固定SQL执行计划

baseline固定SQL执行计划

Oracle 作者：dawn009 时间：2015-04-13 17:36:03 0 删除 编辑

在遭遇执行计划不稳定或者执行计划错误的情况下，通过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博客 ” ，链接：https://blog.itpub.net/29119536/viewspace-1572204/ ，如需转载，请注明出处，否则将追究法律责任。

1
1
上一篇： ORA-7445(koiogex)错误
下一篇： Asmcmd 详解
请登录后发表评论 登录
全部评论
dawn009

注册时间：2013-08-15

  • 博文量
    595
  • 访问量
    4988548

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593