 | | | Performance Problem. | Performance Problem. 2007-11-03 - By Alex Gorbachev
Back
Jon,
*Unless I'm missing something*, chances that Jacob has memory
management issues are small. Looking at semaphores statistics is a
very indirect way of troubleshooting to say the least. Buffer cache
hit ratio might be just as useful.
A good starting point would be a quick 10046 trace on the most
important sessions (a script to enable it can be prepared in advance)
and/or at least several snaps of v$session and maybe
v$sesstat/v$sysstat/v$system_event/v$session_event (mini statspack on
small intervals if you wish).
Btw, Mark's suggestion on bind variables peaking falls into the same
category of "sounds like". ;-) So even though it could be the case -
it's just one of many possibilities and poking around while there are
better troubleshooting methods available is probably a sub-optimal and
counter-productive as I would call it.
There are set of "rules" that are often followed for Peoplesoft in 9i
and 10g like setting OPTIMIZER_FEATURES_ENABLE=8.1.7. We had recently
migrated on customer from HR 8.8 on 9i to HR 8.9 on 10g RAC. We were
working on optimization of set of queries that went mad and were
mostly done but customer decided to finally follow the advice from
Peoplesoft to set some of init.ora parameters including
OPTIMIZER_FEATURES_ENABLE.
I don't have much Peoplesoft experience and I know that administrators
of such applications are typically conservative. However, sticking to
OPTIMIZER_FEATURES_ENABLE=8.1.7 without attempt to make use of all new
CBO features of latest Oracle releases is a short sighted approach. Is
anyone running Peoplesoft on 10g without
OPTIMIZER_FEATURES_ENABLE=8.1.7?
By the way, if OPTIMIZER_FEATURES_ENABLE=8.1.7 is set in Jacob's
environment, then bind variable peaking issue that Mark referenced
won't even be relevant. It first appeared in 9iR1. Didn't it?
Cheers,
Alex
On Oct 31, 2007 8:46 AM, Crisler, Jon <Jon.Crisler@(protected)> wrote:
> You don't say what platform you are on, but on Linux (specifically Red Hat
> 4) you could implement hugepages, which tends to alleviate problems with
> very large SGA's and memory management.
>
> For a good starting point, see Metalink technote 361323.1
>
>
>
> __ ____ ____ ____ ____ ____ ____
>
>
> From: oracle-l-bounce@(protected) [mailto:oracle-l-bounce@(protected)]
> On Behalf Of Zanen van, J (Jacob)
> Sent: Tuesday, October 30, 2007 11:01 PM
> To: oracle-l@(protected)
> Subject: Performance Problem.
>
>
>
>
>
> Need some help on this.
>
>
>
> We have a peoplesoft environment here that seems to have performance issues
> every so often.
>
> When this happens performance comes to s standstill for everyone and a
> SQL*Plus query that takes about 6-7 seconds normally takes about 13 minutes.
> During this time it will pretty much wait for sequential reads only.
>
> I have not yet had the chance to install statspack yet and we are not
> licensed for AWR.
> We are on oracle 10.2.0.3 on solaris 64bit
>
> When I check the database quickly (needs to be done quickly as business
> dictates a reboot at the moment) I do not see any obvious reasons why this
> would be happening.
>
> On unix the admins see quite a large increase in use of semaphores.
>
> After the reboot the problems seem to disappear for several weeks/months
>
> Has anyone seen something similar before and found out why this is
> happening.
>
>
>
> Brgds
>
>
>
> Jack
--
Alex Gorbachev, Oracle DBA Brewer, The Pythian Group
http://www.pythian.com/blogs/author/alex http://www.oracloid.com
BAAG party - www.BattleAgainstAnyGuess.com
--
http://www.freelists.org/webpage/oracle-l
|
|
|