Re: SGA & shared pool size
From: Greg Rahn <greg_at_structureddata.org>
Date: Fri, 16 Apr 2010 20:30:36 -0700
Message-ID: <g2ia9c093441004162030q5c4fec39gc4657df05b97ceee_at_mail.gmail.com>
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.
Date: Fri, 16 Apr 2010 20:30:36 -0700
Message-ID: <g2ia9c093441004162030q5c4fec39gc4657df05b97ceee_at_mail.gmail.com>
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_at_banfield.net> 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 -- http://www.freelists.org/webpage/oracle-lReceived on Fri Apr 16 2010 - 22:30:36 CDT