Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:

By submitting your information, you agree that databasejournal.com may send you databasejournal offers via email, phone and text message, as well as email offers about other products and services that databasejournal believes may be of interest to you. databasejournal will process your information in accordance with the Quinstreet Privacy Policy.

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Sep 2, 2004

Automate the Sizing of your SGA in Oracle 10g - Page 2

By James Koopmann

Letting Oracle Take Control

Using Oracle's Automatic Shared Memory Tuning, you can instruct Oracle to manage a subset of the components that make up the SGA by merely telling the instance the target size of the SGA through the new SGA_TARGET parameter. Oracle will then pool from this value and dynamically distribute memory across selected components of the SGA. You now need not set values for SHARED_POOL_SIZE, JAVA_POOL_SIZE, LARGE_POOL_SIZE, or DB_CACHE_SIZE as Oracle will automatically size them for you. Once you set the SGA_TARGET parameter to a desirable size for your SGA these parameters will take on a value of zero and new parameters will be created designated by __SHARED_POOL_SIZE, __JAVA_POOL_SIZE, LARGE_POOL_SIZE, and __DB_CACHE_SIZE. As workloads go through the system and memory is needed in these areas, Oracle will allocate more memory based on internal statistics trends. Oracle will not manage the DB_KEEP_CACHE_SIZE, DB_RECYCLE_CACHE_SIZE, DBnK_CACHE_SIZE, or the STREAMS_POOL_SIZE and you must still determine the value for these parameters. In order for this all to take place, you must be using an SPFILE as this is the only way Oracle can dynamically make all these changes happen. Also, note that SGA_TARGET is the sum of all parameters that make up the SGA, not just the parameters it controls, so you must take those components it does not control into consideration when you give a value for SGA_TARGET.

Stepping through Letting Oracle Take Control

There is really nothing to switching into automatic shared memory tuning. You only need to set the SGA_TARGET parameter.

1. Take a look and see if you are already in automated sizing of SGA

SQL> show parameter sga_target
NAME                                 TYPE        VALUE
------------------------------------ ----------- --------
sga_target                           big integer 0

2. Alter system to begin automated sizing of SGA

SQL> alter system set sga_target=216m;
System altered.

3. Done

What happens when you switch to Automatic Shared Memory Tuning is a bit interesting. After you alter SGA_TARGET parameter, your SPFILE will undergo a change and now have the following parameters defined. Note that k101 is my instance name and will take on whatever the instance name is.


In addition, when you issue the previously given SQL to show the parameter settings for the individual components of the SGA you will notice that they now have a value of zero.

select name, value
   from v$parameter
  where name in ('shared_pool_size', 'java_pool_size', 'streams_pool_size',
 'log_buffer', 'db_cache_size', 'db_2k_cache_size', 'db_4k_cache_size',
 'db_8k_cache_size', 'db_16k_cache_size', 'db_32k_cache_size',
 'db_keep_cache_size', 'db_recycle_cache_size', 'large_pool_size');
NAME                      VALUE
------------------------- ---------
shared_pool_size          0
large_pool_size           0
java_pool_size            0
streams_pool_size         0
db_cache_size             0
db_2k_cache_size          0
db_4k_cache_size          0
db_8k_cache_size          0
db_16k_cache_size         0
db_32k_cache_size         0
db_keep_cache_size        0
db_recycle_cache_size     0
log_buffer                262144
13 rows selected.

If you truly want to see the parameters after setting the SGA_TARGET you will need to modify the query to include the newly created underscore variables. This can be of some concern if you are relying on the "normal" parameters for any database monitoring scripts.

select name, value
   from v$parameter
  where name in ('__shared_pool_size', '__java_pool_size',
 'streams_pool_size', 'log_buffer', '__db_cache_size', 'db_2k_cache_size',
 'db_4k_cache_size', 'db_8k_cache_size', 'db_16k_cache_size',
 'db_32k_cache_size', 'db_keep_cache_size', 'db_recycle_cache_size',

NAME                      VALUE
------------------------- ---------
__shared_pool_size        67108864
__large_pool_size         4194304
__java_pool_size          8388608
streams_pool_size         0
__db_cache_size           142606336
db_2k_cache_size          0
db_4k_cache_size          0
db_8k_cache_size          0
db_16k_cache_size         0
db_32k_cache_size         0
db_keep_cache_size        0
db_recycle_cache_size     0
log_buffer                262144

13 rows selected.

Switching over to the Automatic Shared Memory Tuning is as easy as setting an initialization parameter. How this will behave under load is yet to be determined but since these numbers are driven by the various advisories and I am mostly happy with them as individual components, I see no reason not to venture down the path of having Oracle automatically size my SGA. Of course, as always, in a test environment first. I would suggest you take a snapshot of your initialization parameters before letting Oracle take control and then compare the end settings that Oracle has implemented. It is always easy to switch back, just reset the SGA_TARGET parameter and set the individual components back to their original values.

» See All Articles by Columnist James Koopmann

Oracle Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM