[20250409]执行计划出现OPT_PARAM('_optim_peek_user_binds' 'false')的相关问题.txt

[20250409]执行计划出现OPT_PARAM('_optim_peek_user_binds' 'false')的相关问题 --//生产系统一些sql执行计划outline出现OPT_PARAM('_optim_peek_user_binds' 'false')，导致选择不合理的执行计划。 1.环境： > @ ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 2.问题探究： > select * from gV$SQL_OPTIMIZER_ENV where name ='_optim_peek_user_binds'; no rows selected > select * from gV$Ses_OPTIMIZER_ENV where name ='_optim_peek_user_binds'; no rows selected > select * from gV$Sys_OPTIMIZER_ENV where name ='_optim_peek_user_binds'; no rows selected --//没有任何该参数的设置，为什么许多sql语句的执行计划出现  OPT_PARAM('_optim_peek_user_binds' 'false')。 --//而且没有任何规律可言，有一些是w3wp.exe，pxxxx.exe(我们应用的执行程序),我主要看了这2个应用程序。 select distinct sql_id from ( SELECT *   FROM V$SQL_PLAN  WHERE     other_xml IS NOT NULL        AND other_xml LIKE '%_optim_peek_user_binds%') SELECT DISTINCT sql_id   FROM V$SQL_PLAN p  WHERE DBMS_LOB.SUBSTR (other_xml, 4000) LIKE '%_optim_peek_user_binds%'; --//我使用以上sql语句查询，发现几乎生产系统数据库都出现这样的情况，注在存在绑定变量的情况下有一些sql语句的执行计划加入该 --//提示有一些没有该提示，这就有点奇怪了。首先排除了安装与数据库维护人员人为设置的可能性。这样让我想起以前toad 12.x版本 --//遇到的问题,这个问题应该在网上早有人遇到，上网查询还找到一些链接： https://dincosman.com/2023/12/10/no-bind-peeking-in-toad/ https://forums.toadworld.com/t/bind-variable-peeking-doesnt-work/33411/1 https://jolliffehk.wordpress.com/2016/05/05/bind-variable-peeking-problems-via-oci/ --//来自链接https://dincosman.com/2023/12/10/no-bind-peeking-in-toad/，设置automatic或者manual效果有一样。 I have revived old thread and thank fully, John Dorlon  replied my post and investigated the issue. As John says, TOAD use an OCI "Describe" command (as documented here) before query execution to learn the datatypes and there is a bug (Bug 9630092) related with OCI_DESCRIBE_ONLY, also Patrick Jolliffe has a blog post(Bind Variable Peeking Problems via OCI) about it, bad news is that the solution offered with bug fix ("_fix_control"='9630092:ON') did not help the issue. Here is the good news, John made a change so that TOAD don't do the describe if OCI Array Buffers size option changed to "Manual". (Under View menu, Toad Options, Oracle, General) and it is available with Toad Beta 17.1.next, To Be Released 11-Dec-2023. --//我也试着尝试"_fix_control"='9630092:ON'; SYS@book> @ fix 9630092 SYS@book> @ pr ============================== SESSION_ID                    : 275 BUGNO                         : 9630092 VALUE                         : 0 SQL_FEATURE                   : QKSFM_COMPILATION_9630092 DESCRIPTION                   : allow peek on 2nd cursor if 1st cursor was a describe cursor OPTIMIZER_FEATURE_ENABLE      : EVENT                         : 0 IS_DEFAULT                    : 1 CON_ID                        : 1 PL/SQL procedure successfully completed. --//缺省等于0， ALTER SYSTEM set "_fix_control"='9630092:ON'; --//测试也一样，仔细看上面的2个信息，似乎都是讲oci describe相关信息，不熟悉这方面知识。 --//也就是讲目前也许仅仅toad 17.1的版本解决这个问题,无法测试. --//我也尝试跟踪一个会话的打开光标，查询v$open_cursor视图，发现不理解的是一些语句(存在绑定变量的情况下)有 --//OPT_PARAM('_optim_peek_user_binds' 'false')提示，而一些没有，根本不知道为什么？ --//即使在会话级别设置cursor_sharing=exact的情况下也一样，注：应用存在许多使用文字变量的情况，许多应用在会话级别设置 --//cursor_sharing=force。 --//换一句话讲我猜测问题应该出现在OCI的驱动上，而非涉及toad，甚至一些应用程序都受影响，感觉应该有团队已经遇到类似问题，我上网 --//查询在国内根本找不到相关链接，不知道我的判断是否正确，我仔细翻看我的工作笔记，发现在toad最早遇到这个问题是2019/12/13. --//而应用程序最早遇到是2020/12/09,当时没注意细节问题。 --//实际上最近一次生产系统1个实例挂起就与这个问题相关，同事加提示规避这个问题，有可能大量的相关问题在冰山之下，团队同事 --//没人继续追踪这个问题。 --//那位知道真正的问题出在哪里，如何彻底解决。