Re: Advice on running Oracle with SGA > 20 GB
Date: Tue, 2 Feb 2010 01:47:05 +0000 (UTC)
Message-ID: <hk806p$sbo$1_at_solani.org>
On Mon, 01 Feb 2010 14:15:33 -0800, 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?
I have 64bit linux and here is one of my instances:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit
Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> show sga
Total System Global Area 1.7180E+10 bytes
Fixed Size 2310752 bytes Variable Size 8578973088 bytes Database Buffers 8589934592 bytes Redo Buffers 8650752 bytesSQL>
$>grep -i huge /proc/meminfo
HugePages_Total: 8192
HugePages_Free: 961
Hugepagesize: 2048 kB
The answer to your question is: yes, I do use huge pages because I don't want to waste memory on page tables. Also, huge pages aren't swapped or paged, handling of huge pages is much simpler. You're reducing your OS overhead by using huge pages.
On a IBM server that has between 100 and 200 GB RAM, I would urge management to license VLM ("Very Large Memory") option which allows me to have 32K blocks and allocate at least half of the memory for SGA. I would then call Connor McDonald to try getting bad BCHR on that machine.
-- http://mgogala.freehostia.comReceived on Mon Feb 01 2010 - 19:47:05 CST