Results of Oracle show sga

Use show sgaYesCheck the size of SGA.,The results of show sga are in the following format:

Total System Global Area AAAAA bytes

Fixed Size BBBBB bytes

Variable Size CCCCC bytes

Database Buffers DDDDD bytes

Redo Buffers EEEEE bytes

ThisProvide a testOn the Oracle instance sga information:

Total System Global Area 369098752 bytes
Fixed Size 1219496 bytes
Variable Size 171967576 bytes
Database Buffers 188743680 bytes
Redo Buffers 7168000 bytes

WhereThe meanings of each part are as follows:

Fixed Size: This is a zone used internally by Oracle,Oracle finds other SGA zones through this zone, Similar to the index of each SGA component, the size of this Part may be different for different platforms and versions.

Variable Size: Includes shared pool, java pool, large pool, memory for managing db_block_buffers, and memory for managing and controlling file information.,Other memory used to manage and control the internal structure of Oracle [this part is usually called Overhead]. The formula is as follows:

Variable Component (Show SGA) = Shared Pool + Large Pool + Java Pool + Overhead + Free Memory (earlier than 9i)

The Overhead size is affected by the following initialization parameters: db_files, open_cursors, and processes. The specific algorithm is unknown.

Database Buffers: It is the total size of db_cache_size, db_keep_cache_size, expires, and db_nk_cache_size. Of course, this is the case where sga_target is 0, that is, in manual SGA management mode, if it is automatic SGA Management (sga ), the value is automatically adjusted based on the sga distribution.

Redo Buffers: This part is the size of the actually allocated Redo log buffer, which is obtained from the initialization parameter log_buffer Based on the smallest allocation unit granule of the SGA.

Here is a testDatabase instance log_buffer parameter size:

SQL>Show parameter log_buffer;

NAME TYPE VALUE
-----------------------------------------------------------------------------
Log_buffer integer7057408

Actual Redo log bufferYou can alsoV $ sgastatView view, and the show sga command is the same result:

SQL>Select * from v $ sgastat where name = 'Log _ buffer ';

POOL NAME BYTES
------------------------------------------------
 Log_buffer7168000

BecauseHereSga is 352 MB <1G, SoGranule is 4 MBYou can also use the following command to query:

SQL>Select * from v $ sgainfo where name = 'granule size ';

NAME BYTES RES
---------------------------------------------
Granule Size4194304No

The Fixed Size in the default SGA will share the granule of an integer multiple with log_buffer:

SQL>Select (1219496 + 7168000)/4194304 from dual;

(1219496 + 7168000)/4194304
-------------------------
1.99973488

Because the accuracy is only in bytes, there may be an error in the ratio.