Oracle UNIX epc_disabled Environment Variable
Oracle UNIX/Linux Tips by Burleson Consulting
The epc_disabled Environment Variable
Starting in Oracle 7.3.2, the Oracle Server
Tracing (otrace) is enabled by default. A practical implication of
this is that every connection and every request sent over Net8 is
logged in the Oracle trace files process.dat and regid.dat. After
long-term use of the database, these trace files can become
enormous, slowing down the connection time dramatically.
The solution is to implement a crontab job
to periodically remove the trace files, or to disable the otrace
facility. It is highly recommended that the DBA disable the otrace
facility unless they require it for session tracing. Here are the
1. Shut down the databases and listeners.
2. Remove the *.dat files from your $ORACLE_HOME/otrace/admin
3. Re-create the dat files with the UNIX
4. Specify 'epc_disabled=TRUE' in the
runtime environment of the UNIX Oracle .profile, .login, or .cshrc
login file. This will disable the otrace facility.
5. Modify the listener.ora file to specify
epc_disabled=TRUE in the sid_desc for each database.
6. Restart the database and listeners.
7. Run the otrccref command from $ORACLE_HOME/bin.
Other Oracle Features that Affect Network
Now that we have covered the basic Oracle
parameters that govern network traffic, let?s look at some
techniques that are used within the Oracle environment that can be
used to manage network activity. In general, there are several
* Using array fetches
* Using the multithreaded server (MTS)
* Using connection pooling
* Using ODBC
* Using Oracle replication
Using Array Fetches to Improve Network
In databases that are using PL/SQL stored
procedures and functions or a language such as C that supports array
fetches, you can reduce Oracle network calls by using bulk array
fetches. For example, instead of fetching one row at a time from a
cursor, it is more efficient to fetch 10 rows with a single network
Many Oracle tools such as SQL*Plus,
SQL*Forms and the language precompilers allow for the use of the
arraysize parameter. The arraysize parameter allows multiple rows to
be returned in a single databases access. This has the effect on the
network of making fewer TCP/IP packets, each with more data inside
each packet. This technique can often greatly aid the performance of
long-running client/server tasks.
Oracle8i also offers enhanced bulk fetching
through the Oracle Call Interface (OCI). The programming aspects of
array fetching are beyond the scope of this text, but you can get
more information on array fetch techniques in the Oracle-supplied
documentation and on Oracle?s MOSC Web site.
Using the Multihreaded Server
When you database server experiences a large
volume of incoming connections, the overhead of spawning a dedicated
process to service each request can cause measurable overhead on the
server. This is because the default listener process ?bequeaths? the
incoming connection, creating a process (PID) on the Oracle server
and directing this process to establish the connection to Oracle.
To reduce this overhead, the MTS can be
implemented to allow new connections to attach to prespawned shadow
processes. Note that Oracle does not recommend using the MTS unless
you average more than 300 connections on the server.
The basic premise of the MTS is that Oracle
creates dispatcher processes, each with a set of preestablished
connections into the Oracle database. Each dispatcher owns a set of
prespawned connections into the database. By preestablishing the
connections to Oracle, server resources are minimized, and RAM
storage is also minimized because each session will not allocate a
personal sort_area_size in the Program Global Area (PGA). Instead,
all dispatchers share the same User Global Area (UGA), thereby
reducing memory demands on the database server.
One of the problems with the dedicated
listener is that each incoming transaction is spawned by the
listener as a separate operating system task. With the MTS, all
communications to a database are handled through a single dispatcher
instead of separate UNIX process IDs (PIDs) on each database. If you
have constant connection loads of 300 users or more, using the MTS
translates into faster performance for most online tasks. The only
real downside to using the MTS is that the DBA cannot directly
observe Oracle connections using the UNIX ps-ef|grep oracle command.
However, be aware that the MTS is not a
panacea, especially at times when you want to invoke a dedicated
process for your program. For Pro*C programs and I/O-intensive
SQL*Forms applications, or any batch processes that have little idle
time, you may derive better performance using a dedicated listener
process. For shops that segregate task into online and batch modes,
the DBA sometimes create separate listeners?one with the MTS and
another for dedicated connections.
In general, the MTS offers benefits such as
reduced memory use, fewer processes per user, and automatic load
balancing. However, the DBA must be careful to set the proper number
of dispatcher processes, and the proper number of servers within
each dispatcher. Also, because the MTS uses the shared_pool for
process sorting, the DBA will also see increased demands on the
Connections using the MTS will place the UGA
inside the Oracle SGA. To hold the UGA storage for MTS connections,
Oracle has provided the large_pool init.ora parameter. The LARGE
pool is an area of the SGA similar to the SHARED pool, but with
restrictions on its usage such that only certain types and sizes of
memory can be allocated in this pool. When using the MTS, Oracle
recommends that the large_pool be set to a value greater than the
default of 614,000 bytes.
Inside Oracle, the v$queue and v$dispatcher
system views will indicate if the number of MTS dispatchers is too
low. Even though the number of dispatchers is specified in the
init.ora file, you can change it online in SQL*DBA with the ALTER
ALTER SYSTEM SET MTS_DISPATCHERS = 'TCPIP,4';
If you encounter problems with the MTS, you
can quickly regress to dedicated servers by issuing an ALTER SYSTEM
command. The following command turns off the MTS by setting the
number of MTS servers to zero:
ALTER SYSTEM SET MTS_SERVERS=0;
The DBA must be careful when bouncing the
database and listener. In some cases, the instance must be bounced
if the listener is stopped, or it will restart in dedicated mode.
Whenever an instance is to be bounced, stop the listener, shut down
the instance, restart the listener, and start up the instance. The
listener reads the MTS parameters only if it is running before
startup of the instance. Therefore, bouncing the listener will
disable the MTS. To implement the MTS, you need to add the following
# Multi-threaded Server parameters
Now that we see how the MTS can relieve
stress on the server, let?s look at the connection pooling features
Connection Pooling and Network
Connection pooling is a resource utilization
feature that enables you to reduce the number of physical network
connections to an MTS dispatcher. This reduction is achieved by
sharing or pooling a set of connections among the client processes.
Connection pooling effectively allows Oracle to maximize the number
of physical network connections to the multithreaded server.
Connection pooling is achieved by sharing or pooling a dispatcher's
set of connections among multiple client processes (Figure 5-3).
Figure 3:Multithreaded server connections
Connection pooling reuses physical
connections and makes them available for incoming clients, while
still maintaining a logical session with the previous idle
connection. By using a timeout mechanism to temporarily release
transport connections that have been idle for a specified period,
connection pooling will ?suspend? a previous connection and reuse
the physical connection. When the idle client has more work to do,
the physical connection is reestablished with the dispatcher. When
the idle client has more work to do, the physical connection is
reestablished with the dispatcher.
By default, connection pooling is disabled
on both incoming and outgoing network connections. To enable
connection pooling, you must alter the mts_dispatchers parameter in
the init.ora file. Below, we can enable the Net8 connection pooling
feature by adding the POOL argument to the mts_dispatchers
MTS_DISPATCHERS = "(PROTOCOL=TCP)(DISPATCHERS=3)(POOL=3)"
If a number is specified, then connection
pooling is enabled for both incoming and outgoing network
connections and the number specified is the timeout in ticks for
both incoming and outgoing network connections.
MTS_DISPATCHERS = "(PROTOCOL=TCP)(DISPATCHERS=3)(POOL=ON)"
If ON, YES, TRUE, or BOTH is specified,
connection pooling is enabled for both incoming and outgoing network
connections and the default timeout (set by Net8) will be used for
both incoming and outgoing network connections.
MTS_DISPATCHERS = "(PROTOCOL=TCP)(DISPATCHERS=3)(POOL=IN)"
If IN is specified, connection pooling is
enabled for incoming network connections and the default timeout
(set by Net8) will be used for incoming network connections.
MTS_DISPATCHERS = "(PROTOCOL=TCP)(DISPATCHERS=3)(POOL=OUT)"
If OUT is specified, connection pooling is
enabled for outgoing network connections and the default timeout
(set by Net8) will be used for outgoing network connections.
In practice, connection pooling is rarely
used except in cases where the database server is overwhelmed with
incoming Net8 requests.
If you like Oracle tuning, see the
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.