检查游标不能共享的具体原因

start version_rpt3_23.sql

SQL> select * from table(version_rpt('50u4uqg9vjk78'));


COLUMN_VALUE
--------------------------------------------------------------------------------
Note:438755.1 Version Count Report Version 3.2.3 -- Today's Date 29-may-14 12:02
RDBMS Version :11.2.0.1.0 Host: standby Instance 1 : kldb
==================================================================
Addr: 00000000AC2FCBF8  Hash_Value: 3552102632  SQL_ID 50u4uqg9vjk78
Sharable_Mem: 7350487 bytes   Parses: 120   Execs:120
Stmt:
0 SELECT p.FReportID, p.fpermission, r.FMenuID FROM T_EXT_Permissi
1 on P INNER JOIN T_BAS_DefineReport R ON p.FReportID = r.fid WHER
2 E (p.FUserID = :1 AND p.FOrgID = :2) UNION ALL SELECT p.FReportI
3 D, p.fpermission, r.FMenuID FROM T_EXT_Permission P INNER JOIN T
4 _BAS_DefineReport R ON p.FReportID = r.fid WHERE (p.FUserID = :3


COLUMN_VALUE
--------------------------------------------------------------------------------
5  AND (p.FOrgID IS NULL)) UNION ALL SELECT p.FReportID, p.fpermis
6 sion, r.FMenuID FROM T_EXT_Permission P INNER JOIN T_BAS_DefineR
7 eport R ON p.FReportID = r.fid INNER JOIN t_pm_userroleorg URO O
8 N p.froleid = uro.froleid WHERE (uro.FUserID = :4 AND uro.FOrgID
9  = :5)
10


Versions Summary
----------------
OPTIMIZER_MISMATCH :119




COLUMN_VALUE
--------------------------------------------------------------------------------
Total Versions:119


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
cursor_sharing = EXACT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


Plan Hash Value Summary
-----------------------
Plan Hash Value Count
=============== =====
     4238305389 120


COLUMN_VALUE
--------------------------------------------------------------------------------


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


119 versions with optimizer_features_hinted = 11.2.0.1
119 versions with _pga_max_size = 1155880 KB
####
To further debug Ask Oracle Support for the appropiate level LLL.
alter session set events
 'immediate trace name cursortrace address 3552102632, level LLL';
To turn it off do use address 1, level 2147483648


COLUMN_VALUE
--------------------------------------------------------------------------------
================================================================


45 rows selected.

alter system set optimizer_features_enable= 10.2.0.5 scope=spfile

alter system set optimizer_features_enable= 10.2.0.5 scope=memory;