Many Oracle DBAs today are faced with the every growing numberof connections to their databases. When using the traditional methodof connecting to the database (dedicated server) each client processis given a dedicated server process that runs on the database server.This server process is sometimes referred to as a \"shadow process\".This server process takes memory away from the system and as the numberof connections increase, so does the demand for memory. Not only ismemory in demand for with these server processes, but so are CPUrequirements.If you have many users connecting and disconnecting from the databaseserver, a good amount of CPU will be required in building anddestroyingthese dedicated server processes. Oracle provides a solution thatanswersthe above two issues called Multi-Threaded Server. This articleprovidessome insight into setting up Multi-Threaded Server (MTS) as well asseveral scripts that can be used to monitor your MTS environment.
Think about a system in which only 4 client processes need accessto the database. With dedicated server, each client process requiresone dedicated server processes. If each server process consumes 8megabytes of memory, you will need 32 megabytes to support thisscenario.Now consider 100 dedicated connections. In this scenario, you wouldrequire 800 megabytes of memory! Using MTS, you can see in Figure 1 that these same 4clientconnections can be supported by only 2 shared server processes.
Another thing to consider is OLTP environment where users arespending most of their time reading and editing data on their screensand very little time actively executing SQL statements against thedatabase. In this scenario, many of those dedicated server processesare sitting idle and needlessly consuming memory. Because MTS allowsthe sharing of the server process between many client processes, theDBA can now make more efficient use of the server processes.
Lastly, MTS can improve with environments where clients areconstantly connecting and disconnecting from the database. Since sharedserver processes are not tied directly to a client process, they arenot built and destroyed each time a client connects or disconnects.Oracle will maintain an active \"pool\" of shared server processes. Newshared server processes are only created when demand exceeds supply.On the same note, shared server processes are only destroyed whendemanddrops off and those extra shared server processes are no longer needed.
Batch Jobs vs. Quick transactions: The use of MTS ispredicated on the assumption that each client will use only a smallfraction of its connect time to perform any work against the database.The more active your clients are, the greater the number of sharedserver processes you will need to respond to their requests in a timelymanner. You may get to a point to where MTS ceases to make sense atall. Batch jobs or clients that extract large amounts of data shouldnot connect to the database through MTS.
User Session-Specific Data (UGA)Every connection to the Oracle database has a \"session-specific\"memory associated with it. This memory is referred to as the UserGlobalArea (UGA) and is used to hold the values of PL/SQL variables, thevaluesof bind variables and other items specific to a session. The UGA alsocontains that part of the sort area specified by theSORT_AREA_RETAINED_SIZEinitialization parameter.
Well, this depends on your environment. If you are supporting alarge number of client connections and where those connections aremostly inactive, then MTS would be ideal here. MTS would not be a goodsetup if your environment does not involve much idle time with yourclient connections. (i.e. batch jobs). Keep in mind that you setup anenvironment that allows for both MTS and dedicate server processes.Setup user connections who only sporadically accessing the databaseusing MTS, while batch jobs and other data intensive connections can bemade using dedicated server connections. Lastly, remember that fromthe client's perspective, MTS does not enhance their performance, butrather reduces the CPU and memory overhead associated with supportingmany client (mostly idle) connections.
NOTE: Do not attemptto perform the above test on a production database when users aretrying to connect. In fact, use a \"testing\" database to ensure that youwill not create connection problems for people in your productionenvironment.
The session uga memory value represents the amount ofmemory that a user is currently using for session-specific information.The other value, session uga memory max represents the maximumamount of session-specific memory that has been used at any one timeduring the user's current session. Lets say that the \"JHUNTER\" user(above) was representative of many of the other users in the databasethat are going to connect via MTS, and you expected 500 suchconnectionsconcurrently, simply multiply both values (session uga memoryand session uga memory max) by 500 to come up with an estimatedrange of 27,182,000 to 28,538,000 bytes. This will be the amount ofadditional memory that you will need to allocate in the SGA for useby these 500 MTS sessions. Remember that MTS uses the shared pool forsession memory, but Oracle recommends the use of the large pool. Byusing the large pool, you avoid fragmenting the shared pool. Given theabove example, I would add the following to my init.ora file:
The above allocates a large pool of 32 MB in size. This is allyou really have to do in order to have Oracle use it for MTS sessionmemory.So, if you want to determine the average amount of memory that eachconnection allocates, you will have to measure the maximum amount ofUGA memory used at a given moment and divide that amount by the numberof current user sessions. The maximum amount of UGA memory allocatedat a given moment can be determined with the following query: SELECT SUM (value) \"MTS maxmemory allocation\" FROM v$sesstat ss,v$statname st WHERE st.name in('session uga memory max', 'session uga memory') AND ss.statistic# = st.statistic#; The number of MTS connections currently in existence can be measuredwith the following query SELECT COUNT (*) FROM v$session WHERE server !='DEDICATED'; The server column in v$session will have a value of either SHARED orNONE if a user is connected through MTS, or DEDICATED if the user isconnected through a dedicated server process. Note that the backgroundprocesses (smon, pmon, etc.) evidently always use dedicated connections.By running the above queries several times, I was able to determinethat the amount of UGA memory used by MTS connections averaged between160 Kb and 185 Kb per connection - less than 4% of the amountof memory used by dedicated connections. These figures correlate veryclosely to figures that I have found on MetaLink; the postings thatI have seen on MetaLink state that MTS connections usually requirebetween150 Kb and 200 Kb each.I then multiplied 185 Kb by 700 connections, and came up with a figureof about 130 Mb as an estimate of how much memory I would need toallocate for the large pool. However, in order to allow lots of paddingfor unanticipated circumstances, I decided to size the large pool aboutthree times larger than I thought I would need, or 400 Mb.This database has now been running with MTS for over six weeks, and theMTS connection memory requirements have remained very consistent. Ihave never seen the maximum UGA memory allocation exceed 140 Mb for 700concurrent connections.
Setting up an MTS environment can be as simple as adding one ormore MTS_DISPATCHERS parameters to your instance parameter file andthen bouncing the database. It's likely however, that you're going towant to tune some of the other parameters above.
Another attribute that deserves attention for MTS_DISPATCHERSis the LISTENER attribute. The LISTENER attribute allows youto specify the listener with which the dispatcher should register.By default, the dispatcher will register with the listener that ismonitoring port 1521 on the local database server. Using the LISTENERattribute though, you can override this and have your dispatcherregisterwith a listener assigned to either a different port or with a listenerrunning on another database server. The following is an example ofusing the LISTENER attribute:
The database administrator may find it necessary to performadministrative functions to adjust the running MTS system. Suchfunctionsinclude monitoring the efficiency of dispatchers and shared servers;and increasing or decreasing the number of dispatchers and sharedservers.Although the RDBMS starts/stops additional dispatchers and sharedservers,such administrative functions may be necessary. You can monitor theutilization of dispatchers and shared servers by using SQL*DBA or byquerying from V$DISPATCHER and V$SHARED_SERVER. To examine theefficiencyof the dispatchers: 1e1e36bf2d