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[17114] / OERI[17125] 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“
Charles Hooper's Oracle Notes 
Ignoring the fact that cursor_sharing = similar will be deprecated for good reason in the next version (although I thought it was already deprecated in 11gR2) and ignoring the fact that problems with simlar + histograms are well documented, in fact ignoring similar altogether, I have already come across three show stopper bugs in an upgrade to 11.2.0.2 when trying to use cursor_sharing=force, that I’ve abandoned any further attempts to use it.
It used to be a very close to a quick, easy fix, preferably short-term fix until the underlying misapplication of literals (because literals can be good obviously) could be addressed. The sheer falability of this feature in recent versions means it’s not an option.
Then again the abundance of “wrong results” bugs with various SQL features and optimizer transformations, etc means that trusting Oracle to return the correct data is becoming ever decreasingly reliable. A real issue. Does nothing work properly in this database any more?
Now look what you’ve done…. set me off ranting…
Dom,
Thanks for the contribution.
I think that it is important for people to realize that just because something is in print does not necessarily make it true. Take, for example, the Metalink (MOS) article that was referenced above. That article states that the SIMILAR parameter value for the CURSOR_SHARING parameter is deprecated as of 11.1 (thus deprecated in 11.1 and 11.2) and will be removed in 12.1. I wonder why the documentation for 11.2 indicates that the value SIMILAR is still a valid (assumed non-deprecated) value:
http://download.oracle.com/docs/cd/E14072_01/server.112/e10820/initparams041.htm
So, who is right, Oracle Corp. or Oracle Corp.? Possibly a retroactive deprecation that caught the documentation team off guard?
Well, it’s been well known for a long time that one uses “new” features in Oracle at the cost of stability.
That used to translate to data or metadata corruption,
Now, what I am seeing more and more is actual wrong results being produced. That is much more insidious as it requires plenty of application logic testing to detect, rather than the much simpler “it’s broke, let’s find out why” that corruptions cause.
I’ve alerted to that situation many times online, since the 9i days. That only earned me the “dinossaur rant” moniker from many other dbas, who would have done a lot better for themselves by listening instead of deriding.
Enjoy the results! I love it when the chooks come home to roost!:-D
> Now, what I am seeing more and more is actual wrong results being produced.
As mentioned, ditto.
A massive, massive issue.
If you can’t trust your database to return the correct data, what can you do?
Tell you what, 12.1 better be bloody good and very reliable – it should be given all these bugs listed as fixed in 12.1 – or it’s the beginning of the end (or maybe that’s already happened?)
There’s an interesting quote in
http://blogs.oracle.com/optimizer/entry/why_are_there_more_cursors_in_11g_for_my_query_containing_bind_variables_1
“We hope that in the future, this feature will persuade people to set cursor_sharing to force.”
A lawyer could probably get away with the “it CAN have a silver bullet effect”, but the general understanding of a silver bullet is that it always works. It won’t help if you’ve got a chronically undersized library cache though (which you may have done to stop it blowing out if using literals rather than variables).
Gary,
Thanks for supplying that other related article. If I read that section of the article correctly, the previous sentences suggested the value SIMILAR can cause problems, and the quoted sentence seems to be saying that if you *must* change the CURSOR_SHARING parameter value, FORCE is less problematic. I need to re-read that article.
Silver bullet: shoot, think, pay (rather than pay for the bullet, think about what will be shot, and then shoot).
Charles,
You say “(I suspect that the same advice might also be found in that author’s recent book)”, so I thought I’d better clarify. I don’t use the phrase “silver bullet”, but I do say that:
* If you can’t change application code to exploit bind variables, you might be able to use the CURSOR_SHARING parameter to get a similar outcome.
I’d stand by that as an accurate statement.
Guy,
Thank you for stopping by and correcting my assumption about your book. As mentioned in this article, I was surprised to see the phrase “silver bullet” on that domain (is it possible to remove that phrase?).
I agree that your statement above is correct, but I also *hope* that you also included some words of warning about potential side-effects from using that parameter. Otherwise, it might appear that you are offering a blanket (this might not translate right for all readers – a general rule that applies in all cases) suggestion that could severely impact the accuracy of results returned from the database and also the stability of the database instance – without giving the reader any advanced notice of what may be a possible outcome of the change. Offering what should be sufficient clarification can be a difficult task, and I hope that you were able to help your readers find the *fine* print regarding that parameter.
As a for instance, take the opening sentence of this article. The words in that opening sentence were carefully selected to demonstrate a point – is “yah-but” missing a letter, or might it be missing a bit of character (and is the definition missing a bit of detail)? I have not read your book, but in some of the books that I have read I frequently see suggestions such as: change this (possibly hidden) parameter, end of discussion. OK, but by how much should it be changed? Specifically, why am I changing the parameter? Specifically, what side-effects might be encountered by making the change? If I change this parameter to a value (SIMILAR for instance), how might that affect the memory used by other items in the SGA? Does it matter if I am using AMM, ASMM, or manually specifying the memory for the various regions?
Guy,
I performed a quick Google search for the phrase:
Oracle Performance “Survival Guide” “silver bullet” harrison
The second link that was returned to me appears to be a small portion of your book – I will not directly link to that article in the event that it is in fact a pirated copy of a portion of your book. There probably is not enough context in the small amount of text that is provided on that page, but it does appear that your book states (apparently on pages 7 and 8):
If the above does not appear in your book, I apologize for the incorrect assumption that I made in this article. If the above quote is in fact found in your book, you might consider working with your publisher to convince that domain found by the Google search to not illegally host your book’s pages.
Guy,
After a bit more investigation, the above quote was apparently from a PDF file that you authored titled “Oracle performance tuning: a systematic approach”. It appears that you have linked to that PDF file on a site that you maintain:
http://guyharrison.squarespace.com/blog/2007/8/17/systematic-oracle-performance-tuning.html
Amazon’s book preview permits viewing some pages of your book. A search for the phrase “silver bullet” in that preview only returned two results, neither of which were related to the CURSOR_SHARING parameter, and neither of which cast the phrase “silver bullet” as a 100% (or even 5%) achievable solution:
It is unfortunate that a Google search made it appear as though the quote was found in your book.
Last year Jonathan Lewis published a blog article about one of the side effects of setting cursor_sharing=force which is related to the use of function based indexes. Imagine that you’ve defined a function based index to cover query having where clause like substr(c1,2,4) = ?
If you set the cursor_sharing to force then you will see that your query is not using anymore your function based index because of the transformation (that you can easily point out into the predicate part ! again the importance of the predicate part) substr(c1, :SYS_B_1,:SYS_B_2) done by the new cursor_sharing value making your query and your function based index not matching any more.
This is whithout mentioning the overhead of CPU du to those litteral to bind values transformation
http://jonathanlewis.wordpress.com/2010/05/03/cursor-sharing-3/
Best regards
Mohamed Houri
Mohamed,
The link that you provided shows a perfect example of what could go wrong when changing the value of the CURSOR_SHARING parameter – thank you for attaching the link. In short, saving a little time for the hard parse might lead to much longer execution times for the actual query processing.
When I first saw your example of substr(c1,2,4), I thought that it seemed like an odd example function for a function-based index. I thought that NVL(C1, -1) might be more common in the event that we need to index NULL values by “changing” their value to an “impossible” value for the column. Of course, it is possible to directly index NULL values in a b*tree index by creating a two column composite index with the second column as a constant (a number, not a letter), so maybe my example is not quite as common as I first thought. This thought process gave me a bit of time to think a bit about the example that you posted. In the United States the phone numbers are typically recorded as 1-999-999-9999 or (999) 999-9999 – the first 999 is the “area code”, and extracting this bit of information from the phone number could very well be a use case for the substr(c1,2,4) example that you provided (of course the 4 would become a 3 in my example use case, but that is a minor detail).
[…] Also see Charles Hooper’s blog post on this topic at https://hoopercharles.wordpress.com/2011/07/03/the-cursor_sharing-parameter-is-a-silver-bullet-what-i… Be Sociable, […]