This module reviewed the Oracle instance memory structures and system processes as well as the Oracle database data file structure.
This module was not intended to be an exhaustive overview, this material refreshes concepts that you should already know.
Now that you completed this module, you should be comfortable with the following:
- Oracle instance overview
- Oracle memory structures
- Oracle processes
- How LGWR, ARCH, DBWR, and CKPT work
- Oracle database file structures
The information presented in this module will be expanded upon in the next several modules with a focus on backup and recovery.
The next module examines recovery file and data structures. Before moving on, take a few minutes to complete the following tasks.
Oracle Database creates and uses memory structures for purposes such as memory for program code, data shared among users, and private data areas for each connected user. The following memory structures are associated with an instance:
- System Global Area (SGA): The SGA is a group of shared memory structures that contain data and control information for one database instance. Examples of SGA components include cached data blocks and shared SQL areas.
- Program Global Areas (PGA): A PGA is a memory region that contain data and control information for a server or background process. Access to the PGA is exclusive to the process. Each server process and background process has its own PGA.
A
log switch is the event during which LGWR stops writing to the current redo log group and starts writing to another. In Figure 7-1, the arrows between Group 1 and 2, 2 and 3, 3 and 4, and 4 back to 1 illustrate log switches. At every log switch,
Oracle will assign a unique log sequence number to the new group. This lo sequence is assigned to a member in the log group to identify them and also in the control file and the header of all data files. The log sequence number is used by Oracle during recovery. If the log sequence number in the header of a data file is not the same as the control file, recovery is required.
The frequency of automatic log switches depends on three factors, the size of the online redo log group, the volume of transactions, and the type of transactions being performed. LGWR writes out all the information contained in the redo log buffer, for a given transaction, when a commit occurs. Inserts and deletes are typically larger than updates. This is because the redo log buffer stores the before and after image of a transaction. For inserts, the before image is simply a rowid while the after image is the entire row. The inverse is true for deletes. The before image is
the entire row while the after image is a rowid. For updates, Oracle only needs to store the rowid, the column or columns that are changing, and the before and after
data values.
Controlling the frequency of automatic log switches is one of the DBA's most importanttasks. This is because at every log switch, a checkpoint occurs. Checkpoints are resource intensive events, generally resulting in minor pauses in database activity while the checkpoint completes. The smaller the online redo log files relative to the transaction volume and the nature of the transactions, the more frequent are the log switches and checkpoints.