oracle 2个以上游标,Oracle high version count子游标过多问题定位

set pages 2000 lines 100

SELECT * FROM TABLE(version_rpt('7r6rbrdwr4v9h'));

COLUMN_VALUE

----------------------------------------------------------------------------------------------------

Note:438755.1 Version Count   Report Version 3.2.3 -- Today's Date 04-jun-18 10:38

RDBMS Version :11.2.0.4.0 Host:   zp-prd-oracle-11 Instance 1 : BDODS1

==================================================================

Addr: 0000000F2CE29848  Hash_Value: 2037542192  SQL_ID 7r6rbrdwr4v9h

Sharable_Mem: 8883118   bytes   Parses: 17448   Execs:17459

Stmt:

0 select t.name   tname,t.servip,t.calcendeid,t.secondname from kett

1 le.execinfo2 t where   t.name=:1  and t.servip=:2  and decode(t.se

2 condname,:3 ,1, 0) =1

3

Versions Summary

----------------

LANGUAGE_MISMATCH :5

ROLL_INVALID_MISMATCH   :400

BIND_LENGTH_UPGRADEABLE   :11

Total Versions:410

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

cursor_sharing = EXACT

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Plan Hash Value Summary

-----------------------

Plan Hash Value Count

=============== =====

1886329918 411

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Details for LANGUAGE_MISMATCH :

No details available

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Details for   ROLL_INVALID_MISMATCH :

No details available

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

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)

======== ========   =============== =============== ======== =============== =================

411        1              32             128        1       Yes          (,)

411        2              32              32        1       No           (,)

411        3              32              32        1       No           (,)

SUM(DECODE(column,Y, 1, 0) FROM   V$SQL

IS_OBSOLETE IS_BIND_SENSITIVE   IS_BIND_AWARE IS_SHAREABLE

=========== =================   ============= ============

0               411             0          411

####

To further debug Ask Oracle   Support for the appropiate level LLL.

alter session set events

'immediate trace name cursortrace address   2037542192, level LLL';

To turn it off do use address   1, level 2147483648

================================================================

61 rows selected.