A common inquiry that I get from sys admins is how much memory is the Oracle database consuming?
There are four pieces in the amount of memory that Oracle uses (called the System Global Area or SGA): fixed size, variable size, database buffers, and redo buffers. You can determine the size of each of these pieces by using svrmgrl, and running the command:
The fixed size is the only parameter that cannot be changed using an init.ora parameter. On Oracle 8.0.x systems, the fixed size is approximately 50K, and around 100K on 8.1.x systems. This section contains general database information about the database (or instance) and the background processes.
The variable size is based on the sum of the shared_pool_size and the large_pool_size on 8.0.x and the sum of the shared_pool_size, large_pool_size and java_pool_size on 8.1.x. This chunk of memory is used for things like the data dictionary cache, sql area, and sort area.
The database buffers section is based on the db_block_buffers parameter multiplied by the block_size. This portion of memory is used for Oracle's caching of data. As data is retrieved from the database, it is stored in memory in the database buffer cache. You can determine the percentage of reads from memory using the following query:
select round(100*(1-(phy.value / (dbg.value + cng.value))),2) buffer_hit from v$sysstat phy, v$sysstat dbg, v$sysstat cng where phy.name = 'physical reads' and dbg.name = 'db block gets' and cng.name = 'consistent gets' ;
The redo buffer section is driven by the init.ora parameter log_buffer (actually it is log_buffer + one db_block). This portion of memory is used to store redo information (changes to the database), before they are written to the redo logs.
About the AuthorJames Giordano is an Oracle database administrator. He has been working with Oracle for about seven years, and also has experience with UNIX and PeopleSoft/Oracle financials.
For More Information
- Feedback: E-mail the editor with your thoughts about this tip.
- More tips: Hundreds of free Oracle tips and scripts.
- Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
- Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
- Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
- Best Web Links: Oracle tips, tutorials, and scripts from around the Web.