Systems Engineering and RDBMS

Automatic Shared Memory Management

Posted by decipherinfosys on April 29, 2009

In Oracle 10g, Oracle introduced Automatic Shared Memory Management by introducing a new initialization parameter called sga_target. This parameter specifies the total size of the SGA (system global area).  SGA manages the following memory areas automatically if the parameters are set correctly.

•    Database buffer cache
•    Shared pool
•    Large pool
•    Java pool
•    Streams pool (included in automatic management in 10gR2)

By specifying a non-zero value for sga_target parameter and by setting up statistics_level parameter to TYPICAL or ALL, we turn on the automatic shared memory management. Once this parameter is in place, it takes over the control of memory allocation of individual pool and based on the load on the application, values for above mentioned memory areas or pools are adjusted accordingly by Oracle. This relieves dba from tuning individual memory area and not worrying about either under allocating or over allocating any one of them.

Do note that sga_target is dynamic parameter. In one of our previous blog post, we had covered how we can check whether a parameter is modifiable at the system level or the session level.

To set the initial value we can go ahead and query the v$sga view and find out the current size of SGA. Connect using SQL*Plus as sysdba and execute following query:

SQL> select sum(value)/1024/1024 from v$sga;

SUM(VALUE)/1024/1024
——————–
510.847656

So to start with we can set value of sga_target to 510M approx. There is another view v$sga_target_advice, which also suggests some values for this parameter.

SQL> select sga_size,estd_physical_reads from v$sga_Target_advice;

SGA_SIZE ESTD_PHYSICAL_READS
---------- -------------------
460               14065
230               14099
345               14065
575               14065
690               10597
805               10597
920               10597

Above results indicate that, if we increase the size of SGA to 690M, we will incur about 25% less physical reads compare to setting it to 575M. Now, if this suggested value is more than the value specified for sga_max_size parameter, we will have to change sga_max_size value first and then modify sga_target value or we will have to reduce the size of sga_target to match the sga_max_size value otherwise following error will be displayed.

SQL> alter system set sga_target=690M scope=both;
alter system set sga_target=690M scope=both
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00823: Specified value of sga_target greater than sga_max_size

For our testing purpose, we will just set the sga_target to match with sga_max_size. We can setup the scope to both (memory and spfile) as well. But for our testing purpose we are setting scope as spfile.

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

System altered.

Since we are setting up sga_target, reset following parameters to 0. If value for these parameters is non-zero, it will act as a low end or minimum threshold for these parameters, that means value for each parameter will not go below the specified value.

•    db_cache_size
•    shared_pool_size
•    java_pool_size
•    large_pool_size
•    streams_pool_size

Now shutdown and startup the database using spfile and let us check the parameter values for sga_target and other memory pools by querying v$sgainfo view.

SQL> select name,bytes/1024/1024 “MB”,resizeable
2    from v$sgainfo
3    where name in (‘Buffer Cache Size’,
4                   ‘Shared Pool Size’,
4                  ‘Large Pool Size’,
5                  ‘Java Pool Size’,
6                  ‘Streams Pool Size’);

NAME                                     MB RES
-------------------------------- ---------- ---
Buffer Cache Size                       372 Yes
Shared Pool Size                        120 Yes
Large Pool Size                           4 Yes
Java Pool Size                           12 Yes
Streams Pool Size                         0 Yes

If for any reason we have to decrease or increase the value of sga_target parameter, all other corresponding memory pools will be adjusted accordingly. We ran few queries and few DML statements and after some time, checked again the sizes of each pool. Buffer Cache size and shared pool size were resized in tandem with load.

NAME                                     MB RES
-------------------------------- ---------- ---
Buffer Cache Size                       368 Yes
Shared Pool Size                        124 Yes
Large Pool Size                           4 Yes
Java Pool Size                           12 Yes
Streams Pool Size                         0 Yes

By setting up automatic shared memory management, we are achieving obvious benefits. One doesn’t have to worry about tuning up different memory pools based on the any specific memory pool intense operation. This also ensures that chances of occurrence of ORA-04031 (unable to allocate ___ bytes of memory) are very minimal.

Certain other SGA components like non standard block size buffer caches, keep and recycle caches are not resized automatically. They still get their values from sga_target parameter and remaining will be distributed among automatically sized components. For example, if non standard block size buffer cache (db_nk_cache_size {n=2,4,8,16}) is used in the database and requires 50M out of 500M specified for sga_target. So all automatically sizable parameters will get their share from 450M(500M-50M).

In case required, we can always revert back to manual sizing of SGA by setting sga_target back to 0. In that case, all other memory pool values should be set accordingly.

Resources:

  • Oracle 10g Performance tuning Guide – here.
  • Oracle Ace Arup Nanda’s article – here.
  • Oracle magazine article – here.

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 )

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

 
%d bloggers like this: