Re: ** time to connect

From: Mark Bole <makbo_at_pacbell.net>
Date: Sun, 13 Feb 2005 12:33:37 -0800
Message-ID: <420FB9A1.5090803@pacbell.net>

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:

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_at_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

Received on Sun Feb 13 2005 - 15:36:26 CST