The CURSOR_SHARING Parameter is a Silver Bullet – What is Wrong with this Quote?

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.”

“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 bookPer 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”

Actions

Information

13 responses

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?

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.

> 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?)

“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).

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).

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?

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 parse activity appears to be excessive (as shown by the “parse time elapsed” category in our time model query) then you can try the “silver bullet” solutions offered by the CURSOR_SHARING and SESSION_CACHED_CURSORS parameters.”

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.

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

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).

Hints for Posting Code Sections in Comments

********************
When the spacing of text in a comment section is important for readability (execution plans, PL/SQL blocks, SQL, SQL*Plus output, etc.) please use a <pre> tag before the code section and a </pre> tag after the code section:

<pre>

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;
TODAY
---------
01-MAR-12

</pre>
********************
When posting test case samples, it is much easier for people to reproduce the test case when the SQL*Plus line prefixes are not included - if possible, please remove those line prefixes. This:

SELECT
SYSDATE TODAY
FROM
DUAL;

Is easier to execute in a test case script than this:

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;

********************
Greater than and Less than signs in code sections are often interpretted as HTML formatting commands. Please replace these characters in the code sections with the HTML equivalents for these characters: