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;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.