RE: High shared pool usage

From: Go-Faster Consultancy Ltd. <info_at_go-faster.co.uk>
Date: Tue, 27 Sep 2011 07:24:20 +0100
Message-ID: <09CBA9464A304FAE9DAA1432F5246065_at_GOFASTER4>

I am not a fan of database-wide forced cursor sharing in PeopleSoft (I got burnt by this a while ago). However, I have enabled it for specific batch processes.

The excessive parse caused by all the literals can cause ASMM to allocate too much memory to library cache at the expense of buffer cache.

If you have a large SGA then you sometimes see ASMM moving memory back and forth between library and buffer caches as load changes. Individual batch processes can do this. ASMM can cause performance problems across the database. I would suggest setting a minimum value for the buffer cache to limit ASMMs behaviour.

Forcing AE to generate bind variables (ReUseStatement) is a good thing, both for the database and the performance of the AE too.

regards

---

David Kurtz
Go-Faster Consultancy Ltd.
tel: +44 (0)7771 760660
fax: +44 (0)7092 348865
mailto:david.kurtz_at_go-faster.co.uk
web: www.go-faster.co.uk
Book: PeopleSoft for the Oracle DBA: http://www.psftdba.com DBA Blogs: PeopleSoft: http://blog.psftdba.com, Oracle: http://blog.go-faster.co.uk
PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba

>-----Original Message-----
>From: oracle-l-bounce_at_freelists.org
>[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Brent Day
>Sent: Tuesday, September 27, 2011 3:46 AM
>To: veeeraman_at_gmail.com
>Cc: David Fitzjarrell; ORACLE-L
>Subject: Re: High shared pool usage
>Importance: High
>
>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
>
>

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

Received on Tue Sep 27 2011 - 01:24:20 CDT