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




EMC Employee Disclaimer

The opinions and interests expressed on EMC employee blogs are the employees' own and do not necessarily represent EMC's positions, strategies or views. EMC makes no representation or warranties about employee blogs or the accuracy or reliability of such blogs. When you access employee blogs, even though they may contain the EMC logo and content regarding EMC products and services, employee blogs are independent of EMC and EMC does not control their content or operation. In addition, a link to a blog does not mean that EMC endorses that blog or has responsibility for its content or use.

This disclaimer was put into place on March 23, 2011.

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

Join 2,890 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: