Solaris: Different page sizes for Oracle PGA

Bad TLB performance?

If you notice more dTLB misses while running Oracle database on a Solaris box, disable the real-free memory manager for PGA (Program Global Area) by setting the parameter _use_realfree_heap=FALSE in Oracle database initialization file (init.ora); and then use MPSS (Multiple Page Size Support) to set the desired page size for the heap with environment variable MPSSHEAP=<pagesize>. Note that mpss.so.1 has to be pre-loaded for the env variable MPSSHEAP to be effective.

Relevant steps are as follows:

1. init.ora:
   
   _use_realfree_heap=FALSE
   
   
   
2. In a shell:
   
   % LD_PRELOAD_64=/usr/lib/sparcv9/mpss.so.1 <- assuming 64-bit Oracle
   % MPSSHEAP=4M <- assuming 4M pages provide good performance
   % export LD_PRELOAD_64 MPSSHEAP
   % sqlplus / as sysdba
   SQL> startup <- start up the database

Experiment with different page sizes supported by the underlying hardware platform, and measure the dTLB performance with trapstat tool. In case of performance degradation, simply revoke the changes.

Related information:

The real-free memory manager for PGA is enabled by default; and Oracle uses mmap()/munmap() calls {instead of malloc() and brk()} to allocate/deallocate anonymous memory for PGA. If _use_realfree_heap is set to TRUE, the memory for working areas (CGA/UGA) will be allocated on independent heaps i.e., UGA and CGA will not be a part of PGA.

You can find an interesting write-up on the advantages/disadvantages of real-free memory manager for PGA in Shrinking PGA of snp processes thread.

Note:

In general Oracle doesn't recommend using undocumented (hidden) parameters in production environments. Consult Oracle support before using any of the hidden parameters you find interesting.

To get the list of undocumented parameters, run the following script against your database instance.

orahiddenparam.sql
------------------

set lines 750
set pages 1000
COL name FORMAT a55
COL value FORMAT a15
COL description FORMAT a150

SELECT

   a.ksppinm                NAME,
   b.ksppstvl               VALUE,
   a.ksppdesc               DESCRIPTION

FROM

   sys.x$ksppi a,
   sys.x$ksppcv b

WHERE
 
   a.indx = b.indx

 AND

   a.ksppinm like '\_%' escape '\'

order by

   NAME;
/

Acknowledgements:
Ravindra Talashikar

__________
Technorati tags:
Solaris | OpenSolaris | Oracle