原 Oracle对索引做monitoring和nomonitoring会导致游标失效

2024年6月13日

0 348 2

Tags： Oracle 原创游标失效游标 monitoring nomonitoring

👉 本文共约1698个字，系统预计阅读时间或需7分钟。

简介
实验1
实验2
总结
参考

简介

在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在执行。

此时，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;

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'));

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
================================================================

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)

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>

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(*)

----------

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(*)

----------

SYS@ORCLCDB>

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！

后续精彩内容已被站长无情隐藏，请输入验证码解锁本文！

获取验证码：请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。

打赏赞(2)

标签： Oracle 原创游标失效游标 monitoring nomonitoring

小麦苗

学习或考证，均可联系麦老师，请加微信db_bao或QQ646634621

发表回复取消回复

要发表评论，您必须先登录。

1、声明：本网站有部分文章整合或转载自网络，文章著作权归属原作者或原团队；若有侵权，深表歉意，请联系本站站长进行处理，谢谢！！！
2、本站提供数据库的培训和考证业务，培训包含但不限于Oracle、MySQL、PostgreSQL、SQL Server、国产数据库等，考证包括但不限于Oracle OCP、Oracle OCM、MySQL OCP、PGCA、PGCE、PGCM等，私聊QQ646634621或微信dbaup66，非诚勿扰，谢谢！！！
3、麦老师新建了《西安DBA数据架构师》微信群，有志同道合的朋友可以私聊我入群，我们一起聊技术，聊生活，空了可以喝酒约饭。
4、为了方便大家更有针对性的讨论学习数据库，故新建数据库分类群，包括Oracle群、MySQL群、SQL Server群、PG&GP群、国产&信创群。志同道合的朋友可以入群聊技术聊人生，捣乱的勿进，喷子勿进，极端人士勿进，谢谢。

原 Oracle对索引做monitoring和nomonitoring会导致游标失效

简介

实验1

相关文章

您可能还喜欢...

发表回复取消回复

网站公告

网站寄语

本站其它工具

搜索本网站

标签云☁

网站日历

网站归档

网站分类

2024 年 11 月
一	二	三	四	五	六	日
				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

原 Oracle对索引做monitoring和nomonitoring会导致游标失效

简介

实验1

相关文章

您可能还喜欢...

Oracle 19c 后台进程

Ubuntu 20.04安装Oracle 11.2.0.4单机

在Docker中快速使用各个版本（从10g到23ai）的Oracle数据库

发表回复 取消回复

网站公告

网站寄语

本站其它工具

搜索本网站

标签云☁

网站日历

网站归档

网站分类

发表回复取消回复