Question

-Distinguish between:
Data dictionary cache and Library cache
SGA and PGA with suitable diagrams
Database buffer cache and Redo log buffer cache
PMON and SMON

Answer 1

`Hey,

Note: Brother if you have any queries related the answer please do comment. I would be very happy to resolve all your queries.

Library Cache

This part of the Shared Pool memory structure is used for storing the recently executed SQL and PL/SQL statements and hence these statements if encountered again will be shared which will subsequently boost the performance of the database.

This memory structure is managed by a LRU (Least Recently Used) algorithm and this memory structure is also composed of two structures. One, Shared SQL area - for storing the most recently executed SQL statements and Two, Shared PL/SQL area - for storing the most recently executed PL/SQL statements.

Data Dictionary Cache

This part of the Shared Pool memory structure is used for storing the most recently used data definitions in the Oracle DB. These data definitions may include information about: database files, tables, indexes, privileges, users, etc.

Caching these inforamtion in memory improves the performance especially for queries and updates using DML. During the parsing phase, the server process scans this memory structure to resolve the database object names and validate access.

Thus we see that Library Cache is used to store the recently executed SQL and PL/SQL statements, which eliminates the need for parsing and compiling the statements again if used subsequently and hence improves performance. Whereas, the Data Dictionary Cache is used to store the information which improves the validation phase of the execution of those SQL and PL/SQL statements. So, both the memory structures can be visualized as being complimentary to each other.

System Global Area (SGA)

The SGA is a read/write memory area that stores information shared by all database processes and by all users of the database (sometimes it is called theShared Global Area).

This information includes both organizational data and control information used by the Oracle Server.

The SGA is allocated in memory and virtual memory.

The size of the SGA can be established by a DBA by assigning a value to the parameter SGA_MAX_SIZE in the parameter file—this is an optional parameter.

The SGA is allocated when an Oracle instance (database) is started up based on values specified in the initialization parameter file (either PFILE or SPFILE).

The SGA has the following mandatory memory structures:

 Database Buffer Cache

 Redo Log Buffer

 Java Pool

 Streams Pool

 Shared Pool – includes two components:

o Library Cache

o Data Dictionary Cache

 Other structures (for example, lock and latch management, statistical data)

Additional optional memory structures in the SGA include:

 Large Pool

Program Global Area (PGA)

A PGA is a nonshared memory region that contains data and control information exclusively for use by an Oracle process.

A PGA is created by Oracle Database when an Oracle process is started.

One PGA exists for each Server Process and each Background Process. It stores data and control information for a single Server Process or a single Background Process.

It is allocated when a process is created and the memory is scavenged by the operating system

when the process terminates. This is NOT a shared part of memory – one PGA to each process

only.

The collection of individual PGAs is the total instance PGA, or instance PGA.

Database initialization parameters set the size of the instance PGA, not individual PGAs.

The Program Global Area is also termed the Process Global Area (PGA) and is a part of memory

allocated that is outside of the Oracle Instance.

REDO LOG BUFFER CACHE

A log buffer is a circular buffer in the SGA that holds information about changes made to the database. This information is stored in the redo entries. Redo entries contain the information necessary to reconstruct or redo changes made to the database by insert, update, delete, create, alter, or drop operations. Redo entries are primarily used for database recovery as necessary.

The server processes generate redo data into the log buffer as they make changes to the data blocks in the buffer. LGWR subsequently writes entries from the redo log buffer to the online redo log.

Database Buffer Cache

The database buffer cache holds copies of data blocks read from the data files. The term data block is used to describe a block containing table data, index data, clustered data, and so on. Basically, it is a block that contains data. All user processes concurrently connected to the instance share access to the database buffer cache. The database buffer cache is logically segmented into multiple sets. This reduces contention on multiprocessor systems.

This area of the SGA contains only the buffers themselves and not their control structures. For each buffer, there is a corresponding buffer header in the variable area of the SGA.

• Process Monitor (PMON): If a background process fails, the PMON process performs the cleanup operations by performing the following tasks:
  • Rolls back the user’s current transaction
  • Releases all the locks that are held on tables or rows
  • Frees other resources used by the users
  • Restarts the dead dispatcher

  System Monitor (SMON): If an Oracle instance crashes, any changes that are made in the SGA are not written to the data files. When you restart the instance, the SMON background process automatically performs instance recovery by performing the following tasks:
  • Rolling forward changes that are made in the online redo log files but not in the data files. Since all the committed transactions are written to the online redo log files, these are successfully recovered as result of rolling forward changes from the online redo log files to the data files.
  • Opening the database. After the database is opened, users can log on and access any data that is not locked by un-recovered transaction.
  • Rolling back all the uncommitted transactions.

Kindly revert for any queries

Thanks.