Oracle10g Memory Tuning Case Study

10g Release 2 (10.2)

SGA_MAX_SIZE
================
SGA_MAX_SIZE specifies the maximum size of the SGA for the lifetime of the instance.

SGA_TARGET
================
SGA_TARGET specifies the total size of all SGA components. If SGA_TARGET is specified, then the following memory pools are automatically sized:

Buffer cache (DB_CACHE_SIZE)
Shared pool (SHARED_POOL_SIZE)
Large pool (LARGE_POOL_SIZE)
Java pool (JAVA_POOL_SIZE)
Streams pool (STREAMS_POOL_SIZE)

If these automatically tuned memory pools are set to non-zero values, then those values are used as minimum levels
by Automatic Shared Memory Management. You would set minimum values if an application component needs a minimum amount of memory to function properly.
The following pools are manually sized components and are not affected by Automatic Shared Memory Management:

Log buffer

Other buffer caches, such as KEEP, RECYCLE, and other block sizes

Fixed SGA and other internal allocations

The memory allocated to these pools is deducted from the total available for SGA_TARGET when
Automatic Shared Memory Management computes the values of the automatically tuned memory pools.

Last login: Sun Dec  2 09:08:49 2012 from 10.106.1.62
[root@oracle ~]# id
uid=0(root) gid=0(root) groups=0(root),1(bin),2(daemon),3(sys),4(adm),6(disk),10(wheel)
[root@oracle ~]#

[root@oracle ~]# su - oracle
-bash-3.1$
-bash-3.1$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Dec 5 08:47:14 2012
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> show parameter sga
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 348M
sga_target                           big integer 348M
SQL> show sga
Total System Global Area  364904448 bytes
Fixed Size                  1219448 bytes
Variable Size             113247368 bytes
Database Buffers          247463936 bytes
Redo Buffers                2973696 bytes
SQL> -- CHECK THE MEMORY OF SGA and PGA
SQL> show parameter sga
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 348M
sga_target                           big integer 348M
SQL> show parameter pga
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 115M
SQL>
SQL>
SQL>

SQL> –CHANGE THE MEMORY OF SGA_TARGET

SQL> alter system set sga_target=400M scope=spfile;

System altered.
SQL> --SHUTDOWN THE INSTANCE AND AGAIN CHECK THE STATUS OF SGA AND PGA
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area  419430400 bytes
Fixed Size                  1219760 bytes
Variable Size             113247056 bytes
Database Buffers          301989888 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
SQL> show parameter sga
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 400M
sga_target                           big integer 400M

SQL>– NOW CHANGE THE SGA_MAX_SIZE

SQL> ALTER SYSTEM SET sga_max_size=425M SCOPE=SPFILE;
System altered.
SQL> SHOW PARAMETER SGA
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 400M
sga_target                           big integer 400M
SQL> --SHUTDOWN THE INSTANCE AND CHECK THE STATUS OF SGA 
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area  448790528 bytes
Fixed Size                  1219952 bytes
Variable Size             146801296 bytes
Database Buffers          297795584 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
SQL> show parameter sga
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 428M
sga_target                           big integer 400M

SQL> —ONLINE CHANGE SGA_TARGET WITHOUT RESTART THE INSTANCE BECAUSE SGA_TARGET IS DYNAMIC PARAMETER

SQL> alter system set sga_target=428m scope=both;
System altered.
SQL> show parameter sga
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 428M
sga_target                           big integer 428M
SQL> -- TRY TO CHANGE THE SGA_MAX_SIZE WHICH NOT ALLOW BECAUSE SGA_MAX_SIZE IS STATIC PARAMETER
SQL> alter system set sga_max_size=450m scope=both;
alter system set sga_max_size=450m scope=both
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

PGA_AGGREGATE_TARGET
=====================
PGA_AGGREGATE_TARGET specifies the target aggregate PGA memory available to all server processes attached to the instance.

Setting PGA_AGGREGATE_TARGET to a nonzero value has the effect of automatically setting the WORKAREA_SIZE_POLICY parameter to AUTO.

This means that SQL working areas used by memory-intensive SQL operators (such as sort, group-by, hash-join, bitmap merge, and bitmap create) will be automatically sized.

A nonzero value for this parameter is the default since, unless you specify otherwise, Oracle sets it to 20% of the SGA or 10 MB, whichever is greater.

Setting PGA_AGGREGATE_TARGET to 0 automatically sets the WORKAREA_SIZE_POLICY parameter to MANUAL. This means that SQL workareas are sized using the *_AREA_SIZE parameters.

Oracle attempts to keep the amount of private memory below the target specified by this parameter by adapting the size of the work areas to private memory.

When increasing the value of this parameter, you indirectly increase the memory allotted to work areas.
Consequently, more memory-intensive operations are able to run fully in memory and less will work their way over to disk.

When setting this parameter, you should examine the total memory on your system that is available to the Oracle instance and
subtract the SGA. You can assign the remaining memory to PGA_AGGREGATE_TARGET.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

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

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.