Remote DBA Plans
Remote DBA Service

The queuesize Parameter in listener.ora

Oracle Tips by Burleson Consulting

The undocumented queuesize parameter determines the number of requests the listener can store while Oracle is working to establish a connection. This parameter
is only used for very high-volume databases, where the listener is spawning thousands of connections per hour. The size of the queuesize parameter should be equal to the number of expected simultaneous connections. Here is an example of this parameter in the listener.ora file:

L 7-5

   LISTENER =
     (ADDRESS_LIST =
           (ADDRESS =
             (PROTOCOL = TCP)
             (HOST = marvin)
             (PORT = 1521)
             (QUEUESIZE = 32)
           )
     )

The disadvantage of this parameter is that it uses more memory and resources because it is preallocating resources for anticipated connect requests. If you have high-volume connections into a dedicated listener, you may want to implement the Multi-Threaded Server (MTS), and use prespawned Oracle connections. Also, note that there are some restrictions of the MTS queue size, and some versions of UNIX do not allow queues greater than five.

The break_poll_skip Parameter of sqlnet.ora

This value specifies the number of packets to skip before checking for a user break. This is a client-only sqlnet.ora parameter and affects the amount of CPU consumed on the Oracle NET client.

The general rules for break_poll_skip are as follows:

The higher the break_poll_skip value, the less frequent ctrl-c checking, and the less CPU overhead used.
The lower the break_poll_skip value, the more frequent ctrl-c checking, and the more CPU overhead used.

The default value for break_poll_skip is 4. Remember, this parameter is only useful on a Oracle NET client sqlnet.ora file, and only functions on servers that support in-band breaks.

The disable_oob Parameter of sqlnet.ora

Out-of-band break checks can be disabled by adding this parameter to the sqlnet.ora file. If for some specific reason the checks should not be performed, set this parameter to ON. By default, Oracle NET assumes OFF for this parameter and
will perform out-of-band checks.

When disable_oob=on, Oracle's use of urgent data messages is disabled. The negative impact of using this parameter is the usage of the interrupt key. When you use disable_oob, you lose the break functionality of the interrupt key such as ctrl-c. A break is a function in Oracle NET that allows a user of an application to interrupt or stop a transaction before it is complete, returning both the client and the server to a state from which they can continue.

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 Oracle NET 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 Remote 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 Multi-Threaded 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 MetaLink Web site.

This is an excerpt from "Oracle9i 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.