Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

UNIX Interaction With the Multi-
Threaded Server


Oracle Tips by Burleson
 

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 you to share prespawned Oracle connections. These prespawned 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. The MTS is sometimes used in Oracle Parallel Server (OPS) environments to give clients transparent failover capabilities when Net8 is set up for transparent application failover (TAF).

When using the MTS, Oracle allocates memory in a shared region called the User Global Area (UGA). If you have the large_pool_size defined in your Oracle file, the UGA memory will be allocated from the large pool. If you do not define a large pool, 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 databases 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 it spends more time manipulating data than retrieving data. The MTS is not appropriate if the database simply retrieves and reformats 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, that session’s MTS dispatcher can become overwhelmed with this single request. Unfortunately, when using the MTS, a “hog” on a dispatcher can adversely affect the response time of other sessions that are connected to that dispatcher.

You 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 MTS parameters:

LARGE_POOL_SIZE=600000000

MAX_DISPATCHERS=5

MAX_SERVERS=50

SHARED_SERVERS=5

DISPATCHERS="(ADDRESS=(PROTOCOL=tcp)(HOST=stool.com))(DISPATCHERS 3)"

DISPATCHERS="ipc, 1"

You have defined close to 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 you 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 Oracle)
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 you 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

The following is a sample output from the dispatcher usage section of the script:

DISPATCHER USAGE ...
(If Time Busy > 50, then change MTS_MAX_DISPATCHERS in Oracle)
********************************************
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

This is a summary of the usage for the Oracle dispatcher processes. Here you see a summary of activity for each dispatcher and time busy for each dispatcher. You 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 you 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                 

 

Here you 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 preceding 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 preceding 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     
    

Here you 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 is 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 you 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 a 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 in Oracle. Starting in Oracle, you 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.

This is an excerpt from "Oracle High Performance tuning with STATSPACK" by Oracle Press.


If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & 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 performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter