Home arrow Site Administration arrow Page 2 - Oracle Database XE: Storage Structures

Oracle Memory Structures - Administration

In this second part of a five-part series on Oracle Database XE administration, you'll learn about physical database structures. This article is excerpted from chapter 28 of the book Beginning PHP and Oracle: From Novice to Professional, written by W. Jason Gilmore and Bob Bryla (Apress; ISBN: 1590597702).

  1. Oracle Database XE: Storage Structures
  2. Oracle Memory Structures
By: Apress Publishing
Rating: starstarstarstarstar / 1
September 30, 2010

print this article



The server memory allocated to Oracle includes the following types of data:

  1. User reading and writing activity (adding, modifying, deleting)
  2. SQL and PL/SQL commands 
  3. Stored procedures and functions 
  4. Information about database objects 
  5. Transaction information 
  6. Oracle program executables

This information is stored in two major memory areas: the System Global Area (SGA) and the Program Global Area (PGA). These areas are shown in Figure 28-3. The Oracle program executables are stored in the Software Code Area (not shown in the diagram).

The overall memory allocated to Oracle falls into two broad categories: shared memory and nonshared memory. The SGA and the Software Code Area are shared among all database users. The PGA is considered nonshared. There is one dedicated PGA allocated for each user connected to the database.

Figure 28-3.  Oracle database memory structures

System Global Area

The SGA is the memory area that all connected database users share. The SGA itself is broken down into many areas. In the following sections we discuss the areas that hold cached data blocks from database tables, recently executed SQL statements, and information on recent structural and data changes in the database. These areas are the database buffer cache, the shared pool, and the redo log buffer, respectively.

Database Buffer Cache

The database buffer cache holds copies of database blocks that have been recently read from or written to the database datafiles. The data cached here primarily includes table and index data, along with data that supports ROLLBACK statements. We cover theROLLBACKstatement and transaction processing in Chapter 32.

Shared Pool

The shared pool contains recently used SQL and PL/SQL statements (stored procedures and functions). It also contains data from system tables (the data dictionary tables), such as character set information, tuning statistics, and security information. Because Oracle frequently caches objects such as PL/SQL stored functions in the shared pool, another user or process that needs the same stored functions can benefit from the performance improvement because the stored function is already in the shared pool.

Redo Log Buffer

The redo log buffer keeps the most recent information regarding changes to the database resulting from SQL statements. The Oracle background processes write these blocks initially to the online redo log files, which can be used to recover, or redo, all recent changes to the database after a failure, as we mentioned earlier.

Program Global Area

The PGA belongs to one user process or connection to the database and is therefore considered nonsharable. It contains information specific to the session, and it can include sort space and information on the state of any SQL or PL/SQL statements that are currently active by the connection.

Software Code Area

The Software Code Area is a shared area containing the Oracle program code (binary executables). Multiple database instances running against the same or different databases can share this code; as a result, it saves a significant amount of memory on the server. Now that youíve heard about the Software Code Area, you will probably never hear about it again; itís a static area of memory that only changes size when you install a new version of Oracle. Itís truly a ďset it and forget itĒ situation. If you meet the overall Oracle memory requirements, the program code uses a relatively insignificant amount of memory compared to the SGA and the PGA.

Please check back next week for the continuation of this article.

>>> More Site Administration Articles          >>> More By Apress Publishing

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Coding: Not Just for Developers
- To Support or Not Support IE?
- Administration: Networking OSX and Win 7
- DotNetNuke Gets Social
- Integrating MailChimp with Joomla: Creating ...
- Integrating MailChimp with Joomla: List Mana...
- Integrating MailChimp with Joomla: Building ...
- Integrating MailChimp with Joomla
- More Top WordPress Plugins for Social Media
- Optimizing Security: SSH Public Key Authenti...
- Patches and Rejects in Software Configuratio...
- Configuring a CVS Server
- Managing Code and Teams for Cross-Platform S...
- Software Configuration Management
- Back Up a Joomla Site with Akeeba Backup

Developer Shed Affiliates


Dev Shed Tutorial Topics: