Oracle Performance Tuning: How Cursor Sharing Can Wreck Performance
It is fairly common to have an application neglect to use bind variables. This is not a terrible disaster, but creates problems if the application generates a lot of sql.
A common solution to this (assuming the app can't/won't be changed) is to change the init.ora parameter, Cursor_Sharing. It is changed to either FORCE or SIMILAR.
Generally, this change works well, but there can be unexpected (and bad) results. Of course, execution plans will likely change on a small percentage of sql, but there are bigger problems.
Function IndexesSuppose the application performance is dependent on certain indexes. For certain function indexes, switching Cursor_Sharing to force binds makes it impossible to use the indexes!
Here is an actual, real-life example. There is a functional index defined as:
Ltrim (Colx, 1)
The sql has a where clause that of course looks like
where Ltrim (Colx,1) = '123'
With the cursor sharing change, this code changes to:
where Ltrim (Colx,:BIND) = '123'
Now, the functional index cannot be used. That index requires that '1' be explicitly present.
FYI: Our workaround was a login trigger for the user who ran this section of the code. We changed Cursor_Sharing just for that one user.
