Re: Help with intermittant slow down

"Lee" <Lee_at_Jamtoday.com> wrote in message news:d9if51$3q6$1_at_reader1.panix.com...
> 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).
>
>
>
>

On second thoughts - there will be one issue for 9.2, older versions of Oracle trap the wait time in centiseconds, 9i records micro seconds. So find the query:

select

    d.indx indx,
    d.kslednam event,
    s.ksleswts total_waits,
    s.kslestim time_waited

    x$kslei s,
    x$ksled d
where s.indx = d.indx
order by

    d.indx
;

and change the line about time_waited to:

    s.kslestim time_waited/10000
before you install the package.

Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/appearances.html Public Appearances - schedule updated June 22nd 2005 Received on Sat Jun 25 2005 - 12:41:34 CDT