Re: Advice on running Oracle with SGA > 20 GB

From: Mladen Gogala <gogala.mladen_at_gmail.com>
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 bytes
SQL>
$>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.com
Received on Mon Feb 01 2010 - 19:47:05 CST

Original text of this message