Re: Tuning sql*net

From: lfree <a_at_a.a>
Date: Thu, 28 Oct 2004 16:22:51 +0800
Message-ID: <clqahn$uvo$1@news.yaako.com>

tuning sq*net is not improve oracle performance. you must tuning or rewrite sql query.

review:"Oracle.High.Performance.Tuning.for.9i.and.10g"

16.1 The Listener
The Listener is a process residing on a database server or other machine that "listens" for database connection requests. When a request is received the Listener hands off or passes on the connection to a database server process. Following is a very simple Listener configuration file. This file is called listener.ora by default and is placed in the $ORACLE_HOME/network/admin directory.

LISTENER =
   (DESCRIPTION_LIST =

      (DESCRIPTION =


         (ADDRESS = (PROTOCOL = TCP) (HOST = <hostname>)

            (PORT = 1521))
      )

   )

SID_LIST_LISTENER =
   (SID_LIST =

      (SID_DESC =


         (GLOBAL_DBNAME = <SID>.<xyz.com>)

         (SID_NAME = <SID>)

         (ORACLE_HOME = /oracle/ora92)

      )

   )
What can be done to tune or at least improve the Listener under certain circumstances?

  a.. Listener Queue Size. Allows a larger number of Listener requests to be serviced by allowing them to wait in a queue.

  b.. Listener Logging and Tracing. Logging is defaulted on and tracing is defaulted off.

  c.. Multiple Listeners and Load Balancing. Randomized load balancing can be created between multiple Listeners, pointing at the same database.

16.1.1 Listener Queue Size
Very large quantities of network requests and traffic to the Listener can cause the Listener to use a queue in order to allow the Listener to keep up with requests. If there are too many requests then waiting requests to the Listener will be queued. The default length of the Listener queue is operating-system-specific and can be increased by setting the QUEUESIZE parameter in the Listener configuration file.

LISTENER =
   (DESCRIPTION_LIST =

      (DESCRIPTION =


         (ADDRESS =

            (PROTOCOL = TCP)
            (HOST = <hostname>)
            (PORT = 1521)
            (QUEUESIZE = 50)
         )
      )

   )

SID_LIST_LISTENER =
   (SID_LIST =

      (SID_DESC =


         (GLOBAL_DBNAME = <SID>.<xyz.com>)

         (SID_NAME = <SID>)

         (ORACLE_HOME = /oracle/ora92)

      )

   )
16.1.2 Switching Off Listener Logging and Tracing

   By default a file called $ORACLE_HOME/network/log/listener.log file is created and constantly appended to. Tracing can also be switched on but is off by default. Logging and tracing of the Listener process can be switched off by setting the appropriate configuration parameters in the listener.ora file. Switch off Listener logging and especially tracing if this is switched on. Log and trace files can become extremely large and will affect performance. Unless really necessary do not log or trace the Listener process.

LISTENER =
   (DESCRIPTION_LIST =

      (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP) (HOST = <hostname>)


         (PORT = 1521))

      )

   )

SID_LIST_LISTENER =
   (SID_LIST =

      (SID_DESC =


         (GLOBAL_DBNAME = <SID>.<xyz.com>)

         (SID_NAME = <SID>)

         (ORACLE_HOME = /oracle/ora92)

      )

   )

LOGGING_LISTENER = OFF
TRACE_LEVEL_LISTENER = OFF 16.1.3 Multiple Listeners and Load Balancing Load balancing can help performance by providing multiple Listener connection points to a database server. If one Listener is busy another Listener process can be deferred to, thus reducing load on the first Listener. So let's add a Listener. The listener.ora configuration might now look something like that shown below. Note that the Listener called LISTENER2 uses a different port number to the Listener called LISTENER. Both of the two Listeners LISTENER and LISTENER2 allow connections to the same database.

LISTENER =
   (DESCRIPTION_LIST =

      (DESCRIPTION =


         (ADDRESS = (PROTOCOL = TCP) (HOST = <hostname>)

            (PORT = 1521))
      )

   )

LISTENER2 =
   (DESCRIPTION_LIST =

      (DESCRIPTION =


         (ADDRESS = (PROTOCOL = TCP) (HOST = <hostname>)

            (PORT = 1522))
      )

   )

SID_LIST_LISTENER =
   (SID_LIST =

      (SID_DESC =


         (GLOBAL_DBNAME = <SID>.<xyz.com>)

         (SID_NAME = <SID>)

         (ORACLE_HOME = /oracle/ora92)

      )

   )

SID_LIST_LISTENER2 =
   (SID_LIST =

      (SID_DESC =


         (GLOBAL_DBNAME = <SID>.<xyz.com>)

         (SID_NAME = <SID>)

         (ORACLE_HOME = /oracle/ora92)

      )

   )
So how do we implement load balancing between multiple Listener processes? The configuration file shown opposite is the client configuration file called the Transparent Network Substrate configuration file. This file is placed in the $ORACLE_HOME/network/ admin directory and is called tnsnames.ora. The tnsnames.ora file, which is a local naming client connection configuration file, can be placed on both Oracle server and client installations, specifying how a client process such as SQL*Plus can communicate with a Listener process. Note how there are two connection strings with ports 1521 and 1522, matching the different Listener address in the previously shown listener.ora file.

<TNSname> =

   (DESCRIPTION =

      (ADDRESS_LIST =


         (LOAD_BALANCE = YES)

         (ADDRESS = (PROTOCOL = TCP) (HOST = <hostname>)

            (PORT = 1521))


         (ADDRESS = (PROTOCOL = TCP) (HOST = <hostname>)

            (PORT = 1522))
      )
      (CONNECT_DATA = (SID = <SID>) (ORACLE_HOME =
         /oracle/ora81))

   )

> we have an environment where between application server and database
> server is a firewall and also they are situated in different physical
> location...
>
> is there anything to do in order to improve sql*net traffic in case of
> heavy queries...

Received on Thu Oct 28 2004 - 03:22:51 CDT