Insights into Statistics, Query Optimization and the Oracle Optimizer

Cursor Sharing

How do adaptive cursor sharing and SQL Plan Management interact?

We've received a lot of questions about how adaptive cursor sharing (ACS) and SQL plan management (SPM) interact.  We discussed this briefly in one of the original SPM posts, but in this post, we'll explain the concepts of how the two features interact in more details, and show an example.  The simplest way to reason about the interaction is to remember that they are responsible for two different tasks.  ACS controls whether or not a child cursor is shared on a...

Monday, February 11, 2013 | Cursor Sharing | Read More

My cursor wasn’t shared because of STB_OBJECT_MISMATCH. What does that mean?

I got a question recently about cursor sharing and why a cursor wasn’t shared due to an STB_OBJECT_MISMATCH. And I thought the question and subsequent investigation might make an interesting blog post. Let me begin by outlining the question in detail. The SQL statement in question is a simple update:UPDATE /*test1*/ test a SET a.name='zjt' WHERE a.id > 6000 AND rownum< 2; After the statement is executed for the first time a cursor is generated as expected. Then the statement is...

Wednesday, January 25, 2012 | Cursor Sharing | Read More

Explain adaptive cursor sharing behavior with cursor_sharing = similar and force.

Here's another question that was submitted during the OpenWorld Optimizer Roundtable. It's a common question that we've discussed a little bit in a couple other posts, but we wanted to summarize everything in one place. First, let's quickly review what the different values for the cursor_sharing parameter mean. We discussed this behavior in some detail in an earlier post about cursor_sharing. Below is a summary of the behavior of the different values in different cases...

Friday, March 12, 2010 | Cursor Sharing | Read More

Why do I have hundreds of child cursors when cursor_sharing is set to similar in 10g

Recently we received several questions regarding an unusual situation where a SQL Statement has hundreds of child cursors. This is in fact the expected behavior when CURSOR_SHARING is set to similar Bind peeking is in use And a histogram is present on the column used in the where clause predicate of query You must now be wondering why this is the expected behavior. In order to explain, let's step back and begin by explaining what CURSOR_SHARING actually does. CURSOR_SHARING was...

Tuesday, May 26, 2009 | Cursor Sharing | Read More

Update on Adaptive Cursor Sharing

In December 2007 we did a post on the new Oracle Database 11g feature Adaptive Cursor Sharing called Why are there more cursor in 11g for my queries containing binds. In this post we explained that in Oracle Database 11g it was possible to have multiple execution plans for a single statement that uses bind variables, thus ensuring that the best execution plan will be used depending on the bind value. However, it's been brought to our attention by Ric Van Dyke from Hotsosthat...

Sunday, April 12, 2009 | Cursor Sharing | Read More

Integrated Cloud Applications & Platform Services