Re: SGA & shared pool size

From: Ram Raman <veeeraman_at_gmail.com>
Date: Mon, 19 Apr 2010 19:36:05 -0500
Message-ID: <w2weffc058d1004191736g98ddc0a7rc4f32208f9af9885_at_mail.gmail.com>

That is what I thought too. But apparently quite a few people are using high SGA and Sharedpool in their databases. Looks like we can use big SGAs and shared pools without negative side effects.

I did not get to see the response times or performance reports of this database that I saw; maybe it is good.

On Mon, Apr 19, 2010 at 2:21 PM, Marcin Przepiorowski <pioro1_at_gmail.com>wrote:

> Hi,
>
> I have always think that if you increasing shared pool to enormous
> size like 22 GB
> you are generating more problems for database and decrease performance due
> to
> long time spend on shared pool management - did you ever try to
> decrease shared pool
> to 2 or 3 GB ?
> How many SQL with plans are you keep in shared pool ? how much pl/sql
> code do you have ?
> and maybe first of all what version do you have ?
>
> regards,
> --
> Marcin Przepiorowski
> http://oracleprof.blogspot.com
>
>
> On Fri, Apr 16, 2010 at 8:13 AM, LS Cheng <exriscer_at_gmail.com> wrote:
> > Hi
> >
> > I configured one a few weeks ago with 48G SGA and 22GB shared pool for a
> > DWH.
> >
> > I think I hit some bug that is why I need to set the shared pool so large
> > because this is a 6 nodes RAC and whenever shared pool is under pressure
> > process LCK starts to free shared pool memory (observed from v$rowcache
> and
> > v$sgastat) and holds the shared pool latch which cause hang in the node
> > (sometimes the entire system, all 6 nodes). Previously I had shared pool
> set
> > to 8GB and the hang situation happened everyday (many times instance
> dies),
> > when I increased to 22GB it only happens like once every couple of weeks.
> >
> > v$rowcache shows 7 million entries, anyone seen larger than that? I am
> > really curious.
> >
> >
> > Thanks
> >
> > --
> > LSC
> >
> >
> >
> >
> > On Fri, Apr 16, 2010 at 3:55 AM, Ram Raman <veeeraman_at_gmail.com> wrote:
> >>
> >>
> >> I saw a big database today with an SGA size of 56GB and a shared pool of
> >> 12Gb. The SGA_TARGET was set to 0. This database size is a few
> terabytes. I
> >> have not heard of such big SGA or SP sizes. I thought having big memory
> >> sizes may cause latch contention and CPU issues.
> >>
> >> What is the maximum SGA and SP sizes of DBs that you have seen.
> >>
> >> Thanks.
> >
>
>
>
> --
>

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

Received on Mon Apr 19 2010 - 19:36:05 CDT