Re: SGA & shared pool size
- From: LS Cheng <exriscer@xxxxxxxxx>
- To: Marcin Przepiorowski <pioro1@xxxxxxxxx>
- Date: Mon, 19 Apr 2010 21:47:17 +0200
Hi
We dont really have problems with storing sql and sql plans nor pl/sql
objects. This is DWH so we dont have much pressure on SQL statements.
This is 10.2.0.4.2 and as I mentioned previously most probably is we are
hitting a RAC bug that is why we need so large shared pool. We have had
smaller shared pool in other nodes like 3gb to 4gb and the time the instance
takes to fail is pretty short.
We have had many problems with shared pool, in the past we hit ORA-04031
constantly and had to decrease subpool to 1, even after that we still hit
4031 time to time, sometimes we get the error such as GES resource
exhausted.
Did I ask who has seen 7 million entries in rowcache...? I think that is
probably one of our problem but nothing much can be done since heavy
partitions and subpartitions are present.
Previously with smaller shared pool (7GB) system hangs and reboots almost
everyday, somehow LCK kicks in to free memory and while it frees the memory
the entire node hangs, sometimes hangs other nodes as well because some
local process hold CF enqueue or sometimes the instance just fails with
waited too long for a row cache lock.
Thanks
--
LSC
On Mon, Apr 19, 2010 at 9:21 PM, Marcin Przepiorowski <pioro1@xxxxxxxxx>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@xxxxxxxxx> 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@xxxxxxxxx> 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.
> >
>
>
>
> --
>
Other related posts:
