利用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>