合 【MOS】故障排除 版本数高(High Version Count)的问题 (Doc ID 2896923.1) SQL 版本数过高 – 原因判断脚本 (Doc ID 1985045.1) Troubleshooting High Version Count Issues (Doc ID 296377.1)
Tags: OracleMos版本数高(High Version Count)version_rptROLL_INVALID_MISMATCHBIND_EQUIV_FAILUREBIND_MISMATCH
- 参考MOS
- 适用于:
- 用途
- 排错步骤
- 什么是 '高' 版本数?
- 什么是共享 SQL ?
- 什么是SQL元数据 'SQL Metadata'?
- 为什么要关注 '高' 版本?
- 如何看版本以及它们为什么没有共享?
- 如何理解v$SQL_SHARED_CURSOR视图给出的理由?
- Version_rpt 脚本:
- 其他可以用来追踪的方法.
- 尽管使用了绑定变量,会有出现高版本数的情况吗?
- 说明:
- 在版本数超过阈值时废弃父游标的功能增强
- 带有自适应游标共享的高版本数
- 已知问题
- 故障排除其他问题
- 参考
- 适用于:
- Purpose
- 提出问题,得到帮助,并分享您的心得
- Requirements
- Configuring
- Instructions
- 根据所有版本数超过100 的游标的 SQL_ID 生成报告(10g 和更高版本)
- 根据所有版本数超过 100 的游标的 HASH_VALUE 生成报告
- 对 SQL_ID 等于 cyzznbykb509s 的游标生成报告
- 注意(s):
- Script
- Sample Output
- 讨论高版本计数问题
- 参考
- "V$SQL_SHARED_CURSOR" Reference Note
- View Columns
- Support and Historical Notes for "V$SQL_SHARED_CURSOR"
故障排除: 版本数高(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语句将会发生什么:
- 对这个SQL语句执行哈希算法,得到的哈希值为4085390015。
- 通过这个在共享池中将会找到这个这个SQL语句。
- 遍历子游标(这是只有一个子游标)
- 由于用户USER2 的EMP表的OBJECT_ID与用户USER1 的EMP表的OBJECT_ID不同,所以这里会遇到"不一致"。
(基本上,这里发生的事情是,我们有一个链接的子列表,我们依次移动,比较当前SQL的元数据和所有子列表的元数据。
如果有100个子游标,那么我们会逐一扫描(寻找可能的不匹配并继续前进),直到找到一个我们可以共享的子游标。
如果不能共享任何(即已经用完了孩子的名单),那么需要创建一个新的子游标)
- 因此,需要创建一个新的子游标 - 这时就会有一个父游标和两个子游标。
为什么要关注 '高' 版本?
非必要的不共享SQL,以及由此产生的SQL版本,是造成库缓存争用的主要原因。争用会降低数据库的性能,在极端情况下,可能会导致数据库出现"挂起"的状况。当有不必要的游标版本时,每次执行该游标时,解析引擎都要在版本列表中搜索,查看哪个是想要的游标。这就浪费了可以用在其他方面的CPU资源。
如何看版本以及它们为什么没有共享?
使用下面文章中的脚本可以非常简单地获得格式清晰的版本信息:
Document 1985045.1 SQL 版本数过高 – 原因判断脚本
如果不能使用该脚本,那么可以通过访问基本视图获取到相同的信息,如下面的例子所说明的。
通过使用上面的例子,看看可以使用什么样的SQL来查看共享池中的信息。
USER1 执行 select count(*) from emp
现在可以运行以下SQL来查看PARENT语句和它的哈希值与地址
1 2 3 4 5 | 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 |
执行下面的SQL语句查看子游标(这时,期待有一个子游标):-
9.2.X.X 以及以前的版本 :
1select * from v$sql_shared_cursor where kglhdpar = '0000000386BC2E58'10.0.X.X 以及以后的版本:
1select * from v$sql_shared_cursor where address = '0000000386BC2E58'
输出结果:
1 2 3 | 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).
因为这是第一个子游标,所以不匹配信息 (U S O O S L etc) 都是N。现在以另外用户USER2 登录执行相同的查询(select count(*) from emp),再次确认,有以下的输出结果:-
1 2 3 4 | 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'表示不匹配):
(1) AUTH_CHECK_MISMATCH 以及
(2) TRANSLATION_MISMATCH
这是因为USER2 用户下的对象没有映射到USER1 (当前的子游标)的对象。因为不能访问USER1 的对象,以及每个用户中的对象都有不同的object_ids,导致转换失败,发生了不匹配现象。
如何理解v$SQL_SHARED_CURSOR视图给出的理由?
下面是原因列表以及实际例子(标记的是非常常见原因) :
UNBOUND_CURSOR
现有的子游标没有构建完全(换言之, 该子游标没有被优化).
SQL_TYPE_MISMATCH
SQL类型与现有的子游标不匹配。例如,在两个版本不同的客户端上运行同一个应用程序,在服务器中产生不同的子游标。
OPTIMIZER_MISMATCH
优化器环境与现有的子游标不匹配 (修改优化器模式之后,现有的子游标不能被重新使用).
例如:
123select count(*) from emp; ->> 1 父, 1 子游标alter session set optimizer_mode=ALL_ROWSselect count(*) from emp; ->> 1 父, 2 子游标注: 该行为适用于跟踪事件的设置。例如,如果使用 10046 打开跟踪,将新添加一个由于OPTIMIZER_MISMATCH引起的子游标。
OUTLINE_MISMATCH
OUTLINES 与现有的子游标不一致。例如:如果用户之前为这个SQL创建了存储OUTLINES,并且这些OUTLINES被存储在不同的分类里(称为"OUTLINES1" 和 "OUTLINES2")。如果执行下面的命令:
12345alter 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,谢谢!