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

pagesflames_at_usa.net (Dusan Bolek) wrote in message news:<1e8276d6.0311210801.2e5bd33a_at_posting.google.com>...
> 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,

I set that once for a non-production system as an experiment. The instance still wasn't up (database open) after 45 minutes.

don't set it system wide.

if you do set it system-wide, you'll have so much trace that it will prove to be utterly useless.

I wouldn't set it system wide for 10 users, let alone 4000. Its likely that the measuring via sql_trace=true will disturb the system to such a degree that the bottleneck that you will be introducing will be your most signigicant bottleneck. Also, imagine if you don't set the trace file size to unlimited. A session that is long running could hit the max dump file size, and no more trace would be written. If the objective (however misguided) was to trace all activity, you have failed to meet the objective.

One of the points that Cary Milsap attempts to drive home in "Optimizing Oracle Performance" is that you need to carefully target generation of your diagnostic data.

The other issue with sql_trace=true, is that you will only be obtaining event 10046 level 1 trace data. You would be far better off to set the event for a specific session, with waits=>true, binds=>true if you really want to be able to reproduce the problem sql code and determine where the excessive waits are occurring. If you don't need the bind_var values, set binds=> false to reduce the size of the trace files.

You are being prudent, not too cautious.

Paul Received on Fri Nov 21 2003 - 15:30:49 CST