Oracle数据库High Version Count问题诊断与优化

1. High Version Count问题概述

在Oracle数据库10.2.0.4和11.2.0.4版本中，High Version Count是一个常见且棘手的问题。简单来说，当一个SQL语句存在大量子游标(child cursor)时，就会出现High Version Count现象。这种现象不仅会消耗大量共享池(shared pool)内存，还可能导致严重的性能问题甚至数据库挂起。

1.1 什么是Version Count

当一个SQL语句首次执行时，Oracle会进行硬解析(hard parse)，创建父游标(parent cursor)和子游标(child cursor)。后续执行相同SQL时，Oracle会先计算SQL语句的hash值，然后在共享池中查找匹配的父游标。如果找到匹配的父游标，就会遍历其下的子游标列表，寻找可重用的执行计划。如果找不到可重用的子游标，就会创建新的子游标。

父游标下的子游标总数就是这个SQL的version count。当version count过高时，就会出现High Version Count问题。

1.2 High Version Count的危害

High Version Count会带来多方面的问题：

  1. 共享池内存消耗：每个子游标都会占用共享池内存，大量子游标会快速耗尽共享池空间
  2. 性能下降：查找和匹配大量子游标会增加CPU开销
  3. 数据库挂起：在某些情况下可能导致数据库完全挂起
  4. 触发ORA-04031错误：当共享池空间不足时出现
  5. 引发其他bug：如ORA-600 [kkssearchchildlist*]等

2. 诊断High Version Count问题

2.1 识别高Version Count的SQL

首先需要找出哪些SQL存在High Version Count问题：

-- 查找version count超过100的SQL SELECT sql_id, version_count, sql_text FROM v$sqlarea WHERE version_count > 100 ORDER BY version_count DESC;

在AWR报告中，默认version count超过20的SQL就会显示在"order by version count"部分。根据经验，version count超过100就需要引起注意。

2.2 分析子游标不共享的原因

找到问题SQL后，需要分析为什么这些SQL会产生大量子游标：

-- 查看特定SQL的子游标不共享原因 SELECT * FROM v$sql_shared_cursor WHERE sql_id = '8n5bcvc2mwjmj';

v$sql_shared_cursor视图中的"Y"值表示对应列存在不匹配(mismatch)情况，这是导致子游标不能共享的直接原因。

2.3 使用version_rpt工具诊断

手工查询上述视图可能比较繁琐，Oracle提供了一个名为version_rpt的小工具，可以更方便地诊断High Version Count问题。这个工具可以从Oracle支持文档(DOC ID 438755.1)下载。

3. 深入诊断技术

3.1 CursorTrace和CursorDump

当v$sql_shared_cursor无法提供足够信息时，可以使用CursorTrace和CursorDump技术进行更深入的诊断。

3.1.1 启用CursorTrace
-- 启用CursorTrace ALTER SYSTEM SET events 'immediate trace name cursortrace level 577, address <hash_value>';

CursorTrace有三个级别：

  • Level 1: 577
  • Level 2: 578
  • Level 3: 580
3.1.2 关闭CursorTrace
-- 关闭CursorTrace ALTER SYSTEM SET events 'immediate trace name cursortrace level 2147483648, address 1';

注意：在10.2.0.4以下版本存在Bug 5555371，可能导致CursorTrace无法彻底关闭，trace文件会不断增长。生产环境建议谨慎使用CursorTrace。

3.1.3 CursorDump(11g及以上版本)
-- 11g中使用CursorDump ALTER SYSTEM SET events 'immediate trace name cursordump level 16';

CursorDump可以收集更全面的信息，包括一些其他方法无法看到的px_mismatch和optimizer_mismatch信息。

3.2 ProcessState Dump和Errorstack(10gR2)

在10gR2中，可以使用processstate dump和errorstack替代CursorDump：

-- 找到问题SQL对应的SPID SELECT spid FROM v$session s, v$process p WHERE s.paddr = p.addr AND s.sql_id = '问题SQL_ID'; -- 使用oradebug进行dump ORADEBUG SETOSPID <spid> ORADEBUG ULIMIT ORADEBUG DUMP PROCESSSTATE 10 ORADEBUG DUMP ERRORSTACK 3

4. 常见导致High Version Count的SQL模式

根据经验，以下类型的SQL最容易导致High Version Count问题：

4.1 使用绑定变量的INSERT语句

INSERT INTO table(column1, column2, ..., column128) VALUES (:1, :2, :3, ..., :128)

特别是当表字段很多且INSERT语句中列出了所有字段时，问题尤为明显。

4.2 使用绑定变量的SELECT INTO语句

SELECT a, b, c, ... INTO :1, :2, :3 FROM table1

4.3 使用INSERT...RETURNING语句

INSERT INTO table(...) VALUES (...) RETURNING id INTO :id

4.4 使用长IN列表且包含绑定变量

SELECT * FROM table WHERE column1 IN (:1, :2, :3, ..., :128)

4.5 超长SQL且包含多个绑定变量

非常长的SQL语句如果使用了绑定变量，更容易出现High Version Count问题。

4.6 在DBLINK调用的SQL中使用绑定变量

-- 不推荐的做法 SELECT * FROM table@dblink WHERE column = :1

5. 配置参数相关问题

5.1 cursor_sharing参数

cursor_sharing参数设置不当容易导致High Version Count问题：

  • 绝对不要使用cursor_sharing=similar：这个设置在10gR2以上版本会导致各种bug，包括产生大量不可共享的子游标
  • 在11.2.0.3版本，cursor_sharing=similar与=force效果相同
  • 在12c中已不支持cursor_sharing=similar
  • 建议设置为exact，除非经过充分测试，否则不要设置为force

5.2 Adaptive Cursor Sharing(ACS)

11g引入的Adaptive Cursor Sharing特性也容易导致High Version Count问题。在未经充分测试前，建议关闭此特性：

ALTER SYSTEM SET "_optimizer_adaptive_cursor_sharing"=FALSE;

相关bug：

  • Bug 12334286：High version counts with CURSOR_SHARING=FORCE
  • Bug 7213010：Adaptive cursor sharing generates lots of child cursors
  • Bug 8491399：ACS does not match the correct cursor version for queries using CHAR datatype

6. 常见Bug及解决方案

6.1 Bug 8575528 / Patch 6795880

这是10gR2中一个非常严重且隐蔽的bug，会导致：

  • 数据库挂起，必须手工重启
  • 系统资源耗尽导致宕机
  • 触发ORA-600 [kkssearchchildlist*]或ORA-07445[kkssearchchildlist*]错误

虽然在10.2.0.5中声称已修复，但实际仍然常见，因为：

  1. 修复代码默认不生效，需要手动设置"_cursor_features_enabled"=10
  2. 即使设置参数，仍可能遇到问题

解决方案：

  • 升级到11gR2
  • 调整问题SQL

6.2 变长字符串绑定变量问题

当表字段使用VARCHAR等变长类型，而应用传入的字符串长度变化很大时，会导致bind mismatch，产生大量子游标。

解决方案：

-- 设置固定的字符串buffer长度 ALTER SYSTEM SET events '10503 trace name context forever, level 4000';

6.3 Bug 8981059

这个bug影响所有10gR2版本，是由绑定变量窥测(bind peeking)导致的。

解决方案：

-- 关闭绑定变量窥测 ALTER SYSTEM SET "_optim_peek_user_binds"=FALSE;

6.4 清除高Version Count的SQL

对于version count特别高的SQL，可以将其从共享池中清除：

-- 10.2.0.4和10.2.0.5中的清除方法 ALTER SESSION SET events '5614566 trace name context forever'; EXEC dbms_shared_pool.purge('&address, &hash_value', 'C');

注意：event 5614566是为了规避Bug 5614566，该bug会导致dbms_shared_pool.purge无法清除parent cursor。

7. 11g中的增强特性

7.1 _cursor_obsolete_threshold参数

11g引入了_cursor_obsolete_threshold参数(默认100)，当子游标数量超过此阈值时，parent cursor会被废弃并创建新的parent cursor。这有效解决了High Version Count问题。

启用方法：

-- 11.2.0.1 ALTER SYSTEM SET "_cursor_features_enabled"=34 SCOPE=SPFILE; ALTER SYSTEM SET event='106001 trace name context forever,level 1024' SCOPE=SPFILE; -- 11.2.0.2 ALTER SYSTEM SET "_cursor_features_enabled"=1026 SCOPE=SPFILE; ALTER SYSTEM SET event='106001 trace name context forever,level 1024' SCOPE=SPFILE;

8. 最佳实践建议

8.1 SQL改写建议

  1. 对于INSERT INTO或SELECT INTO，考虑不使用绑定变量
  2. 字段很多的表，INSERT时不要列出所有字段
  3. 控制IN列表中绑定变量的数量，或改用临时表
  4. 避免编写特别长的SQL语句
  5. 尽量避免在DBLINK调用的SQL中使用绑定变量

8.2 配置建议

  1. 设置cursor_sharing=exact
  2. 关闭adaptive cursor sharing
  3. 对于10.2.0.4，考虑升级到更高版本
  4. 监控并定期清理高version count的SQL

8.3 监控脚本

-- 监控shared pool中SQLA区域大小 SELECT * FROM V$SGASTAT WHERE pool='shared pool' AND name='SQLA' AND bytes/1024/1024/1024 > 5; -- 监控硬解析高的非绑定变量SQL SELECT FORCE_MATCHING_SIGNATURE, COUNT(1) FROM v$sql WHERE FORCE_MATCHING_SIGNATURE > 0 AND FORCE_MATCHING_SIGNATURE != EXACT_MATCHING_SIGNATURE GROUP BY FORCE_MATCHING_SIGNATURE HAVING COUNT(1) > 5000 ORDER BY 2; -- 查找硬解析最多的SQL SELECT TO_CHAR(force_matching_signature), COUNT(*) hard_parses FROM v$sqlarea GROUP BY TO_CHAR(force_matching_signature) HAVING COUNT(*) > 5 ORDER BY 2 DESC;

9. 总结

High Version Count问题在Oracle 10.2.0.4和11.2.0.4中是一个复杂且棘手的问题，其产生原因多样，表现形式各异。通过合理的诊断方法和适当的解决方案，可以有效地应对这一问题。从11gR2开始，通过_cursor_obsolete_threshold特性，这个问题得到了根本性的解决。