oracle扩容完不生效,SQL Patch绑定后生效，执行计划不生效

1、Oracle提供的示例如下:

SQL> select * from v$version where rownum<2;

BANNER                                                                               CON_ID

-------------------------------------------------------------------------------- ----------

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production              0

SQL> create table t01 as select * from dba_objects;

SQL> create index ind_t01_object_id on t01(object_id);

SQL> analyze table t01 compute statistics;

SQL> BEGIN

SYS.DBMS_SQLDIAG_INTERNAL.i_create_patch(

sql_text  => 'select * from t01 where object_id between 10000 and 30000',

hint_text => 'INDEX(T01,IND_T01_OBJECT_ID)',

name      => 't01_sql_patch');

END;

/

These manual SQL patches can be removed in the normal manner.

SQL> BEGIN

DBMS_SQLDIAG.drop_sql_patch(name =>'t01_sql_patch');

END;

/

SQL> select * from t01 where object_id between 10000 and 30000;

19581 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 3295674804

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      | 19706 |  2001K|   431   (1)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| T01  | 19706 |  2001K|   431   (1)| 00:00:01 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("OBJECT_ID"<=30000 AND "OBJECT_ID">=10000)

Note

-----

- SQL patch "t01_sql_patch" used for this statement

Statistics

----------------------------------------------------------

458  recursive calls

0  db block gets

3213  consistent gets

1  physical reads

0  redo size

1058572  bytes sent via SQL*Net to client

14906  bytes received via SQL*Net from client

1307  SQL*Net roundtrips to/from client

36  sorts (memory)

0  sorts (disk)

19581  rows processed

使用DBMS_XPLAN.DISPLAY_CURSOR查看该SQL对应的执行计划还是全表扫描，就是没有想要的索引范围扫描，HINT也是对的

SQL> select /*+INDEX(T01,IND_T01_OBJECT_ID)*/* from t01 where object_id between 10000 and 30000;

19624 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 95018110

---------------------------------------------------------------------------------------------------------

| Id  | Operation                           | Name              | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                    |                   | 19825 |  2013K|   596   (1)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T01               | 19825 |  2013K|   596   (1)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN                  | IND_T01_OBJECT_ID | 19825 |       |    45   (0)| 00:00:01 |

---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("OBJECT_ID">=10000 AND "OBJECT_ID"<=30000)

Statistics

----------------------------------------------------------

1  recursive calls

0  db block gets

3173  consistent gets

0  physical reads

0  redo size

2573622  bytes sent via SQL*Net to client

14939  bytes received via SQL*Net from client

1310  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

19624  rows processed