RE: Performance Problem.

I think you are reading too much into my note- I was just trying to point out that on Linux with very large SGA's, this is a frequent problem we have seen on a number of RAC systems. I have not been able to figure out a way inside of Oracle to single out this metric (not that I tried that hard either), but I believe on a small-pages system it also reports larger than normal session cpu compared to a similar test on systems with hugepages. Most likely there is a wait statistic but I don't know which one, and if it can be reliably traced back to hugepages.

Another gotcha on Peoplesoft systems is that the cursor_sharing option is frequently turned off on 10.2.0.3 systems, due to a bug that throws ORA-01008 errors. This is easily addressed with patch 5863277 - I believe this bug was introduced with 10.2.0.3 and only affects some 64 bit platforms like Linux 64 and Solaris.

-----Original Message-----
From: gorbyx_at_gmail.com [mailto:gorbyx_at_gmail.com] On Behalf Of Alex Gorbachev
Sent: Saturday, November 03, 2007 6:22 PM To: Crisler, Jon
Cc: Jacob.van.Zanen_at_rabobank.com; oracle-l_at_freelists.org; pythianbrinsmead_at_gmail.com
Subject: 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