Oracle对索引做monitoring和nomonitoring会导致游标失效的实验

数据运维 2024-06-14 共饮一杯手机阅读

简介

在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在执行。 
Oracle对索引做monitoring和nomonitoring会导致游标失效的实验-1

不要在数据库繁忙的时间段执行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;