All A DBA Needs To Know About Granules

Remember the time when you attended your first Oracle Administration course?
Remember the lesson that talked about granules and what they are?

Neither do I.

Most likely you have a recollection of the word granule, but don’t remember much about it. This is a concept that is not used in our everyday job, however that doesn’t mean you don’t need to know about it. As a mater of fact, I think this subject would make a great interview question!

So, if you’d like a refresher on granules, then this post is definitely for you.

What is a granule?

A granule is a unit of contiguous memory.
The SGA components, such as the shared pool, the buffer cache, the java pool and other pools, are allocated and de-allocated in granules, same size granules.
The size of the granule for your database depends on the platform and the total size of SGA you have allocated.

Exception from these, is the redo log buffer, which is not allocated in granules. The redo log buffer is a circular buffer instead.

When and how is the size of the granule determined?

The size of the granule for your instance, is determined at instance startup and it depends on the size of the SGA:
SGA_MAX_SIZE parameter.
After the instance has started, the granule size will not change.
The granule size could change if you change the SGA_MAX_SIZE and restart the instance.

Based on Oracle’s documentation, these are the rules around the granule size, for most of the platforms.

        SGA_MAX_SIZE <= 1GB   Granule size:  4 MB
  1GB < SGA_MAX_SIZE <= 8GB   Granule size: 16 MB
  8GB < SGA_MAX_SIZE <= 16GB  Granule size: 32 MB
 16GB < SGA_MAX_SIZE <= 32GB  Granule size: 64 MB
 32GB < SGA_MAX_SIZE <= 64GB  Granule size: 128 MB
 64GB < SGA_MAX_SIZE <= 128GB Granule size: 256 MB
128GB < SGA_MAX_SIZE          Granule size: 512 MB

If you set a value for one of the SGA components, that is not a multiple of the granule size, Oracle will round the size up to the nearest multiple.
For example, if the granule size is 16MB, and you size the shared pool to 500MB (shared_pool_size=500MB), then Oracle will round the shared_pool_size to 512MB, as this is a multiple of 16.

If you want proof for the granule size for your instance, you can query either V$SGAINFO or V$SGA_DYNAMIC_COMPONENTS to get the current value.

SQL>select * from V$SGAINFO;

NAME                                 BYTES RESIZEABLE
------------------------------- ---------- ---------------
Fixed SGA Size                     2264856 No
Redo Buffers                      19968000 No
Buffer Cache Size               3254779904 Yes
Shared Pool Size                2348810240 Yes
Large Pool Size                  167772160 Yes
Java Pool Size                   150994944 Yes
Streams Pool Size                318767104 Yes
Shared IO Pool Size                      0 Yes
Granule Size                      16777216 No
Maximum SGA Size                6263357440 No
Startup overhead in Shared Pool  438663064 No
Free SGA Memory Available                0

12 rows selected.

SQL>select component,current_size,granule_size from V$SGA_DYNAMIC_COMPONENTS;

COMPONENT                 CURRENT_SIZE GRANULE_SIZE
------------------------- ------------ ------------
shared pool                 2348810240     16777216
large pool                   167772160     16777216
java pool                    150994944     16777216
streams pool                 318767104     16777216
DEFAULT buffer cache        3254779904     16777216
KEEP buffer cache                    0     16777216
RECYCLE buffer cache                 0     16777216
DEFAULT 2K buffer cache              0     16777216
DEFAULT 4K buffer cache              0     16777216
DEFAULT 8K buffer cache              0     16777216
DEFAULT 16K buffer cache             0     16777216
DEFAULT 32K buffer cache             0     16777216
Shared IO Pool                       0     16777216
ASM Buffer Cache                     0     16777216

If you enjoyed this article, and would like to learn more about databases, please sign up below, and you will receive
The Ultimate 3 Step Guide To Find The Root Cause Of The Slow Running SQL!

--Diana