Oracle对索引做monitoring和nomonitoring会导致游标失效的实验
Oracle对索引做monitoring和nomonitoring会导致游标失效的实验
AiDBA宝典
发布于 2024-06-18 16:57:52
发布于 2024-06-18 16:57:52
文章被收录于专栏：小麦苗的DB宝专栏
简介
在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住。
此时，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
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;
查询游标情况：
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个子游标：
游标不能共享的原因：
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：
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>
可以看到，在执行完monitoring后，90个子游标全部失效。
接下来再做一次最开始的begin块的插入操作，
begin
。。。。。
end;
插入完成后，再查询子游标个数：
SYS@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;
INVALIDATIONS COUNT(*)
------------- ----------
1 90
0 63
SYS@ORCLCDB>
可以看到，又新增了63个子游标，
接下来对游标做nomonitoring：
SYS@ORCLCDB> alter index idx_T_BG_20240613_LHR_02_N nomonitoring usage;
Index altered.
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 NO NO 06/13/2024 14:20:43 06/13/2024 14:32:15
SYS@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;
INVALIDATIONS COUNT(*)
------------- ----------
1 126
2 27
SYS@ORCLCDB>
可以看到，在执行完monitoring后，INVALIDATIONS为0的SQL语句没有了，说明游标全部失效。
接下来再做一次最开始的begin块的插入操作，然后再查询子游标数：
SYS@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;
INVALIDATIONS COUNT(*)
------------- ----------
1 126
2 27
0 63
SYS@ORCLCDB>
SYS@ORCLCDB> select a.version_count,a.invalidations from v$sqlarea a where a.sql_id ='82hnj2bwpjj85';
VERSION_COUNT INVALIDATIONS
------------- -------------
216 180
SYS@ORCLCDB>
发现新增了63个子游标。
实验2
CREATE TABLE lhr_table_temp (a VARCHAR2(4000));
INSERT INTO lhr_table_temp (a) VALUES ('test data session 1');
commit;
create index idx1 on lhr_table_temp(a);
select * from lhr_table_temp; --每轮执行5次
select sql_text,sql_id,version_count,loads,invalidations,parse_calls from v$sqlarea a where sql_text like '%select * from lhr_table_temp%';
select 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%';
alter index idx1 monitoring USAGE;
alter index idx1 nomonitoring USAGE;
SELECT * 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
本文参与 腾讯云自媒体同步曝光计划，分享自微信公众号。
原始发表：2024-06-13，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
目录
相关产品与服务
数据库
云数据库为企业提供了完善的关系型数据库、非关系型数据库、分析型数据库和数据库生态工具。您可以通过产品选择和组合搭建，轻松实现高可靠、高可用性、高性能等数据库需求。云数据库服务也可大幅减少您的运维工作量，更专注于业务发展，让企业一站式享受数据上云及分布式架构的技术红利！
腾讯云开发者
