Subject: Breakout of space allocation in the SGA Type: FAQ Creation Date: 13-JUL-1999 ----- I am working on this *BUT* it may take a while to finish. If anyone has useful information to add (to cut down on re-discovery), please send me an email (ghayden@uk.oracle.com) ----- What components make up the Total System Global Area ==================================================== Created 13-JUL-99 ----------------- The System Global Area (SGA) is an area of memory set aside for a given instance of an Oracle database. The size of the SGA is determined on a per instance basis and is controlled by the settings of the INIT parameters. During instance startup, Oracle will display the following information, the same information can be viewed while the instance is active by issuing the command SHOW SGA from SVRMGR :- Total System Global Area 19299168 bytes Fixed Size 53216 bytes Variable Size 17083264 bytes Database Buffers 1638400 bytes Redo Buffers 524288 bytes While the above display provides the overall cost of the INIT parameter settings in terms of memory, it does not show memory costs associated with specific INIT parameters etc. Additional information on the makeup of the SGA can be obtained from the following dictionary view. SELECT * FROM SYS.V$SGASTAT; This is a dynamic view comprising both fixed and variable components. Hence memory allocations associated with variable components (by definition) will change a) due to INIT settings and b) due to ongoing database activity. The output depends on the Oracle Server release :- Example output from ORACLE Server Release 7.3.4.3 ================================================= NAME BYTES -------------------------- ---------- DML locks 21280 ENQUEUE STATS 6944 LCCMD SGA Table 24024 PLS non-lib hp 2144 Parameter Table 4432 SEQ S.O. 5376 SYSTEM PARAMETERS 8192 UNDO INFO 5968 calls 6800 character set memory 23184 db_block_buffers 1638400 db_block_hash_buckets 30688 db_files 15256 dictionary cache 182784 distributed_transactions- 4112 enqueue_locks 41568 enqueue_resources 16640 event statistics 43392 fixed allocation callback 384 fixed_sga 53216 free memory 15399880 gc_* 132416 ktlbk state objects 13160 latch nowait fails or sle 10320 library cache 163520 list 4096 log_buffer 524288 log_buffer 16384 log_files 540600 miscellaneous 39936 node map 16384 processes 23600 session param values 26736 sessions 57088 sql area 170696 transaction_branches 5120 transactions 20160 Again, while this provides more information, it does not show the memory costs/allocation associated with each INIT parameter. The notes below have been produced from tests on PORT 89 (Alpha OpenVMS). Later, the equivalent values will be added for UNIX ports. Later again, Oracle 8 and 8i will be included (either here or in similar notes) Note that there are some strange equations active here in calculating the memory costs associated with certain INIT parameters. However, they do give the correct answers. If anyone knows the 'real' equations, please let me know. Also note that the byte values given are for changes to the actual INIT parameter. Any associated memory changes resulting from defaulted INIT parameters are not included. Please review the notes pertaining to the associated INIT parameters to get the additional memory costs. As an example, a change to PROCESSES impacts SESSIONS if SESSIONS is allowed to default. The figures shown for PROCESSES are based on the memory changes associated with a change to PROCESSES parameter alone and do not include any changes associated with the SESSIONS parameter. THIS ARTICLE IS VERY MUCH UNDER EDIT AT THIS TIME. ================================================== Summary ======= INIT Parameter SGA memory cost -------------- ----------------------------------------------- CONTROL_FILES 264 bytes per file DB_BLOCK_LRU_EXTEND_STATISTICS 28 bytes per 'block' DB_BLOCK_LRU_STATISTICS 200 bytes when TRUE DB_BLOCK_BUFFERS 246 bytes PLUS DB_BLOCK_SIZE per buffer DB_FILES 579 bytes per file (Average value) DML_LOCKS 152 bytes per lock DELAYED_LOGGING_BLOCK_CLEANOUTS 13160 bytes when TRUE, 0 when FALSE ENQUEUE_RESOURCES 104 bytes per resource GC_DB_LOCKS 222 bytes per lock (Average value) GC_FREELIST_GROUPS 222 bytes per group (Average value) LOG_FILES 2120 bytes per file PROCESSES 2031 bytes per process (Average value) SESSIONS 3320 bytes per session ############################################################################### INIT Parameter CONTROL_FILES ============================ SGA memory cost = 264 bytes per file ************************************ INIT Parameter dependencies ~~~~~~~~~~~~~~~~~~~~~~~~~~~ None. Internal/hidden dependencies ~~~~~~~~~~~~~~~~~~~~~~~~~~~~ None. SGA components per additional file ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ miscellaneous 264 bytes INIT Parameter DB_BLOCK_LRU_EXTENDED_STATISTICS =============================================== SGA memory cost = 28 bytes per block ************************************ INIT Parameter dependencies ~~~~~~~~~~~~~~~~~~~~~~~~~~~ None. Internal/hidden dependencies ~~~~~~~~~~~~~~~~~~~~~~~~~~~~ None. SGA components per additional block ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ miscellaneous} 28 bytes db_block_lru_extended_sta} Notes: o With small numbers of DB_BLOCK_LRU_EXTENDED_STATISTICS (around 100 or less), the memory costs are associated with the miscellaneous SGA memory component. For larger values of DB_BLOCK_LRU_EXTENDED_STATISTICS, the SGA memory component db_block_lru_extended_sta is used. INIT Parameter INIT Parameter DB_BLOCK_LRU_STATISTICS (Boolean) ===================================================== SGA memory cost = 200 bytes when TRUE ************************************* INIT Parameter dependencies ~~~~~~~~~~~~~~~~~~~~~~~~~~~ None. Internal/hidden dependencies ~~~~~~~~~~~~~~~~~~~~~~~~~~~~ None. SGA components ~~~~~~~~~~~~~~ miscellaneous 200 bytes INIT Parameter DB_BLOCK_BUFFERS =============================== Average SGA memory cost = (DB_BLOCK_SIZE) + 246 bytes ***************************************************** INIT Parameter dependencies ~~~~~~~~~~~~~~~~~~~~~~~~~~~ CACHE_SIZE_THRESHOLD (0.1*DB_BLOCK_BUFFERS - No SGA impact) GC_RELEASABLE_LOCKS (DB_BLOCK_BUFFERS - No SGA impact) Internal/hidden dependencies ~~~~~~~~~~~~~~~~~~~~~~~~~~~~ _db_block_hash_buckets (INTEGER(DB_BLOCK_BUFFERS/4)) _messages (INTEGER(DB_BLOCK_BUFFERS/64)) _small_table_threshold (No SGA impact) SGA components per additional buffer ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ db_block_buffers DB_BLOCK_SIZE bytes db_block_hash_buckest 240 bytes db_block_hash_buckets 4 bytes (average) enqueue_locks} miscellaneous} 2 bytes (average) Notes: o The average 4 bytes db_block_hash_buffers increase results from the _db_block_hash_buckets increase. One _db_block_hash_buckets per 4 db_block_buffers. (See notes below on _DB_BLOCK_HASH_BUCKETS) o The average 2 bytes enqueue_locks/miscellaneous increase results from the _messages increase. One message per 64 db_block_buffers. (See notes below on _MESSAGES) INIT Parameter DB_FILES ======================= Average SGA memory cost = 579 bytes per file ******************************************** INIT Parameter dependencies ~~~~~~~~~~~~~~~~~~~~~~~~~~~ None. Internal/hidden dependencies ~~~~~~~~~~~~~~~~~~~~~~~~~~~~ _ENQUEUE_LOCKS (1*DB_FILES) SGA components per additional file ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ db_files 475 bytes enqueue_locks 96 bytes miscellaneous 8 bytes Notes: o db_files memory is allocated using a repeating pattern of 472, 472, 480, 480, 464, 480, 472 and 480. Hence DB_FILES = 1 will result in 472 bytes, a value of 2 will result in an aditional 472 bytes, 3 results in an additional 480 bytes etc. A total of 3800 bytes are added for every 8 DB_FILES - hence an average of 475 bytes per DB_FILES. INIT Parameter DML_LOCKS ======================== SGA memory cost = 152 bytes per lock ************************************ INIT Parameter dependencies ~~~~~~~~~~~~~~~~~~~~~~~~~~~ ENQUEUE_RESOURCES (DML_LOCKS+20 - see notes later) Internal/hidden dependencies ~~~~~~~~~~~~~~~~~~~~~~~~~~~~ None. SGA components per additional lock ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ dml locks 152 bytes INIT Parameter DELAYED_LOGGING_BLOCK_CLEANOUTS (Boolean) ============================================== SGA memory cost = 13160 bytes when TRUE *************************************** INIT Parameter dependencies ~~~~~~~~~~~~~~~~~~~~~~~~~~~ None. Internal/hidden dependencies ~~~~~~~~~~~~~~~~~~~~~~~~~~~~ None. SGA components ~~~~~~~~~~~~~~ ktlbk state objects 13160 bytes Notes: o This parameter should not be changed from the default of TRUE without advice from Oracle Support Services. INIT Parameter ENQUEUE_RESOURCES ================================ SGA memory cost = 104 bytes per resource **************************************** INIT Parameter dependencies ~~~~~~~~~~~~~~~~~~~~~~~~~~~ None. Internal/hidden dependencies ~~~~~~~~~~~~~~~~~~~~~~~~~~~~ None. SGA components per additional resource ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ enqueue_resources 104 bytes Notes: o ENQUEUE_RESOURCES has a default of the greater of (DML_LOCKS+20) or (((SESSIONS-3)*5)+20) if SESSIONS <11 or (((SESSIONS-10)*2)+55) if SESSIONS >10 INIT Parameter GC_* =================== The description below is applicable to the following GC_ INIT parameters. (ie parameters controlling memory allocation for Parallel Server) INIT Parameter dependencies ~~~~~~~~~~~~~~~~~~~~~~~~~~~ GC_DB_LOCKS None. GC_FREELIST_GROUPS None. GC_SAVE_ROLLBACK_LOCKS None. GC_SEGMENTS GC_FREELIST_GROUPS (5*GC_SEGMENTS) GC_TABLESPACES None. Average SGA memory cost = 222 bytes per GC element ************************************************** Internal/hidden dependencies ~~~~~~~~~~~~~~~~~~~~~~~~~~~~ None. SGA components per additional GC element ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ gc_* 216 bytes (average) miscellaneous} 6 bytes (average) kclnb } Notes: o The additional memory for gc_* is allocated on PRIME NUMBER boundaries at the beginning of each range. Consider the following PRIME NUMBERS :- 3 5 7 11 13 17 19 23 29 31 37 41 43 47 Hence we have a range of numbers between each prime number as follows :- 4->5 = 2 6->7 = 2 8->11 = 4 12->13 = 2 14->17 = 4 18->19 = 2 20->23 = 4 etc. Using GC_DB_LOCKS as an example, when the parameter is set to 14, additional gc_* memory is added to cover the prime number range from 14 to 17 (ie 4*216 bytes = 864 bytes) Subsequent increases from 14 -> 15 -> 16 -> 17 do not result in any additional memory being allocated. When GC_DB_LOCKS reaches 18, an additional 2*216 bytes are added to cover the 18 to 19 range. o (I need to rethink the wording of the following as it is difficult to clearly state the equations in play here!) The additional (miscellaneous/kclnb) memory is added on the basis of 1 byte per additional 36 bytes of gc_* memory. The memory is added on prime number boundaries in a similar way as the memory allocation for the gc_* memory. It is added in chunks of 24 bytes multiplied up by the prime number range. As an example, assume that we are on the prime number range of 18->19 when additional miscellaneous/kclnb memory is to be added. Hence 48 bytes (2*24) are added. Now, as one byte is added for every 36 bytes of gc_* memory, this represents an increase of 48*36 bytes of gc_* memory or an effective increase in one of the above GC parameters of ((48*36)/216) (or 8). Using GC_DB_LOCKS again as an example and assuming that a) we start with a value of 18 and b) that miscellaneous/kclnb memory has just been added, the next increase of miscellaneous/kclnb memory is not due until GC_DB_LOCKS reaches 26. However, the memory associated with a GC_DB_LOCKS value of 28 is actually added when GC_DB_LOCKS starts the prime number range of 24->29. So, when GC_DB_LOCKS reaches 24, additional memory is also added into miscellaneous/kclnb. This time, the memory is added on the basis of the next prime number range of 20->23 (remember that the kclnb memory addition started at the 18->19 range) So, when GC_DB_LOCKS reaches 24, an extra (4*24) or 96 bytes is added to kclnb. Again, this represents a GC_DB_LOCKS increase of (96*36)/216 or 16 GC_DB_LOCKS. Hence the next jump will take place when GC_DB_LOCKS hits the 42->43 prime number range (ie starting at 18, we have added memory for an additional 8 and then 16 GC_DB_LOCKS - hence 42 is the next point at which we add additional memory. This time, we will add on the basis of the next prime number range of 24->29 or (6*48) bytes. And so we go on. INIT Parameter GC_LCK_PROCS =========================== SGA memory cost = 376 bytes per process *************************************** INIT Parameter dependencies ~~~~~~~~~~~~~~~~~~~~~~~~~~~ None. Internal/hidden dependencies ~~~~~~~~~~~~~~~~~~~~~~~~~~~~ _ENQUEUE_LOCKS (1*GC_LCK_PROCS) SGA components per additional process ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ miscellaneous 272 bytes enqueue_locks 96 bytes gc_* 8 bytes INIT Parameter GC_ROLLBACK_LOCKS ================================ These two parameters work together in terms of SGA memory allocation. Average SGA memory cost = (222*GC_ROLLBACK_SEGMENTS) bytes per lock ******************************************************************* INIT Parameter dependencies ~~~~~~~~~~~~~~~~~~~~~~~~~~~ None. Internal/hidden dependencies ~~~~~~~~~~~~~~~~~~~~~~~~~~~~ None. SGA components per additional lock ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ gc_* (216*GC_ROLLBACK_SEGMENTS) bytes miscellaneous} (6*GC_ROLLBACK_SEGMENTS) bytes (average) kclnb } Notes: o The gc_* allocation is derived from *BOTH* GC_ROLLBACK_LOCKS and GC_ROLLBACK_SEGMENTS. The full equation is : 216*GC_ROLLBACK_SEGMENTS*(GC_ROLLBACK_LOCKS+1) Hence, one additional GC_ROLLBACK_LOCK results in an additional (216*GC_ROLLBACK_SEGMENTS) bytes of gc_* SGA memory. Similarly, one additional GC_ROLLBACK_SEGMENT results in an additional (216*(GC_ROLLBACK_LOCKS+1)) bytes of gc_* SGA memory. o The miscellaneous/kclnb increase is based on the same calculations as described for GC_DB_LOCKS above. INIT Parameter GC_ROLLBACK_SEGMENTS =================================== Average SGA memory cost = (222*GC_ROLLBACK_LOCKS+1) + 16 bytes per seg ********************************************************************** INIT Parameter dependencies ~~~~~~~~~~~~~~~~~~~~~~~~~~~ None. Internal/hidden dependencies ~~~~~~~~~~~~~~~~~~~~~~~~~~~~ None. SGA components per additional segment ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ gc_* (216*(GC_ROLLBACK_LOCKS+1)) bytes miscellaneous/kclnb 16 bytes per segment miscellaneous/kclnb (6*(GC_ROLLBACK_LOCKS+1)) bytes (average) Notes: o gc_* allocation - See notes relating to GC_ROLLBACK_LOCKS. o The miscellaneous/kclnb increase of (6*GC_...) is based on the same calculations as described for GC_DB_LOCKS above. INIT parameter LOG_BUFFER ========================= SGA memory cost = LOG_BUFFER + 32*(LOG_BUFFER/1024) bytes ********************************************************* INIT Parameter dependencies ~~~~~~~~~~~~~~~~~~~~~~~~~~~ None. Internal/hidden dependencies ~~~~~~~~~~~~~~~~~~~~~~~~~~~~ None. SGA components ~~~~~~~~~~~~~~ log_buffer (LOG_BUFFER) bytes log_buffer 32*(LOG_BUFFER/1024) bytes Notes: o The setting of LOG_BUFFER is directly reflected as 'Redo Buffers' in the SHOW SGA command. o There is an additional overhead of 32 bytes per 1K of log buffer. The V$SGASTAT view lists two 'log_buffer' rows - one showing the direct LOG_BUFFER setting, the other reflecting the additional 32 bytes per 1K of log buffer. INIT parameter LOG_FILES ======================== SGA memory cost = 2120 bytes per file ************************************** INIT Parameter dependencies ~~~~~~~~~~~~~~~~~~~~~~~~~~~ None. Internal/hidden dependencies ~~~~~~~~~~~~~~~~~~~~~~~~~~~~ None. SGA components per additional file ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ log_files 2120 bytes INIT parameter PROCESSES ======================== Average SGA memory cost = 2031 bytes per process ************************************************ INIT Parameter dependencies ~~~~~~~~~~~~~~~~~~~~~~~~~~~ SESSIONS (1.1*PROCESSES)+5 Internal/hidden dependencies ~~~~~~~~~~~~~~~~~~~~~~~~~~~~ _MESSAGES (2*PROCESSES) _DB_HANDLES (4.2*PROCESSES) SGA components per additional process ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ processes 944 bytes miscellaneous 12 bytes (average) enqueue_locks 192 bytes miscellaneous 144 bytes db_block_hash_buckets 739 bytes (average) Notes: o The average miscellaneous 12 bytes is actually allocated as follows :- Each even numbered process adds 8 bytes of miscellaneous SGA Each odd numbered process adds 16 bytes of miscellaneous SGA (Hence an average of 12 bytes per additional process) o The enqueue_locks increase and 144 bytes miscellaneous increase results from the _MESSAGES increase (see the notes below on the hidden _MESSAGES parameter) o The db_block_hash_buckets increase result from the _DB_HANDLES increase (see notes below) 'Strange equation' time. If _DB_HANDLES is set explicitly then an increase in processes results in the following equation :- If PROCESSES < 28 - no additional SGA memory If PROCESSES = 28 - 176 bytes added to db_block_hash_buckets If PROCESSES > 28 - The increase per process is calculated using the following equation INTEGER(3.375*PROCESSES)*176 Example - increasing from 29 PROCESSES to 30 would result in (INTEGER(3.375*30) - INTEGER(3.375*29)) * 176 ( 101 - 97 ) * 176 = 704 bytes added to db_block_hash_buckets. (Note - always round down integer results) No customers should ever be running their system with _DB_HANDLES defined in INIT.ORA so the above equation should never be needed. INIT parameter SESSIONS ======================= SGA memory cost = 3320 bytes per session **************************************** INIT Parameter dependencies ~~~~~~~~~~~~~~~~~~~~~~~~~~~ ENQUEUE_RESOURCES (((SESSIONS-3)*5)+20) if SESSIONS <11 (((SESSIONS-10)*2)+55) if SESSIONS >10 If < DML_LOCKS+20, then DML_LOCKS+20 TRANSACTIONS (1.1*SESSIONS) TEMPORARY_TABLE_LOCKS (SESSIONS) Internal/hidden dependencies ~~~~~~~~~~~~~~~~~~~~~~~~~~~~ _ENQUEUE_HASH (2 per session if SESSIONS > 10) (5 per session if SESSIONS < 11) _ENQUEUE_LOCKS (10*SESSIONS) SGA components per additional session ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ sessions 1784 bytes SEQ S.O. 168 bytes event statistics} 1360 bytes free memory } 8 bytes Notes: o event statistics and free memory (and other SGA components) combine to give 1368 bytes per additional session. It is difficult to predict which components will be adjusted here. However, the net result is always 1368 bytes. <<< to be continued >>>