简单讨论在11G，10G中如何稳定SQL执行计划 - 数据库编程

; SQL> EXECUTE :cnt :=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id=>'auwza0aq10mk0',plan_hash_value=>1624320650); PL/SQL procedure successfully completed. SQL> SELECT sql_handle, sql_text, plan_name, enabled,accepted FROM dba_sql_plan_baselines; SQL_HANDLE SQL_TEXT PLAN_NAME ENA ACC ------------------------------ -------------------------------------------------------------------------------- ------------------------------ --- --- SQL_32507237f9e5cfe6 select * from haha where a='10000' SQL_PLAN_34n3k6zwybmz6dd4bd292 YES YES 现在，我们再从当前环境下执行该SQL

SQL> select * from haha where a='10000';

A					B
------------------------------ ----------
10000				    10000

SQL> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	auwza0aq10mk0, child number 3
-------------------------------------
select * from haha where a='10000'

Plan hash value: 1624320650

----------------------------------------------------------------------------------------
| Id  | Operation		    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	    |	       |       |       | 14685 (100)|	       |
|   1 |  TABLE ACCESS BY INDEX ROWID| HAHA     |     1 |    11 | 14685	 (1)| 00:02:57 |
|*  2 |   INDEX RANGE SCAN	    | HAHA_IDX |     1 |       |   178	 (0)| 00:00:03 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A"='10000')

Note
-----
   - SQL plan baseline SQL_PLAN_34n3k6zwybmz6dd4bd292 used for this statement

执行计划已经变为了正常的索引扫描，注意Note，说明有一个SQL_BASELINE在使用。

to be continue--

简单讨论在11G，10G中如何稳定SQL执行计划(三)