ORACLE internal memory structure

ORACLE internal memory structure

ORACLE internal memory structure : There are two different types of memory structures that are used by ORACLE database.

1.Global areas

2.Background Processes

1.Global Areas

Following are the global memory are used by all ORACLE database users

•System global areas (SGA)

•Data block buffer cache.

•Dictionary cache.

•Redo log buffer.

•Shared SQL pool.

•Context areas.

•Programmed global areas (PGA).

System global areas:

SGA facilitates the transfer of the information between the users. It also holds the most commonly requested structural information about the database.

Data block buffer cache:

It is a cache memory in the SGA used to hold the data blocks that are read from the data segments in database, such as tables, indexes and clusters. Typically, the data block buffer cache is about1 to 2% of the size of database.

Dictionary cache:

The information about database objects such as table descriptions, user account data, data file names, segment names, etc. are stored in data dictionary tables are stored in cache memory called Directory Cache.

Redo Log buffer:

ORACLE maintains logs of all transactions against the database. These transitions are recorded in files called Online Redo Log Files. Redo entries describe the changes that are made to the database. Redo entries are written to the Online Redo Log Files, however, transactions are first recorded in the SGA in an area called Redo Log Buffer. The database then periodically writes batches of redo entries to the Online Redo Log Files.
Library cache: It contains information about statements that are run against the database. While the data block buffer and directory cache enable sharing of structural and data information between the users in the database, the library cache allows the sharing of commonly used SQL statements.

Shared SQL pool:

It stores the data dictionary cache and the library cache. The shared SQL pool contains the execution plan and parse tree for SQL statements run against the database.

Context Areas:

Within the shared SQL areas, there are both public and private areas. Every SQL statement issued by the user requires a private SQL area.

Program Global Areas:

PGA is an area in memory that is used by a single ORACLE user process. The memory in PGA is not shareable.

2.Background Process

The relationship between databases’s physical and memory structures are maintained enforced by the background processes. These processes are managed by the database and require little administrative work.

SMON:

SMON is a short from used for system monitor. When database is stored it cleans up the database eliminating transaction objects that are no longer needed by the system. It also performs instance recovery as needed.

PMON:

PMON is a short from used for Process Monitor. This process cleans up behind failed user processes. It frees up the resources that the user was using. This occurs through triggering a lock; PMON is responsible for releasing the lock and making it available to another user. Like SMON, PMON wakes up periodically to check if it is needed.

DBWR:

It is short form used for Data Base Writer. It is responsible for managing contents of the data block buffer cache and dictionary cache. It reads the blocks from the data files and stores them in the SGA, DBWR performs the batch writes of changed blocks back to the data files.

LGWR:

It is a short form used for Log Writer. The LGWR background process manages the writing of the contents of the Redo log buffer to the online redo log files. LGWR write log entries to the online Redo Log files in batches. The Redo Log buffer entries always contain the most up to date status of the database. LGWR is the only process that writes to the online Redo Log files and the only one that directly reads the Redo Log buffer during normal database operation.

CKPT:

It is a short form used for Checkpoints. Checkpoints help to reduce the amount of the time needed to perform instance recovery. Checkpoints cause DBWR to write all the blocks that have been modified since the last checkpoint to the data files and update the data file header and control files to record the checkpoints. Checkpoints occur automatically when an online Redo Log file fills. CKPT is an optional background process.

ARCH:

It is a short form used for Archiving Function. LGWR background process writes to online Redo Log files in a cyclical fashion i.e. after filling the current log files, it begins writing the next. Once the last online Redo Log file is filled, LGWR begins to overwrite the contents of first redo log file.
When ORACLE is run in ARCHIVE LOG mode, the database makes a copy of each Redo Log file before overwriting it. The ARCH background process performs this archiving function.

RECO:

It is a short form used for Recovery. The RECO background process is used to resolve failures in distributed databases. RECO attempts to access database involved.
In-doubt distributed transactions and resolve the transactions.

SNPn:

It is a short form used for Snapshot. ORACLE’S Snapshot refreshes an internal job queue scheduling relay on background processes for their execution. These background processes names start with the letters SNP and end with a number or letter.

LCKn:

It is a short form used for Locking. Multiple LCK processes, named LCK0 through LCK9, are used for inter-instance locking when the ORACLE parallel server option is used.

Dnnn:

It is a short form used for Dispatcher Processes. Dispatcher processes are part of the MTS (multi-threaded server) architecture; they help to minimize resource needs by handling multiple connections.

Snnn:

It is a short form used for server Process. Server Processes are created to manage connections to the database that require a dedicated server. Server process may perform I/O against the data files.

ORACLE database structure

ORACLE database structure can be divided into three categories.

Leave a Reply

Your email address will not be published.