简介
在Oracle中，导致游标失效的原因有很多，例如，当对对象（如表或视图）进行DDL 或收集统计信息时，依赖于它们的游标将失效。这里的DDL包括truncate表、索引重建、monitoring和nomonitoring索引，grant、alter操作等等， 这将导致游标在下一次执行时被硬解析，并会影响 CPU 和发生锁竞争。如果存在并发的DDL操作和DML，而DDL一直未完成，此时DDL会持有该对象的library cache object handle的X Lock，DML会请求该对象的Library cache object handle的S lock模式，此时DML就会被hang住。
1此时，AWR 或者 statspack 报告:
2
3 - Library Cache statistics 部分显示 reloads 数很高并且 (每小时几千次) invalidations （ Invali- dations）也很高。
4 - "% SQL with executions>1" 超过 60%, 表明SQL语句共享率较高。
5 - 确认 Dictionary Statistics 部分中的“Modification Requests”的值是否为0，这意味着一些对象上有DDL在执行。
Library cache object 失效过多的解决方法：
-
不要在数据库繁忙的时间段执行DDL或DCL：DDL 或DCL语句使库缓存对象（Library cache object）失效，并会涉及到许多依赖对象，比如游标。 对象失效后需要同时进行多次硬解析，对库缓存、共享池、字典缓存、CPU使用产生很大影响。 这里的DDL包括truncate表、索引重建、monitoring和nomonitoring索引，grant操作等等都会引起游标失效。
-
不要在数据库繁忙的时间段收集统计信息：收集统计信息（ANALYZE或者DBMS_STATS）会使库缓存对象失效，并会涉及到许多依赖对象，比如游标。 对象失效后需要同时进行多次硬解析，对库缓存、共享池、字典缓存、CPU使用产生很大影响。
-
不要在数据库繁忙的时间段执行 TRUNCATE 操作： 参考：Truncate - Causes Invalidations in the LIBRARY CACHE (Doc ID 123214.1)
若大量的游标失效，必然会发生大量的硬解析，并发的SQL硬解析则会出现library cache lock竞争(对象在table的library cache object handle的library cache lock) ，从而会引起严重的library cache lock等待。
我们今天主要是通过实验来验证“monitoring和nomonitoring索引会造成游标失效”的这一现象。
实验1
版本：Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
1 DROP TABLE T_BG_20240613_LHR_02;
2 CREATE TABLE T_BG_20240613_LHR_02(N NUMBER,V VARCHAR2(4000), v2 date);
3
4 CREATE INDEX idx_T_BG_20240613_LHR_02_N on T_BG_20240613_LHR_02(N);
5
6 SELECT COUNT(*) FROM T_BG_20240613_LHR_02;
7 SELECT * FROM T_BG_20240613_LHR_02;
8 truncate table T_BG_20240613_LHR_02;
9
10 -- 插入将近10万行数据，生成多个游标
11 begin
12
13 FOR i IN 1..3000 LOOP
14 -- alter system flush shared_pool;
15 DECLARE
16 N1 NUMBER := TRUNC(DBMS_RANDOM.VALUE(1, i)) ;
17 -- N1 VARCHAR2(11) := TRUNC(DBMS_RANDOM.VALUE(1, i)) ;
18
19 V1 VARCHAR2(32767) := lpad('1', i, '1');
20 --V1 NUMBER := lpad('1', i, '1');
21 --V1 NVARCHAR2(4000) := lpad('1', i, '1');
22 --V1 char(4000) := lpad('1', i, '1');
23
24
25 V2 DATE := sysdate+ DBMS_RANDOM.VALUE(-i, i) ;
26 -- V2 TIMESTAMP := sysdate+ DBMS_RANDOM.VALUE(-i, i) ;
27
28
29 begin
30
31 EXECUTE IMMEDIATE 'INSERT INTO T_BG_20240613_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)' USING N1, V1 ,V2;
32 EXECUTE IMMEDIATE 'INSERT INTO T_BG_20240613_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)' USING TO_CHAR(N1), V1 , V2;
33 EXECUTE IMMEDIATE 'INSERT INTO T_BG_20240613_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)' USING TO_NCHAR(N1), V1 , V2;
34
35
36 EXECUTE IMMEDIATE 'INSERT INTO T_BG_20240613_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)' USING N1, TO_NCHAR(V1) ,V2;
37 EXECUTE IMMEDIATE 'INSERT INTO T_BG_20240613_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)' USING N1, TO_NUMBER(V1) ,V2;
38 EXECUTE IMMEDIATE 'INSERT INTO T_BG_20240613_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)' USING TO_CHAR(N1), TO_NCHAR(V1) , V2;
39 EXECUTE IMMEDIATE 'INSERT INTO T_BG_20240613_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)' USING TO_CHAR(N1),TO_NUMBER(V1) ,V2;
40 EXECUTE IMMEDIATE 'INSERT INTO T_BG_20240613_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)' USING TO_NCHAR(N1),TO_NCHAR(V1) ,V2;
41 EXECUTE IMMEDIATE 'INSERT INTO T_BG_20240613_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)' USING TO_NCHAR(N1),TO_NUMBER(V1) ,V2;
42
43
44
45 EXECUTE IMMEDIATE 'INSERT INTO T_BG_20240613_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)' USING N1, V1 ,TO_CHAR(V2,'YYYY-MM-DD');
46 EXECUTE IMMEDIATE 'INSERT INTO T_BG_20240613_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)' USING N1, V1,cast(V2 as timestamp);
47 EXECUTE IMMEDIATE 'INSERT INTO T_BG_20240613_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)' USING N1, TO_NUMBER(V1) ,TO_CHAR(V2,'YYYY-MM-DD');
48 EXECUTE IMMEDIATE 'INSERT INTO T_BG_20240613_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)' USING N1, TO_NUMBER(V1) ,cast(V2 as timestamp);
49 EXECUTE IMMEDIATE 'INSERT INTO T_BG_20240613_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)' USING N1, TO_CHAR(V1) ,TO_CHAR(V2,'YYYY-MM-DD');
50 EXECUTE IMMEDIATE 'INSERT INTO T_BG_20240613_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)' USING N1, TO_CHAR(V1) ,cast(V2 as timestamp);
51 EXECUTE IMMEDIATE 'INSERT INTO T_BG_20240613_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)' USING N1, TO_NCHAR(V1) ,TO_CHAR(V2,'YYYY-MM-DD');
52 EXECUTE IMMEDIATE 'INSERT INTO T_BG_20240613_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)' USING N1, TO_NCHAR(V1) ,cast(V2 as timestamp);
53
54
55
56 EXECUTE IMMEDIATE 'INSERT INTO T_BG_20240613_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)' USING TO_CHAR(N1), V1 ,TO_CHAR(V2,'YYYY-MM-DD');
57 EXECUTE IMMEDIATE 'INSERT INTO T_BG_20240613_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)' USING TO_CHAR(N1), V1,cast(V2 as timestamp);
58 EXECUTE IMMEDIATE 'INSERT INTO T_BG_20240613_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)' USING TO_CHAR(N1), TO_NUMBER(V1) ,TO_CHAR(V2,'YYYY-MM-DD');
59 EXECUTE IMMEDIATE 'INSERT INTO T_BG_20240613_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)' USING TO_CHAR(N1), TO_NUMBER(V1) ,cast(V2 as timestamp);
60 EXECUTE IMMEDIATE 'INSERT INTO T_BG_20240613_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)' USING TO_CHAR(N1), TO_CHAR(V1) ,TO_CHAR(V2,'YYYY-MM-DD');
61 EXECUTE IMMEDIATE 'INSERT INTO T_BG_20240613_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)' USING TO_CHAR(N1), TO_CHAR(V1) ,cast(V2 as timestamp);
62 EXECUTE IMMEDIATE 'INSERT INTO T_BG_20240613_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)' USING TO_CHAR(N1), TO_NCHAR(V1) ,TO_CHAR(V2,'YYYY-MM-DD');
63 EXECUTE IMMEDIATE 'INSERT INTO T_BG_20240613_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)' USING TO_CHAR(N1), TO_NCHAR(V1) ,cast(V2 as timestamp);
64
65
66 EXECUTE IMMEDIATE 'INSERT INTO T_BG_20240613_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)' USING TO_NCHAR(N1), V1 ,TO_CHAR(V2,'YYYY-MM-DD');
67 EXECUTE IMMEDIATE 'INSERT INTO T_BG_20240613_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)' USING TO_NCHAR(N1), V1,cast(V2 as timestamp);
68 EXECUTE IMMEDIATE 'INSERT INTO T_BG_20240613_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)' USING TO_NCHAR(N1), TO_NUMBER(V1) ,TO_CHAR(V2,'YYYY-MM-DD');
69 EXECUTE IMMEDIATE 'INSERT INTO T_BG_20240613_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)' USING TO_NCHAR(N1), TO_NUMBER(V1) ,cast(V2 as timestamp);
70 EXECUTE IMMEDIATE 'INSERT INTO T_BG_20240613_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)' USING TO_NCHAR(N1), TO_CHAR(V1) ,TO_CHAR(V2,'YYYY-MM-DD');
71 EXECUTE IMMEDIATE 'INSERT INTO T_BG_20240613_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)' USING TO_CHAR(N1), TO_CHAR(V1) ,cast(V2 as timestamp);
72 EXECUTE IMMEDIATE 'INSERT INTO T_BG_20240613_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)' USING TO_NCHAR(N1), TO_NCHAR(V1) ,TO_CHAR(V2,'YYYY-MM-DD');
73 EXECUTE IMMEDIATE 'INSERT INTO T_BG_20240613_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)' USING TO_NCHAR(N1), TO_NCHAR(V1) ,cast(V2 as timestamp);
74
75
76 END;
77
78 END LOOP;
79
80 COMMIT;
81
82 end;
查询游标情况：
1 select address,hash_value,A.sql_profile,A.sql_plan_baseline,A.* from v$sql a where a.sql_text like '%INSERT INTO T_BG_20240613_LHR_02(N,V,V2) VALUES%' ;
2
3
4 select a.sql_id,A.child_number,a.bind_mismatch,a.BIND_LENGTH_UPGRADEABLE from v$sql_shared_cursor a where a.sql_id ='82hnj2bwpjj85';
5
6select a.sql_id,a.child_number,a.name,a.position,a.datatype_string,a.max_length from v$sql_bind_capture a where a.sql_id='610ygu94sf2t4'
7 order by a.sql_id,a.child_number,a.position;
8
9
10 SELECT * FROM TABLE(VERSION_RPT('82hnj2bwpjj85'));
大约生成90个子游标：
游标不能共享的原因：
1Note:438755.1 Version Count Report Version 3.2.5 -- Today's Date 13-jun-24 14:13
2RDBMS Version :19.0.0.0.0 Host: lhrora19c Instance 1 : ORCLCDB
3==================================================================
4Addr: 0000000061FA5750 Hash_Value: 4183344389 SQL_ID 82hnj2bwpjj85
5Sharable_Mem: 1927358 bytes Parses: 99000 Execs:99000
6Stmt:
70 INSERT INTO T_BG_20240613_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)
81
9
10Versions Summary
11----------------
12BIND_MISMATCH :89
13BIND_LENGTH_UPGRADEABLE :63
14
15Total Versions:89
16
17~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
18cursor_sharing = EXACT
19_cursor_obsolete_threshold = 8192 (See Note:10187168.8)
20~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
21
22Plan Hash Value Summary
23-----------------------
24Plan Hash Value Count
25=============== =====
26 0 90
27
28~~~~~~~~~~~~~~~~~~~~~~~~~~~
29Details for BIND_MISMATCH :
30
31Consolidated details for BIND* columns:
32BIND_MISMATCH,USER_BIND_PEEK_MISMATCH,BIND_UACS_DIFF,BIND_LENGTH_UPGRADEABLE,etc and
33BIND_EQUIV_FAILURE (Mislabled as ROW_LEVEL_SEC_MISMATCH BY bug 6964441 in 11gR1)
34
35from v$sql_bind_capture
36COUNT(*) POSITION MIN(MAX_LENGTH) MAX(MAX_LENGTH) DATATYPE BIND GRADUATION (PRECISION,SCALE)
37======== ======== =============== =============== ======== =============== =================
38 60 1 32 32 1 No (,)
39 30 1 22 22 2 No (,)
40 81 2 32 8192 1 Yes (,)
41 9 2 22 22 2 No (,)
42 30 3 32 32 1 No (,)
43 30 3 7 7 12 No (,)
44 30 3 11 11 180 No (,9)
45
46SUM(DECODE(column,Y, 1, 0) FROM V$SQL
47IS_OBSOLETE IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE
48=========== ================= ============= ============
49 0 0 0 27
50~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
51Details for BIND_LENGTH_UPGRADEABLE :
52
53Details shown already.
54####
55To further debug Ask Oracle Support for the appropiate level LLL.
56alter session set events
57 'immediate trace name cursortrace address 4183344389, level LLL';
58To turn it off do use address 1, level 2147483648
59================================================================
接下来对游标做monitoring：
1SYS@ORCLCDB> select count(*) from v$sql a where a.sql_text like '%INSERT INTO T_BG_20240613_LHR_02(N,V,V2) VALUES%' and sql_id ='82hnj2bwpjj85';
2
3 COUNT(*)
4----------
5 90
6
7SYS@ORCLCDB> alter index idx_T_BG_20240613_LHR_02_N monitoring usage;
8
9Index altered.
10
11SYS@ORCLCDB> col index_name format a60
12SYS@ORCLCDB> col table_name format a60
13SYS@ORCLCDB> SELECT * FROM V$OBJECT_USAGE a where a.index_name='IDX_T_BG_20240613_LHR_02_N';
14
15INDEX_NAME TABLE_NAME MONITO USED START_MONITORING END_MONITORING
16------------------------------------------------------------ ------------------------------------------------------------ ------ ------ -------------------------------------- --------------------------------------
17IDX_T_BG_20240613_LHR_02_N T_BG_20240613_LHR_02 YES NO 06/13/2024 14:20:43
18
19SYS@ORCLCDB>
20
21SYS@ORCLCDB> select count(*) from v$sql a where a.sql_text like '%INSERT INTO T_BG_20240613_LHR_02(N,V,V2) VALUES%' and sql_id ='82hnj2bwpjj85' and invalidations>0;
22
23 COUNT(*)
24----------
25 90
26
27SYS@ORCLCDB>
可以看到，在执行完monitoring后，90个子游标全部失效。
接下来再做一次最开始的begin块的插入操作，
1 begin
2
3 。。。。。
4
5 end;
插入完成后，再查询子游标个数：
1SYS@ORCLCDB> select invalidations, count(*) from v$sql a where a.sql_text like '%INSERT INTO T_BG_20240613_LHR_02(N,V,V2) VALUES%' and sql_id ='82hnj2bwpjj85' group by invalidations;
2
3INVALIDATIONS COUNT(*)
4------------- ----------
5 1 90
6 0 63
7
8SYS@ORCLCDB>
可以看到，又新增了63个子游标，
接下来对游标做nomonitoring：
1SYS@ORCLCDB> alter index idx_T_BG_20240613_LHR_02_N nomonitoring usage;
2
3Index altered.
4
5SYS@ORCLCDB> SELECT * FROM V$OBJECT_USAGE a where a.index_name='IDX_T_BG_20240613_LHR_02_N';
6
7INDEX_NAME TABLE_NAME MONITO USED START_MONITORING END_MONITORING
8------------------------------------------------------------ ------------------------------------------------------------ ------ ------ -------------------------------------- --------------------------------------
9IDX_T_BG_20240613_LHR_02_N T_BG_20240613_LHR_02 NO NO 06/13/2024 14:20:43 06/13/2024 14:32:15
10
11SYS@ORCLCDB> select invalidations, count(*) from v$sql a where a.sql_text like '%INSERT INTO T_BG_20240613_LHR_02(N,V,V2) VALUES%' and sql_id ='82hnj2bwpjj85' group by invalidations;
12
13INVALIDATIONS COUNT(*)
14------------- ----------
15 1 126
16 2 27
17
18SYS@ORCLCDB>
可以看到，在执行完monitoring后，INVALIDATIONS为0的SQL语句没有了，说明游标全部失效。
接下来再做一次最开始的begin块的插入操作，然后再查询子游标数：
1SYS@ORCLCDB> select invalidations, count(*) from v$sql a where a.sql_text like '%INSERT INTO T_BG_20240613_LHR_02(N,V,V2) VALUES%' and sql_id ='82hnj2bwpjj85' group by invalidations;
2
3INVALIDATIONS COUNT(*)
4------------- ----------
5 1 126
6 2 27
7 0 63
8
9SYS@ORCLCDB>
10SYS@ORCLCDB> select a.version_count,a.invalidations from v$sqlarea a where a.sql_id ='82hnj2bwpjj85';
11
12VERSION_COUNT INVALIDATIONS
13------------- -------------
14 216 180
15
16SYS@ORCLCDB>
发现新增了63个子游标。
实验2
1CREATE TABLE lhr_table_temp (a VARCHAR2(4000));
2INSERT INTO lhr_table_temp (a) VALUES ('test data session 1');
3commit;
4
5create index idx1 on lhr_table_temp(a);
6
7select * from lhr_table_temp; --每轮执行5次
8select sql_text,sql_id,version_count,loads,invalidations,parse_calls from v$sqlarea a where sql_text like '%select * from lhr_table_temp%';
9select a.executions,a.loads,a.sql_text,a.sql_id,a.first_load_time,a.last_load_time from v$sql a where sql_text like '%select * from lhr_table_temp%';
10
11alter index idx1 monitoring USAGE;
12alter index idx1 nomonitoring USAGE;
13
14
15SELECT * FROM V$OBJECT_USAGE;
-
loads为3表示加载了3次，即硬解析了3次，分别为建表后第1次查询，做monitoring后的查询，做nomonitoring后的查询
-
invalidations为3表示游标失效了2次，因为做monitoring失效1次，做nomonitoring失效1次
-
parse_calls可以认为每次硬解析后的执行次数
总结
1、对表上索引做“monitoring和nomonitoring会造成子游标失效”，在生产环境中，尤其是version_count很高的系统中，对核心生产业务表的DDL操作一定要慎重。
参考
https://www.dbaup.com/oraclezhilibrary-cachexiliedengdaishijianfenxifangfazongjiechixugengxin.html
https://www.dbaup.com/shenmeshigaobanbenyoubiaohigh-version-countruhepaicha.html
https://www.dbaup.com/kehumoutaooracle-racyewukuchuxianyanzhongdelibrary-cachedengdaipaicha.html
