Re: Has anyone experiemented with SQLPLUS connection times ?

One factor that can make a huge difference in connect times is appending to the *.dat files in $ORACLE_HOME/otrace/admin/.

There are by default three *.dat files in $ORACLE_HOME/otrace/admin/ - regid.dat (possibly register.dat), collect,dat and process.dat.

The problem is that every "qualified" connection appends some information to one or more and they may grow quite large. I have seen cases where one (I forget which, but perhaps process.dat) grew to over a gigabyte and connections took several minutes to complete. Once these files are deleted, they are never automatically recreated. Unless one is using OEM's Oracle Trace, they can be deleted. If they need to be recreated, it can be done with $ORACLE_HOME/bin/otrccref.

An experiment to see if this is an issue is to time connections with the environmental variable EPC_DISABLED=TRUE (which disables this behavior - making it not a "qualified" connection) and compare the time to connections with this set to FALSE or null (the default behavior). If there is a consistent significant difference, then this is likely the problem. For further information, see:

www.kevinloney.com/free/otrace.doc - How and Why to Disable Oracle Trace

Don Granaman
(Microsoft-impaired) OraSaurus

PS: So why is this reply in this format when I have my Outlook Express set to send "plain text"? The original *looked* like plain text...

• Original Message ----- From: Johnson, Michael To: Multiple recipients of list ORACLE-L Sent: Monday, May 19, 2003 11:52 AM Subject: Has anyone experiemented with SQLPLUS connection times ?

  List,

  Anyone on the list done any experiments by adjusting   O/S, init.ora or network parameter files to show how   SQLPLUS connections either locally or over the network   can speed up or slow down. If so, could you please   direct me as needed.

  Thank You in advance.

  Mike

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Don Granaman
  INET: granaman_at_cox.net

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).