What can be done to increase SQL*Net performance?

Submitted by admin on Sun, 2004-08-08 12:28.

While a SQL statement is running SQL*Net polls the client continuously to catch CONTROL-C situations. This results into a lot of poll and fstat system calls.
The following SQLNET.ORA parameter can be specified to reduce polling overhead on your system:
```
# Number of packets to skip between checking for breaks (default=4)
BREAK_POLL_SKIP=10000
```
Prespawned server sessions. You can tell the listener to start up a pool of idle server processes. When a connection request is made, it doesn't have to start a server process; it just hands one of the idle processes to the client (and then starts a new connection in its own time). This is configured in LISTENER.ORA, in the SID_LIST_LISTENER section, as follows:
```
SID_LIST_LISTENER =
   (SID_LIST =
     (SID_DESC =
       (SID_NAME = yourSID)
       (PRESPAWN_MAX = 50)
       (PRESPAWN_LIST =
         (PRESPAWN_DESC = (PROTOCOL = TCP) (POOL_SIZE = 5)
         (TIMEOUT = 2))))
)
```
PRESPAWN_MAX: if there are over 50 sessions connected to the database, the listener won't prespawn any more.

POOL_SIZE: the listener will maintain an idle pool of 5 server processes.

TIMEOUT: after a client disconnects, the listener will keep the freed-up server process around for two minutes, waiting for a new connection request, before killing that process.

Multiple listeners with load balancing. You can start multiplelisteners on a server, and reference all of the listeners in the TNSNAMES.ORA file. When a client makes a connection request, the SQL*Net client will randomly pick one of the listeners to contact.
In LISTENER.ORA, specify multiple listeners as in:

# Define listener A...
STARTUP_WAIT_TIME_LISTENER_A = 0
CONNECT_TIMEOUT_LISTENER_A = 10
LISTENER_A=
  (ADDRESS_LIST =
     (ADDRESS =
        (PROTOCOL = TCP)
        (HOST = yourHost.domain)
        (PORT = 1521)))
SID_LIST_LISTENER_A =
   (SID_LIST =
      (SID_DESC =
         (SID_NAME = yourSID)
         (PRESPAWN_MAX = 50)))

# Define the second listener...
STARTUP_WAIT_TIME_LISTENER_B = 0
CONNECT_TIMEOUT_LISTENER_B = 10
LISTENER_B=
  (ADDRESS_LIST =
     (ADDRESS =
        (PROTOCOL = TCP)
        (HOST = yourHost.domain)
        (PORT = 1522)))
SID_LIST_LISTENER_B =
   (SID_LIST =
      (SID_DESC =
         (SID_NAME = yourSID)
         (PRESPAWN_MAX = 50)))

The TNSNAMES.ORA service for this database would be something like:

oradb1.world =
  (description_list=
     (description=
        (address_list=
           (address=
              (protocol=tcp)
              (host=yourHost.domain)
              (port=1521)))
           (connect_data =
              (sid = yourSID)))
     (description =
        (address_list =
           (address=
                (protocol=tcp)
                (host=yourHost.domain)
                (port=1522)))
           (connect_data =
              (sid = yourSID))))

User login

Search

Site navigation

Featured book

Similar entries

What can be done to increase SQL*Net performance?