>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
--
http://www.freelists.org/webpage/oracle-l
Received on Tue Feb 06 2007 - 03:42:03 CST
