RE: OTRACE Archeology

From: <Shreepad.Vaidya_at_alltel.com>
Date: Fri, 14 Jul 2000 08:21:57 +0530
Message-Id: <10557.112015@fatcity.com>

Hi Lucia,
I am sending the details .........

Hope this helps.
"Otrace.txt"
"

Doc ID : Note:1020763.6
Subject: CONNECTION/QUERY TIME IS SLOW OR ORA-07337 WHEN STARTING DATABASE Type: PROBLEM
Status: PUBLISHED
Content Type: TEXT/PLAIN
Creation Date: 23-AUG-1996
Last Revision Date: 28-JUN-1999
Language: USAENG

Problem Description:

The connection time is extremely slow, and the "process.dat" file located in the "$ORACLE_HOME/otrace/admin" directory is unusually large.

Performance problems occur with SQL*Plus after Oracle7 Server 7.3.2.X is installed.

Symptoms:

Slow connection times through SQL*Plus by way of the client or directly on the server.
Performance degradations when querying through SQL*Plus.
On Dec Unix, ORA-07307 or ORA-07337 may occur on startup. (See BUG:374964)
A SQL*Plus session may return one or more of the following errors:

ORA-01034, ORA-07320, OS error 12, OS error 28

Problem Explanation:

"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. SQL*Net v2.3 has been
instrumented with Oracle Trace, as have the RDBMS version 7.3.X and Forms version 5.0.

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.

Search Words:

otrace, otrccref, regid.dat, EPC_DISABLED, sqlplus, hang, hangs, hanging, svrmgrl, lsnrctl, core dump, bus error, ORA-1034, ORA-7320, Digital, sqlnet, ORA-7307, ORA-7429, ORA-7337, 7.dat, process.dat

Solution Description:

This could be BUG:387185 which only occurs on RDBMS version 7.3.X.

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

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):

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 Received on Thu Jul 13 2000 - 21:51:57 CDT