诊断Oracle high version count(高版本游标)问题(3)

SQL>alter system set events
'immediate trace name cursortrace level 577, address hash_value';
 
其中可以使用三个level,level 1为577,level 2为578,level 3为580
 当重用这个游标时将会向user_dump_dest目录中写一个跟踪文件.
 关闭cursortrace:
SQL>alter system set events
'immediate trace name cursortrace level 2147483648, address 1';
 
注意在10.2.0.4以下版本存在Bug 5555371导致cursortrace无法彻底关闭的情况.最终导致其trace文件不停的增长,从而可能导致Oracle文件系统被撑爆的现象
 
在11.2中有了cursordump可以使用如下方式进行cursor dump:
SQL>alter system set events ‘immediate trace name cursordump level 16’;
 
这种方式收集的信息比较全:例如它可以采集部分别的方式无法看到的px_mismatch以及它会进一步展开optimizer_mismatch的信息等

尽管使用绑定变量还是会存在high version cursor
 当cursor_sharing为similar时
 select /* TEST */ * from emp where sal > 100;
 select /* TEST */ * from emp where sal > 101;
 select /* TEST */ * from emp where sal > 102;
 select /* TEST */ * from emp where sal > 103;
 select /* TEST */ * from emp where sal > 104;

SELECT sql_text,version_count,address
 FROM V$SQLAREA
 WHERE sql_text like 'select /* TEST */%';

SELECT * FROM V$SQL_SHARED_CURSOR WHERE kglhdpar = '&my_addr';
 将会出现多个子游标
 Cursor_sharing设置为similar或force都可能导致high version count可以参考:
 High Version Count with CURSOR_SHARING=SIMILAR or FORCE(文档ID 261020.1)

在11g中引入的adaptive cursor sharing特性很容易导致high version count的问题
 可以参考:Bug 12334286 High version count with CURSOR_SHARING=FORCE(BIND_MISMATCH and INCOMP_LTRL_MISMATCH)
 Document 740052.1 Adaptive Cursor Sharing Overview
 Document 7213010.8 Bug 7213010 - Adaptive cursor sharing generates lots of child cursors
 
Document 8491399.8 Bug 8491399 - Adaptive Cursor Sharing does not match the correct cursor version for queries using CHAR datatype
 
在oracle 11g中可以通过其它的一些手段限制child cursor的数量
 Document 10187168.8 Enhancement to obsolete parent cursors if Version Count exceeds a threshold
 引入了一个隐含参数_cursor_obsolete_threshold该参数用来限制单个parent cursor下的child cursor的数量,默认值为100.如果child cursor的数量超过这个阈值就会触发cursor obsolescence特性.这个时候parent cursor就会被丢弃并同时重新创建一个新的parent cursor.
 
1. If 11.2.0.3 and above, set the following parameters:
 "_cursor_obsolete_threshold" to 100 (this is the number of child cursor after which we obsolete it)
 2. If 11.2.0.2.2, then set:
 SQL>alter system set "_cursor_features_enabled"=1026 scope=spfile;
 SQL>alter system set event=’106001 trace name context forever,level 1024’ scope=spfile;
 
3.If 11.2.0.1:
 SQL>alter system set “_cursor_features_enabled”=34 scope=spfile;
 SQL>alter system set event=’106001 trace name context forever,level 1024’ scope=spfile;