What is an Oracle Database Instance ? A Back-To-Basics Guide

02 Apr What is an Oracle Database Instance ? A Back-To-Basics Guide

In simple terms, an Oracle Database Instance is a set of memory structures and background processes that manage the database files. Every active Oracle Database must have at least one associated instance.

When you start an instance, it:

• Allocates memory (called the System Global Area, or SGA)

• Launches multiple background processes that work together to manage the database efficiently

Each instance is uniquely identified by a System Identifier (SID) on a given host.

An instance revolves around three core memory components:

• UGA (User Global Area). – Memory allocated for session variables, it must be available to a database session during the lifetime of the session.
• PGA (Program Global Area). – Memory heap that contains session-dependent variables required by a server process. It is subdivided into different areas
  • Private SQL Area. – Holds information about a parsed SQL statement and other session-specific information for processing
  • SQL Work Area. – Private allocation of PGA memory used for memory-intensive operations.
• SGA (System Global Area). – A group of shared memory structures that contain data and control information for one database instance. The most important components of the SGA are the following:
  • Database Buffer Cache. – Memory area that stores copies of data blocks read from data files. It is divided into different pools:
    • Default pool. – Location where blocks are normally cached.
    • Keep pool. – Intended for blocks that are normally cached.
    • Recycle pool. – Intended for blocks that are infrequently used.
    • Non Standard Block pools. – These are pools for tablespaces that contain nonstandard block sizes.
  • Redo Log Buffer. – It is a circular buffer that caches redo entries describing changes made to the database until it can be written to the online redo log files. Redo entries contain the information necessary to reconstruct, or redo, changes made to the database by DML or DDL operations.
  • Shared pool. – Portion of the SGA that provides critical services for sharing objects among large number of users, it is used to support the execution of shared SQL and PL/SQL packages. This pool is divided into several areas:
    • Library cache. – It stores executable SQL and PL/SQL code.
      • Shared SQL Area. – Contains the hash value and execution plan for a SQL statement.
    • Data Dictionary Cache or Row Cache. – Holds information about database tables and views. The data dictionary cache is also known as the row cache because it holds data as rows instead of buffers, which hold entire data blocks.
    • Reserved Pool. – Memory area that Oracle Database can use to allocate large contiguous chunks of memory.

While this covers the basics, Oracle’s memory architecture is far more extensive and nuanced.

Now we need to define the mandatory processes that are needed for a Database to become an Active Database. You will find these mandatory background processes in every database configuration:

• Process monitor (PMON). – Monitors other background process and performs process recovery. Responsible for cleaning up the buffer cache.
• Listener Registration Process (LREG). – Notifies the listeners about instances, services, handlers and endpoints. In releases before 12c, PMON performed these tasks.
• System Monitor (SMON). – Process in charge of several system-level cleanup tasks
  • Perform Instance Recovery at instance startup (If Necessary)
  • Maintains the undo tablespace
  • Cleans up temporary segments
• Database Writer (DBW). – Writes the contents of the modified database buffers in the buffer cache to the datafiles. There can be up to 100 database writers.
• Log Writer (LGWR). – Responsible for writing the redo entries from one or more redo log buffer strands, which speeds up the performance of each redo write requested. The LGWR writes all redo entries since the last write in all of these situations:
  • A user commits
  • An Online Redo Log switch
  • 3 seconds since the last time it wrote
  • Redo Log Buffer is 1/3 full
  • DBW must write buffers to disk; one thing to keep in mind is that the LGWR must write the redo records before the DBW writes the dirty buffers to disk.
• Checkpoint Process (CKPT). – It updates the control file and data file headers with the checkpoint information that includes checkpoint position and SCN and signals the DBW to write blocks to disk.
• Manageability Monitor (MMON and MMNL). – Performs tasks related to the Automatic Workload Repository.
• Recovery Process (RECO). – In a distributed database, it resolves failures in a distributed transaction.

Each of these processes plays a crucial role in ensuring that the Oracle Database remains reliable, performant, and recoverable.

Whether you’re tuning performance, diagnosing an issue, or capacity planning, knowing what’s going on “under the hood” helps you make better decisions.

Next time you connect to an Oracle Database, remember: you are working with an ecosystem of memory structures and background processes, all orchestrated to manage your data.