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