Re: CURSOR_SHARING=SIMILAR

>Is anyone using the CURSOR_SHARING=SIMILAR out there? 

Leng

A former client used CURSOR_SHARING=SIMILAR as a band-aid for a system that had 
migrated from an ISAM method to Oracle. The ISAM to relational bridge (provided 
by a ven-duh) generated statements with never a bind variable. We were running 
9.2.0.5 on RAC.

Under (LoadRunner test) load, we still tended to run out of memory (ORA-4301); 
although the cursors are recognised as similar, you can still thrash the shared 
pool with all the different children of each cursor. After a while (an hour or 
two of peak load for us) the memory fragmentation inevitably bites you in the 
bottom. Oracle has to work pretty hard to manage everything. BTW because of the 
nature of the bridge, these were all simple single table queries - I suspect 
things would be worse with more complex SQL...

In the end we bit the bullet and fixed the bridge (thanks Phil and Ade, you 
know who you are), adding bind variables and moving back to CS=EXACT. Much 
better. We went from a guaranteed 4031 after a couple of hours to running 24 
hour soak tests happily.

Some more background here: 
http://preferisco.blogspot.com/2006/04/how-many-binds-does-it-take-to-save.html.
 That was 9.2.0.5 - your mileage may vary on 9.2.0.8. But definitely try 
beating up your developers and getting them to do the right thing, rather than 
working around their poor implementation for the rest of your application's 
natural life.

Regards Nigel

Other related posts: