Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

        
 

 Oracle SGA Components and Locking
Oracle Tips by Burleson Consulting

Oracle 11g Grid & Real Application Clusters by Rampant TechPress is written by four of the top Oracle database experts (Steve Karam, Bryan Jones, Mike Ault and Madhu Tumma).  The following is an excerpt from the book.

The Oracle database is accessed through an instance. The combination of SGA (System Global Area) with one or more Oracle processes constitutes an instance. After the instance is started, the database is associated with it. This process is called database mounting. In the case of a RAC system, the database can be associated with multiple instances. The main purpose of the SGA is to store data in memory for quick access and for processing.

SGA – System Global Area

The instance is the structure or entity with which application users connect. The SGA is a group of shared memory structures that contain data and control information for the database instance. Oracle allocates memory for an SGA system whenever the instance is started. Multiple instances can be associated with a database in a RAC system, and each instance has its own SGA. The SGA contains five main areas:

  • The fixed area

  • The variable area

  • The database buffer cache

  • The log buffer

  • The resource directory for a RAC system

The fixed area of the SGA contains several thousand atomic variables. These are small data structures, such as latches and pointers, which refer to other areas of the SGA. The size of the fixed area is static. It also contains general information about the state of the database and the instance which the background processes need to access.

 

The variable part of the SGA is made up of a large pool and a shared pool. All memory in the large pool is dynamically allocated, whereas the shared pool contains both dynamically managed memory and a permanent memory. The database buffer cache is where database block copies are held for processing. All user processes concurrently connected to the instance share access to the database buffer cache. There are many groups of buffers within the SGA.

Shared Pool and Large Pool

The shared pool segment of the SGA contains three major areas: the library cache, the dictionary cache, and buffers for parallel execution messages.

  • Library Cache - The library cache includes the shared SQL areas, PL/SQL procedures and packages and control structures such as library cache handles, locks, synonym translations, and dependency tracking information. It contains parse trees and execution plans for shareable SQL statements, as well as pseudo code for PL/SQL program units. All users access the shared SQL areas.

  • Dictionary Cache – Includes the usernames, segment information, profile data, tablespace information, and the sequence numbers. The dictionary cache also contains descriptive information or metadata about the schema objects. Oracle uses this metadata when parsing SQL cursors or during the compilation of PL/SQL programs.

    The dictionary cache is also known as the row cache because it holds the data in rows instead of buffers. It also holds entire blocks of data. This helps to reduce physical access to the data dictionary tables from the system tablespace and also enables fine-grained locking of individual data dictionary rows.

The large pool is an optional area. If the large_pool_size parameter is set, then the large pool is configured as a separate heap within a variable area of the SGA. The large pool is not a part of the shared pool.

 

Using the large pool instead of the shared pool decreases fragmentation of the shared pool. Unlike the shared pool, the large pool does not have an LRU list. Oracle does not attempt to age memory out of the large pool. The large pool is useful to allocate large memory allocations for:

  • Session memory for the shared server and the Oracle XA interface that is used where transactions interact with more than one database

  • I/O server processes

  • Oracle backup and restore operations - recovery manager can use the large pool to cache I/O buffers during backup and restore operations

     

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.