Bind variable peeking doesn't work

I also tried it and saw that does not solve the issue, it peeks binds on second call if first call is oci_describe_only. If second call is also again oci_describe_only,may be not healing the issue. Because according to the fix_control definition of 9630092;
"allow peek on 2nd cursor if 1st cursor was a describe cursor"
Looks like too much related with usage of oci describe. But providing an option to cancel this, will be an adequate alternative solution.
You may dig down the issue with the underlying coding structure.
Good luck.

Regards.

Toad is running first a "Describe Only" call, then after that a regular execution of the SQL. Oracle is sticking 2 rows in V$SQL, with the first having IS_BIND_SENSITIVE=N and the 2nd having IS_BIND_SENSITIVE=Y.

But the problem is, if the SQL is something along the lines of

Select * from test where pk= :pkval

...as in your example, I am seeing a full table scan rather than an index scan. The only solution to this that I can find is to avoid the "Describe Only". It would be nice if Describe Only didn't cause so much trouble, but I don't know of a solution for that.

@dinch.osman

I forgot to mention - if you use Toad WITHOUT an Oracle client, the problem does not occur.

To do that, uncheck this box in Toad's login window before making your first connection:

-John

1 Like

Thanks John, it works.

hi, I was experimenting with this Oracle feature of bind variable peeking and was wondering if there was going to be a fix to the problem soon. I'm running the following version of TOAD.
17.0.353.2906

Hi Rob.

If you update to the latest version (24.2), you can set Options -> Oracle -> General -> OCI Array Buffer Size to "Manual". Then Toad won't run the OCI describe before your SQL, and bind variable peeking will work.

There are some bugs with the OCI Auto/manual setting in 17.0 and 17.1, thus the need to update Toad if you want to change this setting.

-John