En un caso similar se tuvo que hacer trace para identificar estos eventos de porque no hace "cache" de los cursores. Se tuvo que abrir un caso con Oracle para que analicen los trace
In 10G it is possible to use CURSORTRACE to aid the investigation of why cursors are not being shared. This event should only be used under the guidance of support and the resultant trace file is undocumented. To get the trace for a particular SQL statement you first of all need to get the hash_value (See the above select from v$sqlarea). You then set the trace on using:-
alter system set events
‘immediate trace name cursortrace level 577, address hash_value’;
(level 578/580 can be used for high level tracing (577=level 1, 578=level 2, 580=level 3)
This will write a trace file to user_dump_dest each time we try to reuse the cursor.
To turn off tracing use:-
alter system set events
‘immediate trace name cursortrace level 2147483648, address 1’;
Please note: BUG5555371 exists in 10.2 (fixed in 10.2.0.4) where cursor trace cannot fully be turned off and single line entries will still be made to the trace
file as a result. The w/a is to restart the instance. How invasive this BUG is depends on the executions of the cursor (and the size of the resultant trace file additions)