Dedicated vs Shared Connection

We have an application that's vendor canned installed on windows platform. It was on oracle 10g release 1. It has been running fine with about 200 concurrent connections until we applied a vendor provided patch on it. Right after we applied that patch, we started to get phone calls complaining about not being able to login the application. At that point, there were only about 130 connections. It turned out to be a dedicated vs shared connection issue.

The connection was supposed to be "shared", vendor configuration was to run on the default 1521 port, which our policy doesn't allow. So we changed to run on a non-default port. However this led to all supposedly "shared" connection to become "dedicated" connection from day 1 ever since we started to use the application.

So why do we start to see this issue after the patch? Before the patch, SGA was configured as 1200M, with 2GB of memory limit per application on 32-bit windows, that leaves about 2048M – 1200M = 848M for other processes/memories, including server processes that handle connections. The patch increased the SGA to 1656M, which leaves about 2048M – 1565M = 392M for other processes/memories. You see, we only have 392M instead of 848M to handle connections and other things after the patch, which explained why we hit the problem with only about 130 connections.

The issue was resolved by setting up the local_listener parameter. Once we set it up to register the service with the listener on the non-default port as follows, the connections become "shared" and the server was able to handle 200+ connections without any issue.

local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=hostname)(PORT=port number))'