Re: Help with intermittant slow down

Lee wrote:
> I'm out of my league here, perhaps someone can give me a boost.
>
> We're seeing (to us) mysterious intermittant slow downs (query which
> normally takes say a few seconds , no change of data, run during
> "slowdown" time takes a minute and a half.
>
> The best I can come up with to try to pin down what's happening is
> to run Tom Kyte's "runstats_pkg" for a SQL known to "slow down" when
> the sickness hits; then run it again when the system is slow. (The
> package initializes a stats table, wants you to run "way A" , collect
> statistics, run "way B", then run the "wrap up" routine. That last
> routine pretty prints various run time statistics so you can compare
> and contrast the resources consumed by "way A" and "way B".
>
> Can anyone think of something a bit slicker? For one thing, I'll have
> to wait till the system shows signs of being "sick". Who knows how long
> I'll have to wait, or how I "know" that I'm in the "slow" state.
> Then too, analysing the statistics might lead to more mysteries, but
> lets not put the cart before the horse.
>
> Is there such a thing as "the top N reasons you might see intermittent
> slow down" ? And if so, is there some "differential diagonoses" I can
> do to either eliminate the unlikely or point the quilty finger at the
> more likely cause?
>
> I know, the best advice is "so get a real DBA already", but that isnt
> happening, so I'm the next best thing (and heaven help us).

And the Oracle version number is?

Assuming 9i you want to pull a baseline StatsPack when things are normal and then keep pulling them every 15-20 minutes through a slow period.

Only with a StatsPack will you be able to properly diagnose what is happening inside of Oracle.

Assuming 10g change the setting for AWR (you did pay for the diagnostic pack didn't you?) to 15-20 minutes per snap and same as above.

And the operating system is?

Assuming UNIX pull sar, iostat, vmstat, etc. when things are normal as a baseline and again periodically including when things slow down.

Lack of facts makes it impossible to fix anything.

<RANT>
If management will not support you with training, and will not support you with a consultant, and will not support you with sufficient money to hire what you need.

Then management is being abusive and you need to polish up your resume and leave them with 2 weeks notice and nothing more. You are being set up for failure. And likely unpaid overtime, headaches, high blood pressure and then failure.
</RANT>

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)