Re: login delay with 9.2.0.5

From: Paul Baumgartel <treegarden_at_yahoo.com>
Date: Thu, 22 Jul 2004 11:53:10 -0700 (PDT)
Message-ID: <20040722185310.90076.qmail@web13423.mail.yahoo.com>

You can't trace the whole instance, but you can create a login trigger that starts tracing using DBMS_SUPPORT (note: this package is not installed by default; log in as SYS and run $ORACLE_HOME/rdbms/admin/dbmssupp.sql, then grant execute on DBMS_SUPPORT either to PUBLIC or one or more individual users first):

create or replace trigger trace_all
after logon on schema
begin
DBMS_SUPPORT.START_TRACE(waits=>true,binds=>false); end;
/

Do this for one or two users, then TKPROF (with WAITS=YES) the trace files and inspect the output. Then disable the trigger. I do this all the time and it works very well.

Of course, if the delay occurs before the trigger fires, this won't help you!

PB
--- "Adams, Matthew (GE Consumer & Industrial)" <MATT.ADAMS_at_GE.COM> wrote:
> This last weekend we upgraded an Oracle Apps=20
> instance from 8.1.7 to 9.2.0.5.0.
>
> Now sqlplus logins can take as long as 30 seconds=20
> and we're seeing peformance degradation pretty much=20
> across the board.
>
> It's a sun 6800 with 20 Cpus and 40G of ram. =20
> Machine does not appear to be bottlenecked on hardware
> at any point. Don't see any obvious problems anywhere.
>
> I can't set event 10046 for the whole instance, but I would=20
> very much like to know why logins are taking so long.
>
> Any suggestions?
>
> Matt
>
> ----
> Matt Adams - GE Appliances - matt.adams_at_appl.ge.com
> That's what's cool about working with computers.=20
> They don't argue, they remember everything and they=20
> don't drink all your beer. - Paul Leary, 1991
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>

Do you Yahoo!?
Vote for the stars of Yahoo!'s next ad campaign! http://advision.webevents.yahoo.com/yahoo/votelifeengine/

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Thu Jul 22 2004 - 13:49:45 CDT