Loading

Oracle SGA – System Global Area

The System Global Area (SGA) is a group of shared memory structures, known as SGA components, that contain data and control information for one Oracle Database instance. The SGA is shared by all server and background processes. Examples of data stored in the SGA include cached data blocks and shared SQL areas. Read more

Checking SGA size of your database:-

SELECT * FROM v$sgainfo;

NAME |BYTES |RESIZEABLE|
----------------------------------|----------|----------|
Fixed SGA Size | 2233344|No |
Redo Buffers | 5541888|No |
Buffer Cache Size | 444596224|Yes |
Shared Pool Size | 176160768|Yes |
Large Pool Size | 4194304|Yes |
Java Pool Size | 4194304|Yes |
Streams Pool Size | 0|Yes |
Shared IO Pool Size | 0|Yes |
Granule Size | 4194304|No |
Maximum SGA Size |1068937216|No |
Startup overhead in Shared Pool | 75497472|No |
Free SGA Memory Available | 432013312| |

As you see above, I have 1 GB of SGA in my local DB. Enterprise databases having around 1 TB of SGA is not unusual (to have more buffer cache to reduce the disk reads).

Also, you can see that I have around 424 MB buffer cache and 412 MB free SGA. Let me try to add the free SGA to Buffer cache.

SQL> show parameter cache_size;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_size big integer 0
db_16k_cache_size big integer 0
db_2k_cache_size big integer 0
db_32k_cache_size big integer 0
db_4k_cache_size big integer 0
db_8k_cache_size big integer 0
db_cache_size big integer 0
db_flash_cache_size big integer 0
db_keep_cache_size big integer 0
db_recycle_cache_size big integer 0

SQL> alter system set db_cache_size = 410M scope=both;

System altered.

SQL> show parameter cache_size;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_size big integer 0
db_16k_cache_size big integer 0
db_2k_cache_size big integer 0
db_32k_cache_size big integer 0
db_4k_cache_size big integer 0
db_8k_cache_size big integer 0
db_cache_size big integer 412M
db_flash_cache_size big integer 0
db_keep_cache_size big integer 0
db_recycle_cache_size big integer 0

Query to check SGA target advise:-
select * from v$sga_target_advice order by sga_size;

Related Posts




blog comments powered by Disqus

There's 0 Comment So Far

Share your thoughts, leave a comment!