cursor_sharing=similar or force 1

[Scunningham99]

Hi

We are on Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production on Solaris 8.

We are using cursor_sharing=EXACT.

I have been informed by an Oracle consultant that he has experienced at a few sites whereby he has seen an significate increase in performance when changing setting from default of EXACT to either SIMILAR or FORCE.

I am considering changing to SIMILAR so less re-parsing occurs on the server. I was wondering if anyone else has ghanged this parameter and seen any improvement.

Thanks in advance!!






Sy UK

 

[stefanhei]

If your application is well designed (= uses bindvariables when they should be used) you're likely to see a performance-degratiation instead of better results.
Also a datawarehouse would be killed by this setting.
cursor_sharing=similar or forced was "invented" for OLTP-applications which do not use bindvariables at all.
But you can use this parameter on session level (alter session set...) if for any reason bindvariables can't be used but would be of advantage (I've seen this in a java-app once). This way you can use the advantages where and when they are needed.

Stefan

 

[Scunningham99]

Thanks for reply!!

Yes I have read further on this and you are correct!

Our developers use bind variable already and not literals so I will leave cursor sharing as it is!!

I guess if we used literals i could have set to "similar" meaning similar statements only differeing in literals would have got re-used and saved on re-parsing.

Thanks for your advice!







Sy UK