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
(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.
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
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
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.
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
Shut down the databases and listeners.
Remove the *.dat files from your $ORACLE_HOME/otrace/admin
Re-create the dat files with the UNIX touch
Specify ‘epc_disabled=TRUE' in the runtime environment
UNIX Oracle .profile, .login, or .cshrc login
file. This will disable
the otrace facility.
Modify the listener.ora file to specify epc_disabled=TRUE
in the sid_desc for each database.
Restart the database and listeners.
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 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
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.
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today.