CURSOR_SHARING=SIMILAR

最新推荐文章于 2018-07-31 09:55:44 发布

转载最新推荐文章于 2018-07-31 09:55:44 发布 · 143 阅读

本文探讨了在使用CURSOR_SHARING=SIMILAR时，SQL查询中bind值的安全性评估机制，包括特定操作符和列直方图的影响。通过实例展示了如何检查并理解这些安全规则。

CURSOR_SHARING=SIMILAR

With CURSOR_SHARING=SIMILAR whenever the optimizer looks at a replaced bind value to make a decision then that bind is checked to see if it should be considered unsafe. The check made is :

Is the operator NEITHER of ‘=’ or ‘!=’
OR
Are there Column Histograms present on the column.

If either of these are true then the bind is deemed to be unsafe and a new cursor will be created (So binds used in non equality predicates (eg >, =, <=, LIKE) are unsafe). To check for whether a bind is considered unsafe see

SQL> select count(*) from dba_objects where object_id>3;

COUNT(*)
----------
50363

SQL> select count(*) from dba_objects where object_id>5;

COUNT(*)
----------
50361

SQL> select count(*) from dba_objects where object_id>6;

COUNT(*)
----------
50360

SQL> select count(*) from dba_objects where object_id>4;

COUNT(*)
----------
50362

SQL> select sql_id,child_number from v$sql where sql_text like '%dba_objects%';

SQL_ID        CHILD_NUMBER
------------- ------------
bg5mpjkmsjg7h            0
bg5mpjkmsjg7h            1
bg5mpjkmsjg7h            2
bg5mpjkmsjg7h            3
bg5mpjkmsjg7h            4
bg5mpjkmsjg7h            5
bg5mpjkmsjg7h            6
bg5mpjkmsjg7h            7
2r3tjryp06xfx            0:

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22034023/viewspace-710434/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22034023/viewspace-710434/