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

转到底部

文档内容

用途

提出问题，得到帮助，并分享您的心得

要求

配置

说明

根据所有版本数超过100 的游标的 SQL_ID 生成报告（10g 和更高版本）

根据所有版本数超过 100 的游标的 HASH_VALUE 生成报告

对 SQL_ID 等于 cyzznbykb509s 的游标生成报告

Script

示例输出

参考

适用于:

Oracle Database - Enterprise Edition - 版本 9.2.0.1 和更高版本
Oracle Database - Personal Edition - 版本 9.2.0.1 和更高版本
Oracle Database - Standard Edition - 版本 9.2.0.1 和更高版本
本文档所含信息适用于所有平台
文档中的视图和函数必须安装在 SYS 模式并且以 SYS 用户执行（/ as sysdba）。
该脚本可以运行在 9iR2，10gR1，10gR2，11gR1，11gR2。

用途

该函数基于 v$sql_shared_cursor 视图生成汇总报告，并根据原因代码添加额外的诊断信息。它会统计 v$sql_shared_cursor 视图中任意字段为 'Y' 的 SQL 版本记录和所有字段都 'N' 的 SQL 版本记录。这个脚本在判断 SQL 语句有过高版本数的原因时特别有用，对诊断以下 ORA-600[17059] 错误也可能有用。

Document 8922013.8 Bug 8922013 - OERI [17059] / excess child cursors for SQL referencing REMOTE objects

对于报告中列出的 SQL 版本数的产生原因，请参考以下 note：

Document 120655.1 VIEW: "V$SQL_SHARED_CURSOR" Reference Note (Includes list of bugs)
Document 296377.1 Troubleshooting: High Version Count Issues
Document 1377446.1 Troubleshooting Performance Issues

此函数不会取代 Oracle 支持人员要求的 v$sql_shared_cursor 输出。

提出问题，得到帮助，并分享您的心得

您想和其他 Oracle 客户，Oracle 雇员和行业专家对本话题进行更深入的探讨么？

点击这里加入讨论，您可以提出问题，得到别人的帮助，并分享您的心得。
点击这里访问 My Oracle Support 社区数据库性能优化主页，发现其他文章和有帮助的主题讨论。

另外如果你想对这个函数有任何反馈建议，那么您可以在这篇文章上添加评论。或者，您可以回复博客 https://blogs.oracle.com/PerformanceDiagnosis/entry/diagnosis_of_a_high_version

要求

这个视图和函数必须用 SYS 用户连接到 DB 上安装（使用 / as sysdba连接），同时也必须用 SYS 用户运行使用。

这个函数可以输入 hash_value 或 sql id（10g版本和更高版本）作为参数。

配置

下载附件中的脚本：Script to create version_rpt function V3.2.3

version_rpt3_23.sql
并执行如下：

connect / as sysdba
start version_rpt3_24.sql

说明

根据所有版本数超过100 的游标的 SQL_ID 生成报告（10g 和更高版本）

set pages 2000 lines 100
SELECT b.*
FROM v$sqlarea a ,
  TABLE(version_rpt(a.sql_id)) b
WHERE loaded_versions >=100;                               /*如果是11.2.0.3或者包含了patch 10187168, 那么设置为30 */

根据所有版本数超过 100 的游标的 HASH_VALUE 生成报告

set pages 2000 lines 100
SELECT b.*
FROM v$sqlarea a ,
  TABLE(version_rpt(NULL,a.hash_value)) b
WHERE loaded_versions>=100;                                /*如果是11.2.0.3或者包含了patch 10187168, 那么设置为30 */

对 SQL_ID 等于 cyzznbykb509s 的游标生成报告

set pages 2000 lines 100

SELECT * FROM TABLE(version_rpt('cyzznbykb509s'));

注意:
对于已经包含了patch 10187168的数据库版本, _CURSOR_OBSOLETE_THRESHOLD的默认值是100. 因为这个值被认为太低,所以已经被增长到了1024
对于包含了patch 10187168的数据库版本比如11.2.0.3, 我们应该用一个比较小的值作为LOADED_VERSIONS (而不是大于100)
当然如果你或者你的应用程序需要,也可以把它增加到300或更高
参照
Document 10187168.8 Bug 10187168 - Enhancement to obsolete parent cursors if VERSION_COUNT exceeds a threshold

注意：在11g数据库及以上版本，这个脚本只会对因为 BIND* 原因导致的废弃的游标做统计，详见"Consolidated details for BIND* columns"部分。，如果要统计其它废弃的游标，你需要查询 v$SQL 视图，并且用 IS_OBSOLETE='Y' 作为条件统计行数。

该版本报告脚本试图通过检查 IS_OBSOLETE 字段来统计废弃的游标。但是这个字段在 V$SQLAREA 视图中仅仅为了向后兼容---不管游标废弃与否，它的值都是 ’N’。对于某条 SQL 语句，不管有多少 parent cursor 或者被废弃的游标存在，V$SQLAREA 中只显示一行记录。

警告

此示例代码只为教育目的，Oracle Support不提供技术支持。它已经过内部测试，然而我们无法确保它在任何环境中都能成功使用。请您在使用之前先在测试环境中运行。

Script

请参考文章的附件。

示例输出

Note:438755.1 Version Count Report Version 3.2.4 -- Today's Date 05-jun-15 15:53
RDBMS Version :11.2.0.3.0 Host: XXX Instance 1 : XXX
==================================================================
Addr: 000000006D687E40 Hash_Value: 275110491 SQL_ID b6bkg4n86bqkv
Sharable_Mem: 163488 bytes Parses: 4 Execs:20
Stmt:
0 SELECT COUNT(*) AMC FROM T WHERE X=:B1
1

Versions Summary
----------------
OPTIMIZER_MISMATCH :3
OPTIMIZER_MODE_MISMATCH :6
BIND_LENGTH_UPGRADEABLE :8

Total Versions:11

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
cursor_sharing = EXACT
_cursor_obsolete_threshold = 100 (See Note:10187168.8)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Plan Hash Value Summary
-----------------------
Plan Hash Value Count
=============== =====
2966233522 12

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Details for OPTIMIZER_MISMATCH :

3 versions with statistics_level = all
3 versions with sqlstat_enabled = true
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Details for OPTIMIZER_MODE_MISMATCH :

3 versions with RULE
6 versions with ALL_ROWS
3 versions with FIRST_ROWS
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Details for BIND_LENGTH_UPGRADEABLE :

Consolidated details for BIND* columns:
BIND_MISMATCH,USER_BIND_PEEK_MISMATCH,BIND_UACS_DIFF,BIND_LENGTH_UPGRADEABLE,etc and
BIND_EQUIV_FAILURE (Mislabled as ROW_LEVEL_SEC_MISMATCH BY bug 6964441 in 11gR1)

from v$sql_bind_capture
COUNT(*) POSITION MIN(MAX_LENGTH) MAX(MAX_LENGTH) DATATYPE BIND GRADUATION (PRECISION,SCALE)
======== ======== =============== =============== ======== =============== =================
12 1 32 2000 1 Yes (,)

SUM(DECODE(column,Y, 1, 0) FROM V$SQL
IS_OBSOLETE IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE
=========== ================= ============= ============
0 0 0 4
####
To further debug Ask Oracle Support for the appropiate level LLL.
alter session set events
'immediate trace name cursortrace address 275110491, level LLL';
To turn it off do use address 1, level 2147483648
================================================================

注意：如果大部分 SQL 版本都被报告为"Versions with ALL Columns as "N" "，那么这可能是由 Bug 12539487 之类的报告结构缺陷引起的。

在11g数据库及以上版本，这个脚本只会对因为 BIND* 原因导致的废弃的游标做统计，详见"Consolidated details for BIND* columns"部分。，如果你想统计其它废弃的游标，你必须查询 v$SQL 视图，并且用 IS_OBSOLETE='Y' 作为条件统计行数。

该版本报告脚本试图通过检查 IS_OBSOLETE 字段来统计废弃的游标。但是这个字段在 V$SQLAREA 视图中仅仅为了向后兼容---不管游标废弃与否，它的值都是’N’。对于某条 SQL 语句，不管有多少 parent cursor 或者被废弃的游标存在，在 V$SQLAREA 中只显示一行记录。

参考

NOTE:8922013.8 - Bug 8922013 - OERI [17059] / excess child cursors for SQL referencing REMOTE objects
NOTE:1377446.1 - * Troubleshooting Performance Issues
NOTE:120655.1 - VIEW: "V$SQL_SHARED_CURSOR" Reference Note
NOTE:296377.1 - Troubleshooting: High Version Count Issues