Loading
0

Troubleshooting High Version count

本文目录
[隐藏]

 

  • 什么是 'High'  version count ?

对于一个特定的cursor来说,到底多少数量的version才算是 ‘HIGH’其实没有一个绝对的定论,不同的系统能够处理的version的范围不同。然后,AWR会在一个特定的cursor的versions超过20时记录在报告中以作为任何一个潜在问题的好指标。

Version_count

当你发现某个sql的version count已经进入上百甚至上千范围的时候,那么这无疑是一个‘HIGH’counts,你应该检查一下导致这个问题的原因,鼓励使用共享的SQL以减少这个数字。

 

  • 什么是 shared SQL ?

首先你得记住一件事,所有的SQL都是隐式共享的。当你输入一个SQL语句时,RDBMS会为这个SQL语句的文本创建一个 hash value ,这个hash value会帮助RDBMS很容易的找到已经在shared pool中的SQL语句。这个细节不在本文的讨论范围之内,所以我们只是假设我们输入了一连串的文本后,RDBMS就同时创建了一个hash value。

 

举个例子,我们输入了SQL“select * from emp”,系统给他生成的hash value就是4085390015。

 

现在我们为这个SQL创建了一个parent cursor和一个single child。当一个SQL第一次被解析的时候会创建一个parent和一个single child,即便这个SQL可能从来不会被共享也没有关系。这个很容易就能想到,parent cursor代表了一个hash value,而child cursor(s)代表的是这个sql语句的metadata。

 

  • 什么是‘SQL METADATA’?

Metadata包含了可以让语句执行的所有信息。比如之前的例子,我们给出的表EMP它的所有者是SCOTT,因此就有一个OBJECT_ID指向这个用户所有用的EMP表。当用户SCOTT登陆时,为了在会话中使用这个语句,优化器参数将被初始化,而优化器使用的就是metadata里的信息。本文中还会在其他地方多次提到metadata的例子。

比方说,现在这个会话登出后又登陆回来,然后又执行了相同的命令(同一个用户),那么这时我们在shared pool中已经有了这个SQL(但是其实我们也还不知道是不是已经有了)。我们要做的就是将这个SQL语句生成hash value,然后在shared pool中寻找这个hash value。如果我们在shared pool中找到了与之匹配的hash value,那么我们可以通过查找它的children来决定它们中是否有可以被我们使用的的那个(即,metadata是相同的)。如果有,那么我们就可以共享这个SQL语句。

目前为止在shared pool中我仍然只有这个SQL语句的一个version,因为metadata使我们能够共享这个语句已经存在了的child。基本上也就是parent是不被共享的,而决定是否有共享能力的是children。

现在 -  有另一个用户“TEST”,他拥有他自己的EMP表,有他自己的EMP表的version,如果这时他执行了跟上面“SCOTT”用户同样的SQL语句会发生什么呢?

1.首先这个语句会被hash化 -  hash化之后的值会跟SCOTT用户的那个语句的hash value一样,也是4085390015。

2.然后这个语句会在shared pool中被找到,因为它已经存在。

3.接着这个语句的children会被扫描(这个时间点我们还只有一个child)。

4.由于属于“TEST”这个用户的EMP表的OBJECT_ID跟属于“SCOTT”用户的EMP表的OBJECT_ID是不一样的,所以我们得倒了一个“mismatch”。

(从本质上讲,这里发生的事情其实就是我们拥有一个children的链表,我们在上面逐个移动,使用当前的SQL语句的metadata与这些children的metadata作比较。如果我们有100个children,那么我们将扫描它们的每一个(找到一个可能的mismatch就移动到下一个)直到我们找到那个可以共享的。如果我们没能找到任何一个能共享的child(即,children链表已经用尽),那么我们将创建一个新的child。)

5.因此我们创建了一个新的child - 这时我们有了 1 parent 和 2 children。

 

  • 为什么我们要关注“HIGH” Versions ?

非共享的SQL和其所得到的多版本是导致library cache 争用的一个主要因素,争用会降低数据库性能,在极端情况下,会导致“hang”出现。当你的一个cursor拥有不必要的versions的时候,每次cursor被执行,解析引擎都会通过搜索versions列表来查找哪个version是这个cursor想要的。这会浪费了本可以用在其他地方的CPU时间。

 

  • 我们怎么才能看到这些版本和它们为什么没有被共享的原因呢?

要以一个清晰的格式得到version信息最简单的方法就是执行脚本version_rpt3_22.sql

Download660 downloads
,下载完之后执行以下操作:

使用SQL_ID生成所有version超过100的cursor的报告(10g 以及以上版本)

使用hash_value生成所有version超过100的cursor的报告

生成SQL_ID为cyzznbykb509s的报告

以下为输出样本

 

如果你不能使用这个脚本,你也可以通过查询一些基础视图来获取同样的信息。

接下来我们通过上面的示例来看一看什么样的SQL可以在shared pool中被我们使用

Scott 执行了 select * from emp

我们可以通过以下的SQL来查看这个parent语句还有它的hash value和address

 

查看children的信息(希望这时还是一个children)

    • Version 9.2.X.X 以及之前版本
    • Version 10.0.X.X 以及之后版本

 

输出内容大致如下

我们可以看到目前就只有一个child,地址为0000000386BC2D08,mismatch信息(U S O O S等等)都是N,这是因为它是第一个child。

这时如果我用另一个用户登录,并且执行相同的语句(select * from emp),再看这个视图我们会发现以下输出:

现在我们看到了第二个child以及它为什么不能跟第一个child共享的原因(这些“Y”指明了一个mismatch),这些原因是:

  1. AUTH_CHECK_MISMATCH
  2. TRANSLATION_MISMATCH

这是由于新用户下的对象与SCOTT用户下的对象是无法映射的(在当前child下),mismatch的发生是由于我不能访问SCOTT的对象,每个schema下的对象都有不同的object_id,所以转换失败。

 

  • 那么视图v$sql_shared_cursor中给出的原因究竟是什么意思呢?

我们可以从联机文档上关于这个视图各字段的描述中得到答案,以下“**”打头的是最为常见的mismatch的原因:

 

 

  • 进一步调查

在10G中我们可以使用CURSORTRACE来帮助调查为什么cursor没有被共享,这个event只应该在ORACLE支持人员的指导下使用,并且产生的追踪文件的结果不能作为证据归档记录。你要得到一个特定的SQL的追踪首先需要做的是得到它的hash value(你可以通过查询v$sqlarea得到),然后你可以使用一下语句来设置追踪:

(对于高级别的追踪 level 578/580 可以被使用,577=level 1、578=level 2、580=level 3)

每次我们要重新使用cursor时就会有一个跟踪文件写入到 user_dump_dest  目录下。

使用一下语句关闭这个追踪:

注意:在10.2版本中(10.2.0.4被修复)存在Bug 5555371,cursor trace无法被完全的关闭,结果就是单行条目仍然会被跟踪,建议重启instance,怎么会激发这个Bug依赖于cursor的执行(结果就是跟踪文件会有额外的尺寸变大)

在11.2中也有corsordump:

(请确保是在system层面而不是session层面使用)

它或转储一些额外的信息,诸如对参数 optimizer_mismatch 问题的扩展。

 

  • 是否有时候即便使用了绑定变量 HIGH VERSIONS 依然会存在 ?

考虑一下下面的问题,当使用了cursor_sharing=SIMILAR

你会看到多个version,并且每一个都没有明显的不共享的原因。

 

  • 解释

如果一个执行计划的改变是依赖于文本的值,那么当使用cursor_sharing=SIMILAR文本替换功能被启用后,cursor共享的一个标准就是绑定变量的值应该要匹配绑定变量的初始值,这就是我们也许会得到一个子优化的计划的原因如果使用了同一个cursor。当优化器依赖于字面的值的时候,选择不同的执行计划通常会发生。因此我们这个测试案例中使用了“>”谓词,如果我们使用“=”,那么我们将总是共享同一个cursor。如果应用开发人员想要使用子优化计划并将它保存在内存中,那么应当把cursor_sharing设置为force。

SIMILAR和FORCE的区别在于SIMILAR会强制类似的语句共享 SQL AREA 而不会有日益恶化的执行计划,而FORCE会强制类似的SQL语句共享 SQL AREA 但会存在潜在的恶化的执行计划。

如果这个绑定变量被认为是不安全的,那么也可以用10046事件追踪(level 4/12 – binds)来解释。

9i中 flag oacfl2  ,10g中 flag fl2 会被显示,如果变量是不安全的话。

使用了上面的示例的查询,在10g(10.2.0.5)和11g中会有类似下面的显示:

fl2=0300条目显示了这个字面值不安全,这个变量是由替换生成的:

0x200条目对于决定字面值是否安全是很重要的标记。

 

  • 强化,如果version count超出阀值则是parent cursor过期

在11gR2中,出现了一个child cursor会变得很长的问题,Bug 10187168中一个增强的请求被提出来解决这个问题,当child cursor增长超过了某个数量(比如20或者100)时,那么他们的parent cursor会被标记为过期,为了激活这个强化Bug需要设置如下:

1.如果是11.2.0.3以及以上版本,设置如下参数:

2.如果是11.2.0.2.2版本的,设置如下:

  • Adaptive Cursor Sharing带来的HIGH VERSION COUNT

随着11g引入了adaptive cursor sharing,version count会因为遭遇更多的child cursor而增加,依赖于绑定变量的选择性,adaptive cursor sharing就意味着adapt execution plans,从而产生更多的child cursor。

请尊重我们的辛苦付出,未经允许,请不要转载 Ask600 的文章!