Understanding V$SQL_SHARED_CURSOR in the Presence of Multiple Child Cursors

Cursor sharing is an imperative aspect of the Oracle Optimizer, allowing the same SQL to share child cursors generated by previous executions, often significantly reducing hard parse overhead. So when a substantial number of hard parses and child cursors for the same SQL are seen, it may be of interest to an administrator to better understand why the cursors are not being shared.

There are dozens of reasons why an existing child cursor may not be shared. Some examples can include; optimization parameters changing, parsing schema changing, language changing, auto re-optimization occurring, etc. Every time a query is executed, it first checks if it can share an existing child cursor. If the current execution has a mismatch on any one of the cursor sharing criteria, it cannot share that existing child cursor.

The best method for understanding why child cursors could not be shared is querying V$SQL_SHARED_CURSOR. You can find the definition of V$SQL_SHARED_CURSOR in the Oracle Documentation. This view has a series *_MISMATCH columns that correspond to the numerous cursor sharing criteria, and can help identify which criteria mismatched, causing the creation of the new child cursor. The view also contains a REASON column that provides additional information on why existing cursors were not shared.

Understanding the *_MISMATCH Columns

Within V$SQL_SHARED_CURSOR there are dozens of columns following the form of *_MISMATCH (for example, OPTIMIZER_MISMATCH). These columns can only take the values of ‘Y’ or ‘N’, indicating ‘Yes’ or ‘No” respectively. A ‘Yes’ in one of these columns indicates that the newly created cursor encountered that specific mismatch with at least one existing cursor. For example, if a child cursor has OPTIMIZER_MISMATCH set to ‘Y’, then we know that it has a different optimizer environment than at least one of the existing child cursors.

Understanding the REASON Column

V$SQL_SHARED_CURSOR also contains a REASON column. This is a CLOB column formatted as XML that has the reason why the child was not shared by a newly created cursor, along with some additional diagnostic information that depends on the mismatch reason. Every time a new cursor fails to share an existing cursor, the reason for why it could not be shared is appended to the REASON column of the existing child cursor.

Example

With multiple potential reasons for not sharing a cursor, and potentially several child cursors, it can become difficult to understand the meaning of the content in V$SQL_SHARED_CURSOR. So a simple example may help.

For this example we will be tracking the contents of V$SQL_SHARED_CURSOR for a single SQL as multiple executions with various Optimizer Compilation Environments, language settings, and VPD policies come in.

Imagine the given SQL is executed 5 times, with the given Compilation Environments (CE), Language handles (L), and VPD policies (VPD).

Now let’s track the contents of the relevant columns in V$SQL_SHARED_CURSOR (OPTIMIZER_MISMATCH, LANGUAGE_MISMATCH, AUTH_CHECK_MISMATCH, REASON) for this SQL as it is executed each time. All of the other unmentioned *_MISMATCH columns for this example will be ‘N’ since we are only altering CE, language, and VPD. The REASON column is ordinarily XML formatted and contains additional diagnostic information, but it is paraphrased to only contain the mismatch reason for this example .

For the first execution, a new child cursor is made since there are no existing child cursors yet. It did not mismatch with any other child cursors, so its MISMATCH columns are all populated with ‘N’. The REASON column remains unpopulated for now.

The second execution has a different Compilation Environment from the first child cursor. Due to this, a second child cursor is made. The OPTIMIZER_MISMATCH column is marked with ‘Y’ since child#2 encountered an optimizer mismatch with child#1 during the cursor sharing criteria checks. Then the reason for why the new cursor could not share child#1 is placed in the REASON column of child#1.

In the third execution we match CE of child#1, but have a language mismatch with that child. Due to this we set LANGUAGE_MISMATCH to ‘Y’ for child#3, and append Language Mismatch to child#1 REASON. When comparing child#3 to child#2, we have an optimizer mismatch, so OPTIMIZER_MISMATCH is also set for child#3, and we add Optimizer Mismatch to child#2 REASON. Notice how even though child#3 also has a language mismatch with child#2, we only store the first evaluated mismatch reason in the REASON column for child#2. It is also worth noting at this point that the *_MISMATCH columns are consistent, and are not altered after being written, unlike the REASON column, which can be appended to any time a new child cursor is added.

For the fourth execution we see a new CE, new Language, and new VPD policy. However, when comparing child#4 to each of the existing cursors, we fail to match each of the existing child cursors after only having to check the first cursor sharing criteria (optimizer mismatch). Due to this we only set OPTIMIZER_MISMATCH for child#4, and append Optimizer Mismatch to each of the existing child cursors’ REASON column. However, duplicates of the same reason are not stored, so it is only added to child#3.

For the fifth execution we have the same CE as child#1 and child#3, and the same Language as child#3, but a VPD policy that no other child has. Due to this, there is an optimizer mismatch with child#2 and child#4, a language mismatch with child#1, and a VPD mismatch with child#3. So we have each *_MISMATCH column marked as ‘Y’ for child#5 and each of the named reasons are appended to the REASON column of the respective child.

Extracting Reason Text

Since the REASON column of V$SQL_SHARED_CURSOR can contain additional diagnostic information, it may be easier to extract the reason aspect directly out of it. Here is an example query that can be used to extract only the reasons why a child cursors could not be shared. It will need to be modified to adjust for the sql_id of your interest. It can also be expanded to select any MISMATCH columns that may be of interest.

SELECT ssc.child_number, ssc.sql_id,
       LISTAGG(xmltab.reason_text, ', ') WITHIN GROUP (ORDER BY xmltab.reason_text) AS reasons
FROM V$SQL_SHARED_CURSOR ssc,
     XMLTABLE('/ChildNode/reason' PASSING XMLPARSE(CONTENT ssc.REASON)
              COLUMNS reason_text VARCHAR2(4000) PATH '.') xmltab
WHERE ssc.sql_id = <sql_id>
GROUP BY ssc.sql_id, ssc.child_number;