Many thanks for such a thorough and clear response.
It has, however, thrown up some more questions.
I ran the following to identify the SQL with the most versions and to try to find why there are so many versions (it was on the live system so the counts are not neessarily consistent all the way through):
SQL> SELECT * FROM
(
SELECT sql_text, sql_id, version_count --,loaded_version, fetches, executions, invalidations, parse_calls
FROM v$sqlarea
WHERE version_count > 1
AND parsing_user_id != 0
ORDER BY version_count DESC
)
WHERE rownum < 2; 2 3 4 5 6 7 8 9
UPDATE RFLT_DEPARTURES SET CANCELLED=:"SYS_B_0",DESTINATION=:"SYS_B_1" WHERE FLC=:"SYS_B_2" AND FLN=:"SYS_B_3" AND nvl(FLX,:"SYS_B_4")=nvl(:"SYS_B_5",:"SYS_B_6") AND DOP=:"SYS_B_7" AND SITE=:"SYS_B_8"
0p234t6dap24f 1788
SQL> select count(*) from v$sql_shared_cursor where sql_id = '&sql_id';
Enter value for sql_id: 0p234t6dap24f
1633
SQL> select address, count(*) from v$sql_shared_cursor where sql_id = '&sql_id' group by address;
Enter value for sql_id: 0p234t6dap24f
0700000044775920 1634
SQL> select address, child_address, count(*)
from v$sql_shared_cursor where sql_id = '&sql_id'
group by address, child_address
having count(*) > 1;
2 3 4 Enter value for sql_id: 0p234t6dap24f
no rows selected
SQL> SELECT sql_id, address, sum(decode(UNBOUND_CURSOR, 'Y', 1, 0)) UNBOUND_CURSOR,
2 sum(decode(SQL_TYPE_MISMATCH, 'Y', 1, 0)) SQL_TYPE_MISMATCH,
3 sum(decode(OPTIMIZER_MISMATCH, 'Y', 1, 0)) OPTIMIZER_MISMATCH,
4 sum(decode(OUTLINE_MISMATCH, 'Y', 1, 0)) OUTLINE_MISMATCH,
5 sum(decode(STATS_ROW_MISMATCH, 'Y', 1, 0)) STATS_ROW_MISMATCH,
6 sum(decode(LITERAL_MISMATCH, 'Y', 1, 0)) LITERAL_MISMATCH,
7 sum(decode(SEC_DEPTH_MISMATCH, 'Y', 1, 0)) SEC_DEPTH_MISMATCH,
8 sum(decode(EXPLAIN_PLAN_CURSOR, 'Y', 1, 0)) EXPLAIN_PLAN_CURSOR,
9 sum(decode(BUFFERED_DML_MISMATCH, 'Y', 1, 0)) BUFFERED_DML_MISMATCH,
sum(decode(PDML_ENV_MISMATCH, 'Y', 1, 0)) PDML_ENV_MISMATCH,
10 11 sum(decode(INST_DRTLD_MISMATCH, 'Y', 1, 0)) INST_DRTLD_MISMATCH,
12 sum(decode(SLAVE_QC_MISMATCH, 'Y', 1, 0)) SLAVE_QC_MISMATCH,
13 sum(decode(TYPECHECK_MISMATCH, 'Y', 1, 0)) TYPECHECK_MISMATCH,
14 sum(decode(AUTH_CHECK_MISMATCH, 'Y', 1, 0)) AUTH_CHECK_MISMATCH,
15 sum(decode(BIND_MISMATCH, 'Y', 1, 0)) BIND_MISMATCH,
16 sum(decode(DESCRIBE_MISMATCH, 'Y', 1, 0)) DESCRIBE_MISMATCH,
17 sum(decode(LANGUAGE_MISMATCH, 'Y', 1, 0)) LANGUAGE_MISMATCH,
18 sum(decode(TRANSLATION_MISMATCH, 'Y', 1, 0)) TRANSLATION_MISMATCH,
19 sum(decode(ROW_LEVEL_SEC_MISMATCH, 'Y', 1, 0)) ROW_LEVEL_SEC_MISMATCH,
20 sum(decode(INSUFF_PRIVS, 'Y', 1, 0)) INSUFF_PRIVS,
21 sum(decode(INSUFF_PRIVS_REM, 'Y', 1, 0)) INSUFF_PRIVS_REM,
22 sum(decode(REMOTE_TRANS_MISMATCH, 'Y', 1, 0)) REMOTE_TRANS_MISMATCH,
23 sum(decode(LOGMINER_SESSION_MISMATCH, 'Y', 1, 0)) LOGMINER_SESSION_MISMATCH,
24 sum(decode(INCOMP_LTRL_MISMATCH, 'Y', 1, 0)) INCOMP_LTRL_MISMATCH,
25 sum(decode(OVERLAP_TIME_MISMATCH, 'Y', 1, 0)) OVERLAP_TIME_MISMATCH,
26 sum(decode(SQL_REDIRECT_MISMATCH, 'Y', 1, 0)) SQL_REDIRECT_MISMATCH,
27 sum(decode(MV_QUERY_GEN_MISMATCH, 'Y', 1, 0)) MV_QUERY_GEN_MISMATCH,
28 sum(decode(USER_BIND_PEEK_MISMATCH, 'Y', 1, 0)) USER_BIND_PEEK_MISMATCH,
29 sum(decode(TYPCHK_DEP_MISMATCH, 'Y', 1, 0)) TYPCHK_DEP_MISMATCH,
30 sum(decode(NO_TRIGGER_MISMATCH, 'Y', 1, 0)) NO_TRIGGER_MISMATCH,
31 sum(decode(FLASHBACK_CURSOR, 'Y', 1, 0)) FLASHBACK_CURSOR,
32 sum(decode(ANYDATA_TRANSFORMATION, 'Y', 1, 0)) ANYDATA_TRANSFORMATION,
33 sum(decode(INCOMPLETE_CURSOR, 'Y', 1, 0)) INCOMPLETE_CURSOR,
34 sum(decode(TOP_LEVEL_RPI_CURSOR, 'Y', 1, 0)) TOP_LEVEL_RPI_CURSOR,
35 sum(decode(DIFFERENT_LONG_LENGTH, 'Y', 1, 0)) DIFFERENT_LONG_LENGTH,
36 sum(decode(LOGICAL_STANDBY_APPLY, 'Y', 1, 0)) LOGICAL_STANDBY_APPLY,
37 sum(decode(DIFF_CALL_DURN, 'Y', 1, 0)) DIFF_CALL_DURN,
38 sum(decode(BIND_UACS_DIFF, 'Y', 1, 0)) BIND_UACS_DIFF,
39 sum(decode(PLSQL_CMP_SWITCHS_DIFF, 'Y', 1, 0)) PLSQL_CMP_SWITCHS_DIFF,
40 sum(decode(CURSOR_PARTS_MISMATCH, 'Y', 1, 0)) CURSOR_PARTS_MISMATCH,
41 sum(decode(STB_OBJECT_MISMATCH, 'Y', 1, 0)) STB_OBJECT_MISMATCH,
42 sum(decode(ROW_SHIP_MISMATCH, 'Y', 1, 0)) ROW_SHIP_MISMATCH,
43 sum(decode(PQ_SLAVE_MISMATCH, 'Y', 1, 0)) PQ_SLAVE_MISMATCH,
44 sum(decode(TOP_LEVEL_DDL_MISMATCH, 'Y', 1, 0)) TOP_LEVEL_DDL_MISMATCH,
45 sum(decode(MULTI_PX_MISMATCH, 'Y', 1, 0)) MULTI_PX_MISMATCH,
46 sum(decode(BIND_PEEKED_PQ_MISMATCH, 'Y', 1, 0)) BIND_PEEKED_PQ_MISMATCH,
47 sum(decode(MV_REWRITE_MISMATCH, 'Y', 1, 0)) MV_REWRITE_MISMATCH,
48 sum(decode(ROLL_INVALID_MISMATCH, 'Y', 1, 0)) ROLL_INVALID_MISMATCH,
49 sum(decode(OPTIMIZER_MODE_MISMATCH, 'Y', 1, 0)) OPTIMIZER_MODE_MISMATCH,
50 sum(decode(PX_MISMATCH, 'Y', 1, 0)) PX_MISMATCH,
51 sum(decode(MV_STALEOBJ_MISMATCH, 'Y', 1, 0)) MV_STALEOBJ_MISMATCH,
52 sum(decode(FLASHBACK_TABLE_MISMATCH, 'Y', 1, 0)) FLASHBACK_TABLE_MISMATCH,
53 sum(decode(LITREP_COMP_MISMATCH, 'Y', 1, 0)) LITREP_COMP_MISMATCH
54 FROM v$sql_shared_cursor
55 WHERE sql_id = '&sql_id'
56 GROUP BY sql_id, address;
Enter value for sql_id: 0p234t6dap24f
0p234t6dap24f 0700000044775920 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 264 0
0 0 0 0
SQL>
So there are 1788 versions of this SQL in v$sqlarea, 1633 records for this SQL in v$sql_shared_cursor of which all have a different child_address and there are 264 roll_invalid_mismatch but no other mismatches.
The questions I now have are:
1: Could you give one of your concise explanations of exactly what parent and child cursors are and when they are created so that I can be sure I'm not assuming anything
2: What is a roll_invalid_mismatch? The docs don't explain it clearly enough for me.
3: Why might it be that there are more versions identified in vsqlarea than there are records in v£sql_shared_cursor?
4: Why might there be so many records in v$sql_shared_cursor that do not mismatch on anything?
Thanks again
Alex