Oracle UNIX epc_disabled Environment Variable Administration 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 steps:

1. Shut down the databases and listeners.

2. Remove the *.dat files from your $ORACLE_HOME/otrace/admin directory.

3. Re-create the dat files with the UNIX touch command.

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 Behavior

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 options:

* Using array fetches
* Using the multithreaded server (MTS)
* Using connection pooling
* Using ODBC
* Using Oracle replication

Using Array Fetches to Improve Network Throughput

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

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

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 SYSTEM command:

SVRMGRL> 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:

SVRMGRL> 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 init.ora parameters:

# ----------------------
# Multi-threaded Server parameters
# ----------------------
local_listener="(address_list=
   (address=(protocol=tcp)(host=sting.janet.com)(port=1521))
   )"
MTS_MAX_DISPATCHERS=5
MTS_MAX_SERVERS=20
MTS_DISPATCHERS="(ADDRESS=
   (PROTOCOL=tcp)(HOST=sting.janet.com))(DISPATCHERS=3)
   "
service_names=testb1

Now that we see how the MTS can relieve stress on the server, let?s look at the connection pooling features of Oracle8i.

Connection Pooling and Network Performance

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 parameter:

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