why cursors are not shared <a href=/sql/cursorshare.sql>sql</a> <a href=/sql/cursorshare.txt>output</a> 
<PRE>

From Richard Powell
all "N" in v$sql_shared_cursor is common if you have cursor_sharing=similar when literal replaced binds are marked as unsafe. 
eg: Due to histogram data on equality predicates or CBO decisions made using literal replaced range predicates.
Note 377847.1 has some extra comments on this sort of scenario.





From Riyaj Shamsudeen:

Cursortrace event has been useful in some cases to see why cursors are not shared. Not always an helpful event, but worth a try.
 
-- 1444372379 is hash value
alter session set events 'immediate trace name cursortrace level 2147483648,  address 1444372379';
 
HASH_VALUE ADDRESS  SQL_TEXT
---------- -------- --------------------------------------------------------------------------------
1444372479 227FB0D8 select /*+riyaj*/ n1 from test_cs where n2=:"SYS_B_0"
1444372479 227FB0D8 select /*+riyaj*/ n1 from test_cs where n2=:"SYS_B_0"
 
But, if histograms & cursor_sharing=smilar is the reason, there isn.t much information in the trace file though.
 
CUR#3 XSC 0DEA034C CHILD#-1 CI 00000000 CTX 00000000
 PARSING SQLTEXT=select /*+riyaj*/ n1 from test_cs where n2=55
 SQLHASH=3c934712
 Checking for already pinned child. fbcflg 108
 No valid child pinned
 Parent 224E076C(22796464) ready for search
 kksSearchChildList outside while loop
 kksSearchChildList: no suitable child found ß Not much usefulness here..
 Creating new child object #0
 kksLoadChild: reload 0 child_reload 0
 kksLoadChild: reload 0 child_reload 0
 Child creation successful 2246F564 2271045C 0
 Downgrading child pin to share
 Checking for already pinned child. fbcflg 102
 Cursor about get executed
 Checking for already pinned child. fbcflg 1
 Cursor#3 mapped
 Cursor unmapped