Re: SGA & shared pool size
- From: Greg Rahn <greg@xxxxxxxxxxxxxxxxxx>
- To: Josh.Collier@xxxxxxxxxxxx
- Date: Fri, 16 Apr 2010 20:30:36 -0700
I have to say that seeing the SGA be larger than the PGA on a DW is
very odd to me. I would assume (maybe incorrectly, but hope not) most
Oracle DWs use PX and given PX uses direct path reads from disk
(unless you are using 11.2 in-memory PX), there are little gains from
such large buffer caches. Also, DW workloads generally consist of
"one and done" queries so SQL reuse is minimal so large shared pools
really dont add much value either. On the Oracle Database Machine
nodes, which have 72GB of RAM, the starting point for memory
allocation I use is 8GB SGA, 50GB PGA.
So I have to ask the question: What are the requirements for these DWs
that have such large SGAs and what performance data was used to choose
these sizes?
On Fri, Apr 16, 2010 at 3:31 PM, Josh Collier <Josh.Collier@xxxxxxxxxxxx> wrote:
> 6TB DWH with 20GB pga and 60GB SGA. The large pga especially helps avoid
> disk sorts. Shared pool is 2GB.
--
Regards,
Greg Rahn
http://structureddata.org
--
//www.freelists.org/webpage/oracle-l
Other related posts:
