Q

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> 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

Dig deeper on Oracle DBA jobs, training and certification

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close