Advice on running Oracle with SGA > 20 GB
in [Oracle]
|
From: Mladen Gogala on 2 Feb 2010 12:53 On Mon, 01 Feb 2010 20:02:08 -0800, vsevolod afanassiev wrote: > Linux is a bit different as there is no direct I/O (as far as I know), > unless you use raw devices or ASM. Huh? There is direct I/O, I am using it for years, I have even been writing about it.. > So memory that doesn't get allocated to SGA will be used for filesystem > buffer cache. Yup. That's why I use direct I/O. -- http://mgogala.byethost5.com
From: hpuxrac on 2 Feb 2010 18:22 On Feb 1, 5:15 pm, vsevolod afanassiev <vsevolod.afanass...(a)gmail.com> wrote: snip > The amount of memory provisioned in new servers keeps growing, we > have > 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 > inefficient > 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 > cache)? > - 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.
From: The Boss on 2 Feb 2010 19:23 vsevolod afanassiev wrote: > The amount of memory provisioned in new servers keeps growing, we > have > 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 > inefficient > 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 > cache)? > - 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? Here's a "must read" for Oracle configuration & tuning on AIX: http://www.ibm.com/developerworks/wikis/download/attachments/104533513/Oracle_AIX+Tuning+1.pdf -- Jeroen
From: Mladen Gogala on 2 Feb 2010 21:21
On Tue, 02 Feb 2010 15:22:22 -0800, hpuxrac wrote: > On a 32 gig linux system I would use huge pages and probably setup 16 > gig for a one instance SGA. Yup. That's precisely what I did. -- http://mgogala.freehostia.com |