July 3, 2011 (Modified July 5, 2011)
Define Yah-but: Almost like yeah but, but with one missing letter.
While reading the alpha edition of the “Oracle Database 11g Performance Tuning Recipes” book, specifically recipe 5-19, I found a couple of interesting comments about the CURSOR_SHARING parameter, specifically the FORCE and SIMILAR parameter values. The quotes (as usual, trying to minimize the amount of material that is quoted without losing the context of the quotes):
“The CURSOR_SHARING parameter is one of the few Oracle Silver bullets that’ll improve database performance immediately by eliminating latch contention. Use it with confidence when dealing with library cache latch contention.”
“Thus, the SIMILAR setting seems a safer way to go about forcing the database to use bind variables instead of literals. Although there are some concerns about the safety of setting of the CURSOR_SHARING parameter to FORCE, we haven’t seen any real issues with using this setting.”
I would have expected to find the phrase silver bullet in another book, such as the book “Expert Oracle Database Architecture: Oracle Database Programming 9i, 10g, and 11g Techniques and Solutions, Second Edition“, specifically page 48:
“Note: There are no silver bullets, none. If there were any, they would be the default behavior and you would never hear about them.”
Or possibly in a presentation titled “Playing Russian Roulette with Silver Bullets”, specifically Page 14 (slide 27).
The phrase might also appear in the book “Oracle Tuning the Definitive Reference, Second Edition” specifically page 105 (the phrase silver bullet actually appears on page 102, this quote is related to the second half of the quote at the start of this article):
“Note that in Oracle 11g, cursor_sharing=similar has been debugged, and it is now possible to use cursor sharing with bind variable peeking.”
Strangely, the phrase silver bullet might also appear on a page found in the toadworld.com domain (Edit: July 5, 2011:
I suspect that the same advice might also be found in that author’s recent book Per the book author’s comment attached below, this assumption is incorrect):
“The CURSOR_SHARING is one of the few Oracle parameters that can have a ‘silver bullet’ effect – instantly and dramatically increasing throughput on a parse-constrained database.”
I still have fond memories of the problems caused by the October 2006 patch for Oracle Database 10.2.0.2 that were related to setting the CURSOR_SHARING parameter to FORCE. That problem seems to be related to this article: Metalink (MOS) Doc ID 7272297.8, Bug 7272297 – “Memory corruption / OERI / OERI with literal replacement”.
Of course there are plenty of other resources that suggest utilizing the CURSOR_SHARING parameter to tune performance, including this article that suggests changing that parameter value to SIMILAR.
Is changing the CURSOR_SHARING parameter from EXACT to either FORCE or SIMILAR a good idea, much less a silver bullet? Was it only a problem with Oracle Database release versions prior to 11.1?
A couple of additonal resources to help you decide:
- Carol Dacko reports that the SIMILAR parameter value for the CURSOR_SHARING parameter is deprecated (obsolete) as of Oracle Database 11.1 (and it will be removed in 12.1) per Metalink (MOS) Doc ID: 1169017.1
- The Oracle Optimizer Group answers the question: “Why do I have hundreds of child cursors when cursor_sharing is set to similar in 10g”
- The Oracle Optimizer Group explains adaptive cursor sharing behavior with cursor_sharing = similar and force
- Kyle Hailey and Randolf Geist discuss the CURSOR_SHARING parameter in a blog article titled “Cursor_sharing: a picture is worth a 1000 words“