RE: Shared Pool Tuning

Beware that there are several known bugs with the CURSOR_SHARING=[FORCE|SIMILAR] init.ora parameter -- some of them severe, including incorrect results from some queries. Be sure to do your homework (*extensive* searching of MetaLink) first! I wouldn't use it before 8.1.7.4 nor in 9i before 9.2 because of the severity of some of those bugs For an example, see my recent posts titled "Trouble with multiple versions of same statement in V$SQL". We ended up hitting ORA-4031s and I think they can be directly attributed to Oracle BUG 3406977. I've got a query against V$SQL and V$SQL_SHARED_CURSOR that will show the effects on the shared pool from that bug if anyone wants.

If you can, use binds. If you can't (like Ron and like us), make sure to investigate CS=F|S first to determine if the potential downsides offset the benefits.

Time to opatch!

Rich

-----Original Message-----

Sent: Tuesday, September 28, 2004 7:28 PM Subject: RE: Shared Pool Tuning

Ron,

        You can set cursor_sharing = force in the session or through a logon trigger. You should be able to identify this job by the machine it's coming from. That would be less impactive as some releases have issues with this and I didn't see what release you're on.

        Also, if you're still flushing the shared pool you might consider pining all your sequences beforehand so there's no unnecessary gaps.

        Larry

-----Original Message-----

Sent: Tuesday, September 28, 2004 3:45 PM Subject: Re: Shared Pool Tuning

Have you tried cursor_sharing = force?

--Terry

--

http://www.freelists.org/webpage/oracle-l Received on Wed Sep 29 2004 - 08:54:59 CDT