Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Slow response when connecting via LISTENER vs. Bequeth - any ideas?

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

From: Zoran Marjanski <zoranm_at_sympatico.ca>
Date: Wed, 05 Sep 2001 03:43:29 GMT
Message-ID: <Bbhl7.9488$ln4.736866@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. Received on Tue Sep 04 2001 - 22:43:29 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US