Re: Performance Problem.

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_at_usi.com> 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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
> On Behalf Of Zanen van, J (Jacob)
> Sent: Tuesday, October 30, 2007 11:01 PM
> To: oracle-l_at_freelists.org
> 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