Problem solve Get help with specific problems with your technologies, process and projects.

Fixed vs. variable size

I recently had a discussion about what we are seeing when we issue the SHOW SGA command from SQLPLUS. Could you clarify each item and explain the difference between fixed and variable size?

SQL> show sga

Tot System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
The total size of the SGA is based on many configuration parameters. You can only approximate the SGA size and it'll vary from release to release.

The fixed size component is out of your control. It varies from release to release and platform to platform. It contains a collection of objects that point to other areas inside the rest of the SGA -- think of it as the "roadmap" to the rest of the SGA. It also contains specific variables such as the init.ora parameter db_block_size and so on (constants basically for the currently mounted database).

The variable-sized component of the SGA, as its name implies, contains data structures that are variable sizes (eg: things like "sessions," processes" and "db_files" will affect this marginally -- things like "shared_pool_size" will have large affects on this). The variable size of the SGA is the sum of the sizes of all of these "variably" sized structures (but not block buffers, they are reported separately).

Example of output:

Total System Global Area: 4504072 bytes <-- total size loaded into memory
Fixed Size: 37704 bytes (release specific; varies; out of your control)
Variable Size: 4048576 bytes (control structures for the SGA itself)
Database Buffers: 409600 bytes ('db_block_buffers')
Redo Buffers: 8192 bytes ('log_buffers' or the default minimum for your platform)

Dig Deeper on Oracle DBA jobs, training and certification