Re: High shared pool usage

From: Brent Day <coloradodba_at_gmail.com>
Date: Mon, 26 Sep 2011 20:45:45 -0600
Message-ID: <CAEz8shyUga9hT62F7V1iYK6c6LCrn05J9bU0z=Ofmv0WWYSqjA_at_mail.gmail.com>

We have PS FSCM as well and had similar issues in 10.2.0.4. After moving to 11.2.0.2, I made the following changes to help with this issue:

1. cursor_sharing=force (not supported by PS, but helps).
2. sga=26G
3. shared_pool=3g
4. turned off auto memory management and set other parameters for

environment (buffer cache, large pool, etc) as appropriate.

These may work well in 10.2.0.4, but test and use at your own risk.

Prior to these changes we were seeing 6-8G consumed by the shared_pool.

From my research is seems like shared_pool is consumed because the app developers do not have bind variables turned on for the various PSAE steps. My understanding is that is set on each "step" of an App Engine process so it might be worth checking with developers. We found that changing the PSAE steps to turn on bind variables breaks some of the processes so proceed with caution.

Again test any changes in your environment before pushing to production and these settings should be used at your own risk.

Brent

On Mon, Sep 26, 2011 at 6:55 PM, Ram Raman <veeeraman_at_gmail.com> wrote:

> Resending without older messages.
> To David's questions:
>
> - Aix 6.1 (not sure of the minor ver)
> - oracle-10204
> - I have to find the answers for the other two questions tomorrow.
> - psft fin app.
>
> Thanks David.
>
>

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

Received on Mon Sep 26 2011 - 21:45:45 CDT