A customer of mine run out of memory due to much server processes of dedicated connected client sessions. As an alternative I tried to explain the options between DEDICATED and SHARED SERVER concepts as an initial attempt/workaround for the client software problems. Looking around on the internet for pictures and or newbie documentation, I found the following nugget on YouTube that explains the basics and how to address them.

Due to the fact that the XMLDB XDB Repository Architecture, which makes it possible to connect to the database via HTTP(s)/WebDAV en FTP, also makes use of the Oracle Shared Server Architecture (or referenced in the old days as MTS – Multi Threaded Server), this information is still very valid concerning those XDB Repository HTTP(s), WebDAV and FTP connections and/or APEX connections based on the PL/SQL Gateway (which is a part of the XDB Protocol Server functionality).

For my single instance databases, I configure a value of shared_servers=5, and most of the time that should be enough not to cause connection “hiccups” (clients “freeze” for a short while), and an appropriate value for LARGE_POOL_SIZE. Most of the time in a standard DBCA created database, the DISPATCHER parameter is already configured but not really active, because not a lot of people are aware of the XMLDB Repository access architecture and therefore don’t connect via this method. Also due to an enhancement request I once made during 11.1, HTTP(s)/WebDAV/FTP ports are not enabled via DBMS_XDB.SETHTTPPORT() and DBMS_XDB.SETFTPPORT(), that is, have both a value of 0.

On a production database you could decide to even get rid of the default setup by disabling DISPATCHER and SHARED_SERVER processes, if need be. As said, most of the times the DISPATCHER is enabled and though it doesn’t do any work, it will eat some of your CPU and memory resources. On a 10.x environment this will even be more likely. As I mentioned, my enhancement was honored from 11.1 and upwards, so…

The video talks about configuring DISPATCHER, SHARED_SERVER, LARGE_POOL_SIZE, etc. I never really tweak parameters before it is really needed. So one of the small pieces of advice I would give you, is that you should realize that, besides setting values to be able peak connection strains on the database, you should also realize that LARGE_POOL_SIZE (when set) will also automatically be used for other mechanisms like RMAN backup optimization. So while trying to figure out stuff like “sessions”, “circuit” etc value, keep in mind that this static LARGE_POOL_SIZE parameter, during peak moments, has to take into account stuff like RMAN backup windows/run times on that database.

HTH

Marco