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.