I will start by answering the question: What is the problem?
There is no problem at this time. I started investigating the high version count after finding a generic recommendation by the application vendor that the event 106001 should be set to 100.
Indeed, there is a high version count and it can go up to 1000+. There are no performance problems reported at this time and there are a few "mutex" wait events however they do not appear to be significant.
I have reviewed the Oracle Support Doc ID 296377.1, 120655.1, and 438755.1, and also the following thread
I have also seen this
Bug 6964441 V$SQL_SHARED_CURSOR.ROW_LEVEL_SEC_MISMATCH = Y actually means a bind equivalence failure in 11g
Since using the Oracle-provided script in the note 438755.1 is not an option because it creates objects in the SYS schema, I have done my own analysis based on my understanding of the views V$SQLAREA, V$SQL, and V$SQL_SHARED_CURSOR.
My goal in this post is to ask you to help me understand what is going on.
Based on what I can say, the majority of the cursors are not shared either for ROW_LEVEL_SEC_MISMATCH or for absolutely no reason at all in V$SQL_SHARED_CURSOR, and out of those child cursors that have no reason many are in the INVALID_UNAUTH status, however there are a lot of them in the VALID status.
I may be wrong or misled in my analysis, and if that is so, please let me know and correct my reasoning.
I will post the results of my analysis below.
Thank you,
Emilija
Oracle Version: 11.1.0.7 PSU 13
show parameter cursor_sharing
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_optimizer_adaptive_cursor_sharing boolean FALSE
cursor_sharing string EXACT
select sql_id, version_count, loaded_versions, open_versions, executions, loads, invalidations, kept_versions, address
2 from v$sqlarea
3 where version_count > 100
4 order by version_count desc;
SQL_ID VERSION_COUNT LOADED_VERSIONS OPEN_VERSIONS EXECUTIONS LOADS INVALIDATIONS KEPT_VERSIONS ADDRESS
------------- ------------- --------------- ------------- ---------- ---------- ------------- ------------- ----------------
2j8wvg2hby4vr 3870 3863 2 218881 69380 35872 0 0700000F5F8AF970
bdnw01kkujc1x 2233 850 0 18398 10841 6597 0 0700000EB7C21B48
c0fwwtg7vt8r2 2096 2093 1 17716 18312 9942 0 0700000F57966FB8
fhfrpc176ffv3 2037 2034 1 6449 31949 16962 0 0700000F5F178850
bcnfgmzv6tdgb 1950 1950 1 632724 15222 8255 0 0700000F9726F830
ahn67gnj7m9tr 1867 1302 0 57949 4410 2218 0 0700000FAEE7E8B0
0tbrs8z44pmy8 1765 1765 1 77555 14317 7412 0 0700000F9F12ADB8
4y3wf3m9ynf1a 1663 1356 0 8588 11680 5806 0 0700000E91C948E8
...
aqbm3x47ajfjr 106 15 0 1392 1500 824 0 0700000EC67312D0
1s18fgh3qk1m7 102 97 0 18884 297 196 0 0700000F3ED3FBA0
dfayr8ufsubbs 102 1 0 41 181 180 0 0700000F5ECB9488
163 rows selected.
emilija@PROD> select address, count(*)
2 from v$sql
3 group by address
4 having count(*) > 100
5 order by count(*) desc;
ADDRESS COUNT(*)
---------------- ----------
0700000F5F8AF970 3863
0700000F57966FB8 2093
0700000F5F178850 2034
0700000F9726F830 1950
0700000F9F12ADB8 1765
0700000F4FC0ECB0 1476
0700000F66CD1808 1430
0700000E91C948E8 1356
0700000FAEE7E8B0 1302
...
0700000EAB626FA8 101
0700000F674A0230 101
109 rows selected.
emilija@PROD> select sql_id, child_number, hash_value, address, plan_hash_value, last_load_time,
2 loaded_versions, open_versions, executions, loads, invalidations, object_status, is_obsolete
3 from v$sql
4 where address='&p_address'
5 ;
Enter value for p_address: 0700000F5F8AF970
old 4: where address='&p_address'
new 4: where address='0700000F5F8AF970'
SQL_ID CHILD_NUMBER HASH_VALUE ADDRESS PLAN_HASH_VALUE LAST_LOAD_TIME LOADED_VERSIONS OPEN_VERSIONS EXECUTIONS LOADS INVALIDATIONS OBJECT_STATUS I
------------- ------------ ---------- ---------------- --------------- ------------------- --------------- ------------- ---------- ---------- ------------- ------------------- -
2j8wvg2hby4vr 0 2696876919 0700000F5F8AF970 3084621040 2016-03-06/02:15:31 1 0 1 25 13 INVALID_UNAUTH N
2j8wvg2hby4vr 1 2696876919 0700000F5F8AF970 3084621040 2016-03-06/02:15:31 1 0 1 25 13 INVALID_UNAUTH N
2j8wvg2hby4vr 2 2696876919 0700000F5F8AF970 3084621040 2016-03-06/02:15:31 1 0 2 25 13 INVALID_UNAUTH N
2j8wvg2hby4vr 3 2696876919 0700000F5F8AF970 3084621040 2016-03-06/02:15:31 1 0 2 25 13 INVALID_UNAUTH N
2j8wvg2hby4vr 4 2696876919 0700000F5F8AF970 3084621040 2016-03-06/02:15:31 1 0 1 25 13 INVALID_UNAUTH N
2j8wvg2hby4vr 5 2696876919 0700000F5F8AF970 3084621040 2016-03-06/02:15:31 1 0 4 25 13 INVALID_UNAUTH N
2j8wvg2hby4vr 6 2696876919 0700000F5F8AF970 3084621040 2016-03-06/02:15:31 1 0 2 25 13 INVALID_UNAUTH N
...
2j8wvg2hby4vr 3863 2696876919 0700000F5F8AF970 3084621040 2016-03-08/07:44:11 1 0 1 1 0 VALID N
2j8wvg2hby4vr 3864 2696876919 0700000F5F8AF970 3084621040 2016-03-08/07:44:11 1 0 362 1 0 VALID N
2j8wvg2hby4vr 3865 2696876919 0700000F5F8AF970 3084621040 2016-03-08/07:45:31 1 0 1 1 0 VALID N
2j8wvg2hby4vr 3866 2696876919 0700000F5F8AF970 3084621040 2016-03-08/07:45:31 1 1 108197 1 0 VALID N
2j8wvg2hby4vr 3867 2696876919 0700000F5F8AF970 3084621040 2016-03-08/11:59:13 1 0 1 1 0 VALID N
2j8wvg2hby4vr 3868 2696876919 0700000F5F8AF970 3084621040 2016-03-08/11:59:13 1 0 1 1 0 VALID N
2j8wvg2hby4vr 3869 2696876919 0700000F5F8AF970 3084621040 2016-03-08/11:59:13 1 1 39928 1 0 VALID N
3863 rows selected.
emilija@PROD> select object_status, count(*)
2 from v$sql
3 where address='&p_address'
4 group by object_status;
Enter value for p_address: 0700000F5F8AF970
old 3: where address='&p_address'
new 3: where address='0700000F5F8AF970'
OBJECT_STATUS COUNT(*)
------------------- ----------
INVALID_UNAUTH 2611
VALID 1252
To find the reason why they are not shared - I take only those children that have a reason:
SELECT
2 SQL_ID,
3 CHILD_NUMBER,
4 UNBOUND_CURSOR,
5 SQL_TYPE_MISMATCH,
6 OPTIMIZER_MISMATCH,
7 OUTLINE_MISMATCH,
8 STATS_ROW_MISMATCH,
9 LITERAL_MISMATCH,
10 FORCE_HARD_PARSE,
11 EXPLAIN_PLAN_CURSOR,
12 BUFFERED_DML_MISMATCH,
13 PDML_ENV_MISMATCH,
14 INST_DRTLD_MISMATCH,
15 SLAVE_QC_MISMATCH,
16 TYPECHECK_MISMATCH,
17 AUTH_CHECK_MISMATCH,
18 BIND_MISMATCH,
19 DESCRIBE_MISMATCH,
20 LANGUAGE_MISMATCH,
21 TRANSLATION_MISMATCH,
22 ROW_LEVEL_SEC_MISMATCH,
23 INSUFF_PRIVS,
24 INSUFF_PRIVS_REM,
25 REMOTE_TRANS_MISMATCH,
26 LOGMINER_SESSION_MISMATCH,
27 INCOMP_LTRL_MISMATCH,
28 OVERLAP_TIME_MISMATCH,
29 EDITION_MISMATCH,
30 MV_QUERY_GEN_MISMATCH,
31 USER_BIND_PEEK_MISMATCH,
32 TYPCHK_DEP_MISMATCH,
33 NO_TRIGGER_MISMATCH,
34 FLASHBACK_CURSOR,
35 ANYDATA_TRANSFORMATION,
36 INCOMPLETE_CURSOR,
37 TOP_LEVEL_RPI_CURSOR,
38 DIFFERENT_LONG_LENGTH,
39 LOGICAL_STANDBY_APPLY,
40 DIFF_CALL_DURN,
41 BIND_UACS_DIFF,
42 PLSQL_CMP_SWITCHS_DIFF,
43 CURSOR_PARTS_MISMATCH,
44 STB_OBJECT_MISMATCH,
45 CROSSEDITION_TRIGGER_MISMATCH,
46 PQ_SLAVE_MISMATCH,
47 TOP_LEVEL_DDL_MISMATCH,
48 MULTI_PX_MISMATCH,
49 BIND_PEEKED_PQ_MISMATCH,
50 MV_REWRITE_MISMATCH,
51 ROLL_INVALID_MISMATCH,
52 OPTIMIZER_MODE_MISMATCH,
53 PX_MISMATCH,
54 MV_STALEOBJ_MISMATCH,
55 FLASHBACK_TABLE_MISMATCH,
56 LITREP_COMP_MISMATCH,
57 PLSQL_DEBUG,
58 LOAD_OPTIMIZER_STATS,
59 ACL_MISMATCH,
60 FLASHBACK_ARCHIVE_MISMATCH,
61 LOCK_USER_SCHEMA_FAILED,
62 REMOTE_MAPPING_MISMATCH,
63 LOAD_RUNTIME_HEAP_FAILED,
64 HASH_MATCH_FAILED
65 FROM V$SQL_SHARED_CURSOR
66 WHERE address='&p_address' AND (
67 UNBOUND_CURSOR = 'Y' OR
68 SQL_TYPE_MISMATCH = 'Y' OR
69 OPTIMIZER_MISMATCH = 'Y' OR
70 OUTLINE_MISMATCH = 'Y' OR
71 STATS_ROW_MISMATCH = 'Y' OR
72 LITERAL_MISMATCH = 'Y' OR
73 FORCE_HARD_PARSE = 'Y' OR
74 EXPLAIN_PLAN_CURSOR = 'Y' OR
75 BUFFERED_DML_MISMATCH = 'Y' OR
76 PDML_ENV_MISMATCH = 'Y' OR
77 INST_DRTLD_MISMATCH = 'Y' OR
78 SLAVE_QC_MISMATCH = 'Y' OR
79 TYPECHECK_MISMATCH = 'Y' OR
80 AUTH_CHECK_MISMATCH = 'Y' OR
81 BIND_MISMATCH = 'Y' OR
82 DESCRIBE_MISMATCH = 'Y' OR
83 LANGUAGE_MISMATCH = 'Y' OR
84 TRANSLATION_MISMATCH = 'Y' OR
85 ROW_LEVEL_SEC_MISMATCH = 'Y' OR
86 INSUFF_PRIVS = 'Y' OR
87 INSUFF_PRIVS_REM = 'Y' OR
88 REMOTE_TRANS_MISMATCH = 'Y' OR
89 LOGMINER_SESSION_MISMATCH = 'Y' OR
90 INCOMP_LTRL_MISMATCH = 'Y' OR
91 OVERLAP_TIME_MISMATCH = 'Y' OR
92 EDITION_MISMATCH = 'Y' OR
93 MV_QUERY_GEN_MISMATCH = 'Y' OR
94 USER_BIND_PEEK_MISMATCH = 'Y' OR
95 TYPCHK_DEP_MISMATCH = 'Y' OR
96 NO_TRIGGER_MISMATCH = 'Y' OR
97 FLASHBACK_CURSOR = 'Y' OR
98 ANYDATA_TRANSFORMATION = 'Y' OR
99 INCOMPLETE_CURSOR = 'Y' OR
100 TOP_LEVEL_RPI_CURSOR = 'Y' OR
101 DIFFERENT_LONG_LENGTH = 'Y' OR
102 LOGICAL_STANDBY_APPLY = 'Y' OR
103 DIFF_CALL_DURN = 'Y' OR
104 BIND_UACS_DIFF = 'Y' OR
105 PLSQL_CMP_SWITCHS_DIFF = 'Y' OR
106 CURSOR_PARTS_MISMATCH = 'Y' OR
107 STB_OBJECT_MISMATCH = 'Y' OR
108 CROSSEDITION_TRIGGER_MISMATCH = 'Y' OR
109 PQ_SLAVE_MISMATCH = 'Y' OR
110 TOP_LEVEL_DDL_MISMATCH = 'Y' OR
111 MULTI_PX_MISMATCH = 'Y' OR
112 BIND_PEEKED_PQ_MISMATCH = 'Y' OR
113 MV_REWRITE_MISMATCH = 'Y' OR
114 ROLL_INVALID_MISMATCH = 'Y' OR
115 OPTIMIZER_MODE_MISMATCH = 'Y' OR
116 PX_MISMATCH = 'Y' OR
117 MV_STALEOBJ_MISMATCH = 'Y' OR
118 FLASHBACK_TABLE_MISMATCH = 'Y' OR
119 LITREP_COMP_MISMATCH = 'Y' OR
120 PLSQL_DEBUG = 'Y' OR
121 LOAD_OPTIMIZER_STATS = 'Y' OR
122 ACL_MISMATCH = 'Y' OR
123 FLASHBACK_ARCHIVE_MISMATCH = 'Y' OR
124 LOCK_USER_SCHEMA_FAILED = 'Y' OR
125 REMOTE_MAPPING_MISMATCH = 'Y' OR
126 LOAD_RUNTIME_HEAP_FAILED = 'Y' OR
127 HASH_MATCH_FAILED = 'Y' )
128 ORDER BY child_number;
Enter value for p_address: 0700000F5F8AF970
old 66: WHERE address='&p_address' AND (
new 66: WHERE address='0700000F5F8AF970' AND (
SQL_ID CHILD_NUMBER U S O O S L F E B P I S T A B D L T R I I R L I O E M U T N F A I T D L D B P C S C P T M B M R O P M F L P L A F L R L H
------------- ------------ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
2j8wvg2hby4vr 75 N N N N N N N N N N N N N N N N N N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
2j8wvg2hby4vr 107 N N N N N N N N N N N N N N N N N N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
2j8wvg2hby4vr 108 N N N N N N N N N N N N N N N N N N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
2j8wvg2hby4vr 162 N N N N N N N N N N N N N N N N N N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
2j8wvg2hby4vr 274 N N N N N N N N N N N N N N N N N N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
2j8wvg2hby4vr 331 N N N N N N N N N N N N N N N N N N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
2j8wvg2hby4vr 350 N N N N N N N N N N N N N N N N N N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
2j8wvg2hby4vr 436 N N N N N N N N N N N N N N N N N N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
2j8wvg2hby4vr 443 N N N N N N N N N N N N N N N N N N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
2j8wvg2hby4vr 444 N N N N N N N N N N N N N N N N N N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
2j8wvg2hby4vr 445 N N N N N N N N N N N N N N N N N N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
2j8wvg2hby4vr 446 N N N N N N N N N N N N N N N N N N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
2j8wvg2hby4vr 447 N N N N N N N N N N N N N N N N N N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
2j8wvg2hby4vr 448 N N N N N N N N N N N N N N N N N N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
...
2j8wvg2hby4vr 3866 N N N N N N N N N N N N N N N N N N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
2j8wvg2hby4vr 3867 N N N N N N N N N N N N N N N N N N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
2j8wvg2hby4vr 3868 N N N N N N N N N N N N N N N N N N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
2j8wvg2hby4vr 3869 N N N N N N N N N N N N N N N N N N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
235 rows selected.
Every single child cursor has the reason: ROW_LEVEL_SEC_MISMATCH
Then I try to see how many are there with this particular reason. I find out that there are actually a lot, and then try to compare that number to the total number of child cursors. I find out that there are a lot with no reason (all Ns in v$sql_shared_cursor).
milija@PROD> -- It appears to be a way more efficient to use SQL_ID than to use ADDRESS
emilija@PROD> -- A) give me all SQLs that have version count > 100
emilija@PROD> select sql_id, 'TOTAL_CHILD_CURSORS' as total_child_cursors, count(*) as count
2 from V$SQL
3 group by sql_id, 'TOTAL_CHILD_CURSORS'
4 having count(*) > 100
5 UNION all
6 -- B) for each SQL selected under A) count the total number of child cursors that are not shared because of ROW_LEVEL_SEC_MISMATCH
7 select sql_id, 'TOTAL_ROW_LEVEL_SEC_MISMATCH' as total_child_cursors, count(*) as count
8 from V$SQL_SHARED_CURSOR
9 where sql_id in (select sql_id
10 from v$sql
11 group by sql_id
12 having count(*) > 100)
13 and row_level_sec_mismatch='Y'
14 group by sql_id, 'TOTAL_ROW_LEVEL_SEC_MISMATCH'
15 UNION all
16 -- C) for each SQL selected under A) count the total number of child cursors that do not not have any reason for the mismatch
17 select sql_id, 'X_NO_MISMATCH_CHILD_CURSOR' as total_child_cursors, count(*) as count
18 from V$SQL_SHARED_CURSOR
19 where sql_id in (select sql_id
20 from v$sql
21 group by sql_id
22 having count(*) > 100)
23 AND NOT (
24 UNBOUND_CURSOR = 'Y' OR
25 SQL_TYPE_MISMATCH = 'Y' OR
26 OPTIMIZER_MISMATCH = 'Y' OR
27 OUTLINE_MISMATCH = 'Y' OR
28 STATS_ROW_MISMATCH = 'Y' OR
29 LITERAL_MISMATCH = 'Y' OR
30 FORCE_HARD_PARSE = 'Y' OR
31 EXPLAIN_PLAN_CURSOR = 'Y' OR
32 BUFFERED_DML_MISMATCH = 'Y' OR
33 PDML_ENV_MISMATCH = 'Y' OR
34 INST_DRTLD_MISMATCH = 'Y' OR
35 SLAVE_QC_MISMATCH = 'Y' OR
36 TYPECHECK_MISMATCH = 'Y' OR
37 AUTH_CHECK_MISMATCH = 'Y' OR
38 BIND_MISMATCH = 'Y' OR
39 DESCRIBE_MISMATCH = 'Y' OR
40 LANGUAGE_MISMATCH = 'Y' OR
41 TRANSLATION_MISMATCH = 'Y' OR
42 ROW_LEVEL_SEC_MISMATCH = 'Y' OR
43 INSUFF_PRIVS = 'Y' OR
44 INSUFF_PRIVS_REM = 'Y' OR
45 REMOTE_TRANS_MISMATCH = 'Y' OR
46 LOGMINER_SESSION_MISMATCH = 'Y' OR
47 INCOMP_LTRL_MISMATCH = 'Y' OR
48 OVERLAP_TIME_MISMATCH = 'Y' OR
49 EDITION_MISMATCH = 'Y' OR
50 MV_QUERY_GEN_MISMATCH = 'Y' OR
51 USER_BIND_PEEK_MISMATCH = 'Y' OR
52 TYPCHK_DEP_MISMATCH = 'Y' OR
53 NO_TRIGGER_MISMATCH = 'Y' OR
54 FLASHBACK_CURSOR = 'Y' OR
55 ANYDATA_TRANSFORMATION = 'Y' OR
56 INCOMPLETE_CURSOR = 'Y' OR
57 TOP_LEVEL_RPI_CURSOR = 'Y' OR
58 DIFFERENT_LONG_LENGTH = 'Y' OR
59 LOGICAL_STANDBY_APPLY = 'Y' OR
60 DIFF_CALL_DURN = 'Y' OR
61 BIND_UACS_DIFF = 'Y' OR
62 PLSQL_CMP_SWITCHS_DIFF = 'Y' OR
63 CURSOR_PARTS_MISMATCH = 'Y' OR
64 STB_OBJECT_MISMATCH = 'Y' OR
65 CROSSEDITION_TRIGGER_MISMATCH = 'Y' OR
66 PQ_SLAVE_MISMATCH = 'Y' OR
67 TOP_LEVEL_DDL_MISMATCH = 'Y' OR
68 MULTI_PX_MISMATCH = 'Y' OR
69 BIND_PEEKED_PQ_MISMATCH = 'Y' OR
70 MV_REWRITE_MISMATCH = 'Y' OR
71 ROLL_INVALID_MISMATCH = 'Y' OR
72 OPTIMIZER_MODE_MISMATCH = 'Y' OR
73 PX_MISMATCH = 'Y' OR
74 MV_STALEOBJ_MISMATCH = 'Y' OR
75 FLASHBACK_TABLE_MISMATCH = 'Y' OR
76 LITREP_COMP_MISMATCH = 'Y' OR
77 PLSQL_DEBUG = 'Y' OR
78 LOAD_OPTIMIZER_STATS = 'Y' OR
79 ACL_MISMATCH = 'Y' OR
80 FLASHBACK_ARCHIVE_MISMATCH = 'Y' OR
81 LOCK_USER_SCHEMA_FAILED = 'Y' OR
82 REMOTE_MAPPING_MISMATCH = 'Y' OR
83 LOAD_RUNTIME_HEAP_FAILED = 'Y' OR
84 HASH_MATCH_FAILED = 'Y' )
85 group by sql_id, 'X_NO_MISMATCH_CHILD_CURSOR'
86 order by sql_id, total_child_cursors;
SQL_ID TOTAL_CHILD_CURSORS COUNT
------------- ---------------------------- ----------
000vdjya7x986 TOTAL_CHILD_CURSORS 526
000vdjya7x986 TOTAL_ROW_LEVEL_SEC_MISMATCH 12
000vdjya7x986 X_NO_MISMATCH_CHILD_CURSOR 514
02sxrxhrng0xy TOTAL_CHILD_CURSORS 362
02sxrxhrng0xy TOTAL_ROW_LEVEL_SEC_MISMATCH 169
02sxrxhrng0xy X_NO_MISMATCH_CHILD_CURSOR 193
08qr0x8bz85p5 TOTAL_CHILD_CURSORS 674
08qr0x8bz85p5 TOTAL_ROW_LEVEL_SEC_MISMATCH 20
08qr0x8bz85p5 X_NO_MISMATCH_CHILD_CURSOR 654
09xvrmxqc0n1z TOTAL_CHILD_CURSORS 375
09xvrmxqc0n1z TOTAL_ROW_LEVEL_SEC_MISMATCH 145
09xvrmxqc0n1z X_NO_MISMATCH_CHILD_CURSOR 230
0d0xw464g4u76 TOTAL_CHILD_CURSORS 125
0d0xw464g4u76 TOTAL_ROW_LEVEL_SEC_MISMATCH 1
0d0xw464g4u76 X_NO_MISMATCH_CHILD_CURSOR 124
...
2j8wvg2hby4vr TOTAL_CHILD_CURSORS 3863
2j8wvg2hby4vr TOTAL_ROW_LEVEL_SEC_MISMATCH 235
2j8wvg2hby4vr X_NO_MISMATCH_CHILD_CURSOR 3628
2kafxh4zckxfg TOTAL_CHILD_CURSORS 184
2kafxh4zckxfg TOTAL_ROW_LEVEL_SEC_MISMATCH 175
2kafxh4zckxfg X_NO_MISMATCH_CHILD_CURSOR 9
2m04t3p7jwnp6 TOTAL_CHILD_CURSORS 164
2m04t3p7jwnp6 TOTAL_ROW_LEVEL_SEC_MISMATCH 66
2m04t3p7jwnp6 X_NO_MISMATCH_CHILD_CURSOR 98
2w347y7pth8t6 TOTAL_CHILD_CURSORS 213
2w347y7pth8t6 TOTAL_ROW_LEVEL_SEC_MISMATCH 81
2w347y7pth8t6 X_NO_MISMATCH_CHILD_CURSOR 132
2wrdpa9vcns1m TOTAL_CHILD_CURSORS 817
2wrdpa9vcns1m TOTAL_ROW_LEVEL_SEC_MISMATCH 130
2wrdpa9vcns1m X_NO_MISMATCH_CHILD_CURSOR 687
...
For all child cursors that do not have a reason, give me their cursor_object status
emilija@PROD> SELECT
2 S.OBJECT_STATUS,
3 COUNT(*)
4 FROM V$SQL_SHARED_CURSOR SC,
5 v$sql S
6 WHERE SC.address=S.address and SC.child_number=S.child_number
7 AND S.address='&p_address' AND NOT (
8 UNBOUND_CURSOR = 'Y' OR
9 SQL_TYPE_MISMATCH = 'Y' OR
10 OPTIMIZER_MISMATCH = 'Y' OR
11 OUTLINE_MISMATCH = 'Y' OR
12 STATS_ROW_MISMATCH = 'Y' OR
13 LITERAL_MISMATCH = 'Y' OR
14 FORCE_HARD_PARSE = 'Y' OR
15 EXPLAIN_PLAN_CURSOR = 'Y' OR
16 BUFFERED_DML_MISMATCH = 'Y' OR
17 PDML_ENV_MISMATCH = 'Y' OR
18 INST_DRTLD_MISMATCH = 'Y' OR
19 SLAVE_QC_MISMATCH = 'Y' OR
20 TYPECHECK_MISMATCH = 'Y' OR
21 AUTH_CHECK_MISMATCH = 'Y' OR
22 BIND_MISMATCH = 'Y' OR
23 DESCRIBE_MISMATCH = 'Y' OR
24 LANGUAGE_MISMATCH = 'Y' OR
25 TRANSLATION_MISMATCH = 'Y' OR
26 ROW_LEVEL_SEC_MISMATCH = 'Y' OR
27 INSUFF_PRIVS = 'Y' OR
28 INSUFF_PRIVS_REM = 'Y' OR
29 REMOTE_TRANS_MISMATCH = 'Y' OR
30 LOGMINER_SESSION_MISMATCH = 'Y' OR
31 INCOMP_LTRL_MISMATCH = 'Y' OR
32 OVERLAP_TIME_MISMATCH = 'Y' OR
33 EDITION_MISMATCH = 'Y' OR
34 MV_QUERY_GEN_MISMATCH = 'Y' OR
35 USER_BIND_PEEK_MISMATCH = 'Y' OR
36 TYPCHK_DEP_MISMATCH = 'Y' OR
37 NO_TRIGGER_MISMATCH = 'Y' OR
38 FLASHBACK_CURSOR = 'Y' OR
39 ANYDATA_TRANSFORMATION = 'Y' OR
40 INCOMPLETE_CURSOR = 'Y' OR
41 TOP_LEVEL_RPI_CURSOR = 'Y' OR
42 DIFFERENT_LONG_LENGTH = 'Y' OR
43 LOGICAL_STANDBY_APPLY = 'Y' OR
44 DIFF_CALL_DURN = 'Y' OR
45 BIND_UACS_DIFF = 'Y' OR
46 PLSQL_CMP_SWITCHS_DIFF = 'Y' OR
47 CURSOR_PARTS_MISMATCH = 'Y' OR
48 STB_OBJECT_MISMATCH = 'Y' OR
49 CROSSEDITION_TRIGGER_MISMATCH = 'Y' OR
50 PQ_SLAVE_MISMATCH = 'Y' OR
51 TOP_LEVEL_DDL_MISMATCH = 'Y' OR
52 MULTI_PX_MISMATCH = 'Y' OR
53 BIND_PEEKED_PQ_MISMATCH = 'Y' OR
54 MV_REWRITE_MISMATCH = 'Y' OR
55 ROLL_INVALID_MISMATCH = 'Y' OR
56 OPTIMIZER_MODE_MISMATCH = 'Y' OR
57 PX_MISMATCH = 'Y' OR
58 MV_STALEOBJ_MISMATCH = 'Y' OR
59 FLASHBACK_TABLE_MISMATCH = 'Y' OR
60 LITREP_COMP_MISMATCH = 'Y' OR
61 PLSQL_DEBUG = 'Y' OR
62 LOAD_OPTIMIZER_STATS = 'Y' OR
63 ACL_MISMATCH = 'Y' OR
64 FLASHBACK_ARCHIVE_MISMATCH = 'Y' OR
65 LOCK_USER_SCHEMA_FAILED = 'Y' OR
66 REMOTE_MAPPING_MISMATCH = 'Y' OR
67 LOAD_RUNTIME_HEAP_FAILED = 'Y' OR
68 HASH_MATCH_FAILED = 'Y' )
69 GROUP BY S.OBJECT_STATUS;
Enter value for p_address: 0700000F5F8AF970
old 7: AND S.address='&p_address' AND NOT (
new 7: AND S.address='0700000F5F8AF970' AND NOT (
OBJECT_STATUS COUNT(*)
------------------- ----------
INVALID_UNAUTH 2436
VALID 1192
emilija@PROD> /
Enter value for p_address: 0700000F57966FB8
old 7: AND S.address='&p_address' AND NOT (
new 7: AND S.address='0700000F57966FB8' AND NOT (
OBJECT_STATUS COUNT(*)
------------------- ----------
INVALID_UNAUTH 1554
VALID 338