Re: Advice on running Oracle with SGA > 20 GB
From: The Boss <usenet_at_No.Spam.Please.invalid>
Date: Wed, 3 Feb 2010 01:23:36 +0100
Message-ID: <4b68c209$0$7967$e4fe514c_at_dreader24.news.xs4all.nl>
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?
Date: Wed, 3 Feb 2010 01:23:36 +0100
Message-ID: <4b68c209$0$7967$e4fe514c_at_dreader24.news.xs4all.nl>
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
-- JeroenReceived on Tue Feb 02 2010 - 18:23:36 CST