| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Odd Oracle slowdown
DENNIS WILLIAMS 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.
Both update and hot back will cause a lot of redo log activity. If you happento run
out of redo log before a checkpoint completes, db activity will stall and you
will see this in the alert*.log:<timestamp>Thread 1 cannot allocate new log, sequence
119
Checkpoint not complete
Current log# 8 seq# 118 mem# 0: /db/dbs/log02.dbf
If you see this, then increase your redo log file sizes, you don't have to bring the instance down for that. Anything else in alert.log? traces?
> 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.
>
issue select count(*), event from v$session_wait group by event; when problem occurs and try to seewhat sessions are waiting on. Post output here? Sometimes networks that disconnect cause all interactive users to disconnect and PMON to cleanup, watch for any one process hogging the cpu.
> Dennis W. Williams
> DBA
> Lifetouch, Inc.
> dwilliams_at_lifetouch.com
--
__ _ _ __ _ _ _ _ ___ ______________________________ (( /\\ /\\ ||) |\V/| /\\ /\\ >/ Principal Performance Engineer_))//-\\//-\\||\ |||||//-\\\\//<_ Oracle Corporation Digital SBU
////////////////// Drop x's in email (spam) //////////////////////Received on Wed Jul 01 1998 - 13:31:27 CDT
 |
 |