SGA memory is allocated in unit of contiguous memory chunks called granule. The SGA memory components(shared pool,buffer cache, redo log buffer,java pool,streams pool,large pool) are sized as multiples of granules.
The granule size is determined based on the amount of memory requested at the instance startup. It is based on the SGA_MAX_SIZE. If MEMORY_MAX_TARGET is specified, then SGA_MAX_SIZE defaults to MEMORY_MAX_TARGET for the purpose of sizing the granule. Once set, the granule size does not change for the life of the instance.
However granule maximum size = 16MB on 32-bit platforms.
SQL> show parameter memory_max_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
memory_max_target big integer 800M
SQL> select bytes/1024/1024 bytes_Mb from v$sgainfo where name like 'Granule Size';
BYTES_MB
----------
4
| RDBMS | SGA_MAX_SIZE (or memory_max_target) | GRANULE SIZE |
|---|---|---|
| 9.2 | <= 128MB | 4MB |
| > 128MB | 16MB | |
| 10.2 | <= 1GB | 4MB |
| > 1GB | 16MB | |
| 11gR1 | <= 1GB | 4MB |
| 1Gb - 4GB | 16MB | |
| 4Gb - 16GB | 64MB | |
| 16Gb - 64GB | 256MB | |
| > 64GB | 512MB | |
| 11gR2 (and 11gR1 with patch 8813366 applied *) | < 1Gb | 4Mb |
| 1Gb - 8Gb | 16Mb | |
| 8Gb - 16Gb | 32Mb | |
| 16Gb - 32Gb | 64Mb | |
| 32Gb - 64Gb | 128Mb | |
| 64Gb - 128Gb | 256Mb | |
| > 128Gb | 512Mb |
How to test from above table. If I need granule size 16Mb, I have to Memory 1Gb - 4GB for SGA???
SQL> select * from v$version;I have only 900Mb for Physical Memory. Find Idea to test then created /tmp/pfile file for modified MEMORY_MAX_TARGET parameter.
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for Linux: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production
SQL> !free -m
total used free shared buffers cached
Mem: 888 849 38 0 2 740
SQL> create pfile='/tmp/pfile' from spfile;then faked /dev/shm size (this's just test for granule size to fake memory_max_target parameter)
File created.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
Filesystem Size Used Avail Use% Mounted onthen modified /tmp/pfile (MEMORY_MAX_TARGET=4G) - If MEMORY_MAX_TARGET is specified, then SGA_MAX_SIZE defaults to MEMORY_MAX_TARGET.
tmpfs 4.0G 0 4.0G 0% /dev/shm
SQL> startup pfile='/tmp/pfile';Used granule size 16M.
ORACLE instance started.
Total System Global Area 2058981376 bytes
Fixed Size 1300968 bytes
Variable Size 1644168728 bytes
Database Buffers 402653184 bytes
Redo Buffers 10858496 bytes
Database mounted.
Database opened.
SQL> show parameter memory_max_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
memory_max_target big integer 4G
SQL> select bytes/1024/1024 bytes_Mb from v$sgainfo where name like 'Granule Size';
BYTES_MB
----------
16
If we set a value in the spfile that is not a multiple of the granule size, the actual size allocated will be rounded up to the nearest granule.
SQL> select bytes/1024/1024 bytes_Mb from v$sgainfo where name like 'Granule Size';The java_pool_size 4M in the spfile, after increased granule size, then the actual allocation for the java_pool_size will be rounded up to 16M.
BYTES_MB
----------
4
SQL> alter system set java_pool_size=4M;
System altered.
SQL> show parameter java_pool_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
java_pool_size big integer 4M
SQL> alter system set MEMORY_MAX_TARGET=4G scope=spfile;
System altered.
SQL> shutdown
SQL> startup
SQL> select bytes/1024/1024 bytes_Mb from v$sgainfo where name like 'Granule Size';
BYTES_MB
----------
16
SQL> show parameter java_pool_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
java_pool_size big integer 16M
