Re: ** time to connect

A Joshi wrote:

> Hi,
>   Is there a way for me to find out time taken to connect to database once 
> the request
 > to logon comes in. Basically once a valid username/password reaches 
the machine or
  > if there are delays before that.  Especially if there is a way to 
know from logs (listener)
  in case of delays.
> Thank you a lot for your help.
>  
>  

One approach is as follows:

1) set the following parameters in your listener.ora:

TRACE_LEVEL_LISTENER = USER
TRACE_TIMESTAMP_LISTENER = TRUE

This will create a trace file in the default location 
($ORACLE_HOME/network/trace) and put timestamp info in the file.

 From a client run something like the following:

echo "select to_char(systimestamp,'HH:MI:SS.FF3') from dual;"\
   | sqlplus user/passwd@connect

The sqlplus output shows the time you were first able to accomplish 
something useful in the database:

TO_CHAR(SYSTIMESTA
------------------
11:49:17.194

Meanwhile the listener trace file will show you when the listener got 
the request:

[13-FEB-2005 11:49:16:846] nsopen: opening transport...

Of course, the time to complete the 'select' after logging in is buried 
in there somewhere.  But for a rough gauge if you're trying to 
trouble-shoot, this is a start.  There is a trcasst utility for more 
thorough analysis of the listener trace output.

If you are trying to get data on possible network delays from remote 
clients to the server, you could try synchronizing the clocks and then 
precede your sqlplus command with something like "date +%T%S%N" (under 
Linux) to get a starting time from the client side.  Or, get some 
network packet sniffing utility...

-Mark Bole




--
http://www.freelists.org/webpage/oracle-l

Other related posts: