| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: slowness in making and closing connections
In article <9610ii$vun$1_at_nnrp1.deja.com>,
Enivaldo Freire do O' <enivaldo_at_my-deja.com> wrote:
> Hi,
>
> I have a problem with a 7.3.3 Oracle on Dec-Unix. When using sqlplus
> locally (no listener up, only ORACLE_SID set), the sqlplus time (cpu
+
> system) to get the prompt is very big (50 secs), about the same of the
> dedicated process started to serve sqlplus. The queries are returned
> very fast but when quitting the same problem occurs (more 50 secs for
> both processes).
>
> This didn't happen before, only from a few days ago, without notice.
> It also happens for a sid1 database resident on the same server.
>
> Help needed!
>
> Enivaldo
>
> Sent via Deja.com
> http://www.deja.com/
>
This sounds like you may be encountering the ora trace problem: Here
is something I had on the subject that would allow you to determine if
this could be your problem. You may want to contact support if this
turns out to be your problem before following the procedures outlined
here.
How and why to disable ORACLE Trace
by Kevin Loney.
This is an edited excerpt from the book ORACLE8 DBA Handbook, by Kevin
Loney, copyright
Osborne/McGraw-Hill, reprinted by permission. See
http://www.kevinloney.com for additional articles and news.
One of the components of ORACLE Enterprise Manager (OEM) can have a significant negative impact on the performance of your database connections. Typically, the problem symptom is a steadily increasing login time for users. Depending on your configuration, you may see this problem for server-based connections, for client-based connections, or both. To resolve the problem, you must disable an underlying component of the OEM Performance Pack.
By default, OEM does not come with the Performance Pack toolset installed. The Performance Pack is a set of add-on utilities designed to help with the tuning and performance management aspects of the database. The Performance Pack includes tools such as Top Sessions and ORACLE Expert.
One of the Performance Pack tools, ORACLE Trace, may impact your performance even if you do not use it. ORACLE Trace evaluates databaselevel trace information to identify potential performance problems. To generate the information it needs in order to perform this evaluation, ORACLE Trace collects trace information about the database activity.
You should not run ORACLE Trace constantly; however, it is enabled by default in many installations in ORACLE7.3 and above. As a result, your database may be constantly generating trace data that you may never use. Because you are generating and maintaining this trace information, your database may exhibit performance-related problems, such as poor query response time, aborted sessions, and database connection attempts that take a very long time. This problem is most prevalent in the UNIX implementations of ORACLE Trace.
To determine if ORACLE Trace is collecting data on your server, check for the existence of the process.dat and regid.dat files in the /otrace/admin subdirectory of your ORACLE software home directory. An example of this check, as performed on a UNIX server, is shown in the following listing. The cd command changes the directory, and the ls command lists the files that match the *.dat filename format.
> cd $ORACLE_HOME/otrace/admin
> ls -alt *.dat
-rw-rw-rw- 1 oracle dba 11948576 Sep 16 10:40 process.dat
-rw-rw-rw- 1 oracle dba 1023292 Sep 16 10:40 regid.dat
The listing shows that the process.dat file has grown to over 11MB. In general, the performance of database connections is affected when the process.dat file exceeds 5MB in size. Before beginning to resolve the problem, you should first shut down the databases on the server. To resolve the problem, delete the process.data and regid.data files and run the otrccref command (which re-initializes the ORACLE Trace files). These actions are shown in the following example:
> cd $ORACLE_HOME/otrace/admin
> rm -f process.dat regid.dat
> otrccref
In the listing, the UNIX rm command is used to delete the trace files created by ORACLE Trace. To prevent new entries from being written to the trace files, you should set the EPC_DISABLED environment variable to TRUE in the profile (login) file for the user account that owns the ORACLE software (usually named oracle). For example, the following Bourne shell command set this variable:
EPC_DISABLED=TRUE; export EPC_DISABLED
You must also disable ORACLE Trace log generation by modifying the listener.ora file. Each listener.ora file includes a section that lists the instances to listen for and the ORACLE software home directory for the instances. A sample listener.ora section for the ‘loc’ instance is shown in the following listing:
SID_LIST_LISTENER =
(SID_LIST = (SID_DESC = (SID_NAME = loc) (ORACLE_HOME = /orasw/app/oracle/product/8.0.3.1))
To make sure SQL*Net V2 and Net8 connections to your database do not enable ORACLE Trace, you must add a clause to the listener.ora file. The new clause, which sets a value for the ENVS variable, should follow the ORACLE_HOME variable setting, as shown in the following listing:
SID_LIST_LISTENER =
(SID_LIST = (SID_DESC = (SID_NAME = loc) (ORACLE_HOME = /orasw/app/oracle/product/8.0.3.1) (ENVS='EPC_DISABLED=TRUE')
For all of the environment variables to take effect, you should log in to the server again, shut down and restart the listener process, and restart the databases.
When making these changes, you should be sure to make the changes in both the user profile file and the listener.ora file. If you change only the user profile, then connections from the server will perform adequately while the performance of connections from clients using SQL*Net gradually deteriorate. If you need to use ORACLE Trace, use it only during specific periods when you are tuning a known set of database activities.
-- Mark D. Powell -- The only advice that counts is the advice that you follow so follow your own advice -- Sent via Deja.com http://www.deja.com/Received on Fri Feb 09 2001 - 10:18:39 CST
 |
 |