The Oracle (tm) Users' Co-Operative FAQ

Why does it take so long to connect to the database (v7.3)


Author's name: Connor McDonald

Author's Email: connor_mcdonald@yahoo.com

Date written: August 28, 2001

Oracle version(s): 7.3

Why does it take so long to connect to the database (v7.3)

Back to index of questions


This taken directly from the Oracle Metalink Note 1020763.6

Problem Description:
====================

You are encountering one or more of the following symptoms:

 -  Very slow connections to the database

 -  SQL*NET client connections consume close to 100% of the cpu

 -  Performance degradations during query processing

 -  High disk space usage by the file 'process.dat'

 -  Oracle errors attaching to the SGA, such as ORA-7307 or ORA-7337

 -  Protocol errors from SQL*Net, such as TNS-12164

 -  Bus errors and/or core dumps from SQL*PLUS or SVRMGRL

 -  Shadow process fails to spawn and you receive ORA-12547

 -  DBSNMP Agent is taking a very long time to startup

 -  ORA-12537, ORA-12560, ORA-507, 29:illegal seek when starting the listener

 -  Trace files being written to the default USER_DUMP_DEST rather than the 
    actual setting of USER_DUMP_DEST

 -  Zero length trace files

 -  Connect internal prompts for a password

 -  Errors of the form "ORA-7445 [epcrid_next()+452] [SIGSEGV]" in some 
    Oracle 8.0 releases.


Problem Explanation:
====================

Oracle Trace "Otrace", a new, common tracing mechanism, is automatically 
enabled in versions 7.3.X, and can result in large tracing files in the 
"$ORACLE_HOME/otrace/admin" directory.  The "*.dat" files "regid.dat", 
"process.dat", and "collect.dat" grow as connections are made to the database. 
Over time these files can grow quite large and eventually they will cause 
memory errors as they are mapped into Oracle's processes.  They will also 
affect connection time to the database and may cause SVRMGRL or SQLPLUS to 
hang. SQL*Net v2.3 has been instrumented with Oracle Trace, as have the RDBMS
version 7.3.X and Forms version 5.0.

Note: There have also been cases of svrmgrl/sqlplus hanging when the otrace 
files are small in size (i.e. Less than 500k).


What is Oracle Trace?
~~~~~~~~~~~~~~~~~~~~~

Oracle Trace allows you to turn on collections of performance related data 
through a common interface for any trace instrumented product.  Additionally,
it automatically correlates data collected in one instrumented product such as
SQL*Net, with data collected in another such as the RDBMS, so that you can view
the performance of various parts of a logical thread.  For example, you can get
a view of a given transaction that shows the performance data for it on the
client, on the network, and on the server.


Solution Description: 
=====================

Disable Oracle Trace.


To Disable Oracle Trace (otrace): 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Bring the listener and the database instance down prior to performing
the steps listed below.  

If the listener is running, shut it down.  To verify if the listener is
running, to stop it, or to start it, use the commands listed below:

    % lsnrctl status  (informs user if listener is running)
    % lsnrctl stop    (stops the listener process)
    % lsnrctl start   (starts the listener process)

If the database is running, shut it down with the following commands:

   % svrmgrl
   % connect internal
   % shutdown immediate
   % exit

1.  Change your location to the "admin" directory. 
 
       % cd $ORACLE_HOME/otrace/admin 
 
2.  Ensure the process.dat, regid.dat, collect.dat, and facility.dat
    files are not greater than 5MB in size.

       % ls -al *.dat

3.  Delete the process.dat, regid.dat, collect.dat, and facility.dat
    files with the following commands:

       % cd $ORACLE_HOME/otrace/admin
       % ls -al *.dat
       % rm process.dat
       % rm facility.dat
       % rm collect.dat
       % rm facility.dat
       % ls -al *.dat  (to verify the files have been removed)

4.  Recreate process.dat, regid.dat, collect.dat, and facility.dat.
    
       % cd $ORACLE_HOME/bin
       % otrccref  (this command recreates the *.dat files
                    to the default size)

5.  Set the user's environment variable EPC_DISABLED in .profile,
    .login, or .cshrc  to disable otrace:

    For Korn or Bourne Shell:

       $ EPC_DISABLED=TRUE; export EPC_DISABLED

    For C Shell:

       % setenv EPC_DISABLED TRUE

   *Note:  These files will not be updated when users login.

6. For specific SQL*Net connections, modify the 'SID_LIST_listener'
   clause in the 'listener.ora' file.  Set EPC_DISABLED=TRUE by
   adding it to the SID_DESC in each 7.3 database after the 
   (ORACLE_HOME=...) clause.

   Example:

   BEFORE:

      SID_LIST_LISTENER=
       (SID_LIST=
        (SID_DESC=
          (SID_NAME=DB1)
          (ORACLE_HOME=/oracle/OFA_base/app/oracle/product/7.3.2)

        )
        (SID_DESC=
          (SID_NAME=DB2)
          (ORACLE_HOME=/oracle/OFA_base/app/oracle/product/7.3.2)

        )
      )

   AFTER:

      SID_LIST_LISTENER=
       (SID_LIST=
        (SID_DESC=
          (SID_NAME=DB1)
          (ORACLE_HOME=/oracle/OFA_base/app/oracle/product/7.3.2)
          (ENVS='EPC_DISABLED=TRUE')
        )
        (SID_DESC=
          (SID_NAME=DB2)
          (ORACLE_HOME=/oracle/OFA_base/app/oracle/product/7.3.2)
          (ENVS='EPC_DISABLED=TRUE')
        )
      )

6.  Restart the database and SQL*Net listeners.


To Enable Oracle Trace (otrace): 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 
 
1.  Unset EPC_DISABLED 
 
    For Korn or Bourne Shell: 
 
       $ unset EPC_DISABLED 
 
    For C Shell: 
 
       % unsetenv EPC_DISABLED 
 
2.  Change your location to the "bin" directory 
 
       % cd $ORACLE_HOME/bin 
 
3.  Run the "otrccref" command 
 
       % ./otrccref 
 
    This recreates the regid.dat and process.dat files to the
    default sizes of 14K for regid.dat and 330K for process.dat.

Further reading: N/A


Back to top

Back to index of questions