Re: Slow OLTP users 14CPU SunFire

BLGuy wrote:
> The one with the bees on? Yep - sat on my desk - and very informative,
> if only to tell you that statspack is flawed and 8.1.7 does not capture
> all the waits it should at the resolution it should.
>
> However, I've use a modified version of Cary's prof.pl script on a
> very large DSS system in the past and got 400% improvement in
> throughput.
>
> Just a little nervous about starting 10046 traces on a production box
> with MTS sessions. You can't trace individual sessions and you
> can't switch it off quickly - the logs only close once the MTS
> sessions have closed and that can take many hours. Also a 10046 does
> have quite a hit on the performance and I would not like to tip the
> balance.
>
> Any thoughts on this? Am I being overly cautious?

Sorry I didn't see the MTS thing ... yeah that certainly complicates getting properly scoped diagnostic data.

Getting a statspack run during an appropriate and relevant short time frame ( 10 minutes or less ) is one approach.

One approach about getting the 10046 data is to somehow cobble together a webserver connection ( from a test machine perhaps? ) in that is not using MTS (dedicated) along with a unique oracle username. Then you could put in a database logon trigger that would start the trace. Make sure that you are the only one that can use this mechanism, get an appropriate trace ... then disable the logon trigger.

Of course you would have to proceed carefully in such a process but it is an approach that might be very complementary to the overall aggregate picture that you can get from a statspack attack.

Do you have a test system that can be used to produce a similar problem? If not why not ... ( standard disclaimer ).

If I were to make a totally blind stab in the dark guess (and that's what we want to avoid by getting properly scoped diagnostic data) I would wonder if bind variables are not being used by the DML.

Are bind variables being used when selecting? Received on Tue Jun 07 2005 - 11:59:07 CDT