Database Reference
In-Depth Information
7 decode(x.optimizer_mode_current,
8 1, 'ALL_ROWS',
9 2, 'FIRST_ROWS',
10 3, 'RULE',
11 4, 'CHOOSE', x.optimizer_mode_current) AS optimizer_mode_current
12 FROM v$sql_shared_cursor s,
13 XMLTable('/ChildNode'
14 PASSING XMLType(reason)
15 COLUMNS
16 reason VARCHAR2(100) PATH '/ChildNode/reason',
17 optimizer_mode_cursor NUMBER PATH '/ChildNode/optimizer_mode_cursor',
18 optimizer_mode_current NUMBER PATH '/ChildNode/optimizer_mode_current'
19 ) x
20 WHERE s.sql_id = '5tjqf7sx5dzmj'
21 AND s.child_number = 0;
REASON OPTIMIZER_MODE_CURSOR OPTIMIZER_MODE_CURRENT
---------------------- --------------------- ----------------------
Optimizer mismatch(10) ALL_ROWS FIRST_ROWS
The aim of the third example, also based on the
sharable_child_cursors.sql
script, is to show you that the
execution environment can not only influence the execution plan, but also that the result of SQL statements might be
different. This is another reason why the execution environment must be compatible for sharing a child cursor. For
example, the output of the following SQL statements illustrates the impact of the
nls_sort
initialization parameter:
SQL> ALTER SESSION SET nls_sort =
binary
;
SQL> SELECT * FROM t ORDER BY pad;
N PAD
--- ---
1 1
2 =
3 Z
4 z
SQL> ALTER SESSION SET nls_sort =
xgerman
;
SQL> SELECT * FROM t ORDER BY pad;
N PAD
--- ---
2 =
4 z
3 Z
1 1
