Oracle Transparent Gateway

From: <ethibert_at_comcast.net>
Date: 13 Dec 2006 10:40:39 -0800
Message-ID: <1166035239.758967.110100@80g2000cwy.googlegroups.com>

We are currently implementing Oracle Transparent Gateway to connect to MS SQL Server 2000.

We see the Gateway making an extremely large number of socket connections & SQL logins, over 250 per second.

For every record processed in Oracle, there is a new SQL SPID created for each lookup needed for the corresponding row. (ie For every record we process in Oracle we need to retrieve from SQL Server a Country Code, Currency Type, Minor Issue Type, Asset Class, GL Group, Legal Status...) Each of these lookups is creating its own SQL SPID.

Initially the Gateway server was receiving a "Not Enough Endpoints" message from SQL server. I removed this bottleneck by putting a TcpTimedWaitDelay of 5 on the Gateway to remove the unused TCP sockets. This tranferred to load to SQL Server, which is now processing Hundreds of logins per second.

Is there a way to configure the Oracle Gateway to not use so many connections, or to reuse connections? Received on Wed Dec 13 2006 - 12:40:39 CST