oracle 高版本 High SQL Version Counts 原因查找

High SQL Version Counts - Script to determine reason(s)   Doc ID 438755.1)


--官网下载脚本并执行：
--下载 version_rpt3_25.sql
connect / as sysdba
start version_rpt3_25.sql


--方法一：用SQL_ID （10g及以上）为超过100个版本的所有游标生成报告
set pages 2000 lines 100000
SELECT b.*
  FROM v$sqlarea a, TABLE(version_rpt(a.sql_id)) b
 WHERE loaded_versions >= 100;
 
 
 
 --方法二：使用HASH_VALUE为超过100个版本的所有游标生成报告
 
 set pages 2000 lines 100000
SELECT b.*
  FROM v$sqlarea a, TABLE(version_rpt(NULL, a.hash_value)) b
 WHERE loaded_versions >= 100;

--方法三：为指定的sql_id 的所有游标生成报告

set pages 2000 lines 10000
SELECT * FROM TABLE(version_rpt('&sql_id'));

--报告示例：
Note:438755.1 Version Count Report Version 3.2.5 -- Today's Date 31-dec-24 16:41
RDBMS Version :11.2.0.4.0 Host: node2 Instance 1 : orcl
==================================================================
Addr: 00000000AE4A7928  Hash_Value: 2634394274  SQL_ID 9bz6rqafhbap2
Sharable_Mem: 30100 bytes   Parses: 10   Execs:10
Stmt:
0 select count(*) from lijie1 where name = :a
1

Versions Summary                    
----------------
BIND_LENGTH_UPGRADEABLE :1            

Total Versions:1

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

Plan Hash Value Summary
-----------------------
Plan Hash Value Count
=============== =====
      663811371 2

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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)
======== ======== =============== =============== ======== =============== =================
       2        1              32            4000        1     Yes          (,)

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

48 rows selected.

SQL>