This content has been marked as final. Show 6 replies
I am interested if anyone has any tips or knows of anything to watch out for in implementing cursor_sharing=force.
I.e. bugs we could hit, potential performance degradations, any other lessons or tips that you may be able to share.
Both of these experts, Jonathan Lewis and Charles Hooper, have plenty to say about it.,
And be sure to see Jonathan's reply date Oct 13, 2009 9:44AM in this thread
Need huge shared pool size
There are (at least) three different issues that conspire to cause you problems.
a) Literal string SQL is typically non-sharable SQL - but can get the best execution plans each time, at a cost in optimisation overheads (CPU, latching, memory).
b) If you set cursor_sharing = force, statement which differ only in literal values become "the same" because of bind variable substitution into the text. But this means the plan for every execution of the statement will be the same (until something flushes the current plan from memory and a new plan appears). With bind variables in place, Oracle uses bind variable peeking to find that values that should be used for the first optimisation. This means that at random intervals you may get a plan which was great for the first person and terrible for everyone else. Typical causes of extreme variation: queries that use widely varying date ranges, queries against columns on which you have built histograms, queries where the parition key of a partitioned object. Typical ymptoms: wild variations in performance, but only a very small number of copies (often only one) of substituted SQL statements in the library cache.
c) If you set cursor_sharing = similar, Oracle does bind variable substitution - then checks to see if any of the substitutions are for range-based predicates (e.g. between two date values), are predicates on columns with histograms or partitioning keys. If any of these are seen then Oracle re-optimises the query if it hasn't got a plan for the same set of values already. Typical symptoms: large numbers of copies of substituted SQL statements in the library cache.
You've tried cursor_sharing = exact - but did you get rid of histograms at the same time. If not this could explain performance problems (although this combination should not cause an extreme growth in library cache usage).
The replies so far are ignoring a criticcal 11g new feature: Adaptive Cursor Sharing. ACS means that theorectically you can set cursor_sharing=force, and Oracle will develop (if necessary) multiple plans that are suitable for different bind variable values, and switch between them for each execution. ACS really does work. It relies on repeated executions, and so can take a while to stabilize. The interaction with SQL plan baselining (if you've enabled it) can complicate things.
I have found that in some circumstances, particularly with 11.1, ACS can result in an excessive number of child cursors; it is better in 11.2. But overall, I would say go for cursor_sharing=force, and see how the ACS mechanism handles it. Possibly, very well.
The replies so far are ignoring a criticcal 11g new feature: Adaptive Cursor Sharing
Charles Hooper's article mentions adaptive and also refers to this article by the Oracle Optimizer group