Oracle - SPM固定执行计划(一)

生产中偶尔会碰到一些sql，有多种执行计划，其中部分情况是统计信息过旧造成的，重新收集下统计信息就行了。但是有些时候重新收集统计信息也解决不了问题，而开发又在嗷嗷叫，没时间让你去慢慢分析原因的时候，这时临时的解决办法是通过spm去固定一个正确的执行计划，等找到真正原因后再解除该spm。

二、解决办法 1. 通过dbms_xplan.display_cursor查看指定sql都有哪些执行计划

SQL> select * from table(dbms_xplan.display_cursor('&sql_id',null,'TYPICAL PEEKED_BINDS')); 

Enter value for sql_id: 66a4184u0t6hn old 1: select * from table(dbms_xplan.display_cursor('&sql_id',null,'TYPICAL PEEKED_BINDS')) new 1: select * from table(dbms_xplan.display_cursor('66a4184u0t6hn',null,'TYPICAL PEEKED_BINDS')) SQL_ID 66a4184u0t6hn, child number 0 ------------------------------------- select /*for_test*/ * from test1 where object_id = 1 Plan hash value: 4122059633 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 693 (100)| | |* 1 | TABLE ACCESS FULL| TEST1 | 173K| 15M| 693 (1)| 00:00:09 | --------------------------------------------------------------------------- SQL_ID 66a4184u0t6hn, child number 1 ------------------------------------- select /*for_test*/ * from test1 where object_id = 1 Plan hash value: 2214001748 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID| TEST1 | 11 | 1056 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_TEST1 | | | 1 (0)| 00:00:01 | -----------------------------------------------------------------------------------------