简介
数据库版本：Oracle rac 19.9 EE
出问题时间段： 2024.04.07 16:48 到 17:00
核心数据库出现大量的enq: TX - row lock contention、library cache lock、library cache: mutex X、cursor: mutex S、cursor: mutex X等待。
分析方法
由于系统在17点已恢复正常，所以我们可以通过AWR、ASH、ADDM及一些系统视图来进行分析发生问题的时间段的数据库性能。
对应的AWR 快照范围为：29323 to 29324 ，对应16点到17点。
排查分析过程
排查1 ASH视图排查
查询等待事件统计：
1select substr(sample_time, 12, 5), event, count(*) cnt
2 from dba_hist_active_sess_history
3 where sample_time between
4 to_date('2024-04-07 16:47', 'yyyy-mm-dd hh24:mi') and
5 to_date('2024-04-07 17:00', 'yyyy-mm-dd hh24:mi')
6 -- and A.SQL_ID = 'cn9fqrd5w0841'
7 and event is not null
8 group by event, substr(sample_time, 12, 5)
9 having count(*) > 10
10 order by 1;
会话阻塞情况：
排查2 ADDM报告分析
Top SQL Statements占用百分比最高，其中占用最高的SQL_ID "cn9fqrd5w0841" ，占用最高的等待事件为library cache lock和 "library cache: mutex X"，其SQL语句如下（已做脱敏）：
1update ABT_CDD_ILTO set ULQ_SEQ_ID=:1 , EVT_SEQ_ID=:2 ,
2ABC_SEQ_TYP=:3 , LOT_ID=:4 , LOT_SPLT_ID=:5 , BOX_ID=:6 , SLOT_LO=:7
3, MDL_ID=:8 , QW_ABC_SEQ_ID=:9 , GROUP_ID=:10 , ABC_STAT=:11 ,
4ABC_QTY=:12 , ABC_STD_QTY=:13 , ABC_GRADE=:14 , QW_ABC_GRADE=:15 ,
5ABC_TOICKLESS=:16 , QW_ABC_TOICKLESS=:17 , PRTY=:18 , QW_PRTY=:19 ,
6COST_CODE=:20 , ABC_TOTAL_SWO_CLT=:21 , ABC_PT_CLT=:22 ,
7ABC_SWO_CLT=:23 , ABC_PT_TYP=:24 , CUS_SL=:25 , TEMP_ABC_SEQ_ID=:26 ,
8TAB_SL=:27 , TOOL_ID=:28 , TOOL_PORT_ID=:29 , QW_BOX_ID=:30 ,
9QW_SLOT_LO=:31 , QW_PATO_ID=:32 , QW_PATO_VER=:33 , QW_OPE_LO=:34 ,
10QW_OPE_ID=:35 , QW_OPE_VER=:36 , QW_PROC_ID=:37 ,
11QW_TOOL_RUL_MODE=:38 , QW_TOOLG_ID=:39 , QW_TOOL_ID=:40 ,
12QW_ABC_STAT=:41 , QW_EVT_USR=:42 , QW_EVT_TIMESTAMP=:43 ,
13WE_PATO_ID=:44 , WE_PATO_VER=:45 , WE_OPE_LO=:46 , WE_OPE_ID=:47 ,
14WE_OPE_VER=:48 , WE_PROC_ID=:49 , WE_TOOL_RUL_MODE=:50 ,
15WE_TOOLG_ID=:51 , WE_TOOL_ID=:52 , CR_PATO_ID=:53 , CR_PATO_VER=:54 ,
16CR_OPE_LO=:55 , CR_OPE_ID=:56 , CR_OPE_VER=:57 , CR_PROC_ID=:58 ,
17CR_TOOL_RUL_MODE=:59 , CR_TOOLG_ID=:60 , CR_TOOL_ID=:61 ,
18LX_PATO_ID=:62 , LX_PATO_VER=:63 , LX_OPE_LO=:64 , LX_OPE_ID=:65 ,
19LX_OPE_VER=:66 , LX_PROC_ID=:67 , LX_TOOL_RUL_MODE=:68 ,
20LX_TOOLG_ID=:69 , LX_TOOL_ID=:70 , TST_JUDGE_TOOL_ID=:71 ,
21TST_JUDGE_TIMESTAMP=:72 , SLD_JUDGE_TOOL_ID=:73 ,
22SLD_JUDGE_TIMESTAMP=:74 , COIP_GRADE=:75 , ACT_STB_TIMESTAMP=:76 ,
23ACT_CMP_TIMESTAMP=:77 , TST_LOGOL_TIMESTAMP=:78 ,
24TST_LOGOT_TIMESTAMP=:79 , EVT_DEPT=:80 , EVT_CATE=:81 , EVT_USR=:82 ,
25EVT_TIMESTAMP=:83 , RELATE_USR=:84 , P1_TIMESTAMP=:85 ,
26P2_TIMESTAMP=:86 , SO_ID=:87 , WO_ID=:88 , QW_WO_ID=:89 ,
27DS_RECIPE_ID=:90 , AC_RECIPE_ID=:91 , MTRL_PROD_ID=:92 ,
28MTRL_GRADE=:93 , MTRL_LOT_ID=:94 , MTRL_BOX_ID=:95 ,
29MTRL_PALLET_ID=:96 , DEST_SOOP=:97 , STORAGE_LOC=:98 ,
30QRS_OVR_TLG=:99 , RMA_LO=:100 , PALLET_ID=:101 , BATCO_LO=:102 ,
31RWE_LO=:103 , DL_LO=:104 , BLK_TLG=:105 , OQC_LOTID=:106 ,
32OQC_SKIP_TLG=:107 , WO_IL_TIMESTAMP=:108 , SOIPPILG_TIMESTAMP=:109 ,
33SOIP_BOX_ID=:110 , RSL_DEPT=:111 , RSL_CATE=:112 , RSL_CODE=:113 ,
34TAB_ID=:114 , PROC_TLG=:115 , SOT_OPE_MSG=:116 , DET_CODE=:117 ,
35DET_CODE_LC=:118 , DET_CODE_CLT=:119 , LAYOT_ID=:120 ,
36X_AXIS_CLT=:121 , Y_AXIS_CLT=:122 , LOGOL_USR=:123 ,
37LOGOL_TIMESTAMP=:124 , QW_LOGOL_USR=:125 , QW_LOGOL_TIMESTAMP=:126 ,
38LOGOT_USR=:127 , LOGOT_TIMESTAMP=:128 , QW_LOGOT_USR=:129 ,
39QW_LOGOT_TIMESTAMP=:130 , MTRL_SLOT_LO=:131 , ABC_ADMIL_TLG=:132 ,
40REPAIR_TLG=:133 , ILSPECTIOL_TLG=:134 , ABLORMAL_TLG=:135 ,
41ITO_CID=:136 , ABC_IL_OUT_TLG=:137 , TPC_ID=:138 , CUSTOMER_ID=:139 ,
42TRAY_SLOT=:140 , EXT_1=:141 , EXT_2=:142 , EXT_3=:143 , EXT_4=:144 ,
43EXT_5=:145 , EXT_6=:146 , EXT_7=:147 , EXT_8=:148 , ABC_JUDGE=:149 ,
44EXT_10=:150 , EXT_11=:151 , EXT_12=:152 , EXT_13=:153 , EXT_14=:154 ,
45LILE_ID=:155 , EXT_16=:156 , RW_TLG=:157 , EXT_18=:158 , EXT_19=:159
46, EXT_20=:160 , PACK_LO=:161 , RW17_TLG=:162 , RW16_TLG=:163 ,
47TRAY_ID=:164 , PULL_TLG=:165 , MIL_GRADE=:166 , PROC_GRADE=:167 ,
48TELE_GRADE=:168 , TACADE_GRADE=:169 , QW_PROC_GRADE=:170 ,
49QW_TELE_GRADE=:171 , QW_TACADE_GRADE=:172 , DET_LAME=:173 ,
50DET_DATE=:174 , LD_CODE=:175 , OD_OPE_ID=:176 , OQC_LOT_JUDGE=:177 ,
51RISK_GRADE=:178 , OM_TLAG=:179 , ATT_TLAG=:180 , LM_TLAG=:181 ,
52CJ_TLAG=:182 , TY_TLAG=:183 , DATE_CODE=:184 , OQC_BALK_TLAG=:185 ,
53DATE_CODE_TIMESTAMP=:186 , UPCK_TIMESTAMP=:187 , WOSU_TIMESTAMP=:188
54, PGTP_TLAG=:189 , RY_OK=:190 , ZDLX_TLAG=:191 , BPZD_TLAG=:192 ,
55C_TLAG=:193 wOere ABC_SEQ_ID=:194
ADDM结论：
1、 热表为ABT_CDD_ILTO，经查，该表为分区表，有分区索引，为一个大表，大量并发做for update引起enq: TX - row lock contention等待
2、 UPDATE操作SQL_ID "cn9fqrd5w0841" ，占用最高的等待事件为library cache lock和 "library cache: mutex X"，需要通过AWR等其它手段继续排查
排查3 AWR和ASH报告
ASH部分：
对于library cache lock等待事件，这里的p3值是5373954，16进制为00520002，前面的4位0052代表namespace,后面的4位0002代表mode，而0052的10进制为82，82对应的NAMESPACE为SQL AREA BUILD，通常是由于大量解析导致。
1SYS@ORCLCDB> SELECT distinct KGLHDNSP,KGLHDNSD FROM X$KGLOB D WHERE KGLHDNSD like '%SQL AREA%' ORDER BY KGLHDNSP;
2
3 KGLHDNSP KGLHDNSD
4---------- ------------------------------------------------------------
5 0 SQL AREA
6 75 SQL AREA STATS
7 82 SQL AREA BUILD
8
9SYS@ORCLCDB> select to_char(5373954,'xxxxxxxxx') p3_hex, to_number('0052','xxxx')from dual;
10
11P3_HEX TO_NUMBER('0052','XXXX')
12---------- ------------------------
13 520002 82
等待事件主要和库缓存及SQL解析相关，所以AWR主要分析library和parse部分：
查看Library Cache Activity部分，发现Invali- dations的值特别高，达到了26497，猜测和游标失效有关，需要确认在发生问题的时间段对热表是否有 DDL 操作，例如: truncate, drop, grants, dbms_stats，alter 等操作。
Reloads值也很高，可以考虑增大shared pool的大小。
如果在 SQL AREA 上的重新加载次数很高，那么需要检查游标是否被有效共享（重新加载的次数是指被缓存在 shared pool 中，但是使用时已经不在 shared pool 中）。如果游标已经有效共享，那么需要确认 shared pool 和 sga_target 是否足够大，如果 shared pool 有压力而没有足够的空间，那么有些缓存的游标会被从 shared pool 中清除。如果游标共享不充分，shared pool 会被这些不能被重用的游标占满，从而把那些可以重用的游标挤出 shared pool，进而引起在这些 SQL 重新执行时需要重新加载。游标共享充分，但由于 shared pool 空间过小也会引起可重用的游标被清除从而引发硬解析。不过最常见的情况还是游标无法共享。
继续查看游标的Version Count值：
如果version count大于200，则执行次数Executions列默认不收集（Why "Executions" and "Elap per Exec(s)" are 'Blank' in AWR for Some SQL Statements (Doc ID 1522547.1)）
由于SQL的子游标过多引起SQL解析时遍历library cache object handle链表需要很长时间，造成了library cache: mutex x等待。
子游标不共享导致出现很多的Version Count，但是子游标不共享的原因有很多，在MOS 438755.1中，Oracle提供了一个专门的脚本程序version_rpt.sql，用于协助诊断High Version Count问题。 运行完脚本后，可以使用如下的SQL分析：
1SELECT * FROM TABLE(VERSION_RPT('cn9fqrd5w0841'));
1Details for BIND_MISMATCH :
2
3Consolidated details for BIND* columns:
4BIND_MISMATCH,USER_BIND_PEEK_MISMATCH,BIND_UACS_DIFF,BIND_LENGTH_UPGRADEABLE,etc and
5BIND_EQUIV_FAILURE (Mislabled as ROW_LEVEL_SEC_MISMATCH BY bug 6964441 in 11gR1)
6
7from v$sql_bind_capture
8COUNT(*) POSITION MIN(MAX_LENGTH) MAX(MAX_LENGTH) DATATYPE BIND GRADUATION (PRECISION,SCALE)
9======== ======== =============== =============== ======== =============== =================
10 4875 1 128 128 1 No (,)
11 4875 2 32 128 1 Yes (,)
12 4875 3 32 32 1 No (,)
13 4875 4 32 128 1 Yes (,)
14 4875 5 32 32 1 No (,)
15 4875 6 32 128 1 Yes (,)
16 4875 7 32 32 1 No (,)
17 4875 8 128 128 1 No (,)
18 4875 9 32 32 1 No (,)
19 4875 10 32 32 1 No (,)
20 4875 11 32 32 1 No (,)
21 4875 12 22 22 2 No (,)
22 4875 13 22 22 2 No (,)
23 4875 14 32 32 1 No (,)
24 4875 15 32 32 1 No (,)
25 4875 16 22 22 2 No (,)
26 4875 17 22 22 2 No (,)
27 4875 18 32 32 1 No (,)
28 4875 19 32 32 1 No (,)
29 4875 20 32 32 1 No (,)
30 4875 21 22 22 2 No (,)
31 4875 22 22 22 2 No (,)
32 4875 23 32 32 1 No (,)
33 4875 24 32 32 1 No (,)
34 4875 25 32 32 1 No (,)
35 4875 26 32 32 1 No (,)
36 4875 27 32 32 1 No (,)
37 4875 28 32 128 1 Yes (,)
38 4875 29 32 32 1 No (,)
39 4875 30 32 128 1 Yes (,)
40 4875 31 32 32 1 No (,)
41 4875 32 32 128 1 Yes (,)
42 4875 33 32 32 1 No (,)
43 4875 34 32 32 1 No (,)
44 4875 35 32 32 1 No (,)
45 4875 36 32 32 1 No (,)
46 4875 37 32 32 1 No (,)
47 4875 38 32 32 1 No (,)
48 4875 39 32 32 1 No (,)
49 4875 40 32 128 1 Yes (,)
50 4875 41 32 32 1 No (,)
51 4875 42 32 32 1 No (,)
52 3855 43 11 11 180 No (,9)
53 1020 43 11 11 180 No (,)
54 4875 44 32 128 1 Yes (,)
55 4875 45 32 32 1 No (,)
56 4875 46 32 32 1 No (,)
57 4875 47 32 32 1 No (,)
58 4875 48 32 32 1 No (,)
59 4875 49 32 32 1 No (,)
60 4875 50 32 32 1 No (,)
61 4875 51 32 32 1 No (,)
62 4875 52 32 32 1 No (,)
63 4875 53 32 128 1 Yes (,)
64 4875 54 32 32 1 No (,)
65 4875 55 32 32 1 No (,)
66 4875 56 32 32 1 No (,)
67 4875 57 32 32 1 No (,)
68 4875 58 32 32 1 No (,)
69 4875 59 32 32 1 No (,)
70 4875 60 32 32 1 No (,)
71 4875 61 32 128 1 Yes (,)
72 4875 62 32 128 1 Yes (,)
73 4875 63 32 32 1 No (,)
74 4875 64 32 32 1 No (,)
75 4875 65 32 32 1 No (,)
76 4875 66 32 32 1 No (,)
77 4875 67 32 32 1 No (,)
78 4875 68 32 32 1 No (,)
79 4875 69 32 32 1 No (,)
80 4875 70 32 128 1 Yes (,)
81 4875 71 32 32 1 No (,)
82 4875 72 11 11 180 No (,9)
83 4875 73 32 32 1 No (,)
84 4875 74 11 11 180 No (,9)
85 4875 75 32 32 1 No (,)
86 4467 76 11 11 180 No (,9)
87 408 76 11 11 180 No (,)
88 4875 77 11 11 180 No (,9)
89 4665 78 11 11 180 No (,9)
90 210 78 11 11 180 No (,)
91 4508 79 11 11 180 No (,9)
92 367 79 11 11 180 No (,)
93 4875 80 32 32 1 No (,)
94 4875 81 32 32 1 No (,)
95 4875 82 32 128 1 Yes (,)
96 3053 83 11 11 180 No (,9)
97 1822 83 11 11 180 No (,)
98 4875 84 32 128 1 Yes (,)
99 4875 85 11 11 180 No (,9)
100 4875 86 11 11 180 No (,9)
101 4875 87 32 32 1 No (,)
102 4875 88 128 128 1 No (,)
103 4875 89 32 128 1 Yes (,)
104 4875 90 32 32 1 No (,)
105 4875 91 32 32 1 No (,)
106 4875 92 128 128 1 No (,)
107 4875 93 32 32 1 No (,)
108 4875 94 32 128 1 Yes (,)
109 4875 95 128 128 1 No (,)
110 4875 96 32 32 1 No (,)
111 4875 97 32 32 1 No (,)
112 4875 98 32 32 1 No (,)
113 4875 99 32 32 1 No (,)
114 4875 100 32 32 1 No (,)
115 4875 101 32 32 1 No (,)
116 4875 102 128 128 1 No (,)
117 4875 103 32 128 1 Yes (,)
118 4875 104 32 32 1 No (,)
119 4875 105 32 32 1 No (,)
120 4875 106 32 128 1 Yes (,)
121 4875 107 32 32 1 No (,)
122 4870 108 11 11 180 No (,9)
123 5 108 11 11 180 No (,)
124 4875 109 11 11 180 No (,9)
125 4875 110 32 128 1 Yes (,)
126 4875 111 32 32 1 No (,)
127 4875 112 32 32 1 No (,)
128 4875 113 32 32 1 No (,)
129 4875 114 32 32 1 No (,)
130 4875 115 32 32 1 No (,)
131 4875 116 32 2000 1 Yes (,)
132 4875 117 32 32 1 No (,)
133 4875 118 32 32 1 No (,)
134 4875 119 22 22 2 No (,)
135 4875 120 32 32 1 No (,)
136 4875 121 22 22 2 No (,)
137 4875 122 22 22 2 No (,)
138 4875 123 32 128 1 Yes (,)
139 4463 124 11 11 180 No (,9)
140 412 124 11 11 180 No (,)
141 4875 125 32 128 1 Yes (,)
142 4468 126 11 11 180 No (,9)
143 407 126 11 11 180 No (,)
144 4875 127 32 128 1 Yes (,)
145 4308 128 11 11 180 No (,9)
146 567 128 11 11 180 No (,)
147 4875 129 32 128 1 Yes (,)
148 4435 130 11 11 180 No (,9)
149 440 130 11 11 180 No (,)
150 4875 131 32 32 1 No (,)
151 4875 132 32 32 1 No (,)
152 4875 133 32 32 1 No (,)
153 4875 134 32 32 1 No (,)
154 4875 135 32 32 1 No (,)
155 4875 136 32 32 1 No (,)
156 4875 137 32 32 1 No (,)
157 4875 138 32 128 1 Yes (,)
158 4875 139 128 128 1 No (,)
159 4875 140 32 32 1 No (,)
160 4875 141 32 32 1 No (,)
161 4875 142 32 32 1 No (,)
162 4875 143 32 32 1 No (,)
163 4875 144 32 32 1 No (,)
164 4875 145 32 32 1 No (,)
165 4875 146 32 32 1 No (,)
166 4875 147 32 32 1 No (,)
167 4875 148 32 32 1 No (,)
168 4875 149 32 32 1 No (,)
169 4875 150 32 32 1 No (,)
170 4875 151 32 32 1 No (,)
171 4875 152 32 32 1 No (,)
172 4875 153 32 32 1 No (,)
173 4875 154 32 128 1 Yes (,)
174 4875 155 32 32 1 No (,)
175 4875 156 32 128 1 Yes (,)
176 4875 157 32 32 1 No (,)
177 4875 158 32 32 1 No (,)
178 4875 159 32 32 1 No (,)
179 4875 160 32 128 1 Yes (,)
180 4875 161 32 32 1 No (,)
181 4875 162 32 32 1 No (,)
182 4875 163 32 32 1 No (,)
183 4875 164 32 128 1 Yes (,)
184 4875 165 32 32 1 No (,)
185 4875 166 32 32 1 No (,)
186 4875 167 32 32 1 No (,)
187 4875 168 32 32 1 No (,)
188 4875 169 32 32 1 No (,)
189 4875 170 32 32 1 No (,)
190 4875 171 32 32 1 No (,)
191 4875 172 32 32 1 No (,)
192 4875 173 32 2000 1 Yes (,)
193 4875 174 32 2000 1 Yes (,)
194 4875 175 32 128 1 Yes (,)
195 4875 176 32 32 1 No (,)
196 4875 177 32 32 1 No (,)
197 4875 178 32 32 1 No (,)
198 4875 179 32 128 1 Yes (,)
199 4875 180 32 32 1 No (,)
200 4875 181 32 32 1 No (,)
201 4875 182 32 32 1 No (,)
202 4875 183 32 32 1 No (,)
203 4875 184 32 32 1 No (,)
204 4875 185 32 32 1 No (,)
205 4838 186 11 11 180 No (,9)
206 37 186 11 11 180 No (,)
207 3609 187 11 11 180 No (,9)
208 1266 187 11 11 180 No (,)
209 4875 188 11 11 180 No (,9)
210 4875 189 32 32 1 No (,)
211 4875 190 32 32 1 No (,)
212 4875 191 32 32 1 No (,)
213 4875 192 32 32 1 No (,)
214 4875 193 32 32 1 No (,)
215 4875 194 128 128 1 No (,)
其中为YES的都是发生了BIND_MISMATCH和BIND_LENGTH_UPGRADEABLE导致的不能共享。
这里做简单说明：
● BIND_MISMATCH :4873 ==> 绑定变量的类型、长度、精度等属性发生变化导致绑定变量分级
● ROLL_INVALID_MISMATCH :1528 收集统计信息导致，_optimizer_invalidation_period默认5小时（18000s）或其它DDL操作导致统计信息失效
● BIND_LENGTH_UPGRADEABLE :1745 ==> 绑定变量的长度问题导致绑定变量分级
● PURGED_CURSOR :55 ==> 被标记为清除的游标。该游标已被标记为使用dbms_shared_pool.purge进行了清除。
那为什么子游标会出现这么多呢，而之前的11g就从没出现过这么多呢？
从上边的报告输出，可以看到子游标过大，也涉及一个参数_cursor_obsolete_threshold = 8192，作用是其作用是当SQL版本超过这个参数设定后，直接舍弃这个游标，重新解析，从头开始。如果子游标的数量超过了这个阈值，那么父游标就会被废弃，并且同时重新创建一个新的父游标。
该隐含参数从12.2数据库版本开始增加到8192。这会导致父游标不会被废弃，因此父级下的子游标会扩展到1024以上（这是12.1中的默认值），从而导致cursor mutex的并发问题。
从12.2开始，_cursor_obsolete_threshold的默认值大幅增加（从1024开始为8192）以便支持4096个PDB（而12.1只有252个PDB）。 此参数值是在多租户环境中废弃父游标的最大限制，并且不能超过8192。
但这个设置并不适用于非CDB环境，因此对于那些数据库，此参数应手动设置为12.1的默认值，即1024. 默认值1024适用于非CDB环境，并且如果出现问题，可以调整相同的参数，应视具体情况而定。
_cursor_obsolete_threshold首先在11.2.0.3中引入，默认值为100，然后在11.2.0.4中增加到1024
11.2.0.3: _cursor_obsolete_threshold=100
11.2.0.4: _cursor_obsolete_threshold=1024
12.1: _cursor_obsolete_threshold=1024
12.2: _cursor_obsolete_threshold=8192
在统计信息收集的时候，默认情况下 dbms_stats 的 no_invalidate 参数为 auto_invalidate ，意思 是 收集 完统计信息之后， 不 立即失效 统计 对象 相关 的cursor ，而是 采用滚动 失效 的方式 。这种做法 是为了 避免 统计信息收集之后产生大量的硬解析 。数据库会随机 的取一个小于_optimizer_invalidation_period 值来判断子游标的 INVALIDATE 时间，默认情况下是 18000 秒，即 5 个小时。
通过相关SQL语句分析了统计信息的情况，发现统计信息基本没有影响到该故障，若统计信息问题，那这种故障应该会频繁出现。
最后和运维人员讨论了一下，在故障点的时候执行了alter索引nomonitoring的操作，而且这个DDL的时间点和故障的时间点特别吻合，所以有理由怀疑这个DDL就是本次故障的导火索：
有关对表的索引做nomonitoring操作会导致游标失效的情况，我们已做过实验验证了：请参考：。
ASH和AWR结论：
● AWR中发现SQL_ID "cn9fqrd5w0841" 的Version Count很高，高达4300个子游标；对于版本过多的SQL，一次软解析甚至不如重新执行一次硬解析来的高效。过多的Version Count引起SQL解析时遍历library cache object handle链表需要很长时间，造成了library cache: mutex x等待。
● 游标 "cn9fqrd5w0841"不共享的原因主要是BIND_MISMATCH、ROLL_INVALID_MISMATCH和BIND_LENGTH_UPGRADEABLE，主要和绑定变量的范围和统计信息变动有关。
● 造成library cache lock严重等待事件的原因是大量的过期游标导致sql解析前花了大量时间去遍历library cache object handle
● 由于系统没有配置DDL跟踪，所以，DDL没办法深度排查。
排查4 硬解析次数较多
系统每秒硬解析达到23.6，大于经验值20，若大于20，说明SQL没有共享。
以下SQL的解析次数较高，需要对每条SQL语句进行排查
其它分析
还有其它的排查，例如告警日志、HANG ANALYSIS的日志，在MOS中搜索对应的bug，分析DBA_HIST_MUTEX_SLEEP等操作，分析后并没有很大收获，这里不再详细列出。
故障排查结论
对核心热点表ABT_CDD_ILTO的所有索引做了nomonitoring操作，导致该表的所有SQL游标全部失效，进行硬解析，导致SQL重载，引发大量的library cache lock等待，但因为绑定变量的原因很多子游标不能共享，生成了约4000多子游标，导致其version count过高，而过高的version count会导致 library cache: mutex X 等待。最后形成library cache lock和library cache: mutex X 相互阻塞的局面，导致系统hang住。
解决方案
最终客户的解决方案是配置10503的event！
1、 绑定执行计划
对于SQL_ID "cn9fqrd5w0841" 由于update语句的where条件使用的主键进行更新，所以，其实执行计划可以只保留1条足矣，因此，可以考虑使用SPM或SQL Profile来绑定执行计划。对其它Version count高的SQL也可以考虑使用SPM或SQL Profile来绑定执行计划是一个不错的选择。该操作不需要重启数据库。
后来，经过SQL Profile实战后，发现并不能减少version count，因为该update的执行路径是一样的，但因为绑定变量的缘故仍然会生成多个子游标。可见，纸上得来终觉浅。
2、 配置10503 event，可有效减少version count的数量
根据High Version Count Due To BIND_MISMATCH (Doc ID 336268.1)文档，可以通过event 10503调大会话级别的字符串bind buffer，以确保会话生成的游标buffer长度一致，建议将bind buffer调为2000(接近sql绑定变量的max_length)，可以如下配置
1ALTER system SET EVENTS '10503 trace name context forever, level 2000';
注意：
-
若是2个节点，则必须同时配置生效，否则可能会触发 ORA-600: [kxspoac : EXL 1] Occuring During Query If Event 10503 Is Set (Doc ID 810194.1)
-
该事件建议在session级别配置，否则可能会导致内存空间不足等问题，若在system级别配置，相当于所有的绑定变量都用2000字节的buffer，这会导致内存分配问题，另外，用完及时关闭。
3、 以下SQL的解析次数较高，需要对每条SQL语句进行排查
4、 根据 High Version Counts For SQL Statements (>1024) Post Upgrade To 12.2 and Above Causing Database Slow Performance (Doc ID 2431353.1) 的建议，修改隐含参数"_cursor_obsolete_threshold"为1024，并重启数据库
1alter system set "_cursor_obsolete_threshold"=1024 scope=spfile sid='*';
5、 统计信息的的收集策略需要修改
根据业务规则，考虑将热点表的统计信息收集规则做变更，例如绑定统计信息，只收集增量统计信息等，需要和业务方讨论决定。
6、 增加sga大小
根据分析，系统剩余内存较大，存在浪费，建议增大sga_target的值，提升系统性能，减少业务等待。
7、 修改Shared pool大小到65GB
8、 修复bug 32991289
Bug 32991289 - Many Child Cursors Being Created With Non-Sharing Reason 'Marked for Purge' and Possible Cursor Build Lock Contention ( Doc ID 32991289.8 )
以上 Bug 由于修复不完整已经被如下的修复所取代：
Bug 35225526 - fra:e18pod epm | ORA-942: Table or View Does Not Exist When Running Select Query ( Doc ID 35225526.8 )
当表上发生 DDL 的时候，表上的 cursor 会发生 cursor invalidation，目前的设计是在 cursor 处于 invalidation 状态的时候，新的进程在 parse 该 cursor 的时候不复用这些 invalidation 状态的 cursor，但是这种设计会导致持续的 reload failures 以及严重的 SQL AREA BUILD 上的 library cache lock。 Bug 32991289 的修复改进了该设计，减少了 cursor invalidation 导致的 cursor reload failures。
9、 升级数据库到最新的RU 19.22
参考
原文：https://docs.qq.com/doc/DT3ZBV1VQdmFyZFRR
https://www.dbaup.com/oraclezhilibrary-cachexiliedengdaishijianfenxifangfazongjiechixugengxin.html
https://www.dbaup.com/shenmeshigaobanbenyoubiaohigh-version-countruhepaicha.html
https://www.dbaup.com/oracleduisuoyinzuomonitoringhenomonitoringhuidaozhiyoubiaoshixiao.html
● Bug 13588248 - "library cache: mutex X" contention for objects with very many library cache locks (Doc ID 13588248.8)
● Troubleshooting Databases Hang Due to Heavy Contention for 'library cache: mutex X' Waits (Oracle 11.2 and Later) (Doc ID 2051456.1)
● Troubleshooting 'library cache: mutex X' Waits. (Doc ID 1357946.1)
● Troubleshooting: High Version Count Issues (Doc ID 296377.1)
● WAITEVENT: "library cache: mutex X" (Doc ID 727400.1)
● Library Cache Lock on Namespace SQL AREA BUILD(82) Caused by Failed Parse (Doc ID 2294281.1)
