Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: SQL Trace on very busy server - experiences, warnings, horror stories ...

Re: SQL Trace on very busy server - experiences, warnings, horror stories ...

From: Holger Baer <holger.baer_at_science-computing.de>
Date: Fri, 21 Nov 2003 17:42:37 +0100
Message-ID: <bplf9u$ika$1@news.BelWue.DE>


Dusan Bolek wrote:
> Greetings,
>
> does anyone here has an experience with using SQL_TRACE = True on
> quite busy business critical system?
> Right now, one guy here wants to use SQL Trace on 24x7 business
> critical server with up to 4000 concurrent users. Database is 8.1.7EE
> 64-bit on Solaris (SF15k). During peaks, CPUs occupation is about 80%,
> with day averages between 50% - 60%.
> My feelings about this are somehow mixed. I fear that we can run into
> big trouble, because performance of system can go down and client's
> voices high.
> The problem with SQL_TRACE is that it is a static parameter, so if we
> will encounter any problem we will be forced to restart our database
> during peak hours. So for me is really important to estimate
> probability that we will need to do this. I can think about switching
> SQL Trace off and restarting server as backup solution with 10%
> probability, but I can't accept this as fifty-fifty scenario.
> I've used SQL Trace in past, but never on server of such importance
> and with load that is quite high. Do you have any experiences with
> this? I would accept even horror stories and suggestions like "never
> do that, you fool!" :-)
>
> P.S. Maybe I'm too cautious, but I really dislike this whole idea.
> However, I can't operate just with my feelings. I need some solid
> ground to make qualified decision.
>
> Thanks
>
> --
> Dusan Bolek

Although I don't have practical experience with a system under load and sql_trace turned on, I'd rather not.

That's what

	dbms_system.set_sql_trace_in_session,
	dbms_support,
	alter session,
	alter system set events
	after logon trigger

are for. You turn them on when you really got a problem. You turn them off when you don't need them. But you don't let them run forever. And these can be changed dynamic without need to restart at all.

I'd expect you don't have permanently problems so most of the time the traces will never be used. What a waste of space.

My 42 ct

Holger Received on Fri Nov 21 2003 - 10:42:37 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US