Re: Odd Oracle slowdown

This sounds similar to a problem I encountered once...

Check svrmgr and see if you are getting any sorts-disk.

If so, try increasing sort_area_size init.ora parameter. Be careful though, I believe this affects each user's memory allocation...

-Frank

In article <01bda435$72960c20$f96916ac_at_williams-dennis>,   "DENNIS WILLIAMS" <DWILLIAMS_at_LIFETOUCH.COM> wrote:
>
> We are experiencing a strange Oracle problem and would like to know if
> anyone else has seen a similar problem. The problem is that Oracle will
> infrequently experience a dramatic change in performance. Symptoms are a
> lack of responsiveness to interactive users, reports run in a much longer
> time than normal. CPU utilization drops off to about 50%, but the system
> doesn't do much paging or swapping out. This condition persists for several
> hours, and may clear itself eventually, or killing a couple of the reports
> may bring the system back immediately.
> The system is a Digital Alpha 8400 running Digital UNIX 3.2C, with 2 -
> 440mhz CPUs, and 1.5-gig of memory. The Oracle version is 7.2.3. The system
> is a client-server manufacturing application developed in-house, and has
> been in production since July, 1996, with relatively few Oracle problems.
> Normally it supports about 100 interactive users.
> This problem seems to mostly occur at unusual times, like on Monday
nights
> or Saturdays (naturally, not last night once we thought we saw a problem).
> We have looked at the processes running at the times the problem has
> occurred, but so far have not divined any commonalties. Sometimes, but not
> always, an update job was running. Sometimes, but not always, hot backups
> were running. Given the times at which the problem occurred, it seems
> unlikely that interactive users were doing significant work. We have tried
> replicating the situation several times, but alas, Oracle and Digital just
> worked great.
> Once the problem occurred when we were able to observe it and we ran the
> Oracle utlbstat/utlestat statistics. Several indicators pointed to
> inadequate SGA: Latch Free count had the highest count of waits, and these
> were comprised of shared pool (hit ratio 63%), and library cache (hit ratio
> 62%). Based on this, we increased the Shared Pool memory allocation from
> 100-meg to 125-meg. However, the problem has occurred several times since
> then.
> At this point, we would appreciate any ideas on what to look for.
>
> Dennis W. Williams
> DBA
> Lifetouch, Inc.
> dwilliams_at_lifetouch.com
>
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Tue Jun 30 1998 - 14:34:06 CDT