Home > Articles > Data > Oracle

  • Print
  • + Share This
From the author of

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).

  • + Share This
  • 🔖 Save To Your Account