RE: Spotting the real cause of a Production slowdown (10g)

Yeah, we already have that idea on the agenda, the hard part is convincing upper admin that we should do it now. In the past, we have observed rapid resizes (v$sga_resize_ops shows that shared pool can grow very quickly early in the "slowdown"), but yesterday we did not observe the same dramatic behavior. Kinda makes me think we are battling we two problems that like to trade days. =)

Thanks for the ideas. Hopefully we will get to the bottom of this soon.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Alex Gorbachev Sent: Friday, April 21, 2006 2:35 AM
To: Schultz, Charles
Cc: oracle-l_at_freelists.org
Subject: Re: Spotting the real cause of a Production slowdown (10g)

Another point is huge shared pool you mentioned and its resizing during the problem. I do not trust automatic SGA management in 10g for critical OLTP systems with high workload. Perhaps, Oracle increases shared pool and this causes some overhead. If library cache is huge than more objects are protected by a single child latch. I.e. huge shared pool can in fact cause the problem.
See if you can correlate shared pool increase BEFORE (or maybe at the very same moment) the start of the problem. Perhaps, you should disbale automatic SGA management feature.

2006/4/20, Schultz, Charles <sac_at_uillinois.edu>:
> That is insightful, thank you.
>
> In light of trying to prove it (so that we can officially point our
> finger and resolve this issue so it does not happen again), it sounds
> like we are going to have to focus on the AWR to see if the workload
> is actually higher or not. Again, hypothetically, if the latch waits
> are merely a nuisance, they make the analysis that much harder because

> they in turn cause some of the cpu load. Is it possible to use AWR and

> attempt to exclude latch waits and their side effects? Perhaps we need

> to concentrate on periods of time before latch waits become an issue?
>
> Again, thanks for this thought. Another direction for us to pursue.
>

--
Best regards,
Alex Gorbachev
--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l