Re: High shared pool usage

From: Subodh Deshpande <deshpande.subodh_at_gmail.com>
Date: Fri, 30 Sep 2011 23:53:00 +0530
Message-ID: <CAJsOtB5Rn9CMsN4+5ERiVqbb2ak7uDhLvqcZGdritFqkCb_tBA_at_mail.gmail.com>

Ram,
I think there is a lot of scope for memory tunning if you feel 660 hard parses/minute is too much.
to support your observation collect stats at different intervals over a period and then you can decide..
if its OLTP application menu driven or even if you know what queries are going to be fired (i.e. users, session and work load is known) and then if you are coming acorss your this observation then yes memory tunning is required.

you did not informed what hardware, OS, OS memory, oracle memory allocation is..is it RAC..is ther any unused memory at OS level.. current users, sessions etc..to support your observation I will suggest you to collect some stats and then you can take action like pinning of certain objects into memory etc..

hard parses should be avoided as much as possible..

thanks and take care..subodh

On 30 September 2011 21:01, Ram Raman <veeeraman_at_gmail.com> wrote:

> Laimutis,
> 660 hard parses a minute in an OLTP is not bad? I thought it was a bad
> number. Out of curiousity, what kind of hard parsing do other listers have
> seen in best/worst/normal cases. (I realize that this could be dependent on
> the application, design, the workload, etc). Has anyone imposed size
> restrictions on shared pool like Brent has suggested.
>
> I like David's (Kurtz) idea of setting a minimum value for buffer cache so
> it does not get stolen.
>
> Thanks.
>
> On Tue, Sep 27, 2011 at 2:04 AM, <Laimutis.Nedzinskas_at_seb.lt> wrote:
>
> > > more than a million hard parse
> > a day. That number looks too much to me. This is an OLTP financial
> > application. Any comments?
> >
> > Comment 1: that's about 11.5 hard parses a second. Not that bad.
> >
> > Comment 2: looking into counters is good but looking into time spent is
> > even better: first comes hard parsing time then contention: lathing on
> pool
> > structures, etc. Specially at hard parsing peak times.
> >
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
==============================
DO NOT FORGET TO SMILE TODAY
==============================


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

Received on Fri Sep 30 2011 - 13:23:00 CDT