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 executed again and we can see from v$SQL that the same cursor (child cursor 0) is used for the statement (execution count has gone up to 2). So far so good.

However, after the statement is executed a third time a new child cursor (child cursor 1) appears in v$SQL for this SQL Statement.

So the question is why wasn’t the initial cursor used on the third execution?

We should be able to get some sort of hint at what is going on by looking up the SQL_ID (cuqcxr9jurqgb) in v$SQL_SHARED_CURSOR.

From the output above we can see that the reason we can’t share child cursor 0 on the third execution is because ‘SQL Tune Base Object dif’. You will also notice that there is a ‘Y’ in column 45 of the view. This column is the STB_OBJECT_MISMATCH. If you look up the explanation for the STB_OBJECT_MISMATCH column in the Oracle Database 11g Reference Guide it says ‘STB has come into existence since cursor was compiled’. So the database has a genuine reason for not sharing the cursor even if it’s not crystal clear to us what it is.

The question now becomes what is a SQL Tune Base Object or STB_OBJECT?

STB_OBJECTS are what we officially refer to as SQL Management Object, such as SQL profiles, SQL plan baselines, or a SQL patches. The reason the cursor was not being shared on the third execution was because a new SQL Management Object was created between the second and third execution for this SQL statement. When a SQL Management Object is created for a SQL statement the corresponding cursor is marked invalid to trigger a hard parse the next time it is executed. The hard parse is necessary so that the metadata about the SQL Management Object can be added to the cursor. Remember cursors are read-only only objects so if something changes or new information becomes available about the objects accessed in the SQL statement a new child cursor is required.

We still don’t know what SQL Management Object was created for our SQL statement. But we can find out by querying v$SQL, which shows what SQL Management Objects are associated with each child cursors.

So the SQL Management Object is a SQL plan baseline but where did it come from? If we look in the origins column of DBA_SQL_PLAN_BASELINES we can see it was automatically capture.


Most likely the baseline was created because automatic plan capture was enabled via the init.ora parameter, OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES. We can confirm this by checking the value of the init.ora parameter.

So the SQL plan baseline was automatically created because when automatic plan capture is enabled ( OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE). When automatic plan capture is enabled Oracle will create a SQL plan baseline for any repeatable SQL statement. So the first time the statement was executed its SQL_Handle was recorded. The second time it was executed it was marked as a repeatable SQL statement and a SQL plan baseline was created. The original child cursor 0 was marked invalid to trigger a hard parse the next time it is executed so Oracle could include information about the new SQL plan baseline. Hence on the third execution a new child cursor 1 was created.

You can get a copy of the script I used to generate this post here.

+Maria Colgan

Comments:

Crystal clear walkthrough - great, thanks.

Posted by Dominic Brooks on January 26, 2012 at 04:34 AM PST #

Nice writeup; well written with a simple but good example.

Posted by Anantha on January 27, 2012 at 12:52 AM PST #

Hi,

Just so it's clear for me:
So, when a SQL statement is executed sevral time (with the proper parameter in the init.ora), the optimizer will re-generate a new execution plan , and save it in a plan baseline.
So, as this statement is executed often, its performances wouldn't change much because it'll always use the same plan, excepted if a new plan is accepted by the DBA via the SQL_PLAN_BASELINES, isn't it?
And, when the new plan is generated on the third executions, does it use the possible difference between estimated cardinality and real cardinality? The statement has already been executed twice, so maybe it gets a more accurate view.

Posted by guest on January 27, 2012 at 01:08 AM PST #

Thanks for sharing. Great blog post.

Posted by Vishal Gupta on February 09, 2012 at 12:52 PM PST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

The Oracle Optimizer blog is written by members of the Optimizer development team. The goal of this blog is to provide an insight into the workings of the Optimizer and the statistics it relies on. The views expressed on this blog are our own and do not necessarily reflect the views of Oracle and its affiliates. The views and opinions expressed by visitors on this blog are theirs solely and may not reflect ours.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today