ORACLE cursor_sharing参数导致函数索引失效

数据库版本为:11.2.0.4

当cursor_sharing为SIMILAR或者FORCE时候，将会导致函数索引失效；

表doc_order_header列有LASTSHIPMENTTIME得函数索引；

当时通过v$session视图发现，过滤条件to_char(h.lastshipmenttime,:"YYYY-MM-DD")=xxx被转换为

to_char(h.lastshipmenttime,:"SYS_B_0")=xxx，开始怀疑和oracle强制绑定变量有关；

 

SQL执行计划：

SQL_ID fv6z9b3xavdkx, child number 0
-------------------------------------
select count(*) from wms_user.doc_order_header h where
to_char(h.lastshipmenttime,:"SYS_B_0")=:"SYS_B_1"

Plan hash value: 3276129394

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1325K(100)| |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | TABLE ACCESS FULL| DOC_ORDER_HEADER | 229K| 1791K| 1325K (1)| 04:25:07 |
---------------------------------------------------------------------------------------

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

2 - filter(TO_CHAR(INTERNAL_FUNCTION("H"."LASTSHIPMENTTIME"),:SYS_B_0)=:SYS_B_1)

 

 

加入hint后/*+ CURSOR_SHARING_EXACT */

sys@JXDSP>select /*+ CURSOR_SHARING_EXACT */count(*) from wms_user.doc_order_header h
2 where to_char(h.lastshipmenttime,'YYYY-MM-DD')='2021-12-02';

COUNT(*)
----------
169513

Execution Plan
----------------------------------------------------------
Plan hash value: 3002984962

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 606 (1)| 00:00:08 |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
|* 2 | INDEX RANGE SCAN| IDX_LASTSHIPMENTTIME | 229K| 1567K| 606 (1)| 00:00:08 |
------------------------------------------------------------------------------------------

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

2 - access(TO_CHAR(INTERNAL_FUNCTION("LASTSHIPMENTTIME"),'YYYY-MM-DD')='2021-12-02')

 

 

查询mos后，确实是oracle一个bug；

通过hint和alter session set CURSOR_SHARING=exact可临时解决；

有对应得补丁可解决问题；