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:

LARGE_POOL_SIZE=600000000
MTS_MAX_DISPATCHERS=5
MTS_MAX_SERVERS=50
MTS_SERVERS=5
MTS_DISPATCHERS="(ADDRESS=(PROTOCOL=tcp)(HOST=stool.com))(DISPATCHERS 3)"
MTS_DISPATCHERS="ipc, 1"

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
--**************************************************************
--   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 ********************************************
select
   sum(value) all_sess_mem
from
   v$sesstat   s,
   v$statname  n
where
   s.statistic# = n.statistic#
and
   n.name = 'session uga memory max';

prompt ********************************************
prompt Session memory detail
prompt ********************************************
select
   substr(b.username,1,10) username,
   substr(b.program,1,30)  program,
   value sess_mem
from
   v$session   b,
   v$sesstat   s,
   v$statname  n
where
   b.sid = s.sid
and
   s.statistic# = n.statistic#
and
   n.name = 'session uga memory'
and
   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 ********************************************
select
   name,
   status,
   idle,
   busy,
   (busy/(busy+idle))*100 "Time Busy"
from
   v$dispatcher;

prompt ********************************************
prompt Existing shared server processes
prompt ********************************************
select
   count(*) "Shared Server Processes"
from
   v$shared_server
where
   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"
FROM
   v$queue      q,
   v$dispatcher d
WHERE
   q.type = 'DISPATCHER'
   AND
   q.paddr = d.paddr
GROUP BY network;

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

prompt ********************************************
prompt All statistics from pq_sysstat
prompt ********************************************
select
   statistic,to_char(value) value
from
   sys.v_$pq_sysstat;

prompt ********************************************
prompt Percent busy for each MTS dispatcher
prompt ********************************************
select
   network
   "Protocol",to_char((sum(busy)/(sum(busy)+sum(idle))*100),'99.99999')
%Busy"
from
   v$dispatcher
group by
   network;

prompt ********************************************
prompt Dispatcher and queue details with average wait time
prompt ********************************************
select
   network "Protocol",
   decode(sum(totalq), 0, 'No Responses',
   to_char(sum(wait)/sum(totalq),'99.99999')||' hundreths of seconds')
   "AWT/Response"
from
   v$queue      q,
   v$dispatcher d
where
   q.type='DISPATCHER'
and
   q.paddr=d.paddr
group by
   network;

prompt ********************************************
prompt Sum of UGA Memory
prompt ********************************************
select 
   s.type,
   s.server,
   s.status,
   sum(st.value)   uga_mem
from
   v$session   s,
   v$sesstat   st,
   v$statname  sn
where
   s.sid = st.sid
and 
   st.statistic# = sn.statistic#
and 
   sn.name = 'session uga memory'
group by
   type,
   server,
   status;

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
********************************************

ALL_SESS_MEM
------------
   5,588,088

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        ?  @donald.janet.com (TNS V       55,344
READER        ?  @donald.janet.com (TNS V       55,344
READER        ?  @donald.janet.com (TNS V       40,088
READER        ?  @donald.janet.com (TNS V       39,816
READER        ?  @donald.janet.com (TNS V       39,816
READER        ?  @donald.janet.com (TNS V       29,720
READER        ?  @donald.janet.com (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.

DISPATCHER USAGE ...
(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
-----------------------
                      6

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
********************************************

Protocol                                                                       
----------------------------------------------------------Average Wait Time per
Response                                                  

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

(ADDRESS=(PROTOCOL=tcp)(HOST=diogenes)(PORT=59602))
.028715926624378535916338076461512086405 hundredths of seconds

(ADDRESS=(PROTOCOL=tcp)(HOST=diogenes)(PORT=59604))
.006032482598607888631090487238979118329 hundredths of seconds

(ADDRESS=(PROTOCOL=tcp)(HOST=diogenes)(PORT=59605))
.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
--------------------------------------------------    ---------
 (ADDRESS=(PROTOCOL=ipc)(KEY=#24326.1))
.00022

(ADDRESS=(PROTOCOL=tcp)(HOST=diogenes)(PORT=59602))
.00115

(ADDRESS=(PROTOCOL=tcp)(HOST=diogenes)(PORT=59604))
.00201

(ADDRESS=(PROTOCOL=tcp)(HOST=diogenes)(PORT=59605))   
.00308

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
********************************************

Protocol
----------------------------------------------------------------------
AWT/Response
---------------------------------------------------------------------- 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
********************************************

TYPE       SERVER    STATUS      UGA_MEM
---------- --------- -------- ----------
BACKGROUND DEDICATED ACTIVE       217056
USER       DEDICATED ACTIVE       147968
USER       DEDICATED INACTIVE      59440
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.