Re: High shared pool usage

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_at_gmail.com>                                                                             
                                                                                                                                        
  To:         veeeraman_at_gmail.com                                                                                                       
                                                                                                                                        
  Cc:         ORACLE-L <oracle-l_at_freelists.org>                                                                                         
                                                                                                                                        
  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_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





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