Re: Advice on running Oracle with SGA > 20 GB
Date: Tue, 2 Feb 2010 15:22:22 -0800 (PST)
On Feb 1, 5:15 pm, vsevolod afanassiev <vsevolod.afanass..._at_gmail.com> wrote:
> The amount of memory provisioned in new servers keeps growing, we
> several servers where single image of OS (Solaris, AIX, HP-UX) has
> access to
> more than 32 GB RAM. A question arises how to use this memory most
> efficiently to run Oracle databases, in particular what problems one
> may experience when running Oracle 9iR2 or 10gR2 with SGA > 20 GB?
> I am particularly interested in systems with mixed workload, i.e.
> where we see simultaneously highly efficient index-based queries, very
> queries joining multiple tables (consuming in excess of 800 million
> buffer gets
> per execution), large updates, dynamic SQL, ad-hoc queries, etc.
> What would you do on a server with 100 - 200 GB RAM that runs one
> Oracle instance?
> - would you configure direct I/O (bypass UNIX filesystem buffer
> - would you configure SGA around 50 - 70% of physical RAM?
> - would you use 8K block size of bigger blocks?
> - would you allow automatic resizing of SGA components in 10g (ASMM)
> or use fixed sizes?
> - would you use some OS features like "large pages" on AIX in
> combination with LOCK_SGA?
On a 32 gig linux system I would use huge pages and probably setup 16 gig for a one instance SGA.
Lots of rumours of problems with huge pages and ASMM in 10g/11g so I would stay away from that.
Probably go for a 16 gig SGA and 8 to 10 gig PGA ( aggregate target ) and leave some memory left over since the aggregate target is not a hard limit.
Throw most of your memory in the SGA at buffer cache. How big does your shared pool need to be? Got any monster apps with a ton of dynamic SQL that is not using bind variables?
Except for one system running Solaris all my stuff is linux these days ( OEL 5.4 ) so not sure exactly on your AIX question but if it makes sense to reserve memory permanently for oracle ( that's huge pages ) then go for it probably. Received on Tue Feb 02 2010 - 17:22:22 CST