故障排除: 版本数高(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

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

• TYPECHECK_MISMATCH

  现有的子游标没有完全优化。

• AUTH_CHECK_MISMATCH

  对于现有的子游标，认证/翻译检查失败。

  用户没有权限访问以前任何版本游标中的对象。一个典型的例子是，对于一个表，每个用户都有一个属于自己的副本。

• BIND_MISMATCH

  绑定元数据与现有的子游标不匹配。例如，在下面的语句中，绑定变量'a'的定义在两条语句中发生了变化：

  variable a varchar2(100); select count(*) from emp where ename = :a ->> 1 PARENT, 1 CHILD variable a varchar2(400); select count(*) from emp where ename = :a ->> 1 PARENT, 2 CHILDREN

  1 2 3 4 5

  variable a varchar2(100); select count(*) from emp where ename = :a ->> 1 PARENT, 1 CHILD   variable a varchar2(400); select count(*) from emp where ename = :a ->> 1 PARENT, 2 CHILDREN

  其它原因：① 绑定元数据与现有的子游标不匹配，如长度（传入变量varchar2长度跨度很大，同一个变量值，传入的长度出现分别为32,128,2000,4000,8192）、类型（如传入TIMESTAMP，但列类型为DATE）等；② SQL绑定变量输入null值触发BUG 8198150

  其它原因：① 绑定元数据与现有的子游标不匹配，如长度（传入变量varchar2长度跨度很大，同一个变量值，传入的长度出现分别为32,128,2000,4000,8192）、类型（如传入TIMESTAMP，但列类型为DATE）等；② SQL绑定变量输入null值触发BUG 8198150

  实验：

  DROP TABLE T_BG_20280414_LHR_02; CREATE TABLE T_BG_20280414_LHR_02(N NUMBER(10),V VARCHAR2(1000)); DECLARE N1 NUMBER(10) :=1; --V1 VARCHAR2(4000) :=''; --V1 VARCHAR2(4000) :='11111111111111111111'; -- 20 --V1 VARCHAR2(4000) :='1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111'; -- 100 --V1 VARCHAR2(100) :=''; --V1 VARCHAR2(2000) :='11111111111111111111'; -- 20 --V1 VARCHAR2(3000) :='1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111'; -- 100 V1 VARCHAR2(3000) :='11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111'; -- 200 BEGIN EXECUTE IMMEDIATE 'INSERT INTO T_BG_20280414_LHR_02 VALUES(:N1,:V1)' USING N1, V1; COMMIT; END; / select * from v$sql a where a.sql_text like '%INSERT INTO T_BG_20280414_LHR_02 VALUES%' ; select * from v$sql_bind_capture a where a.sql_id='cp0v2szajj9c2'; select * from v$sql_shared_cursor a where a.sql_id ='cp0v2szajj9c2'; select a.sql_id,a.bind_mismatch,a.BIND_LENGTH_UPGRADEABLE from v$sql_shared_cursor a where a.sql_id ='cp0v2szajj9c2'; SELECT * FROM TABLE(VERSION_RPT('cp0v2szajj9c2'));

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

  DROP TABLE T_BG_20280414_LHR_02; CREATE TABLE T_BG_20280414_LHR_02(N NUMBER(10),V VARCHAR2(1000));   DECLARE N1 NUMBER(10) :=1;             --V1 VARCHAR2(4000) :=''; --V1 VARCHAR2(4000) :='11111111111111111111';  -- 20 --V1 VARCHAR2(4000) :='1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111';  -- 100   --V1 VARCHAR2(100) :=''; --V1 VARCHAR2(2000) :='11111111111111111111';  -- 20 --V1 VARCHAR2(3000) :='1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111';  -- 100 V1 VARCHAR2(3000) :='11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111';  -- 200 BEGIN EXECUTE IMMEDIATE 'INSERT INTO T_BG_20280414_LHR_02  VALUES(:N1,:V1)' USING N1, V1; COMMIT; END; /   select * from v$sql a where a.sql_text like '%INSERT INTO T_BG_20280414_LHR_02  VALUES%' ; select * from v$sql_bind_capture a where a.sql_id='cp0v2szajj9c2';   select * from v$sql_shared_cursor a where a.sql_id ='cp0v2szajj9c2';   select a.sql_id,a.bind_mismatch,a.BIND_LENGTH_UPGRADEABLE from v$sql_shared_cursor a where a.sql_id ='cp0v2szajj9c2';   SELECT * FROM TABLE(VERSION_RPT('cp0v2szajj9c2'));

  参考：https://www.dbaup.com/moshigh-version-count-due-to-bind_mismatch-doc-id-3362681-10503-events.html

• DESCRIBE_MISMATCH

  在描述子游标时，类型检查堆不存在。

• LANGUAGE_MISMATCH

  语言句柄与现有的子游标不匹配。

• TRANSLATION_MISMATCH

  现有子游标的基本对象不匹配。

  该对象的定义与当前的任何版本不匹配。通常这表明与对象不同的"AUTH_CHECK_MISMATCH"相同的问题。

• ROW_LEVEL_SEC_MISMATCH

  行级安全策略不匹配。

• INSUFF_PRIVS

  现有子游标所参考的对象的权限不足。

• INSUFF_PRIVS_REM

  现有子游标所参考的远程对象的权限不足。

• REMOTE_TRANS_MISMATCH

  现有子游标的远程基础对象不匹配。比如说：

  USER1: select count(*) from table@remote_db USER2: select count(*) from table@remote_db

  1 2 3 4 5

  USER1: select count(*) from table@remote_db     USER2: select count(*) from table@remote_db

  尽管SQL是相同的，但remote_db所指向的dblink可能是一个私有的dblink，它解析到一个完全不同的对象。

• LOGMINER_SESSION_MISMATCH

• INCOMP_LTRL_MISMATCH

• OVERLAP_TIME_MISMATCH

  Error_on_overlap_time 不匹配。

• SQL_REDIRECT_MISMATCH

  SQL 重定向不匹配。

• MV_QUERY_GEN_MISMATCH

  生成物化视图查询。

• USER_BIND_PEEK_MISMATCH

  用户的 BIND PEEK 不匹配。

• TYPCHK_DEP_MISMATCH

  游标有类型检查的依赖性。

• NO_TRIGGER_MISMATCH

  触发器不一致。

• FLASHBACK_CURSOR

  对于闪回没有游标共享。

• ANYDATA_TRANSFORMATION

  数据转换有变化。

• INCOMPLETE_CURSOR

  不完整的游标。当绑定长度可以升级时(也就是说，找到了一个子游标，除了绑定长度不够之外，其他都匹配)，旧的游标不能使用，并且建立一个新的。 这意味着该版本可以被忽略。

• TOP_LEVEL_RPI_CURSOR

  最顶端的RPI游标。在并行查询的调用中，这是预期的行为(故意不分享)。

• DIFFERENT_LONG_LENGTH

  LONG值的长度不一致。

• LOGICAL_STANDBY_APPLY

  逻辑备库应用上下文不匹配。

• DIFF_CALL_DURN

  调用期间不一致。

• BIND_UACS_DIFF

  绑定UAC不匹配。

• PLSQL_CMP_SWITCHS_DIFF

  PL/SQL编译器开关不匹配。

• CURSOR_PARTS_MISMATCH

  游标 "parts executed" 不匹配。

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

  验证码：

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

  相关文章

  • 【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)
  • 在物理 standby 数据库上如何解决 MRP 进程卡住的问题？ (Doc ID 2331842.1)(Doc ID 1221163.1)
  • ORA-16191 and ORA-1017 reported for Log Transport to Standby when using redo_transport_user (Doc ID 2848435.1)
  • Troubleshooting ORA-16191 and ORA-1017 ORA-1031 in Data Guard Log Transport Services or Data Guard Broker (Doc ID 1368170.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)
  打赏赞(2)分享