Oracle Database 11g Automatic Memory Management – Part V. More About PRE_PAGE_SGA.

In my recent blog entry entitled Oracle Database 11g Automatic Memory Management – Part IV. Don’t Use PRE_PAGE_SGA, OK?, I offered a lot of examples of the results of combining Automatic Memory Management (AMM) with the PRE_PAGE_SGA init.ora parameter in an 11g Linux environment. Several blog readers emailed me to point out that I offered no proof that staying with the default setting of PRE_PAGE_SGA (FALSE by default) offers page table relief. They were right.

In the following text box I’ll show that 96 PQ processes configured—but not pre-paging the SGA—results in very little page table consumption especially when compared to the nearly 2GB PRE-PAGE_SGA result I showed in the previous post.

In the following text box you’ll see evidence of a non-pre-paged SGA with AMM and 96 PQ slaves costing only roughly 85MB of page tables. But, first, a quick peek at the simple script I use to report the aggregate RSS and page table size:

$ cat rss.sh

DESC="$1"

RSS=`ps -elF | grep test | grep -v ASM | grep -v grep  | awk '{ t=t+$12 } END { printf("%7.2lf\n", (t * 1024) / 2^ 30 ) }'`
PT=`grep -i paget /proc/meminfo | awk '{ print $2 }'`

echo "$RSS  $PT   $DESC"

And now the actual proof:

$
$ sqlplus '/ as sysdba' <<EOF
> show parameter PRE_PAGE
> exit;
> EOF

SQL*Plus: Release 11.1.0.7.0 - Production on Wed May 13 11:54:45 2009

Copyright (c) 1982, 2008, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL>
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pre_page_sga                         boolean     FALSE
SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
$
$ ps -ef | grep ora_p | egrep -v 'ping|pmon|psp|grep' | wc -l # How many PQ processes?
96
$
$ sh ./rss.sh "96 PQO slaves, no PRE_PAGE_SGA"
 2.79 85120  96 PQO slaves, no PRE_PAGE_SGA
$ 

Just to push the test a bit further, the next text box shows that 256 PQ processes with AMM, but no pre-paged SGA consumes on 150MB of page tables.

SQL> startup force pfile=./256.ora
ORACLE instance started.

Total System Global Area 8551575552 bytes
Fixed Size                  2161400 bytes
Variable Size            8455718152 bytes
Database Buffers           67108864 bytes
Redo Buffers               26587136 bytes
Database mounted.
Database opened.
SQL>  host ps -ef | grep ora_p | egrep -v 'ping|pmon|psp|grep' | wc -l # How many PQ processes?
256

SQL> host ./rss.sh "no prepage, 256 PQ processes"
 4.94 150504  no prepage, 256 PQ processes

SQL> show parameter PRE_PAGE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pre_page_sga                         boolean     FALSE