Re: High shared pool usage
- From: Laimutis.Nedzinskas@xxxxxx
- To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 1 Oct 2011 14:27:26 +0300
> 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
Other related posts:
