利用version_rpt3_24.sql脚本来诊断sql游标多版本

1：脚本下载 可以在metalink id 438755.1 中下载

2：运行方法:
     sqlplus / as sysdba
     @version_rpt3_24.sql
     会创建4个视图

3: 查找多版本大于100个

根据sql_id

set pages 2000 lines 100
SELECT b.*
FROM v$sqlarea a ,
  TABLE(version_rpt(a.sql_id)) b
WHERE loaded_versions >=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;  

根据sql_id:

set pages 2000 lines 100

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

4:具体案例分析
以下是2个例子:
1:optime_mode 优化器模式不同 可以看detail部分

 Details for OPTIMIZER_MODE_MISMATCH :
 1 versions with ALL_ROWS
 1 versions with FIRST_ROWS

SQL> select * from table(version_rpt('7b2twsn8vgfsc'));
 
COLUMN_VALUE
--------------------------------------------------------------------------------
Note:438755.1 Version Count Report Version 3.2.4 -- Today's Date 18-4? -16 11:56
RDBMS Version :11.2.0.4.0 Host: PAYI-CN Instance 1 : orcl
==================================================================
Addr: 000007FF36BEFE58  Hash_Value: 297253644  SQL_ID 7b2twsn8vgfsc
Sharable_Mem: 29074 bytes   Parses: 2   Execs:2
Stmt:
0 select count(*) from test
1
Versions Summary
----------------
OPTIMIZER_MODE_MISMATCH :1
Total Versions:1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
cursor_sharing = EXACT
_cursor_obsolete_threshold = 1024 (See Note:10187168.8)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 
COLUMN_VALUE
--------------------------------------------------------------------------------
Plan Hash Value Summary
-----------------------
Plan Hash Value Count
=============== =====
     1950795681 2
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Details for OPTIMIZER_MODE_MISMATCH :
1 versions with ALL_ROWS
1 versions with FIRST_ROWS
####
To further debug Ask Oracle Support for the appropiate level LLL.
alter session set events
 'immediate trace name cursortrace address 297253644, level LLL';
To turn it off do use address 1, level 2147483648
================================================================
 
37 rows selected
2：绑定变量长度不同,可以看

可以看detail部分 Details for BIND_MISMATCH

SQL> select * from table(version_rpt('3dz5hgntqn0am'));
 
COLUMN_VALUE
--------------------------------------------------------------------------------
Note:438755.1 Version Count Report Version 3.2.4 -- Today's Date 18-4? -16 11:59
RDBMS Version :11.2.0.4.0 Host: PAYI-CN Instance 1 : orcl
==================================================================
Addr: 000007FF46FE1B88  Hash_Value: 862585171  SQL_ID 3dz5hgntqn0am
Sharable_Mem: 45571 bytes   Parses: 6   Execs:41
Stmt:
0 SELECT NVL(MAX(LAST_SAMPLE_DATE), SYSDATE-7) FROM GC$FU_STATISTI
1 CS WHERE FEATURE_ID = :B1
2
Versions Summary
----------------
BIND_MISMATCH :1
Total Versions:1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
cursor_sharing = EXACT
_cursor_obsolete_threshold = 1024 (See Note:10187168.8)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 
COLUMN_VALUE
--------------------------------------------------------------------------------
Plan Hash Value Summary
-----------------------
Plan Hash Value Count
=============== =====
     1959252720 2
~~~~~~~~~~~~~~~~~~~~~~~~~~~
Details for BIND_MISMATCH :
Consolidated details for BIND* columns:
BIND_MISMATCH,USER_BIND_PEEK_MISMATCH,BIND_UACS_DIFF,BIND_LENGTH_UPGRADEABLE,etc
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 (PREC
======== ======== =============== =============== ======== =============== =====
       2        1             128             128        1     No           (,)
SUM(DECODE(column,Y, 1, 0) FROM V$SQL
IS_OBSOLETE IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE
 
COLUMN_VALUE
--------------------------------------------------------------------------------
=========== ================= ============= ============
          0                 2             0            2
####
To further debug Ask Oracle Support for the appropiate level LLL.
alter session set events
 'immediate trace name cursortrace address 862585171, level LLL';
To turn it off do use address 1, level 2147483648
================================================================
 
49 rows selected
 
SQL>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7199859/viewspace-2083252/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7199859/viewspace-2083252/