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

0 Responses to “Oracle Database 11g Automatic Memory Management – Part V. More About PRE_PAGE_SGA.”



  1. Leave a Comment

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




DISCLAIMER

I work for Amazon Web Services. The opinions I share in this blog are my own. I'm *not* communicating as a spokesperson for Amazon. In other words, I work at Amazon, but this is my own opinion.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,976 other followers

Oracle ACE Program Status

Click It

website metrics

Fond Memories

Copyright

All content is © Kevin Closson and "Kevin Closson's Blog: Platforms, Databases, and Storage", 2006-2015. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Kevin Closson and Kevin Closson's Blog: Platforms, Databases, and Storage with appropriate and specific direction to the original content.

%d bloggers like this: