Key Concepts - Troubleshooting Oracle Performance - page 25

■ as the previous example shows, the optimizer_mode column doesn't show the right value for child
number 1. In fact, the column shows FIRST_ROWS instead of FIRST_ROWS_1 . the same behavior can be observed with
FIRST_ROWS_10 , FIRST_ROWS_100 , and FIRST_ROWS_1000 as well. this fact leads to the potential problem that even
though the execution environment is different, the SQL engine doesn't distinguish that difference. as a result, a child
cursor might be incorrectly shared.

Caution

To know which mismatch led to several child cursors, you can query the v$sql_shared_cursor view. In it you
might find, for each child cursor (except the first one, 0), why it wasn't possible to share a previously created child
cursor. For several types of incompatibility (64 of them in version 12.1), there's a column that is set to either N
(no mismatch) or Y (mismatch). With the following query, it's possible to confirm that in the previous example,
the mismatch for the second child cursor was because of a different optimizer mode:

SQL> SELECT optimizer_mode_mismatch
2 FROM v$sql_shared_cursor
3 WHERE sql_id = '5tjqf7sx5dzmj'
4 AND child_number = 1;

OPTIMIZER_MODE_MISMATCH
-----------------------
Y

As of version 11.2.0.2, the v$sql_shared_cursor view provides a column named reason . Its aim is to show not
only a textual description of the mismatch leading to a new child cursor, but also additional information about the
mismatch. Because the information the reason column contains is highly dependent on the type of the mismatch,
its datatype is CLOB , and the data is an XML fragment. For example, in the following case, three XML elements contain
the key information. The reason (“Optimizer mismatch”) is stored in the reason element, the optimizer mode
(which is 1 , meaning ALL_ROWS ) of the cursor already stored in the library cache is stored in the optimizer_mode_cursor
element, and the optimizer mode required by the session parsing the statement ( 2 , meaning FIRST_ROWS ) is stored in
the optimizer_mode_current element:

SQL> SELECT reason
2 FROM v$sql_shared_cursor
3 WHERE sql_id = '5tjqf7sx5dzmj'
4 AND child_number = 0;

REASON
--------------------------------------------------------------------------------------------
<ChildNode><ChildNumber>0</ChildNumber><ID>3</ID><reason>Optimizer mismatch(10)</reason><siz
e>3x4</size><optimizer_mode_hinted_cursor>0</optimizer_mode_hinted_cursor><optimizer_mode_cu
rsor>1</optimizer_mode_cursor><optimizer_mode_current>2</optimizer_mode_current></ChildNode>

SQL> SELECT x.reason,
2 decode(x.optimizer_mode_cursor,
3 1, 'ALL_ROWS',
4 2, 'FIRST_ROWS',
5 3, 'RULE',
6 4, 'CHOOSE', x.optimizer_mode_cursor) AS optimizer_mode_cursor,