Oracle Essentials Oracle Database 11g (12 page)

SMON

PMON

RECO

SGA

Redo Log

Database Buffer Cache

Shared Pool

Buffer

DBWR

CKPT

LGWR

ARCH

Datafiles

Control Files

Redo Logs

Figure 2-8. An Oracle instance

Instance Memory and Processes

|

49

Additional background processes may exist when you use certain other features of the database: for example, shared servers (formerly the Multi-Threaded Server or MTS prior to Oracle9
i
), or job queues and replication.

Memory Structures for an Instance

As shown in
Figure 2-8,
the System Global Area is composed of multiple areas.

These include a database buffer cache, a shared pool, and a redo log buffer as shown in the figure, and also possibly a Java pool, a large pool, and a Streams pool. The following sections describe these areas of the SGA. For a more detailed discussion of performance and the SGA, see
“How Oracle Uses the System Global Area”
in

Chapter 7.

Database buffer cache

The database buffer cache holds blocks of data retrieved from the database. This buffer between the users’ requests and the actual datafiles improves the performance of the Oracle database. If a piece of data can be found in the buffer cache (for example, as the result of a recent query), you can retrieve it from memory without the overhead of having to go to disk. Oracle manages the cache using a
least recently used
(LRU) algorithm. If a user requests data that has been recently used, the data is more likely to be in the database buffer cache; data in the cache can be delivered immediately without a disk-read operation being executed.

When a user wants to read a block that is not in the cache, the block must be read and loaded into the cache. When a user makes changes to a block, those changes are made to the block in the cache. At some later time, those changes will be written to the datafile in which the block resides. This avoids making users wait while Oracle writes their changed blocks to disk.

This notion of waiting to perform I/O until absolutely necessary is common throughout Oracle. Disks are the slowest component of a computer system, so the less I/O

performed, the faster the system runs. By deferring noncritical I/O operations instead of performing them immediately, an Oracle database can deliver better performance.

Since Oracle8, the database buffer cache can be configured with buffer pools of the following types:

DEFAULT

The standard Oracle database buffer cache. All objects use this cache unless otherwise indicated.

KEEP

For frequently used objects you wish to cache.

RECYCLE

For objects you’re less likely to access again.

50

|

Chapter 2: Oracle Architecture

Both the KEEP and RECYCLE buffer pools remove their objects from consideration by the LRU algorithm.

Youcan mark a table or index for caching in a specific buffer pool. This helps to keep more desirable objects in the cache and avoids the “churn” of all objects fight-ing for space in one central cache. Of course, to use these features properly you must be aware of the access patterns for the various objects used by your application.

Oracle Database 10
g
simplifed management of buffer cache size by introducing a new dynamic parameter, DB_CACHE_SIZE. This parameter can be used to specify cache memory size and replaced the DB_BLOCK_BUFFERS parameter present in previous Oracle releases. DB_CACHE_SIZE is automatically sized if MEMORY_TARGET or SGA_TARGET is set. Other initialization parameters include DB_KEEP_CACHE_

SIZE and DB_RECYCLE_CACHE_SIZE and these must be manually sized if used.

Shared pool

The shared pool caches various constructs that can be shared among users. For example, SQL queries and query fragments issued by users and results are cached so they can be reused if the same statement is submitted again. PL/SQL functions are also loaded into the shared pool for execution and the functions and results are cached, again using an LRU algorithm. As of Oracle Database 11
g
, a PL/SQL function can be marked in such a way that its result will be cached to allow lookup rather than recalculation when it is called again using the same parameters. The shared pool is also used for caching information from the Oracle data dictionary, which is the metadata that describes the structure and content of the database itself.

Youcan specify a SHARED_POOL_SIZE initialization parameter, or it will be automatically sized if MEMORY_TARGET or SGA_TARGET is specified. Note that prior to Oracle Database 10
g
, “out of memory” errors were possible if the shared pool was undersized, but current Oracle database releases now can leverage automatic shared memory tuning.

Redo log buffer

The redo log buffer caches redo information until it is written to the physical redo log files stored on a disk. This buffer also improves performance. Oracle caches the redo until it can be written to a disk at a more optimal time, which avoids the overhead of constantly writing the redo logs to disk.

Other pools in the SGA

The SGA includes several other pools:

Large pool

Provides memory allocation for various I/O server processes, backup, and recovery, and provides session memory where shared servers and Oracle XA for transaction processing are used.

Instance Memory and Processes

|

51

Java pool

Provides memory allocation for Java objects and Java execution, including data in the Java Virtual Machine in the database.

Streams pool

Provides memory allocation used to buffer Oracle Streams queued messages in the SGA instead of in database tables and provides memory for capture and apply.

Dynamic initialization parameters available for these pools include LARGE_POOL_

SIZE, JAVA_POOL_SIZE, and STREAMS_POOL_SIZE. These are automatically set if MEMORY_TARGET or SGA_TARGET is specified.

Automatic PGA management

Oracle automatically manages the memory allocated to an instance Program Global Area (PGA). The PGA consists of session memory and a private SQL area. The memory amount can be controlled by setting the PGA_AGGREGATE_TARGET initialization parameter. Automatic PGA management, available since Oracle Database 10
g
, greatly simplified management of SQL work areas and eliminated the need to set several different initialization parameters that previously existed. As of Oracle Database 11
g
, PGA memory allocation is automatically tuned along with the SGA memory allocations by setting MEMORY_TARGET.

Background Processes for an Instance

The most common background processes are shown in
Figure 2-8
and vary from Oracle release to release. Among the background processes in Oracle Database 11
g
are the following:

Database Writer (DBWn)

Writes database blocks from the database buffer cache in the SGA to the datafiles on disk. An Oracle instance can have up to 20 DBW processes to handle the I/O load to multiple datafiles—hence the notation DBW
n
. Most instances run one DBW. DBW writes blocks out of the cache for two main reasons:

• If Oracle needs to perform a checkpoint (i.e., to update the blocks of the datafiles so that they “catch up” to the redo logs). Oracle writes the redo for a transaction when it’s committed, and later writes the actual blocks. Periodically, Oracle performs a checkpoint to bring the datafile contents in line with the redo that was written out for the committed transactions.

• If Oracle needs to read blocks requested by users into the cache and there is no free space in the buffer cache. The blocks written out are the least recently used blocks. Writing blocks in this order minimizes the performance impact of losing them from the buffer cache.

52

|

Chapter 2: Oracle Architecture

Log Writer (LGWR)

Writes the redo information from the log buffer in the SGA to all copies of the current redo log file on disk. As transactions proceed, the associated redo information is stored in the redo log buffer in the SGA. When a transaction is committed, Oracle makes the redo information permanent by invoking the Log Writer to write it to disk.

System Monitor (SMON)

Maintains overall health and safety for an Oracle instance. SMON performs crash recovery when the instance is started after a failure and coordinates and performs recovery for a failed instance when youhave more than one instance accessing the same database, as with Real Application Clusters. SMON also cleans up adjacent pieces of free space in the datafiles by merging them into one piece and gets rid of space used for sorting rows when that space is no longer needed.

Process Monitor (PMON)

Watches over the user processes that access the database. If a user process terminates abnormally, PMON is responsible for cleaning up any of the resources left behind (such as memory) and for releasing any locks held by the failed process.

Archiver (ARCn)

Reads the redo log files once Oracle has filled them and writes a copy of the used redo log files to the specified archive log destination(s).

Up to 10 Archiver processes are possible—hence the notation ARC
n
. LGWR

will start additional Archivers as needed, based on the load, up to a limit specified by the initialization parameter LOG_ARCHIVE_MAX_PROCESSES. By default, this initialization parameter has a default value of 2 and is rarely changed.

Checkpoint (CKPT)

Updates datafile headers whenever a checkpoint is performed.

Recover (RECO)

Automatically cleans up failed or suspended distributed transactions.

Dispatcher

Optional background processes used when shared server configurations are deployed.

Global Cache Service (LMS)

Manages resources for Real Application Clusters and interinstance resource control.

Job Queue

Provides a scheduler service used to schedule user PL/SQL statements or procedures in batch.

Instance Memory and Processes

|

53

Queue Monitor (QMNn)

Monitors Oracle Streams message queues with up to 10 monitoring processes supported.

Automatic Storage Management (ASM) processes

RBAL coordinates rebalancing of activities for disk groups. ORB
n
performs the actual rebalancing. ASMB provides communication between the database and the ASM instance.

Processes or Threads?

With all this talk about processes, you may be wondering whether Oracle actually uses threads or processes in the underlying operating system to implement these services.

For simplicity, throughout this book we use the term
process
generically to indicate a function that Oracle performs, such as DBW or LGWR. For Oracle on Windows, each

“Oracle process” is a
thread
within a process. For Oracle on Unix, the “processes” are more commonly actual operating system processes, not threads. Thus, on Unix DBW

and LGWR are specific operating system processes, while on Windows they are threads within a single process.

There are some exceptions, however, and how the database is implemented at this level of detail can be both database version and operating system dependent. In the final analysis, Oracle makes this issue largely unimportant to users and administrators, as database management using Enterprise Manager is consistent regardless of platform.

The Data Dictionary

Each Oracle database includes a set of
metadata
that describes the data structure including table definitions and integrity constraints. The tables and views that hold this metadata are referred to as the Oracle
data dictionary
. All of the components discussed in this chapter have corresponding system tables and views in the data dictionary that fully describe the characteristics of the component. You can query these tables and views using standard SQL statements.
Table 2-1
shows where you can find some of the information available about each of the components in the data dictionary.

The SYSTEM tablespace always contains the data dictionary tables. Data dictionary tables that are preceded by the V$ or GV$ prefixes are dynamic tables, which are continually updated to reflect the current state of the Oracle database. Static data dictionary tables can have a prefix such as DBA_, ALL_, or USER_ to indicate the scope of the objects listed in the view.

54

|

Chapter 2: Oracle Architecture

Table 2-1. Partial list of database components and their related data dictionary views
Component

Data dictionary tables and views

Database

V$DATABASE, V$VERSION, V$INSTANCE

Shared server

V$QUEUE, V$DISPATCHER, V$SHARED_SERVER

Connection pooling

DBA_CPOOL_INFO, V$CPOOL_STAT, V$CPOOL_CC_STATS

Tablespaces

USER_FREE_SPACE, DBA_FREE_SPACE, V$TEMPFILE, DBA_USERS, DBA_TS_QUOTAS

Control files

V$CONTROLFILE, V$PARAMETER, V$CONTROLFILE_RECORD_SECTION

Datafiles

V$DATAFILE, V$DATAFILE_HEADER, DBA_DATA_FILES, DBA_EXTENTS, USER_EXTENTS

Segments

DBA_SEGMENTS, USER_SEGMENTS

Extents

DBA_EXTENTS, USER_EXTENTS

Redo logs

V$THREAD, V$LOG, V$LOGFILE, V$LOG_HISTORY

Undo

V$UNDOSTAT, V$ROLLSTAT, V$TRANSACTION

Archiving status

V$DATABASE, V$LOG, V$ARCHIVED_LOG, V$ARCHIVE_DEST

Database instance

V$INSTANCE, V$PARAMETER, V$SYSTEM_PARAMETER

Memory structure