Calculate the approximate size of the SGA (System Global Area)

How to calculate the approximate size of the SGA (System Global Area)

It is very difficult and time-consuming process to find the exactly SGA size based on values of init.ora parameters. It is difficult because of different port specific sizes of data structures that are allocated in the SGA.

It is time consuming because there are so many parameters that influence the SGA size.

For example, any parameter that configures a number of resources, such as PROCESSES and SESSIONS, will have an impact on the SGA size. SGA size information displayed upon startup of the database
. You can display it by using svrmgrl or sqlplus.

Here are few examples to find the SGA in various oracle versions.

In Oracle 8.0.X – svrmgrl connect internal show sga In Oracle 8.1.X – svrmgrl or sqlplus /nolog connect internal show sga In Oracle 9.X – sqlplus connect sys as sysdba show sga What are different sub-divisions of the SGA?

Let’s take an sample output from svrmgrl (SHOW SGA)

Total System Global Area 23460696 bytes

Fixed Size 72536 bytes

Variable Size 22900736 bytes

Database Buffers 409600 bytes

Redo Buffers 77824 bytes

Total System Global Area : – Total in bytes of all the sub-divisions that makes up the SGA.

Fixed Size: Fixed size contains general information about the state of the database and the instance, which the background processes need to access. This does not store user data. Usually this area is less than 100k in size.

Variable Size: This part is influenced by the following init.ora parameters. shared_pool_size large_pool_size java_pool_size Database Buffers:

This holds data blocks copies that are read from datafiles and can be calculated by using following formula. size = db_block_buffers * block size Redo Buffers: This is another buffer in the SGA that holds information about changes made to the database.

Approximating size calculation of the SGA

In 8.0.X :Use the following formula. ((db_block_buffers * block size) + (shared_pool_size + large_pool_size + log_buffers) + 1MB

In 8.1.X : Use the following formula. ((db_block_buffers * block size) + (shared_pool_size + large_pool_size + java_pool_size + log_buffers) + 1MB

In 9.X : To approximate size of the SGA (Shared Global Area), use following formula: db_cache_size + db_keep_cache_size + db_recycle_cache_size + db_nk_cache_size + shared_pool_size + large_pool_size + java_pool_size + log_buffers + 1mb

Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: