Ask the Expert

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?

Example:
SQL>

    Requires Free Membership to View

show sga

Tot System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
SQL>
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)

This was first published in January 2004

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: