原 Oracle对索引做monitoring和nomonitoring会导致游标失效
Tags： Oracle原创游标游标失效nomonitoringmonitoring
👉 本文共约1670个字，系统预计阅读时间或需7分钟。
简介
在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 2 3 4 5 | 此时，AWR 或者 statspack 报告: - Library Cache statistics 部分显示 reloads 数很高并且 (每小时几千次) invalidations （ Invali- dations）也很高。 - "% SQL with executions>1" 超过 60%, 表明SQL语句共享率较高。 - 确认 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 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 | DROP TABLE T_BG_20240613_LHR_02; CREATE TABLE T_BG_20240613_LHR_02(N NUMBER,V VARCHAR2(4000), v2 date); CREATE INDEX idx_T_BG_20240613_LHR_02_N on T_BG_20240613_LHR_02(N); SELECT COUNT(*) FROM T_BG_20240613_LHR_02; SELECT * FROM T_BG_20240613_LHR_02; truncate table T_BG_20240613_LHR_02; -- 插入将近10万行数据，生成多个游标 begin FOR i IN 1..3000 LOOP -- alter system flush shared_pool; DECLARE N1 NUMBER := TRUNC(DBMS_RANDOM.VALUE(1, i)) ; -- N1 VARCHAR2(11) := TRUNC(DBMS_RANDOM.VALUE(1, i)) ; V1 VARCHAR2(32767) := lpad('1', i, '1'); --V1 NUMBER := lpad('1', i, '1'); --V1 NVARCHAR2(4000) := lpad('1', i, '1'); --V1 char(4000) := lpad('1', i, '1'); V2 DATE := sysdate+ DBMS_RANDOM.VALUE(-i, i) ; -- V2 TIMESTAMP := sysdate+ DBMS_RANDOM.VALUE(-i, i) ; begin EXECUTE IMMEDIATE 'INSERT INTO T_BG_20240613_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)' USING N1, V1 ,V2; EXECUTE IMMEDIATE 'INSERT INTO T_BG_20240613_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)' USING TO_CHAR(N1), V1 , V2; EXECUTE IMMEDIATE 'INSERT INTO T_BG_20240613_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)' USING TO_NCHAR(N1), V1 , V2; EXECUTE IMMEDIATE 'INSERT INTO T_BG_20240613_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)' USING N1, TO_NCHAR(V1) ,V2; EXECUTE IMMEDIATE 'INSERT INTO T_BG_20240613_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)' USING N1, TO_NUMBER(V1) ,V2; EXECUTE IMMEDIATE 'INSERT INTO T_BG_20240613_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)' USING TO_CHAR(N1), TO_NCHAR(V1) , V2; EXECUTE IMMEDIATE 'INSERT INTO T_BG_20240613_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)' USING TO_CHAR(N1),TO_NUMBER(V1) ,V2; EXECUTE IMMEDIATE 'INSERT INTO T_BG_20240613_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)' USING TO_NCHAR(N1),TO_NCHAR(V1) ,V2; EXECUTE IMMEDIATE 'INSERT INTO T_BG_20240613_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)' USING TO_NCHAR(N1),TO_NUMBER(V1) ,V2; 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'); EXECUTE IMMEDIATE 'INSERT INTO T_BG_20240613_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2)' USING N1, V1,cast(V2 as timestamp); 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'); 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); 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'); 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); 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'); 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); 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'); 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); 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'); 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); 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'); 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); 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'); 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); 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'); 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); 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'); 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); 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'); 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); 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'); 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); END; END LOOP; COMMIT; end; |
查询游标情况：
1 2 3 4 5 6 7 8 9 10 | 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%' ; 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'; select 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' order by a.sql_id,a.child_number,a.position; SELECT * FROM TABLE(VERSION_RPT('82hnj2bwpjj85')); |
大约生成90个子游标：
游标不能共享的原因：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 | Note:438755.1 Version Count Report Version 3.2.5 -- Today's Date 13-jun-24 14:13 RDBMS Version :19.0.0.0.0 Host: lhrora19c Instance 1 : ORCLCDB ================================================================== Addr: 0000000061FA5750 Hash_Value: 4183344389 SQL_ID 82hnj2bwpjj85 Sharable_Mem: 1927358 bytes Parses: 99000 Execs:99000 Stmt: 0 INSERT INTO T_BG_20240613_LHR_02(N,V,V2) VALUES(:N1,:V1,:V2) 1 Versions Summary ---------------- BIND_MISMATCH :89 BIND_LENGTH_UPGRADEABLE :63 Total Versions:89 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ cursor_sharing = EXACT _cursor_obsolete_threshold = 8192 (See Note:10187168.8) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Plan Hash Value Summary ----------------------- Plan Hash Value Count =============== ===== 0 90 ~~~~~~~~~~~~~~~~~~~~~~~~~~~ Details for BIND_MISMATCH : Consolidated details for BIND* columns: BIND_MISMATCH,USER_BIND_PEEK_MISMATCH,BIND_UACS_DIFF,BIND_LENGTH_UPGRADEABLE,etc and BIND_EQUIV_FAILURE (Mislabled as ROW_LEVEL_SEC_MISMATCH BY bug 6964441 in 11gR1) from v$sql_bind_capture COUNT(*) POSITION MIN(MAX_LENGTH) MAX(MAX_LENGTH) DATATYPE BIND GRADUATION (PRECISION,SCALE) ======== ======== =============== =============== ======== =============== ================= 60 1 32 32 1 No (,) 30 1 22 22 2 No (,) 81 2 32 8192 1 Yes (,) 9 2 22 22 2 No (,) 30 3 32 32 1 No (,) 30 3 7 7 12 No (,) 30 3 11 11 180 No (,9) SUM(DECODE(column,Y, 1, 0) FROM V$SQL IS_OBSOLETE IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE =========== ================= ============= ============ 0 0 0 27 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Details for BIND_LENGTH_UPGRADEABLE : Details shown already. #### To further debug Ask Oracle Support for the appropiate level LLL. alter session set events 'immediate trace name cursortrace address 4183344389, level LLL'; To turn it off do use address 1, level 2147483648 ================================================================ |
接下来对游标做monitoring：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | SYS@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'; COUNT(*) ---------- 90 SYS@ORCLCDB> alter index idx_T_BG_20240613_LHR_02_N monitoring usage; Index altered. SYS@ORCLCDB> col index_name format a60 SYS@ORCLCDB> col table_name format a60 SYS@ORCLCDB> SELECT * FROM V$OBJECT_USAGE a where a.index_name='IDX_T_BG_20240613_LHR_02_N'; INDEX_NAME TABLE_NAME MONITO USED START_MONITORING END_MONITORING ------------------------------------------------------------ ------------------------------------------------------------ ------ ------ -------------------------------------- -------------------------------------- IDX_T_BG_20240613_LHR_02_N T_BG_20240613_LHR_02 YES NO 06/13/2024 14:20:43 SYS@ORCLCDB> SYS@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; COUNT(*) ---------- 90 SYS@ORCLCDB> |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
