Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

Oracle Concepts - SGA System Global Area

Oracle Tips by Burleson Consulting

Oracle SGA Concepts

The System Global Area (SGA) is a group of shared memory areas that are dedicated to an Oracle ?instance? (an instance is your database programs and RAM).

All Oracle processes use the SGA to hold information. The SGA is used to store incoming data (the data buffers as defined by the db_cache_size parameter), and internal control information that is needed by the database. You control the amount of memory to be allocated to the SGA by setting some of the Oracle ?initialization parameters?.  These might include db_cache_size, shared_pool_size and log_buffer.

In Oracle Database 10g you only need to define two parameters (sga_target and sga_max_size) to configure your SGA. If these parameters are configured, Oracle will calculate how much memory to allocate to the different areas of the SGA using a feature called Automatic Memory Management (AMM). As you gain experience you may want to manually allocate memory to each individual area of the SGA with the initialization parameters.

We have already noted that the SGA was sub-divided into several memory structures that each have different missions. The main areas contained in the SGA that you will be initially interested in have complicated names, but are actually quite simple:

* The buffer cache (db_cache_size)

* The shared pool (shared_pool_size)

* The redo log buffer (log_buffer)

Let?s look at these memory areas in more detail.

Note:  AMM and dynamic Oracle memory management has measurable overhead.  See my important notes on Oracle dynamic memory management.  Also see my  notes on the Oracle User Global Area (UGA).

 

Inside the Data Buffer Cache

The Buffer Cache (also called the database buffer cache) is where Oracle stores data blocks.  With a few exceptions, any data coming in or going out of the database will pass through the buffer cache.

The total space in the Database Buffer Cache is sub-divided by Oracle into units of storage called ?blocks?. Blocks are the smallest unit of storage in Oracle and you control the data file blocksize when you allocate your database files.

An Oracle block is different from a disk block.  An Oracle block is a logical construct -- a creation of Oracle, rather than the internal block size of the operating system. In other words, you provide Oracle with a big whiteboard, and Oracle takes pens and draws a bunch of boxes on the board that are all the same size. The whiteboard is the memory, and the boxes that Oracle creates are individual blocks in the memory. 

Each block inside a file is determined by your db_block_size parameter and the size of your ?default? blocks are defined when the database is created. You control the default database block size, and you can also define tablespaces with different block sizes.  For example, many Oracle professionals place indexes in a 32k block size and leave the data files in a 16k block size.

Google: ?oracle multiple blocksizes?

When Oracle receives a request to retrieve data, it will first check the internal memory structures to see if the data is already in the buffer. This practice allows to server to avoid unnecessary I/O. In an ideal world, DBAs would be able to create one buffer for each database page, thereby ensuring that Oracle Server would read each block only once.

The db_cache_size and shared_pool_size parameters define most of the size of the in-memory region that Oracle consumes on startup and determine the amount of storage available to cache data blocks, SQL, and stored procedures.

Google: ?oracle sga size?

The default size for the buffer pool (64k) is too small. We suggest you set this to a value of 1m when you configure Oracle.  

This is an excerpt from the bestselling "Easy Oracle Jumpstart" by Robert Freeman and Steve Karam (Oracle ACE and Oracle Certified Master).  It?s only $19.95 when you buy it directly from the publisher here.

If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2016

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.