翻译metalink上关于high version count的文章-白红宇

翻译metalink上关于high version count的文章

阅读量：2432 次

发布时间：2019-05-10

本文共 13142 字，大约阅读时间需要 43 分钟。

什么是“high version count”？

对于一个cursor，没有一个明确的定义到底多高的verstion算高，不同的系统可以容忍不同的version count。

AWR报告列出了versions排前20的cursor，使你很容易发现潜在的问题。

当你发现了成百上千个version的cursor，就需要考虑version为何如此之高，以及产生高version的原因，并通过让SQL共享来减少version count。

什么是SQL共享？

首先，所有SQL都是有可能被共享的，输入SQL时，数据库计算SQL的hash值，通过hash值来方便地查找它是否已经存在于共享池了。这方面的细节不是这片文章的讨论范畴，你只要知道SQL被输入后，会被计算成hash值就行了。

例如：select count(*) from emp 的hash值是 4085390015

当这条SQL第一次被解析时，不管这条SQL将来会不会被共享，我们都会为这条它创建一个父游标（parent cursor）和一个子游标。简而言之，父游标描述了一个SQL的hash值，而子游标描述了SQL的元数据（metadata）。

什么是SQL Metadata？

metadata是让一个SQL能够执行所需要的全部信息。例如，在这个例子里，我给出了SCOTT用户的EMP表，那么，就有一个object_id指向这个用户下的EMP。当SCOTT登陆，在session的范围里，这条SQL锁需要的优化器参数就初始化了。这些都属于metadata信息，它们会被优化器使用。下面还会提到另一个metadata的例子。

再说说这个session登出后又重新登陆的情况。他有执行了一次和刚才相同的SQL。这是，共享池里已经有一个相同的SQL了（但是session不知道）。我们要做的就是计算这条SQL的hash值，然后通过它在共享池中搜索（译者：搜索父游标），如果找到了，就进一步查看它有没有可以被我们使用的子游标（需要这个子游标有与我们这条SQL相同的metadata）这样一来，我们在共享池中，这个cursor还是只有一个version，因为我们能共享使用已存在的cursor。

也就是说，所谓游标共享，共享的不是父游标，而是子游标。

现在，一个叫TEST的用户，也有一张叫EMP的表，他也执行了和上面相同的SQL。

此时会发生下面的事情：

1 SQL语句被计算出hash值，同样是4085390015

2 在共享池中会找到相同的SQL

3 子游标被找到

4 因为TEST.EMP的object_id不等于SCOTT.EMP的object_id（译者：sql metadata不相同），因此发生一次‘mismatch’

从内部来讲，刚才的过程是，父游标下的子游标被串成了一个链表，扫描子游标的时候，将这条SQL的metadata和已有子游标的metadata进行比对，如果有100个子游标，就要扫描100个子游标（遇到一个mismatch就继续查看下一个），知道找到了可以共享的子游标。如果最终没有找到可以共享的子游标，就需要创建一个新的子游标（译者：链表被加长，version count加1，version就是子游标的个数）。

5 由于找不到可共享的子游标，因此需要创建新的子游标。现在这个父游标包含了两个子游标。

我为什么要关心‘high versions’？

无谓的non-sharing SQL，和它所造成的versions，是library cache挣用的主要原因。挣用会降低数据库的性能，在极端情况下，还会把数据库HANG住。SQL每次执行时，解析器都必须在子游标的链表上搜索，看看有没有游标可以共享，这些被浪费的CPU周期完全可以去做其它事情。

我怎么查看游标的version？怎么知道他们为什么没有共享？

有个简单的办法，可以把version的信息格式化成可读性强的格式，就是使用下面的脚本：

Document 438755.1 High SQL Version Counts - Script to determine reason(s)

要查看游标mismatch的原因，可以查看v$sql_shared_cursor。

如果你得不到上面的脚本，可以通过下面的方法查询到相同的信息。

我们还用上面的SQL，看看共享池中有没有可以共享的游标。

SCOTT用户执行select count(*) from emp

执行下面查询，查看父游标和它的hash值

select sql_text, hash_value,address from v$sqlarea where sql_text like 'select count(*) from emp%';

SQL_TEXT HASH_VALUE ADDRESS

------------------------ ------------ ----------------

select count(*) from emp 4085390015 0000000386BC2E58

查看子游标：

oracle 9.2.x.x之前：

select * from v$sql_shared_cursor where kglhdpar = '0000000386BC2E58'

oracle 10.0.X.X以后：

select * from v$sql_shared_cursor where address = '0000000386BC2E58'

输出如下：

ADDRESS KGLHDPAR U S O O S L S E B P I S T A B D L T R I I R L I O S M U T N F

---------------- ---------------- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

0000000386BC2D08 0000000386BC2E58 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N

可以看到，有一个子游标（地址0000000386BC2D08 ）

mismatch信息（U S O O S L）都是N，因为这是第一个子游标。现在，如果我登陆另一个用户，执行相同的SQL

再查询，输出如下

ADDRESS KGLHDPAR U S O O S L S E B P I S T A B D L T R I I R L I O S M U T N F

---------------- ---------------- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

0000000386BC2D08 0000000386BC2E58 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N

0000000386A91AA0 0000000386BC2E58 N N N N N N N N N N N N N Y N N N Y N N N N N N N N N N N N N

我们发现第二个子游标（0000000386A91AA0 ），以及它不能共享第一个游标的原因（‘Y’表示mismatch）

原因是：

1 AUTH_CHECK_MISMATCH

2 TRANSLATION_MISMATCH

因为当前用户的对象不能映射到SCOTT的对象，它们的object_id不一样，我们不能访问SCOTT的EMP，因此发生了mismatch。

v$sql_shared_cursor里给出的原因都是什么意思？

下面列出了不能共享的原因（带**的说明是经常发生的情况）

UNBOUND_CURSOR - 已存在的子游标并不完整（或者说，它没有被优化）

SQL_TYPE_MISMATCH - SQL语句类型和已存在的子游标不一致

**OPTIMIZER_MISMATCH - 优化器环境参数和已存在的子游标不一致

例如：

select count(*) from emp; ->> 一个父游标、一个子游标

alter session set optimizer_mode=ALL_ROWS

select count(*) from emp; ->> 一个父游标，两个子游标

(设置event也会产生同样的现象 - 如果开启了10046，同样会出现一次OPTIMIZER_MISMATCH，并且产生第三个子游标)

从cursor trace能看到更多细节

Optimizer mismatch(12)

上面的数字给出了具体的原因

        1 = 使用了非默认的并行度。

        2 = 在RAC环境中，实例编号，session使用的CPU个数，或thread发生变化。

        3 = _parallel_syspls_obey_force 被设为 FALSE。

        4 = PQ mode发生变化。

        5 = 并行度发生变化。

        6 = 并行度策略发生变化。

        7 = The session limit is not the same as the cursor limit but the cursor limit is the same as the degree used.

        8 = The cursor limit is greater than the degree used and the session limit is less than the cursor limit

        9 = The cursor limit is less than the degree used and the session limit is not the same as the cursor limit

       10 = 优化器模式发生变化。

       11 = 物化视图不匹配

       12 = 优化器参数不匹配

       13 = 使用了基数反馈

OUTLINE_MISMATCH - outlines和已有cursor不一致。

例如

alter session set use_stored_outlines = OUTLINES1;

select count(*) from emp;

alter session set use_stored_oulines= OUTLINES2;

select count(*) from emp;

将会创建第二个子游标，和第一个的outline不一样。

STATS_ROW_MISMATCH - 统计信息发生变化。查看10046/sql_trace是否被设置到所有session上了，这样也会造成这种现象。

LITERAL_MISMATCH - 非数据的字面值与现有游标不匹配。

SEC_DEPTH_MISMATCH - 安全级别发生变化。

EXPLAIN_PLAN_CURSOR - 这个子游标是由explain plan for强制创建的。

BUFFERED_DML_MISMATCH - Buffered DML does not match the existing child cursor

PDML_ENV_MISMATCH - PDML environment does not match the existing child cursor

INST_DRTLD_MISMATCH - Insert direct load does not match the existing child cursor

SLAVE_QC_MISMATCH - 新游标和老游标，其中一个是coordinator发起的，另一个是slave cursor。

TYPECHECK_MISMATCH - 已有游标还没有完全优化完成。

AUTH_CHECK_MISMATCH - 权限认证不匹配。用户无权访问已有游标中的同名对象。

**BIND_MISMATCH - 绑定变量定义不匹配。

例如

variable a varchar2(100);

select count(*) from emp where ename = :a ->> 一个父游标，一个子游标

variable a varchar2(400);

select count(*) from emp where ename = :a ->> 一个父游标，两个子游标

DESCRIBE_MISMATCH - The typecheck heap is not present during the describe for the child cursor

LANGUAGE_MISMATCH - The language handle does not match the existing child cursor

TRANSLATION_MISMATCH - 和已有游标的同名对象不是一个对象。通常和AUTH_CHECK_MISMATCH是一个意思，只不过不是权限问题，只是对象同名。

ROW_LEVEL_SEC_MISMATCH - 行级安全设置不匹配。

INSUFF_PRIVS - 对已有SQL操作的对象没有权限。

INSUFF_PRIVS_REM - 对已有SQL操作的远程对象没有权限。

REMOTE_TRANS_MISMATCH - 远程对象和已有SQL同名，但是不匹配。

USER1: select count(*) from table@remote_db

USER2: select count(*) from table@remote_db

虽然SQL一样，但是他们调用的对象可能不一样。

LOGMINER_SESSION_MISMATCH

INCOMP_LTRL_MISMATCH

OVERLAP_TIME_MISMATCH - error_on_overlap_time mismatch

SQL_REDIRECT_MISMATCH - sql redirection mismatch

MV_QUERY_GEN_MISMATCH - SQL是由物化视图产生的。

USER_BIND_PEEK_MISMATCH - 绑定变量窥探导致执行计划变化。

TYPCHK_DEP_MISMATCH - cursor has typecheck dependencies

NO_TRIGGER_MISMATCH - no trigger mismatch

FLASHBACK_CURSOR - 闪回查询。

ANYDATA_TRANSFORMATION - anydata transformation change

INCOMPLETE_CURSOR - 当绑定变量长度增加，已有游标的绑定变量不够长了，就会产生新游标，同时将老游标标记为不可用。

TOP_LEVEL_RPI_CURSOR - top level/rpi cursor，在并行查询中，这是正常的行为。

DIFFERENT_LONG_LENGTH - long数据长度发生变化。

LOGICAL_STANDBY_APPLY - logical standby apply mismatch

DIFF_CALL_DURN - different call duration

BIND_UACS_DIFF - bind uacs mismatch

PLSQL_CMP_SWITCHS_DIFF - plsql compiler switches mismatch

CURSOR_PARTS_MISMATCH - cursor-parts executed mismatch

STB_OBJECT_MISMATCH - 查看https://blogs.oracle.com/optimizer/entry/my_cursor_wasn_t_shared

ROW_SHIP_MISMATCH - row shipping capability mismatch

PQ_SLAVE_MISMATCH - PQ slave mismatch

Check you want to be using PX with this reason code, as the problem could be caused by running lots of small SQL statements which do not really need PX. If you are on < 11i you may be hitting Bug:4367986

TOP_LEVEL_DDL_MISMATCH - top-level DDL cursor

MULTI_PX_MISMATCH - multi-px and slave-compiled cursor

BIND_PEEKED_PQ_MISMATCH - bind-peeked PQ cursor

MV_REWRITE_MISMATCH - MV rewrite cursor

ROLL_INVALID_MISMATCH - rolling invalidation window exceeded

This is caused by the rolling invalidation capability in DBMS_STATS. The child cannot be shared as it's invalidation window is exceeded. See:

Document 557661.1 Rolling Cursor Invalidations with DBMS_STATS in Oracle10g

OPTIMIZER_MODE_MISMATCH - 优化器模式发生变化

PX_MISMATCH - parallel query mismatch

If running 11.1.0.6 and RAC see Bug:7352775. Check that if (on each instance) parallel_instance_groups is set then instance_groups is set to the same.

MV_STALEOBJ_MISMATCH - mv stale object mismatch

FLASHBACK_TABLE_MISMATCH - flashback table mismatch

LITREP_COMP_MISMATCH - literal replacement compilation mismatch

11g新增 :

* PLSQL_DEBUG - 当前session的plsql_debug为true。

* LOAD_OPTIMIZER_STATS - Load optimizer stats for cursor sharing

* ACL_MISMATCH - Check ACL mismatch

* FLASHBACK_ARCHIVE_MISMATCH - Flashback archive mismatch

* LOCK_USER_SCHEMA_FAILED - Failed to lock user and schema

* REMOTE_MAPPING_MISMATCH - Remote mapping mismatch

* LOAD_RUNTIME_HEAP_FAILED - Runtime heap mismatch

* HASH_MATCH_FAILED - Hash mismatch

Set to "Y" if sharing fails due to a hash mismatch, such as the case with mismatched histogram data or a range predicate marked as unsafe by literal replacement (See Bug 3461251)

11.2新增 :

* PURGED_CURSOR - cursor marked for purging

The cursor has been marked for purging with dbms_shared_pool.purge

* BIND_LENGTH_UPGRADEABLE - bind length upgradeable

Could not be shared because a bind variable size was smaller than the new value beiing inserted (marked as BIND_MISMATCH in earlier versions).

* USE_FEEDBACK_STATS - cardinality feedback

Cardinality feedback is being used and therefore a new plan could be formed for the current execution.

* BIND_EQUIV_FAILURE - The bind value's selectivity does not match that used to optimize the existing child cursor

11.2中不再有ROW_LEVEL_SEC_MISMATCH

Version_rpt脚本:

这个脚本可以用来生成基于v$sql_shared_cursor的分析报告。可以从下面文档找到该脚本

Document 438755.1 High SQL Version Counts - Script to determine reason(s)

执行脚本:

给所有子游标数超过100的SQL_ID生成报告 (10g以上):

select b.* from v$sqlarea a ,table(version_rpt(a.sql_id)) b where loaded_versions >=100;

-- 给所有子游标数超过100的HASH_VALUE生成报告:

select b.* from v$sqlarea a ,table(version_rpt(null,a.hash_value)) b where loaded_versions>=100;

-- 为特定SQL生成报告

select * from table(version_rpt('cyzznbykb509s'));

接下来还能进行哪些排查

10g中可以通过corsor trace来帮助排查游标共享失败的原因。这个事件只能在售后指导下使用，并且生成的trace文件可读性较差。要得到这个trace，需要先从v$sqlarea中取得sql的hash_value。

alter system set events 'immediate trace name cursortrace level 577, address hash_value';

(level 578/580 用来进行高级 tracing (577=level 1, 578=level 2, 580=level 3)

这个事件会在每次执行这个SQL时，在user_dump_dest生成trace文件。

关掉event：

alter system set events 'immediate trace name cursortrace level 2147483648, address 1';

在10.2中有一个Bug 5555371 (在10.2.0.4中被修正了) 就是cursor trace没法彻底关掉，其结果是在trace文件中仍然会产生条目。只能重启实例解决。这个BUG的危害取决于执行的cursor(以及它生成的trace文件大小)

在11.2中还有一个cursordump事件:

alter system set events 'imemediat trace name cursordump level 16'

(please ensure system , not session, is used as the level meaning changes)

这个dump包含了一些额外的信息，例如扩展了'optimizer_mismatch'.

有没有即使使用了绑定变量，但还是希望有多个version count的时候？

当设置了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，SQL中的字面值就会被替换成绑定变量，此时要共享游标的一个条件就是绑定的变量需要和字面值需要匹配。执行计划是否会发生变化取决于SQL使用的字面值，当我执行相同的cursor时，有可能需要针对具体的字面值，进一步优化出更好的执行计划。

具体到这个例子，我们使用了大于号条件 > , 如果采用的是等值条件，游标将会一直被共享。如果开发人员执意要拒绝优化器根据具体的字面值进一步优化执行计划，可以将cursor_sharing设为force。

"SIMILAR和FORCE的区别是，SIMILAR强迫相似的SQL共享SQL area，同时又避免很差的执行计划被执行。如果将CURSOR_SHARING设置为FORCE同样强迫相似的SQL共享SQL area，但是有可能会出现对于某个字面值来说很差的执行计划"

从4和12级的10046 trace可以看到字面值被替换成绑定变量后，是否是unsafe的

9i的oacfl2标志和10g的fl2标志显示是否是unsafe的

9i：

BINDS #2:

bind 0: dty=2 mxl=22(04) mal=00 scl=00 pre=00 oacflg=10 oacfl2=500 size=24

offset=0

bfp=1036d6408 bln=22 avl=04 flg=09

value=16064

bind 1: dty=2 mxl=22(04) mal=00 scl=00 pre=00 oacflg=10 oacfl2=500 size=24

offset=0

bfp=1036d4340 bln=22 avl=04 flg=09

10g (10.2.0.5)和11g

alter session set cursor_sharing=force;

alter session set events '10046 trace name context forever,level 12';

select /* TEST */ * from emp where sal > :"SYS_B_0"

END OF STMT

BINDS #3071441600:

Bind#0

oacdty=02 mxl=22(03) mxlc=00 mal=00 scl=00 pre=00

oacflg=10 fl2=0300 frm=00 csi=00 siz=24 off=0

kxsbbbfp=295c96f0 bln=22 avl=03 flg=09

value=103

"fl2=0300"表示当字面值被替换成绑定变量后，这个变量值是Unsafe的:

#define UACFBLTR 0x00000100 /* Bind was generated by LiTeRal replacement */

#define UACFUNSL 0x00000200 /* UNSafe Literal */

0x200是判断字面值是否'safety'的重要标志。

更多细节需要查看：

Document 377847.1 Unsafe Literals or Peeked Bind Variables

Document 261020.1 High Version Count with CURSOR_SHARING = SIMILAR or FORCE

cursor版本过高，超过了一个阈值，就会将父游标老化

11.2，子游标增长过多的问题更加恶化了，具体参考Bug 10187168。

所以当子游标数量增长到20到100需要自动将父游标老化掉。

要启用这一特性，需要查看:

1. 11.2.0.3以上使用这个参数:

"_cursor_obsolete_threshold"设置为100 (当子游标数量超过它时，父游标就被老化)

2. 11.2.0.2.2，设置:

"_cursor_features_enabled"=1026

event 106001 with value 100 (as the parameter _cursor_obsolete_threshold is not present)

更多信息请查看下面文档:

Document 10187168.8 Enhancement to obsolete parent cursors if Version Count exceeds a threshold

自适应游标共享

当子游标慢慢增加，version count也随之子鞥家，自适应游标共享的意思是基于变量的选择性自动适配执行计划。更多信息请参考下面文档:

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

解决其他问题

处理性能问题请参考下面文档：

Document 1377446.1 Troubleshooting Performance Issues

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

转载于:http://blog.itpub.net/26239116/viewspace-1272199/

你可能感兴趣的文章

聊聊Spring中的数据绑定 --- DataBinder本尊（源码分析）

查看>>

Spring MVC 框架的请求处理流程及体系结构

查看>>

mybatis-generator-gui界面工具生成实体

查看>>

Github访问速度很慢的原因，以及解决方法

笔记︱风控分类模型种类（决策、排序）比较与模型评估体系（ROC/gini/KS/lift）

查看>>

MySQL存储引擎之MyISAM与InnoDB区别