Re: case insensitvity and cursor sharing

On 08/10/2016 03:37 PM, ed lewis wrote:
> Hello,
> We are currently using a third party app, which
> uses an Oracle database configured for "case-insensitivity".
> The vendor *usually* recommends that we set
> "cursor_sharing" to "force" when using the app.
> With the "case-insensitive" database, they are saying
> the "cursor_sharing" must be set to "exact", as per Oracle.
> I've been unable to find where that is documented.
> Must "cursor_sharing" be set to "exact" in this case ?
> Appreciate your help.
> Oracle 11.2.0.3
> Ec

Hi Ed,

I agree that there is an amazing lack of case sensitivity with today's youth. As per setting cursor_sharing to "force", it has nothing to do with case sensitivity. Setting cursor_sharing to "force" simply replaces all the constants on the right side of the equal sign with the system generated bind variables. Depending on your version, there are gazillion bugs related to the forced cursor bondage. As of Oracle 11G, you can use bind aware cursors. There is an excellent article about that, by Maria Colgan, here:

https://blogs.oracle.com/optimizer/entry/how_do_i_force_a

The problem with forced cursor bondage is that optimizer usually cannot use statistics, since it doesn't have the right values. Optimizer has to rely on bind variable peeking instead. The greater number of bind variables you have, the greater is the chance that one of them will not be a "typical representative" of the variables used during so called "normal operations" and that the generated plan will simply be wrong. Vendors using forced cursor bondage also tend to use things like optimizer_index_cost_adj, which is also a bad idea. That is, essentially, turning the clock back, to the times of the rule based optimizer and its 20 access paths, neatly ranked in a table.

The mere fact that you turned to this list with such question tells me that there is a trouble in paradise. Forcing the cursor sharing in all cases, by replacing constants with system generated bind variables, usually creates more problems than it solves. Depending on version, it also significantly increases your chances of having fun with ORA-00600 and ORA-07445. In other words, CURSOR_SHARE=FORCE is big no-no. It's usually used by the application developers who are still stuck with Oracle 9i, along with optimizer_index_cost_adj.

Regards

-- 
Mladen Gogala
Oracle DBA
Tel: (347) 321-1217


--
http://www.freelists.org/webpage/oracle-l