igor-tkachev/bltoolkit — Issue #65: Bind parameters(command parameters) vs hard/plain query
Author: lvaleriu
Created: 2011-11-09 09:55:35+00:00
State: open

Hello
The Bltoolkit project uses statements with bind variables throughout the application. Bind variables are good for performance, as Oracle does not need to hard-parse the query every time it is executed. However, in some cases, bind variables lead to major performance problems. One of the more significant problems we faced was the issue of bind variables in SQL LIKE predicates. By default Bltoolkit uses bind variables in passing query criteria values entered by our application users. In many cases, where users used trailing wildcards (asterisk), Oracle 10g/11g Optimizer’s calculated query paths that were inefficient, resulting in queries that took from several minutes to an hour.

A more detailed explanation that oracle gave us is the following :
"yes the optimizer calculate the cardinalty and the cost when bind variables are used different than that when laterals are used.
this is limitation to CBO when using bind variables , see See Note 212809.1 Limitations of the Oracle Cost Based Optimizer

Also please note If your application uses bind variables against columns whose data is skewed and different plans are optimal depending on different
column data then you are going to have problems with some data and not with others depending on which value you use first in your queries.
There isn't really any way around this since there is not a single plan that is good for all values. The only real solution is to change the application to use literals for this kind of select where the value is critical. This would mean that cursors were not shared but also that the optimizer has a good chance of picking an acceptable access path for the query.

From the optimizer point of view we do not recommend the use of bind variables for queries where data values are critical to the selection of a good plan. Bind variables are recommended for situations where cursors are frequently executed with different column values and would otherwise cause shared pool fragmentation and contention.
"

So, would you give a thought for letting a user choose (or making a bltoolkit configuration option) whether Bltoolkit uses command parameters or generates hard/plain query to send to the database server?
I could do this by myself in the Bltoolkit fork, but I wanted to get your feeling about this, before passing to act.

Best regards,
Valeriu