合 【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_rptBIND_EQUIV_FAILUREBIND_MISMATCHROLL_INVALID_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
现有的子游标是一个工作游标,而新的游标是由协调者发出的(或者,现有的子游标是由协调者发出的,而新的是一个工作游标)。
TYPECHECK_MISMATCH
现有的子游标没有完全优化。
AUTH_CHECK_MISMATCH
对于现有的子游标,认证/翻译检查失败。
用户没有权限访问以前任何版本游标中的对象。一个典型的例子是,对于一个表,每个用户都有一个属于自己的副本。
BIND_MISMATCH
绑定元数据与现有的子游标不匹配。例如,在下面的语句中,绑定变量'a'的定义在两条语句中发生了变化:
12345variable a varchar2(100);select count(*) from emp where ename = :a ->> 1 PARENT, 1 CHILDvariable 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
实验:
123456789101112131415161718192021222324252627DROP TABLE T_BG_20280414_LHR_02;CREATE TABLE T_BG_20280414_LHR_02(N NUMBER(10),V VARCHAR2(1000));DECLAREN1 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'; -- 100V1 VARCHAR2(3000) :='11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111'; -- 200BEGINEXECUTE 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
现有子游标的远程基础对象不匹配。比如说:
12345USER1:select count(*) from table@remote_dbUSER2: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,谢谢!