Re: Hints on optimizing Oracle!

From: Chuck Hamilton <chuckh_at_safeaccess.net>
Date: Mon, 02 Nov 1998 15:44:47 GMT
Message-ID: <363ed096.8840404@news2.axs2000.net>

There are actually a couple of things you can try. Do you know how you're resolving database names? 99% of the time it's down with a tnsnames.ora file. Make sure your name resolution method is listed first in the the sqlnet.ora file on the line that says NAMES.DIRECTORY_PATH = (TNSNAMES, HOSTNAME) Second, make sure the first address in your tnsnames.ora file for the server is connecting to the same address that the listener is listening on. To do this you need to compare the listener.ora file on the server with the tnsnames.ora file on the client. I've seen several cases were a clent tried to connect with the wrong protocol first, or the wrong IP port #, etc.. The end result is very slow connection times.

The last thing you can do has to do with how Oracle launches server processes on the server. When a connection comes in to the listener, unless you're using MTS (which I don't believe is available on NT), it first launches a server process, then hands the connection over to it. You can tell the listener to prespawn server processes so they're already waiting when the connection comes in. Here's what the SQLNet 2 manual says about prespawning server processes:

Prespawned Dedicated Server Processes

If you want the listener to create prespawned dedicated server processes when it is started, use Network Manager to include the following parameters in each SID_DESC in LISTENER.ORA.

PRESPAWN_ MAX The maximum number of prespawned dedicated server processes the listener will create. This number must be at least as many as the sum of the pool size for each protocol. For greatest efficiency, Oracle Corporation recommends a large value, so that prespawned dedicated server processes are always available for new connection requests.

PROTOCOL The protocol on which the listener creates prespawned dedicated server processes. If a listener listens on more than one community, you can choose whether to have pre-spawned servers on any or all of them.

POOL_SIZE The number of unused prespawned dedicated server processes for the listener to maintain on the selected protocol. Choose a number that is greater than 0 but no greater than the PRESPAWN_MAXIMUM value. The value should be about what you expect the average number of connections to be at any given time.

TIME OUT Time in minutes that an inactive prespawned dedicated server process waits for the next connection. The value should be greater than 0. (A value of 0 will allow an inactive shadow process to continue indefinitely, thus wasting machine resources.) For greatest efficiency, Oracle Corporation recommends a short Time Out value. The time out is activated only after a prespawned dedicated server process has carried a connection and been disconnected. In other words, prespawned dedicated server processes that are waiting for their first connection do not time out.

Here is an example of a SID_DESC section of LISTENER.ORA that includes information about prespawned dedicated
server processes:

(SID_LIST =

        (SID_DESC =


                (GLOBAL_DBNAME = sales.acme.com


                (SID_NAME = DB1)


                (ORACLE_HOME = /usr/bin/oracle)


                (PRESPAWN_MAX = 99)


                (PRESPAWN_LIST=


                        (PRESPAWN_DESC=
                                (PROTOCOL=TCP) 
                                (POOL_SIZE=10)
                                (TIMEOUT = 2)
                        )
                )
        )

)
On Sun, 01 Nov 1998 12:07:54 GMT, adrianh_at_globalnet.co.uk (Adrian Harrison) wrote:

>Using Oracle 7.3 NT, VB 6 enterprise  on same NT 4 server PC!
>
>I'm a VB/C++ programmer developing VB front end/ Oracle backend applications
>
>My main concern is the length of time it takes to connect to Oracle - especially the first time in
>the morning - it may take on average 8-10 sec's - I know this doesn't sound alot but when your
>constantly connnecting/disconnecting maybe 50 ot more times a day it becomes a real pain!
>
>Is there any setting in Oracle that can reduce this connection time?
>
>Any good Oracle web sites that might offer optimization advice?
>
>Thanks in advance
>
>Adrian Harrison

--
Chuck Hamilton
chuckh_at_safeaccess.net

If at first you don't succeed, sky diving isn't for you. Received on Mon Nov 02 1998 - 09:44:47 CST