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).
>
>
>
>

There is a note on my website about a package I use to take various performance snapshots. I've just had a look at it, and found that the published version is dated Sept 1998 for 7.3 - but I've just downloaded it and checked it that it installs against 9.2

    http://www.jlcomp.demon.co.uk/perf_p.html

The report you want (of the ones available in this version of the package) is probably system_events. When the system slows down simply start up an SQL*Plus session and do:

set serveroutput on size 1000000 format wrapped set trimspool on

execute performance_snapshot.system_events(30)

This will take a couple of snapshots of the system level wait events with a 30 second lag (appearing to hang whilst it does so), and then report the difference.

The top few wait events may give you a clue.

Common cause of problem - activity relating to log file switches. Even on a read-only action, it may be that a log switch will make the database writer work so hard that any other I/O intensive action will slow down. Check the alert log (found in the subdirectory identified by the parameter background_dump_dest) to see
if you have any timestamps for log file
switches that match recent slow-downs.
You may even find error messages about
'checkpoint not complete' in extreme cases.

-- 
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