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 Database Tips by Donald Burleson


 

Changes to Memory Structures  

The major memory structures associated with an Oracle instance are System Global Area (SGA) and Program Global Area (PGA). SGA is shared by all server and background processes, while PGA is exclusive to the server and background processes.

System Global Area (SGA)

System Global Area (SGA) is a group of shared memory structures that contain data and control information for one Oracle database instance. When multiple users are connected to the same instance, the data in the SGA is shared by all users. This is why it is called the Shared Global Area.

 

An Oracle instance is made of the SGA and Oracle processes. Oracle allocates memory for the SGA when the database instance is started and returns the memory when the instance is shut down. The maximum size of the SGA is determined by sga_max_sizeinitialization parameter in the initInstanceName.orafile or server parameter (SPFILE) file.

 

 

Figure 2.1 Oracle Database 10g Memory Structures

 

The SGA contains the following data structures:

Database Buffer Cache- The Database Buffer Cache is the portion of the SGA that holds copies of data blocks read from data files. All concurrent user processes share access to the database buffer cache. The size of the database buffer cache is set by the initialization parameter file db_block_size. Usual values are from 2K to 32K. This standard block size is used by the SYSTEM tablespace.

Redo Log Buffer - Redo Log Buffer is a circular buffer in the SGA that holds information relating to changes made in the database in the form of redo entries. The background process, LGWR , writes the redo log buffer to the active redo log file or group on disk.

Shared Pool - Shared Pool contains the library cache, the dictionary cache, buffers for parallel execution and control structures. It is sized by shared_pool_sizeparameter.

Java Pool  - Java Pool is used in memory for all session-specific java code and data within the JVM (Java Virtual Machine). The Java pool advisor provides information on how the size of the Java pool can affect the parse rate.

Large Pool (Optional) - Large Pool is a large optional memory area used to provide memory allocations for memory requests larger than the size of shared pool.

Data Dictionary Cache- Data Dictionary Cache, also known as row cache is a special location in memory to hold data dictionary data. The Data dictionary is a collection of database tables and views containing reference information about the database, its structures, and its users.

Streams pool - Streams pool controls the Streams memory.

Database Buffer Cache - Oracle supports multiple block sizes in a database. The sizes and numbers of non-standard block size buffers are specified by the following parameters.

   db_2k_cache_size

   db_4k_cache_size

   db_8k_cache_size

   db_16k_cache_size , and

   db_32k_cache_size.

If the cache is large, a request for data is more likely to find the information, resulting in a cache hit.

Other information - The SGAcontains general information about the database and the instance, which is accessed by the background processes. This part is called fixed SGA.

All SGA components allocate and deallocate memory in units called granules. The granule size is determined by the total SGA size and the operating system. It is 4 MB if the total SGA is less than 1 GB and 16MB if the SGA is over 1 GB. For 32bit Windows, the granule size is 8MB for a SGA larger than 1 GB.

 

The size of the SGA is determined by several initialization parameters, of which the following are of higher relevance.

        db_cache_size- size of the cache of standard blocks

        log_buffer- number of bytes allocated for the redo log buffer

        shared_pool_size- size in bytes of the area for shared SQL and PL/SQL

        large_pool_size- size of the large pool (default = 0)

        java_pool_size- size of the Java Pool.

        db_nk_cache_size- size of non-default block size cache

In previous database versions, the DBA had to manually specify different SGA component sizes by setting the above parameters. Oracle Database 10g has the Automatic Shared Memory Management(ASMM) feature to simplify this memory management process. ASMM methods will be explored in a later chapter.


Program Global Area

Program Global Area (PGA) is a memory area that contains data and control information for a server process. Access to the PGA is exclusive to server processes and software code acting on its behalf.

 

The contents of PGA memory vary depending on how the instance is running, whether the shared server or dedicated server option is in effect. But generally, the PGA memory can be classified into the following areas: Private SQL Area, Cursors and SQL Areas, and Session Memory

Private SQL Area- A Private SQL Area has data on bind information and runtime memory structures. Every session or user that issues an SQL statement has a private SQL area. Many private SQL areas can be associated with the same shared SQL area. The private SQL area of a cursor is divided into the persistent area, which is freed only when the cursor is closed, and the run-time area, which is freed when the execution is terminated.
The actual location of a private SQL area depends on the session's connection. For a session connected through a dedicated server, private SQL areas are located in the server process's PGA. If a session is connected through a shared server, part of the private SQL area is kept in the SGA.
Cursors - A cursor is a handle or name for a private SQL area, which is used as a named resource throughout the execution of the program. The number of private SQL areas that a user process can allocate is limited by open_cursorsparameter. The default value is 50.
Session memory - Session memory is the memory allocated to hold a session's variables and other information related to the session. For a shared server, the session memory is shared and not private.

This section has reviewed the Oracle memory structures, namely the System Global Area (SGA) and the Program Global Area (PGA) and introduced some of the new features that affect them in Oracle 10g. The next section will introduce some of the new features in Oracle 10g.


This is an excerpt from the book Easy Oracle Automation.  You can get it for more than 30% by buying it directly from the publisher and get instant access to scripts from the code depot:

Easy Oracle Automation
Oracle10g Automatic Storage, Memory and Diagnostic Features

Dr. Arun Kumar R. 

Buy it now for 30% off - Only $19.95
 

 

 

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

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.