RE: Performance Problem.
- From: "Crisler, Jon" <Jon.Crisler@xxxxxxx>
- To: "Alex Gorbachev" <ag@xxxxxxxxxxxx>
- Date: Mon, 5 Nov 2007 10:53:50 -0500
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@xxxxxxxxx [mailto:gorbyx@xxxxxxxxx] On Behalf Of Alex
Gorbachev
Sent: Saturday, November 03, 2007 6:22 PM
To: Crisler, Jon
Cc: Jacob.van.Zanen@xxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx;
pythianbrinsmead@xxxxxxxxx
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@xxxxxxx> 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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]
> On Behalf Of Zanen van, J (Jacob)
> Sent: Tuesday, October 30, 2007 11:01 PM
> To: oracle-l@xxxxxxxxxxxxx
> 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
