Enabling Large Pages on Oracle Database running on IBM AIX

AIX support two virtual page sizes: 4 kilobytes (standard page) and 16 megabytes (large page). When using large pages to map virtual memory, there are fewer page boundaries, which improve the performance of prefetching.

Both online transaction processing (OLTP) and data warehouse environments can benefit from using large pages.

For applications such as Oracle, which typically uses a large amount of virtual memory, using large page memory will generally result in improved performance. There are three types of memory with which Oracle can use large pages: shared memory (SGA), process data (.data), and the instruction text (.text).

1) Configure the AIX large page pool by calculating the number of large pages required for the SGA:

num_of_large_pages = INT((total_SGA_size-1)/16MB)+1

As root:

vmo -p -o lgpg_regions=num_of_large_pages -o lgpg_size=16777216
vmo -o lru_file_repage=0

2) Enable the Oracle UNIX account to access large memory pages.
As root, check the current settings for the user account:

lsuser -a capabilities oracle

Add the ‘CAP_BYPASS_RAC_VMM’ and ‘CAP_PROPAGATE’ if needed:

chuser capabilities=CAP_BYPASS_RAC_VMM,CAP_PROPAGATE oracle

Database Changes Required

1) Set ‘lock_sga=true’ so that you get a contiguous piece of memory that can’t be taken by another process on the box.

alter system set LOCK_SGA=true scope=spfile

2) Set ‘sga_target=0’ so that automatic memory management is turned off.We dont need to dynamically resize memory settings when a large SGA is in use

Set the db_cache_size,shared_pool _size and shared_pool_reserved_size appropriately

alter system set SGA_TARGET=0 scope=spfile;
alter system set DB_CACHE_SIZE=10G scope=spfile;
alter system set SHARED_POOL_SIZE=1G scope=spfile;
alter system set SHARED_POOL_RESERVE_SIZE=.1G scope=spfile;

Once both the changes are done,Bounce the database to have those changes in effect

sqlplus / as sysdba
shutdown immediate
startup

Verify the parameter in the oracle database.

Sqlplus / as sysdba
show parameter lock_sga SGA_TARGET DB_CACHE_SIZE SHARED_POOL_SIZE SHARED_POOL_RESERVE_SIZE

Also check the large pages uses on the OS side

svmon –G