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:
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!