如何固定执行计划

在遭遇执行计划不稳定或者执行计划错误的情况下，通过baseline来固定SQL执行计划以确保执行计划稳定性、提高性能。baseline是oracle 11G提供的稳固sql执行计划的功能，是spm功能的一部分。

步骤如下:

[html]  view plain copy

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;