Re: Slow response when connecting via LISTENER vs. Bequeth - any ideas?

"Zoran Marjanski" <zoranm_at_sympatico.ca> wrote in message news:<Bbhl7.9488$ln4.736866_at_newsread1.prod.itd.earthlink.net>...
> Hi All,
>
> I think we may have misconfigured something to cause a dramatic difference
> in performance when a client (like sqlplus) connects via the LISTENER as
> opposed to the direct bequeth connection when the client and Oracle DB are
> on the same machine. Can anyone give any clues as to what to look at?
>
> We have Oracle 8.1.6 on a Compaq Alpha ES40 with 8 Gig of RAM running Tru64
> 5.1.
>
> Connections are established instantly, but when the DB needs to return a lot
> of data, connections establised via the Listener take much longer to return
> all the data. The more data being returned, the more dramatic the
> difference.
>
> I executed the following timing.sql script with sqlplus two ways:
>
> 1. sqlplus usr/pwd @timing
> 2. sqlplus usr/pwd_at_local_db @timing
>
> set timing on
> set termout off
> spool timing.out
> select * from wsku;
> spool off
> set termout on
> quit
>
> The script returns 2400 rows producing a 16M output file. The first
> invocation completes in 1.17 seconds whereas the second invocation method
> (using the @local_db connect string) completes in 36 seconds consistently.
>
> We've seen that it's the amount of data being returned that makes the entire
> difference. Complicated queries that return very little data (1 row) are
> just as fast with either connection method and the time it takes to
> establish the connection is instantaneous.
>
> We are pursuing the second invocation method because we are ultimately
> setting up our DB and application on a Clustered Tru64 environment where the
> Oracle DB is on one node and the application will run on another node and
> connect via Net*8; but very poor performance via Net*8 is causing us great
> concern.
>
> Can anyone offer any suggestions as to why on the same machine the bequeth
> connection is so much faster at returning data?
>
> What is interesting to note, is that when I do a "ps -ef | grep ora", I get
> to see LOCAL=YES (or NO), PROTOCOL=BEQ as arguments to the dedicated server
> process (not prespawned). Both connection methods show the dedicated server
> process with PROTOCOL=BEQ, but connecting with the @ shows LOCAL=NO and
> painfully slow response when any substantial amount of data is being
> returned.
>
> With this kind of performance degradation when the LISTENER gets involved in
> establishing connections, I can't believe people would want to run their DB
> and application on different servers. We must be doing something wrong
> somewhere, but what?
>
> Thanks, Zoran.

Zoran,
you can check in v$session_connect_info which protocol the session is using.
LOCAL=NO usually means you are using TCP/IP. Pre Oracle8i any TCP/IP connection on the server automatically reverted to IPC (a faster version of Bequeath. Both are using semaphores to communicate with the database) Your problem has nothing to do with the listener, but with the TCP/IP protocol. The max transmission unit of any ethernet NIC is 1508 bytes, the Session Data Unit of Net8 is 2048 (1 block!), so by default your net8 packets are getting fragmented by the tcp/ip layer. You can run iptrace and ipreport to confirm this fact. SDU can be adjusted. However it needs to be setup in both listener.ora and tnsnames.ora. Exact details can be found in the net8 administrators manual.
Ideally SDU should be a multiple of the MTU of the NIC.

Hth,

Sybrand Bakker, Senior Oracle DBA Received on Wed Sep 05 2001 - 07:37:10 CDT