Lesson 12

This module discussed how Oracle uses Sort Areas, shrinking and expanding them as necessary, depending on how much data there is to sort.
Now you should be able to:

1. Describe the memory structures that make up the SGA
2. Describe how the database buffer cache manages data blocks
3. Describe the purpose of the three types of buffer pools
4. Describe the function of the redo log buffer.
5. Identify the function of the large pool
6. Describe how each session gets its own memory
7. Name the contents of the PGA.
8. Describe how Oracle uses memory for sorting

The following section discusses Oracle memory architecture definitions that were covered for the most significant points in this module.

Objective: Understand, at a relatively high level, how Oracle works. Just as a pilot needs to understand the basics of flight and how an airplane is put together, a database administrator (DBA) of an Oracle database needs to understand what parts make up both 1) the Oracle instance and the database, how these are configured, and how client applications access the database to query and modify data. However, the pilot of an airplane is not the person who has all the answers. Where an aircraft mechanic has more knowledge about the inner workings of the jet engine, for example, the database administrator does not necessarily need to know intimate details about how the different components of Oracle work but does need to understand the interrelationships between them.

Shared pool	Contains the data dictionary cache, the shared SQL area, and shared PL/SQL code
LRU list	Organizes database buffers in order of use
Program Global Area	Contains process specific information
SORT_AREA_SIZE	Is the maximum size allowed for a Sort Area
Large pool	An area of memory from which large allocations can be made
SORT_AREA_RETAINED_SIZE	Is the maximum size to be kept allocated to a Sort Area after a sort has completed
Dirty List	Identifies modified buffers in the database buffer cache
Shared SQL Area	Contains SQL execution plans so they can be used by all processes

This module introduced you to the following terms:

1. background processes
2. dedicated server process:A database configuration in which a server process handles requests for a single client process.
3. default buffer pool: Placing a table into the keep pool merely changes the part of the buffer cache where the blocks are stored. Instead of the blocks being cached in the default buffer pool, they are cached in the keep buffer pool. No separate algorithm is used to control keep pool caching.
4. dirty list: The dirty list points to all the buffers that have been modified and that need to be written back to disk. Sometimes dirty buffers will be found in the LRU List as well, but eventually they too will make it to the dirty list.
5. execution plan
6. keep buffer pool: An alternate buffer pool where by convention you would assign segments that were accessed fairly frequently, but still got aged out of the default buffer pool due to other segments needing space.
7. large pool: Optional area in the SGA that provides large memory allocations for backup and restore operations, I/O server processes, and session memory for the shared server and Oracle XA.
8. Program Global Area: The Program Global Area (PGA) is a private memory region that contains the data and control information for a server process. Only a server process can access the PGA. Oracle Database reads and writes information in the PGA on behalf of the server process.
9. parsing: The process of pulling apart a SQL statement and figuring out how best to execute it.
10. PGA:
11. recycle buffer pool: A pool of buffers in the database buffer cached used for that should be held in memory for the minimum amount of time possible.
12. server process: A process on the server that communicates with a user process, and which processes SQL statements submitted by that user process.
13. Sort Area: SORT_AREA_SIZE specifies (in bytes) the maximum amount of memory Oracle will use for a sort. After the sort is complete, but before the rows are returned, Oracle releases all of the memory allocated for the sort, except the amount specified by the SORT_AREA_RETAINED_SIZE parameter.
14. System Global Area: In the database management systems developed by the Oracle Corporation, the System Global Area (SGA) forms the part of the system memory (RAM) shared by all the processes belonging to a single Oracle database instance. The SGA contains all information necessary for the instance operation.

In the next module, you will prepare to create your first database.