RE: SGA & shared pool size

  • From: Josh Collier <Josh.Collier@xxxxxxxxxxxx>
  • To: "exriscer@xxxxxxxxx" <exriscer@xxxxxxxxx>, "veeeraman@xxxxxxxxx" <veeeraman@xxxxxxxxx>
  • Date: Fri, 16 Apr 2010 15:31:25 -0700

6TB DWH with 20GB pga and 60GB SGA. The large pga especially helps avoid disk 
sorts. Shared pool is 2GB.

From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of LS Cheng
Sent: Friday, April 16, 2010 12:14 AM
To: veeeraman@xxxxxxxxx
Subject: Re: SGA & shared pool size


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 



On Fri, Apr 16, 2010 at 3:55 AM, Ram Raman 
<veeeraman@xxxxxxxxx<mailto: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.


Other related posts: