Re: Why connection to the database takes > than 10 minutes

In article <712k36$8rp$1_at_nnrp1.dejanews.com>,   drona_at_my-dejanews.com wrote:
> The following is a brief explanation of the database connection problem I am
> experiencing.
> The databases are functioning normally!!!!!!!!!!!!!!. Yes there is no error
> message written to the log file.
>
> When the problem occurred few days back we assumed it is of resource problem.
> Every GUI connection was taking 30% of the CPU and we have 2 CPU on server.
> Then I opened a tar with Oracle Support thinking it may be of corrupted
> system table problem. Oracle Support thinks it is resource problem . Any how
> I havn't closed the tar with Oracle yet.
>
> If a user gets the connection to the database he/she will not have any
> problem executing any statements on database. Looks like even when we have
> 20% of CPU is free the connection to the database takes approximately 10
> minutes. Whe load on Servr is absolutely low then I have no problem
> connecting to the databases.Can it be any of the UNIX resource( any parameter
> settings not being set properly) being not available at that instant to the
> user trying to get connected? By the way I am running on HP-UX 10.20 with
> 7.3.4.2.0 oracle database. We have two instances one being run in Archive
> mode.
>
> Any clues and suggestions are welcome.
> Drona
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
>

You may be experiencing problems with the SQL Trace Facility. This "feature" was introduced in Oracle 7.3. It essentially logs all SQLnet activity on your server. It is, by default, always on. It doesn't cause too many problems until the files that the logging is being written into get too large. After that, your SQLnet connections from client machines (PCs or other servers) will go right down the tubes. If too many simultaneous connection attempts are active, the listener process will eventually hang completely, forcing you to stop and restart it.

To disable the Trace Facility, perform the following:

To disable Oracle Trace for local SQLPLUS connections, make sure you have exported the following environment variable (usually done thru .profile) for all Oracle users:

EPC_DISABLED=TRUE For connections coming in through SQLNET (PCs or other servers), you must include the following line in your listener.ora file:

(ENVS='EPC_DISABLED=TRUE') This would make your sid entry look as follows:

(SID_DESC =

  (SID_NAME = mydb)
  (ORACLE_HOME =/orasw/app/oracle/product/v7.3.4)
  (ENVS='EPC_DISABLED=TRUE')

When I got hit by this, I logged a TAR with Oracle and went down the same path with them that you have described. Only after some ranting and raving with our Gold Support mgr. did we finally get a Tech Rep who understood the nature of the problem.

Hope this helps!!

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Mon Oct 26 1998 - 15:41:53 CST