Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 









Oracle UNIX Administration Interacting with the MTS Server

Oracle UNIX/Linux Tips by Burleson Consulting

UNIX interaction with the Multi-threaded Server

As you may know, the Multi-threaded server (MTS) was developed in Oracle7 to provide an alternative to a dedicated server connection for Oracle processes.  Instead of each connection spawning a UNIX PID and a UNIX RAM region for PGA memory, the MTS allows for us to share pre-spawned Oracle connections. These pre-spawned connections share RAM memory by using the Oracle large pool.  By sharing connections and RAM memory, Oracle connections can happen faster and with less overall resource consumption on the server.

When using the MTS, Oracle allocates memory in a shared region called the User Global Area, or UGA.  If you have the large_pool_size defined in you init.ora file, then the UGA memory will be allocated from the large pool.  If you do not define a large pool, then the UGA memory will be allocated from the Oracle shared pool.  Oracle always recommends that you allocate a large pool if you are using the MTS.

Prerequisites for using the MTS

In practice, the MTS is not for every Oracle application.  In some cases, using the MTS will induce problems and cause the application to run slower than dedicated connections.  There are several criteria that must be met when considering turning on the MTS.

* High User Volume - Oracle only recommends using the MTS for database that experience more than 300 concurrent connections, since the MTS does not always work well for low system loads.

* High think time ? A database has a high think time when they spend more time manipulating data than retrieving data. The MTS is not appropriate for database where the database simply retrieves and re-formats data. Using the MTS in this type of environment can cause performance degradation due to the overhead involved in switches and the amount of time that requests may wait in the queue before a shared server becomes available.

* Small SQL result sets ? The MTS is designed to manage SQL queries that retrieve small result sets, such as a typical OLTP system.  If SQL retrieves a very large result with thousands of rows, then that session?s MTS dispatcher can become overwhelmed with this single request.  Unfortunately, when using the MTS, a ?hog? on a dispatcher can adversely effect the response time of other sessions that are connected to that dispatcher.

We also need to note that the Oracle DBA has a great deal of control over the number and configuration of the MTS connections and the size of the large pool.  Here is a quick summary of the MTX parameters that are defined in the init.ora file:


Here we see that we have defined 600 megabytes for use by the large pool.  The large pool is used as shared memory for MTS connections, primarily for sorting.  Hence, the amount of the large_pool used by each MTS connection is directly related to the sort_area_size parameter.

Here we have defined that the MTS will start with three dispatcher processes and allocated dispatchers until five dispatcher processes exist.

--   mts.sql
--   ? 2001 by Donald K. Burleson
--   No part of this SQL script may be copied. Sold or distributed
--   without the express consent of Donald K. Burleson
set pages 999;

spool mts.lst

column all_sess_mem format 999,999,999;
column sess_mem     format 999,999,999;
column username     format a10
column program      format a30

prompt ********************************************
prompt Total of all session RAM memory usage 
prompt ********************************************
   sum(value) all_sess_mem
   v$sesstat   s,
   v$statname  n
   s.statistic# = n.statistic#
and = 'session uga memory max';

prompt ********************************************
prompt Session memory detail
prompt ********************************************
   substr(b.username,1,10) username,
   substr(b.program,1,30)  program,
   value sess_mem
   v$session   b,
   v$sesstat   s,
   v$statname  n
   b.sid = s.sid
   s.statistic# = n.statistic#
and = 'session uga memory'
   s.sid  in
      (select sid from v$session)
order by 3 desc

prompt ********************************************
prompt  Dispatcher Detail Usage
prompt ********************************************
prompt (If Time Busy > 50, then change MTS_MAX_DISPATCHERS in init.ora)
column "Time Busy" format 999,999.999
column busy        format 999,999,999
column idle        format 999,999,999

prompt ********************************************
prompt Time busy for each MTS dispatcher
prompt ********************************************
   (busy/(busy+idle))*100 "Time Busy"

prompt ********************************************
prompt Existing shared server processes
prompt ********************************************
   count(*) "Shared Server Processes"
   status = 'QUIT';

prompt ********************************************
prompt Average wait times for requests
prompt ********************************************
SELECT network     "Protocol",
       DECODE( SUM(totalq), 0, 'No Responses',
       SUM(wait)/SUM(totalq) || ' hundredths of seconds')
      "Average Wait Time per Response"
   v$queue      q,
   v$dispatcher d
   q.type = 'DISPATCHER'
   q.paddr = d.paddr
GROUP BY network;

prompt ********************************************
prompt All average wait times for common requests
prompt ********************************************
   DECODE( totalq, 0, 'No Requests',
   wait/totalq || ' hundredths of seconds')
   "Average Wait Time Per Requests"
   type = 'COMMON';

prompt ********************************************
prompt All statistics from pq_sysstat
prompt ********************************************
   statistic,to_char(value) value

prompt ********************************************
prompt Percent busy for each MTS dispatcher
prompt ********************************************
group by

prompt ********************************************
prompt Dispatcher and queue details with average wait time
prompt ********************************************
   network "Protocol",
   decode(sum(totalq), 0, 'No Responses',
   to_char(sum(wait)/sum(totalq),'99.99999')||' hundreths of seconds')
   v$queue      q,
   v$dispatcher d
group by

prompt ********************************************
prompt Sum of UGA Memory
prompt ********************************************
   sum(st.value)   uga_mem
   v$session   s,
   v$sesstat   st,
   v$statname  sn
   s.sid = st.sid
   st.statistic# = sn.statistic#
and = 'session uga memory'
group by

spool off;

This script combines a great deal of important information about the internals of the Oracle dispatchers and UGA memory. Here is a sample of the output from this script.  Let?s look at each section separately.

Total of all RAM session memory


Here we see the total for all session RAM memory within Oracle.  This metric is especially useful when you need to know the total RAM memory demands of individual sessions in your Oracle instance.

This is the session UGA memory max statistic, and it is important to understand that this value is not the high-water mark for the database since startup time.  Rather, it is the sum of all UGA memory that is currently being used at the time the query was executed.

Session memory detail

USERNAME   PROGRAM                            SESS_MEM
---------- ------------------------------ ------------
OPS$ORACLE sqlplus@diogenes (TNS V1-V3)        124,832
           oracle@diogenes (SMON)               63,984
           oracle@diogenes (RECO)               59,952
READER        ? (TNS V       55,344
READER        ? (TNS V       55,344
READER        ? (TNS V       40,088
READER        ? (TNS V       39,816
READER        ? (TNS V       39,816
READER        ? (TNS V       29,720
READER        ? (TNS V       29,720
           oracle@diogenes (PMON)               23,728
           oracle@diogenes (LGWR)               23,728
           oracle@diogenes (CKPT)               23,728
           oracle@diogenes (DBW0)               21,936

Below we see a sample output from the dispatcher usage section of the script.

(If Time Busy > 50, then change MTS_MAX_DISPATCHERS in init.ora)
Time busy for each MTS dispatcher

NAME STATUS                   IDLE         BUSY    Time Busy
---- ---------------- ------------ ------------ ------------
D000 WAIT               47,708,571          551         .001
D001 WAIT               47,708,153          960         .002
D002 WAIT               47,707,636        1,469         .003
D003 WAIT               47,708,990          105         .000

Existing shared server processes

Shared Server Processes

Above, we see a summary of the usage for the Oracle dispatcher processes. Here we see a summary of activity for each dispatcher and time busy time for each dispatcher.  We can also see the total number of shared server processes.

Average wait times for requests

----------------------------------------------------------Average Wait Time per

---------------------------------------------------------------------- (ADDRESS=(PROTOCOL=ipc)(KEY=#24326.1))
No Responses

.028715926624378535916338076461512086405 hundredths of seconds

.006032482598607888631090487238979118329 hundredths of seconds

.145350552668013345740938287296579586464 hundredths of seconds

Here we see the average latency for every dispatcher. This listing is good for determining the load balancing and ensuring that the response time for each dispatcher is acceptable.

All average wait times for common requests

Average Wait Time Per Requests
----------------------------------------------------------------------.007257240204429301533219761499148211244 hundredths of seconds

Above we calculate the system-wide total for MTS requests response time.  This information is great when determining when additional dispatchers are required.

All statistics from v$pq_sysstat

STATISTIC                      VALUE                                           
------------------------------ ----------------------------------------
Servers Busy                   3
Servers Idle                   1
Servers Highwater              4
Server Sessions              233
Servers Started                0
Servers Shutdown               0
Servers Cleaned Up             0
Queries Initiated              0
DML Initiated                  0
DFO Trees                      0
Sessions Active                0
Local Msgs Sent             2615
Distr Msgs Sent                0
Local Msgs Recv'd            743
Distr Msgs Recv'd              0

The above listing show details of all of the statistics in the v$pq_sysstat structure. This listing can be useful when you need to monitor MTS totals.

Percent busy for each MTS dispatcher

Protocol                                              %Busy
--------------------------------------------------    ---------




The above report shows the relative load balancing for each MTS dispatcher.  Since the assignments are supposed to be directed randomly to each dispatcher, the number for each dispatcher should be roughly the same.

Dispatcher and queue details with average wait time

---------------------------------------------------------------------- ADDRESS=(PROTOCOL=ipc)(KEY=#24326.1))                No Responses

(ADDRESS=(PROTOCOL=tcp)(HOST=diogenes)(PORT=59602))   .02872 hund/secs

(ADDRESS=(PROTOCOL=tcp)(HOST=diogenes)(PORT=59604))   .00603 hund/secs

(ADDRESS=(PROTOCOL=tcp)(HOST=diogenes)(PORT=59605))   .14535 hund/secs

Above we see the average wait times for each dispatcher.  Again, the loads to each dispatcher are balanced by Oracle, and there should not be significant differences between the average wait times, unless a specific task hogging a dispatcher.

Sum of UGA Memory

---------- --------- -------- ----------
USER       DEDICATED ACTIVE       147968
USER       PSEUDO    INACTIVE    5126256

Here we see both background processes and MTS connections and the total amount of UGA memory used by these connections. It is important to note that Oracle will create new dispatcher process as the load on the system increases, and this interaction with UNIX can be measured.

Of course, each new release of Oracle offers new techniques for managing RAM memory management in Oracle.  Starting in Oracle9i, we see a whole new approach to RAM memory management and the RAM memory shifts from the UGA to the PGA region.  Let?s take a closer look at these new techniques.


If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.


Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational