- Introduction
- Performance Impact of the Database Cache
- Obtaining Database Cache Size Advice in Oracle9i
- Dynamic SGA Sizing in Oracle9i
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Dynamic SGA Sizing in Oracle9i
As discussed earlier, Oracle9i allows the size of the SGA (or at least some components of the SGA) to be dynamically changed. The database buffer cache is one component that can be dynamically changed in Oracle9i. The dynamic SGA infrastructure allows the limits to be set at runtime as to how much physical memory will be used for the SGA. The instance starts under-configured and it will use as much memory as the operating system allows it to use.
To facilitate the dynamic sizing of the SGA, Oracle9i introduces the concept of a granule—a unit of contiguous virtual memory allocation. The granule size is determined by the estimated total SGA size:
If SGA size is less than 128MB, the granule size is 4MB.
If SGA size is greater than or equal to 128MB, the granule size is 16MB.
Buffer cache, shared pool, and large pool are allowed to grow and shrink in size in units of granules. The SGA can be dynamically changed by using the ALTER SYSTEM command as shown below:
SQL> select name, value from v$parameter where name='db_cache_size'; Sample output is as follows: NAME VALUE --------------- ------------------------ db_cache_size 33554432 SQL>alter system set db_cache_size=64M; SQL> select name, value from v$parameter where name='db_cache_size'; Sample output is as follows: NAME VALUE --------------- ------------------------ db_cache_size 67108864
The following formula can be used to determine the approximate SGA size in Oracle9i:
SGA size = DB_CACHE_SIZE + DB_nK_CACHE_SIZE + DB_KEEP_CACHE_SIZE + DB_RECYCLE_CACHE_SIZE + SHARED_POOL_SIZE + LARGE_POOL_SIZE + JAVA_POOL_SIZE + LOG_BUFFERS + 1MB
In the above formula, up to four DB_nK_CACHE_SIZE parameters can be defined (for 2KB, 4KB, 8KB, 16KB, and 32KB).