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).
The server memory allocated to Oracle includes the following types of data:
User reading and writing activity (adding, modifying, deleting)
SQL and PL/SQL commands
Stored procedures and functions
Information about database objects
Transaction information
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.
Site Administration 
