ITPub博客

首页 > 数据库 > Oracle > sql High Version Count

sql High Version Count

Oracle 作者:wwjfeng 时间:2019-06-27 21:22:04 0 删除 编辑

转载自:小麦苗 公众号,DBA宝典   DB笔试面试题

一个父游标下对应的子游标个数被称为 Version Count ,每个子游标对应一个执行计划。对于一个特定的游标有多少个版本( Version Count )就属于高版本游标是没有明确定义的。对于不同的系统有不同的数量界定。 High Version Count 不仅产生的原因是多种多样的,而且会导致各种令人头痛的问题,轻则导致数据库的性能急剧下降, CPU 利用率剧增,重则导致数据库挂起,触发 ORA-04031 或者其它 BUG 导致宕机。

AWR 报告中, Version Count 大于 20 就会被报告出来,如下图所示:

 

SQL 执行计划的生成,是受到很多因素影响的。相同父游标只是表示输入 SQL 的字面值相同。子游标对应的因素,如优化器类型、优化器的模式( OPTIMIZER_MODE )、对应对象权限等的差异,都会影响到子游标的共享。

Oracle 11g 中, V$SQL_SHARED_CURSOR 可以用来诊断子游标不共享问题的原因。该视图通过 SQL_ID CHILD_NUMBER 就可以定义某个特定子游标的信息。该视图中大部分列都是以 VARCHAR2(1) Y/N 取值,每列的含义都是一个不能共享的理由。需要注意的是,这个理由 N 表示的是不能与第一个子游标( CHILD_NUMBER=0 )共享的理由。

下面举一个由于优化器模式不同导致游标不能共享的例子:

SYS@lhrdb> SELECT * FROM SCOTT.EMP WHERE ROWNUM<=0;
no rows selected
SYS@lhrdb> select sql_id, version_count from v$sqlarea where sql_text like 'SELECT * FROM SCOTT.EMP WHERE ROWNUM<=0%';
SQL_ID        VERSION_COUNT
------------- -------------
7u75n20ktntsb             1
SYS@lhrdb>  show parameter optimizer_mode
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode                       string      ALL_ROWS
SYS@lhrdb>  alter session set optimizer_mode=first_rows;
Session altered.
SYS@lhrdb> SELECT * FROM SCOTT.EMP WHERE ROWNUM<=0;
no rows selected
SYS@lhrdb> select sql_id, version_count,SQL_TEXT from v$sqlarea where sql_text like 'SELECT * FROM SCOTT.EMP WHERE ROWNUM<=0%';
SQL_ID        VERSION_COUNT
------------- -------------
7u75n20ktntsb             2
SYS@lhrdb>  select sql_id, child_number, OPTIMIZER_MODE from v$sql where sql_id='7u75n20ktntsb';
SQL_ID        CHILD_NUMBER OPTIMIZER_
------------- ------------ ----------
7u75n20ktntsb            0 ALL_ROWS
7u75n20ktntsb            1 FIRST_ROWS
SYS@lhrdb> select sql_id, child_number, OPTIMIZER_MODE_MISMATCH from v$sql_shared_cursor where sql_id='7u75n20ktntsb';
SQL_ID        CHILD_NUMBER O
------------- ------------ -
7u75n20ktntsb            0 N
7u75n20ktntsb            1 Y



有时候会遇到某些 SQL V$SQL_SHARED_CURSOR 所有的字段的结果都为 N ,但是其 Version Count 还是很高的情况。这种情况主要的原因是存在部分 BUG ,可能导致 V$SQL_SHARED_CURSOR 的信息不准确。例如:

Bug 12539487 – gv$sql_shared_cursor may not show all reasons to not share a cursor (Doc ID 12539487.8)
--所以在Oracle 10g以上版本中可以使用cursortrace来查找High Version Count的原因,打开cursortrace的方法如下所示:
alter system set events 'immediate trace name cursortrace level 577, address <hash_value>';
--如需关闭cursortrace,则可以使用以下方式进行关闭:
alter system set events 'immediate trace name cursortrace level 2147483648, address 1';
--或者使用以下方式关闭:
alter session set events 'immediate trace name cursortrace level 128 , address <address>';



MOS 438755.1 中, Oracle 提供了一个专门的脚本程序,用于协助诊断 High Version Count 问题。运行脚本 version_rpt.sql 可以创建函数 VERSION_RPT 。具体使用的方法有三个场景:

--① 列出Version Count大于某个阈值的报告,以SQL_ID方式显示
SELECT B.*
  FROM V$SQLAREA A, TABLE(VERSION_RPT(A.SQL_ID)) B
 WHERE LOADED_VERSIONS >= 4;
 
--② 列出Version Count大于某个阈值的报告,以SQL_HASH方式显示
SELECT B.*
  FROM V$SQLAREA A, TABLE(VERSION_RPT(NULL, A.HASH_VALUE)) B
 WHERE LOADED_VERSIONS >= 4;
--③ 列出某个特定SQL_ID的Version Count
SELECT * FROM TABLE(VERSION_RPT('7u75n20ktntsb'));

对于版本过多的 SQL ,一次软解析甚至不如重新执行一次硬解析来的高效,所以 Oracle 引入了一系列的控制手段来处理这些特殊的游标。从 Oracle 11.2.0.3 开始, Oracle 提供了一个隐含参数“ _CURSOR_OBSOLETE_THRESHOLD ”,其作用是当 SQL 版本超过这个参数设定后,直接舍弃这个游标,重新解析,从头开始,该隐含参数的默认值为 100 。如果子游标的数量超过了这个阈值,那么父游标就会被废弃,并且同时重新创建一个新的父游标。如果 Oracle 数据库的版本低于 11.2.0.3 ,那么除了需要给系统打 Patch Enhancement Request Bug 10187168 : OBSOLETE PARENT CURSORS IF VERSION COUNT EXCEEDS A THRESHOLD )外,还同时需要设置以下参数:

--Oracle 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;
--Oracle 11.2.0.2:
SQL> alter system set "_cursor_features_enabled"=1026 scope=spfile;
SQL> alter system set event='106001 trace name context forever,level 1024' scope=spfile;


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15412087/viewspace-2649006/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 如何提高dml性能
请登录后发表评论 登录
全部评论

注册时间:2010-03-27

  • 博文量
    146
  • 访问量
    91261