Re: High shared pool usage
- From: Cary Millsap <cary.millsap@xxxxxxxxxxxx>
- To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 1 Oct 2011 16:14:05 -0700
*Parsing*?irrespective of whether it's "hard" or "soft"?is not a database
tuning topic. It is an application development topic.
Cary Millsap
Method R Corporation
http://method-r.com
On Sat, Oct 1, 2011 at 4:27 AM, <Laimutis.Nedzinskas@xxxxxx> wrote:
> > 660 hard parses a minute in an OLTP is not bad?
>
> I'll be unpopular. But critics is sometimes a necesity. Why - because
> Orcale is not the only database in the world and I dare to say some manage
> to perform better in some areas.
> I've already told - sql allows for literal sql. RDBMS's task is to handle
> it. End of this story for a time beeing.
>
> Now is it bad or not - it depends on how much resources parsing takes on
> your database. Do an AWR report (Enterprise license is required) or collect
> the stats manually and look into time spent on parsing andrelated issues.
>
> >Out of curiousity, what kind of hard parsing do other listers have
> > seen in best/worst/normal cases.
>
> I've seen a case when each insert of some ETL generated a new sql. There
> was no way to handle it with cursor_sharing options: the guys(developers)
> decided to use a new alias for each insert: insert into table t001; insert
> into table t002 ...
>
> What can I tell - there was a very defined limit of shared pool size we
> could not go bellow w/o risking shared pool errors, about 1.5 GB.
> Memory today is not that expensive. I think oracle's way of managing shared
> pool was great when memory was expensive (see interview with Ted Hoff at
> http://www.bbc.co.uk/news/technology-13260039 ) and developers generally
> were more qualified but nowdays it tends to fragment the pool. Good news -
> increasing the shared pool usually helps.
>
> But as for shared pool lathching... At least in 10r2 I've experienced a few
> cases of minute long pool lathing (after schema wide statistics import for
> example)
>
> my 2 cents, Laimis N
>
>
>
>
> ---------------------------------------------------------------------------------
>
> Please consider the environment before printing this e-mail
>
>
>
> From: Subodh Deshpande <deshpande.subodh@xxxxxxxxx>
>
> To: veeeraman@xxxxxxxxx
>
> Cc: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
>
> Date: 2011.10.01 00:48
>
> Subject: Re: High shared pool usage
>
>
>
>
>
>
> 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@xxxxxxxxx> 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@xxxxxx> 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.
> > >
> >
> >
> > --
> > //www.freelists.org/webpage/oracle-l
> >
> >
> >
>
>
> --
> ==============================
> DO NOT FORGET TO SMILE TODAY
> ==============================
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
