Having issue with an oracle stored procedure that fetches 5k array size to an down stream application using oracle client interface. It is creating phantom arrays and keeps sending arrays that do not exist to begin with and congesting the connections. This happened when we upgraded from oracle 22.214.171.124 to 126.96.36.199 ! The stored procedure uses stored memory for the variables since it is much faster. Any thoughts or experienced something like that before? if so what was done to fix it ?
Oracle provided a fix. That fixed the OCI connection crash by running this alter statement.
alter system set events = '10946 trace name context forever, level 2097152';</SPAN>
It fixed the crash issue. However, arrays being fetched are not clearing now and they keep bouncing back and forth between the stored procedure in form of response and an ack from the oci connection program. When we run the tcpdump to check the packets. The last array fetched is stuck in memory eventhu it was acknowledged ! Somehow in oracle 188.8.131.52 is not compatible with the oci connections when it comes to arrays. The alter statement above is suppose to force the pl/sql to behave as 184.108.40.206 which is working fine in 220.127.116.11 Any suggestions what else we can do to get around this issue of memory not clearing in oracle dbms 18.104.22.168?
Last edited by mrn6430; 10-16-2015 at 12:33 AM..
Reason: Fix CODE tag.