This blog has moved here.

Thursday, August 02, 2007

Automatic Shared Memory Management

Well, it's been a while since the last post... However, I'm back and I am going to dig a little bit on the “Automatic Shared Memory Management” 10g feature.

As you already (probably) know, every Oracle instance has a big shared buffer called SGA (System Global Area). This big memory buffer is further divided in several slices, each of them being used for special kind of memory allocations. Till Oracle 9i, the only possibility to adjust their sizes were to manually set a couple of initialization parameters like: DB_CACHE_SIZE, SHARED_POOL_SIZE, JAVA_POOL_SIZE etc. However, in 10g some buffers from the SGA were endowed with a nice capability to resize themselves according to the current workload and other parameters.

The figure below depicts a regular section through the SGA.

As you can see the maximum size upon to which the whole SGA may extend is given by the SGA_MAX_SIZE parameter. However, the SGA_TARGET parameter is the one which actually set the amount of memory to be used by all SGA buffers.
The correlation between the values of the SGA_MAX_SIZE and the SGA_TARGET parameters is important mostly when you query the V$SGA_DYNAMIC_FREE_MEMORY.
Lets have a look onto the following output:

SQL> show parameter sga_target;

------------------------------------ -------------------------------- ------------------------------
sga_target big integer 1504M

SQL> select current_size/1024/1024 size_M from v$sga_dynamic_free_memory;


SQL> show parameter sga_max_size

------------------------------------ -------------------------------- ------------------------------
sga_max_size big integer 2G

At the very first sight you may think that from the SGA_TARGET which is 1504M, about 544M are free which, of course, is not true because the Oracle server will always try to expand SGA buffers so that the whole SGA_TARGET amount to be allocated. The free memory reported by the V$SGA_DYNAMIC_FREE_MEMORY view is the difference between the SGA_MAX_SIZE and the SGA_TARGET, which should be read as: “I still have 544M free from the whole SGA_MAX_SIZE which I can use for further expansions of the SGA_TARGET”.

A nice view which I also like to query when I want to find out a brief summary of the SGA composition is the V$SGAINFO.

SQL> select * from v$sgainfo;

----------------------------------- ---------- ---
Fixed SGA Size 2074152 No
Redo Buffers 14700544 No
Buffer Cache Size 671088640 Yes
Shared Pool Size 838860800 Yes
Large Pool Size 16777216 Yes
Java Pool Size 16777216 Yes
Streams Pool Size 16777216 Yes
Granule Size 16777216 No
Maximum SGA Size 2147483648 No
Startup overhead in Shared Pool 100663296 No
Free SGA Memory Available 570425344

11 rows selected.

Please notice that I can also look at the size of the so-called “granules”, which are the allocation units for the SGA memory. The size of one granule varies according to the whole size of the SGA, operation system and platform. You can also find the size of the granule by querying the V$SGA_DYNAMIC_COMPONENTS.

SQL> select granule_size from v$sga_dynamic_components where rownum <= 1;


Another important thing to remember is that if you set values for the parameters which controls the size of the dynamic buffers into the SGA then, those values will be taken as minimum thresholds which means that Oracle server will try to adjust the size of the buffers when necessary but will never decrease them under the manually set value.
Of course, there is also the possibility to disable the “Automatic Shared Memory Management” feature by simply setting the SGA_TARGET value to zero. Likewise, this feature will also be disabled if the STATISTICS_LEVEL parameter is NONE, but this is not recommended at all as it disables other Oracle features too (ADDM, for example).

Well, that's it... not very new information but just a short review.

1 comment:

Anonymous said...

You have really great taste on catch article titles, even when you are not interested in this topic you push to read it