故障排除: 版本数高(High Version Count)的问题 (Doc ID 2896923.1)

参考MOS

Troubleshooting: High Version Count Issues (Doc ID 296377.1)

故障排除: 版本数高(High Version Count)的问题 (Doc ID 2896923.1)

SQL 版本数过高 – 原因判断脚本 (Doc ID 1985045.1)

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

适用于:

Oracle Database - Personal Edition - 版本 10.2.0.1 和更高版本
Oracle Database - Enterprise Edition - 版本 10.2.0.1 和更高版本
Oracle Database Cloud Schema Service - 版本 N/A 和更高版本
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - 版本 N/A 和更高版本
Oracle Cloud Infrastructure - Database Service - 版本 N/A 和更高版本
本文档所含信息适用于所有平台

用途

本故障排除指南提供帮助如何调试SQL共享问题。在可能的情况下，本文档中包括了诊断工具，以协助排除问题。本文件不包含bug/补丁相关内容，关于这些主题可以参考文档底部引用的相关文档。

排错步骤

什么是 '高' 版本数?

对于特定的游标，关于所谓的"高"版本数，并没有明确的定义，不同的系统可能会有不同版本范围。不过，AWR报告开始报告一个特定游标的版本超过20，这是一个很好的存在潜在问题的指标。

一旦你发现版本数达到了数百或者数千个的时候，那么很明显版本数高了，应该调查原因，建议用户通过共享SQL来降低版本数。重要的是要理解，有时高版本数是预期的，而不是由于任何问题(缺陷)产生的的结果。

什么是共享 SQL ?

首先要记住的是，所有的SQL都是隐性可共享的。当输入一个SQL语句时，RDBMS将为该SQL语句创建一个哈希值，然后RDBMS通过该哈希值可以轻松地找到已经在共享池中存在的SQL。

例如 :- 'select count(*) from emp' 的哈希值为4085390015。

现在我们为这个sql创建一个父游标和一个子游标。一个SQL语句可能永远不会被共享，这并没有问题--当它第一次被解析时，会创建一个父游标和一个子游标。简单地说，父游标代表该SQL的哈希值，子游标代表该SQL的元数据。

什么是SQL元数据 'SQL Metadata'?

元数据是使语句能够运行的所有信息。例如，USER1 用户有一张表EMP，这样会有一个OBJECT_ID，通过这个OBJECT_ID可以定位到从属于USER1 用户的这个EMP表。当用户USER1 登录时，在这个会话中初始化供语句使用的优化器参数，优化器也会用到这些初始化参数，因此也属于元数据。还有其他一些元数据的例子，将在本文档中进一步提及。

这个会话退出，然后又登录回来。再次执行相同的命令（作为同一个用户）。这次在共享池中已经存在这个SQL(但是我们并不知道这些)。我们对语句执行哈希运算，通过哈希值在共享池中寻找。如果我们能够找到，然后我们通过查找子游标来判断我们是否可以重用它们(比如元数据一致)。如果是这样，那么我们就可以共享该SQL语句。因为元数据让我们能够共享已经存在子游标，所以这时共享池中仍然只有这个SQL的一个版本。基本原则是，父游标不会被共享，根据子游标来判断是否可以被共享。

接下来，另一个用户USER2 ，这个用户同样有一张名为EMP的表，如果用过户执行之前的SQL语句将会发生什么：

1. 对这个SQL语句执行哈希算法，得到的哈希值为4085390015。
2. 通过这个在共享池中将会找到这个这个SQL语句。
3. 遍历子游标(这是只有一个子游标)
4. 由于用户USER2 的EMP表的OBJECT_ID与用户USER1 的EMP表的OBJECT_ID不同，所以这里会遇到"不一致"。

(基本上，这里发生的事情是，我们有一个链接的子列表，我们依次移动，比较当前SQL的元数据和所有子列表的元数据。
如果有100个子游标，那么我们会逐一扫描（寻找可能的不匹配并继续前进），直到找到一个我们可以共享的子游标。
如果不能共享任何(即已经用完了孩子的名单)，那么需要创建一个新的子游标)

5. 因此，需要创建一个新的子游标 - 这时就会有一个父游标和两个子游标。

为什么要关注 '高' 版本?

非必要的不共享SQL，以及由此产生的SQL版本，是造成库缓存争用的主要原因。争用会降低数据库的性能，在极端情况下，可能会导致数据库出现"挂起"的状况。当有不必要的游标版本时，每次执行该游标时，解析引擎都要在版本列表中搜索，查看哪个是想要的游标。这就浪费了可以用在其他方面的CPU资源。

如何看版本以及它们为什么没有共享?

使用下面文章中的脚本可以非常简单地获得格式清晰的版本信息：

Document 1985045.1 SQL 版本数过高 – 原因判断脚本

如果不能使用该脚本，那么可以通过访问基本视图获取到相同的信息，如下面的例子所说明的。

通过使用上面的例子，看看可以使用什么样的SQL来查看共享池中的信息。

USER1 执行 select count(*) from emp

现在可以运行以下SQL来查看PARENT语句和它的哈希值与地址

执行下面的SQL语句查看子游标(这时，期待有一个子游标)：-

• 9.2.X.X 以及以前的版本 :

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

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

• 10.0.X.X 以及以后的版本:

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

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

输出结果:

可以确认到有一个子游标 (地址 0000000386BC2D08).
因为这是第一个子游标，所以不匹配信息 (U S O O S L etc) 都是N。现在以另外用户USER2 登录执行相同的查询(select count(*) from emp)，再次确认，有以下的输出结果：-

这次可以确认到出现了第二个子游标(0000000386A91AA0)，与第一个子游标不能共享的理由('Y'表示不匹配)：

(1) AUTH_CHECK_MISMATCH 以及
(2) TRANSLATION_MISMATCH

这是因为USER2 用户下的对象没有映射到USER1 (当前的子游标)的对象。因为不能访问USER1 的对象，以及每个用户中的对象都有不同的object_ids，导致转换失败，发生了不匹配现象。

如何理解v$SQL_SHARED_CURSOR视图给出的理由?

下面是原因列表以及实际例子(标记的是非常常见原因) :

• UNBOUND_CURSOR

  现有的子游标没有构建完全(换言之, 该子游标没有被优化).

• SQL_TYPE_MISMATCH

  SQL类型与现有的子游标不匹配。例如，在两个版本不同的客户端上运行同一个应用程序，在服务器中产生不同的子游标。

• OPTIMIZER_MISMATCH

  优化器环境与现有的子游标不匹配 (修改优化器模式之后，现有的子游标不能被重新使用).

  例如:

  select count(*) from emp; ->> 1 父, 1 子游标 alter session set optimizer_mode=ALL_ROWS select count(*) from emp; ->> 1 父, 2 子游标

  1 2 3

  select count(*) from emp;    ->> 1 父, 1 子游标 alter session set optimizer_mode=ALL_ROWS select count(*) from emp;    ->> 1 父, 2 子游标

  注: 该行为适用于跟踪事件的设置。例如，如果使用 10046 打开跟踪，将新添加一个由于OPTIMIZER_MISMATCH引起的子游标。

• OUTLINE_MISMATCH

  OUTLINES 与现有的子游标不一致。例如：如果用户之前为这个SQL创建了存储OUTLINES，并且这些OUTLINES被存储在不同的分类里(称为"OUTLINES1" 和 "OUTLINES2")。如果执行下面的命令：

  alter session set use_stored_outlines = OUTLINES1; select count(*) from emp; alter session set use_stored_oulines= OUTLINES2; select count(*) from emp;

  1 2 3 4 5

  alter session set use_stored_outlines = OUTLINES1; select count(*) from emp;   alter session set use_stored_oulines= OUTLINES2; select count(*) from emp;

  第二次执行"select from emp" 将创建另一个子游标，因为使用的OUTLINES与第一次运行的OUTLINES不同。这个子游标将被标记为 OUTLINE_MISMATCH。

• STATS_ROW_MISMATCH

  现有的统计数据与现有的子游标不匹配。检查是否在所有会话上都设置了10046/sql_trace，因为这可能导致这种情况。

• LITERAL_MISMATCH

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

• SEC_DEPTH_MISMATCH

  安全级别与现有的子游标不匹配。

• EXPLAIN_PLAN_CURSOR

  子游标是一个 explain plan 游标，不应该被共享。 explain plan 句将默认生成一个新的子游标--这种情况将不匹配。

• BUFFERED_DML_MISMATCH

  缓冲的DML与现有的子游标不匹配。

• PDML_ENV_MISMATCH

  PDML环境与现有的子游标不匹配。参数 parallel_dml_mode 和/或 parallel_max_degree 可能已经改变。

• INST_DRTLD_MISMATCH

  直接加载插入与现有的子游标不匹配。

• SLAVE_QC_MISMATCH

  现有的子游标是一个工作游标，而新的游标是由协调者发出的(或者，现有的子游标是由协调者发出的，而新的是一个工作游标)。

  本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
  后续精彩内容已被小麦苗无情隐藏，请输入验证码解锁本站所有文章！

  验证码：

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

  相关文章

  • 【MOS】数据库挂起 由于 LANGUAGE_MISMATCH 的 High Version Count 导致 'cursor：mutex X' 争用 (Doc ID 2577528.1) (Doc ID 2542447.1)
  • Oracle之library cache系列等待事件分析方法总结（持续更新）
  • Mos脚本version_rpt3_25.sql用于排查高版本游标的原因
  • 【MOS】高版本游标数的SQL语句在升级到12.2及更高版本后会导致数据库性能下降 (Doc ID 2469639.1)
  • 由于BIND_EQUIV_FAILURE导致的游标不能共享问题
  • 【MOS】收集统计信息导致游标失效ROLL_INVALID_MISMATCH Rolling Cursor Invalidations with DBMS_STATS.AUTO_INVALIDATE (Doc ID 557661.1)
  • 什么是Oracle的高版本游标（High Version Count）？如何排查？（持续更新）
  • 【MOS】RAC 环境中最常见的 5 个数据库和或实例性能问题 (Doc ID 1602076.1)
  • 解析失败导致大量的Library Cache Lock锁Library Cache Lock on Namespace SQL AREA BUILD(82) Caused by Failed Parse (Doc ID 2294281.1)
  • 【MOS】故障排除“cursor: pin S wait on X”等待事件 (Doc ID 2901617.1)
  • 【MOS】How to Identify Hard Parse Failures Such as Error=923 (Doc ID 1353015.1)
  • 【MOS】How to Determine the Blocking Session for Event 'cursor: pin S wait on X' (Doc ID 786507.1)
  • 【MOS】High Version Count Due To BIND_MISMATCH (Doc ID 336268.1) -- 10503 events事件介绍
  • 【MOS】诊断 ’library cache: mutex X’ 等待 (Doc ID 2331144.1) Troubleshooting 'library cache: mutex X' Waits. (Doc ID 1357946.1)
  • 【MOS】故障排除 Shared Pool优化 (Doc ID 1523934.1) Troubleshooting: Understanding and Tuning the Shared Pool (Doc ID 62143.1)
  • 【MOS】'library cache lock' 等待事件 原因和解决方案 (Doc ID 2896611.1) library cache lock' Waits: Causes and Solutions (Doc ID 1952395.1)
  • 【MOS】High 'library cache lock' Wait Time Due to Invalid Login Attempts (Doc ID 1309738.1)
  • Oracle为何在查询中索引未被使用 (文档 ID 1549181.1)
  • Oracle MOS中文文档列表 - Oracle Database (Doc ID 1533057.1)
  • 零宕机时间迁移 ASM 磁盘组到另一个 SAN、磁盘阵列、DAS 的准确步骤 (Doc ID 1946664.1)
  打赏赞(2)分享