Oracle Architectural components

                                                                ORACLE ARCHITECTURE

BASIC ORACLE ARCHITECTURE:

                                                Oracle Server

An Oracle server:

• Is a database management system that provides an open, comprehensive, integrated approach to information management

• Consists of an Oracle Instance and an Oracle database. i.e.,

                oracle server=oracle instance + oracle database

                                    Oracle Instance        

An Oracle Instance:

• Is a means to access an Oracle database

• Always opens one and only one database

• Consists of memory and background process structures

                oracle instance=Memory structures + background process

Establishing a Connection and Creating a Session

Connecting to an Oracle Instance:

• Establishing a user connection

• Creating a session

Connection

A connection is a communication pathway between a user process and an Oracle server. A

database user can connect to an Oracle server in one of three ways:

• The user logs on to the operating system running the Oracle instance and starts an

application or tool that accesses the database on that system. The communication

pathway is established using the interprocess communication mechanisms available on

the host operating system.

• The user starts the application or tool on a local computer and connects over a network

to the computer running the Oracle instance. In this configuration, called client-server,

network software is used to communicate between the user and the Oracle server.

• In a three-tiered connection, the user’s computer communicates over the network to an

application or a network server, which is connected through a network to the machine

running the Oracle instance. For example, the user runs a browser on a computer on a

network to use an application residing on an NT server that retrieves data from an

Oracle database running on a UNIX host.

Sessions

A session is a specific connection of a user to an Oracle server. The session starts when the user is validated by the Oracle server, and it ends when the user logs out or when there is an abnormal termination.

                                                                Memory Structures

1.SGA: The SGA (System Global Area) is an area of memory (RAM) allocated when an Oracle Instance starts up. The SGA's size and function are controlled by initialization (INIT.ORA or SPFILE) parameters.  SGA is shared by all server and background processes.

SGA Components:

• Shared pool: Contains the library cache for storing SQL and PL/SQL parsed code in order to share it among users. It also contains the data dictionary cache, which holds key data dictionary information.

• Database buffer cache: Holds copies of data blocks read from datafiles.

• Redo log buffer: Contains the information necessary to reconstruct changes made to the database

by DML operations. This information is then recorded in the redo logs by the log writer.

• Java pool: Represents the heap space for instantiating your java objects.

• Large pool: Stores large memory allocations, such as RMAN backup buffers.

• Streams pool: Supports the Oracle Streams feature.

FIXED SGA AND VARIABLE SGA:

·         The fixed SGA is a component of SGA that varies in size from platform to platform and release to release. It is compiled in to the database. The fixed SGA contains a set of variables that point to the other components of the SGA and variables that contain the values of various parameters.

·         The variable component of SGA is the sum of the "NAMED" pools----shared pool, java pool, large pool. We call it variable SGA because we can alter the size of each of these components manually using ALTER SYSTEM command. The size of each of the components of variable SGA is determined by INIT.ORA parameters.

• SGA is dynamic

• Sized by the SGA_MAX_SIZE parameter

• Allocated and tracked in granules by SGA components

– Contiguous virtual memory allocation

– Granule size based on total estimated SGA_MAX_SIZE.

however, if the parameter SGA_MAX_SIZE is specified, the total size of all SGA areas must not

exceed the value of SGA_MAX_SIZE. If SGA_MAX_SIZE is not specified, but the parameter SGA_

TARGET is specified, Oracle automatically adjusts the sizes of the SGA components so that the total

amount of memory allocated is equal to SGA_TARGET. SGA_TARGET is a dynamic parameter; it can be changed while the instance is running.

The parameter MEMORY_TARGET, new to Oracle 11g, balances all memory available to Oracle between the SGA and the Program Global Area to optimize performance.

Memory in the SGA is allocated in units of granules. A granule can be either 4MB or 16MB,

depending on the total size of the SGA. If the SGA is less than or equal to 128MB, a granule is

4MB; otherwise, it is 16MB.

Shared Pool:

The Shared Pool environment contains both fixed and variable structures.

·         The fixed structures remain relatively the same size, whereas

·         the variable structures grow and shrink based on user and program requirements.

The actual sizing for the fixed and variable structures is based on an initialization parameter and the work of an Oracle internal algorithm.

• Used to store:

– Most recently executed SQL statements

– Most recently used data definitions

• It consists of two key performance-related memory structures:

– Library Cache

– Data Dictionary Cache

• Sized by the parameter

ALTER SYSTEM SET SHARED_POOL_SIZE = 64M;

LIBRARY CACHE:

Oracle stores all compiled SQL statements in the library cache component of the shared pool. The library cache component of the shared pool memory is shared by all users of the database. Each time you issue a SQL statement, Oracle first checks the library cache to see if there is an already parsed and ready-to-execute form of the statement in there. If there is, Oracle uses the library cache version, reducing the processing time considerably—this is called a soft parse.

If Oracle doesn’t find an execution-ready version of the SQL code in the library cache, the

executable has to be built fresh—this is called a hard parse.

Oracle uses the library cache part of the shared pool memory for storing newly parsed code. If there isn’t enough free memory in the shared pool, Oracle will throw away older code from the shared pool to make room for your new code.

• Stores information about the most recently used SQL and PL/SQL statements

• Enables the sharing of commonly used statements

• Is managed by a least recently used (LRU) algorithm

• Consists of two structures:

– Shared SQL area

– Shared PL/SQL area

• Size determined by the Shared Pool sizing

DATA DICTIONARY CACHE:

The data dictionary cache component of the shared pool primarily contains object definitions, usernames, roles, privileges, and other such information. When you run a segment of SQL code, Oracle first has to ascertain whether you have the privileges to perform the planned operation. It checks the data dictionary cache to see whether the relevant information is there, and if not, Oracle has to read the information from the data dictionary into the data dictionary cache. There is no direct way to adjust the data dictionary cache size. You can only increase or decrease the entire shared pool size.

• A collection of the most recently used definitions in the database

• Includes information about database files, tables, indexes, columns, users, privileges, and other

database objects

• During the parse phase, the server process looks at the data dictionary for information to resolve object names and validate access

• Caching data dictionary information into memory improves response time on queries and DML

• Size determined by the Shared Pool sizing.

DATABASE BUFFER CACHE:

The database buffer cache consists of the memory buffers that Oracle uses to hold the data read by

the server process from datafiles on disk in response to user requests. Buffer cache access is, of

course, much faster than reading the data from disk storage. When the users modify data, those

changes are made in the database buffer cache as well. The buffer cache thus contains both the original blocks read from disk and the changed blocks that have to be written back to disk.

You can group the memory buffers in the database buffer cache into three components:

• Free buffers: These are buffers that do not contain any useful data, and, thus, the database can

reuse them to hold new data it reads from disk.

• Dirty buffers: These contain data that was read from disk and then modified, but hasn’t yet

been written to the datafiles on disk.

• Pinned buffers: These are data buffers that are currently in active use by user sessions.

what happens when sqlstatement is triggered?

When a user process requests data, Oracle will first check whether the data is already available

in the buffer cache. If it is, the server process will read the data from the SGA directly and send it to

the user. If the data isn’t found in the buffer cache, the server process will read the relevant data from the datafiles on disk and cache it in the database buffer cache. Of course, there must be free buffers available in the buffer cache for the data to be read into them. If the server process can’t find a free buffer after searching through a threshold number of buffers, it asks the database writer process to write some of the dirty buffers to disk, thus freeing them up for writing the new data it wants to read into the buffer cache.

Oracle maintains an LRU list of all free, pinned, and dirty buffers in memory. It’s the database writer process’s job to write the dirty buffers back to disk to make sure there are free buffers available in the database buffer cache at all times. To determine which dirty blocks get written to disk, Oracle uses a modified LRU algorithm, which ensures that only the most recently accessed data is retained in the buffer cache. Writing data that isn’t being currently requested to disk enhances the performance of the database.

The larger the buffer cache, the fewer the disk reads and writes needed and the better the performance of the database. Therefore, properly sizing the buffer cache is very important for the proper performance of your database. Of course, simply assigning an extremely large buffer cache can hurt performance, because you may end up taking more memory than necessary and causing paging and swapping on your server.

Database buffer cache is again divided into 3 different types of cache.

1. Default Cache
2. Keep Cache
3. Recycle Cache 

If we define the cache size using DB_CACHE_SIZE (or DB_BLOCK_BUFFER and specify the block size) then this will be default cache. The cache has a limited size, so not all the data on disk can fit in the cache. When the cache is full, subsequent cache misses cause Oracle to write dirty data already in the cache to disk to make room for the new data. You can configure the database buffer cache with separate buffer pools that either keep data in the buffer cache or make the buffers available for new data immediately after using the data blocks.

• The KEEP buffer pool retains the schema object’s data blocks in memory. This is defined using the INIT.ORA parameter DB_KEEP_CACHE_SIZE
• The RECYCLE buffer pool eliminates data blocks from memory as soon as they are no longer needed. This is defined using the INIT.ORA parameter DB_RECYCLE_CACHE_SIZE

considering that there may be tablespaces in the database with block sizes other than the default block size; tablespaces with up to five different block sizes (one block size for the default, and up to four others) require their own buffer cache. As the processing and transactional needs change during the day or during the week, the values of DB_CACHE_SIZE and DB_nK_CACHE_SIZE can be dynamically changed without restarting the instance to enhance performance for a tablespace with a given block size.

You can also define multiple DB block sizes using following parameters. Example if you have defined standard default block size of 4K, then following parameters can be used to define a size of 2K, 8K, 16K and 32K.

You can set size using ALTER SYSTEM SET DB_CACHE_SIZE = 96M;

DB_2K_CACHE_SIZE
DB_8K_CACHE_SIZE
DB_16K_CACHE_SIZE
DB_32K_CACHE_SIZE

Note that you can define the Keep and Recycle cache only on standard block size and buffer cache size is the sum of sizes of each of these pools.

You derive the buffer cache hit ratio as follows:

Hit rate = (1 – (physical reads)/(logical reads)) * 100

REDO LOG BUFFER:

The Redo Log Buffer is a circular buffer that contains changes made to datafile blocks. This information is stored in redo entries. Redo entries contain the information necessary to re-create the data prior to the change made by INSERT, UPDATE, DELETE, CREATE, ALTER, or DROP operations.

• Records all changes made to the database data blocks

• Primary purpose is recovery

• Changes recorded within are called redo entries

• Redo entries contain information to reconstruct or redo changes

• Size defined by LOG_BUFFER

Java pool:

• Services parsing requirements for Java commands

• Required if installing and using Java

• Sized by JAVA_POOL_SIZE parameter

Large pool:

• An optional area of memory in the SGA

• Relieves the burden placed on the Shared Pool

• Used for:

– Session memory (UGA) for the Shared Server

– I/O server processes

– Backup and restore operations or RMAN

– Parallel execution message buffers

– PARALLEL_AUTOMATIC_TUNING set to TRUE

• Does not use an LRU list

• Sized by LARGE_POOL_SIZE

2.PGA: The PGA (Program or Process Global Area) is a memory area (RAM) that stores data and control information for a single process. For example, it typically contains a sort area, hash area, session cursor cache, etc. PGA is private to each server and background process; there is one PGA for each process.

• Memory reserved for each user process connecting to an Oracle database

• Allocated when a process is created

• Deallocated when the process is terminated.

 why PGA is required for every user? The reason being that even though the parse information for SQL or PLSQL may be available in library cache of shared pool, the value upon which the user want to execute the select or update statement cannot be shared. These values are stored in PGA. This is also called Private Global Area.

The size and content of the PGA depends on the Oracle-server options installed. This area consists of the following components:

·         Stack-space: the memory that holds the session's variables, arrays, and so on

·         Session-information: unless using the multithreaded server, the instance stores its session-information in the PGA. (In a multithreaded server, the session-information goes in the SGA.)

·         Private SQL-area: an area which holds information such as bind-variables and runtime-buffers. Each session that executes a SQL statement will have its own private SQL area.

·         Sorting area: an area in the PGA which holds information on sorts, hash-joins, etc.

·         Runtime Area: The runtime area is created for a user session when the session issues a SELECT, INSERT, UPDATE, or DELETE statement. After an INSERT, DELETE, or UPDATE statement is run, or after the output of a SELECT statement is fetched, the runtime area is freed by Oracle.

DBAs can monitor PGA usage via the V$SESSTAT system view.

3.UGA: The UGA (User Global Area) is a memory that is associated with your session, it can be found in the PGA or SGA depending on whether you are connected to the database via shared server or a dedicated server.

·         Shared server - The UGA will be in SGA.

·         Dedicated Server - The UGA will be in PGA.

                                                Process Structures

A process is essentially a connection or thread to the operating system that performs a task or

job. Oracle processes are divided into two general types both for efficiency and to keep client processes separate from the database server’s tasks:

• User processes: These processes are responsible for running the application that connects the

user to the database instance.

• Oracle processes: These processes perform the Oracle server’s tasks, and you can divide them

into two major categories: server processes and background processes. Together, these processes

perform all the actual work of the database, from managing connections to writing to log files

and datafiles to monitoring the user processes.

Interaction Between the User and Oracle Processes

User processes run application programs and Oracle tools, such as SQL*Plus. The user processes

communicate with the server processes through the user interface and request that the Oracle server processes perform work on their behalf. Oracle responds by having its server processes service the user processes’ requests. It’s the job of the server processes to monitor user connections, accept requests for data, and return the results to the users. You’ll examine the two types of Oracle processes—the server processes and the background processes

The Server Process

The server process is the process that services an individual user process. Each user connected

to the database has a separate server process created for the duration of the session. The server

process is created to service the user’s process and is used by the user process to communicate with the Oracle database server.

The number of user processes for each server process depends on the type of server configuration.

You can have three types of server configuration, as explained here:

Dedicated server configuration: The most common configuration for the server process is to

assign each user a dedicated server process. Under the dedicated server process approach, each

user has a one-to-one connection to the database through a dedicated server process.

Shared server configuration: Multiple user processes share a server process. When you use the

shared server architecture, several users connect through a dispatcher and use a shared server

process. When you use a shared server configuration, you can also configure shared server connection pooling. Connection pooling lets you reuse existing timed-out connections to service other active sessions. You can also configure shared server session multiplexing, which combines multiple sessions for transmission over the same network connection.

Database resident connection pooling (DRCP): This connection method, introduced in the

Oracle Database 11g release, is useful for applications that must maintain persistent connection

to the database, which leads to an increased demand on server resources. DRCP lets you

set up pooled dedicated connections across applications and processes. When a client requires

a connection to the database, a connection broker, instead of the dedicated server, will connect

the client to the database. The connection broker is in charge of managing client connections,

by allocating servers from a pool of dedicated servers. The connection broker hooks up the

client connection to a dedicated server, and once the client’s request is fulfilled, the dedicated

server is returned to the pool of available servers.

The Background Processes

The background processes are the real workhorses of the Oracle instance—they enable large numbers of users to concurrently and efficiently use information stored in database files. Oracle creates these processes automatically  when you start an instance

 Maintains and enforces relationships between physical and memory structures

• Mandatory background processes:

– DBWn               PMON                  CKPT

– LGWR               SMON

• Optional background processes:

– ARCn                 LMDn                   RECO

– CJQ0                  LMON                   Snnn

– Dnnn                 Pnnn

– LCKn                                 QMNn

Database Writer (DBWn):

The database writer process is then responsible for writing the “dirty” (modified) data from the memory areas known as database buffers to the actual datafiles on disk.

It is the DBWn process’s job to monitor the use of the database buffer cache, and if the free space in the database buffers is getting low, the database writer process makes room available by writing some of the data in the buffers to the disk files. The database writer process uses the least recently

used (LRU) algorithm (or a modified version of it), which retains data in the memory buffers based

on how long it has been since someone asked for that data.

DBWn writes when:                                                

• Checkpoint occurs

• Dirty buffers reach threshold

• There are no free buffers

• Timeout occurs

• RAC ping request is made

• Tablespace OFFLINE

• Tablespace READ ONLY

• Table DROP or TRUNCATE

• Tablespace BEGIN BACKUP

Oracle provides for the use of multiple database writer processes to share heavy data modification workloads. You can have a maximum of 20 database writer processes (DBW0 through DBW9, and DBWa through DBWj). Oracle recommends using multiple database writer processes, provided you have multiple processors. You can specify the additional database writer processes by using the DB_WRITER_PROCESSES initialization parameter in the SPFILE Oracle configuration file. If you don’t specify this parameter, Oracle allocates the number of database writer processes based on the number of CPUs and processor groups on your server. For example, on my 32-processor HP-UX server, the default is four database writers (one database writer per eight processors), and in another 16-processor server, the default is two database writers.

Log Writer:

The job of the log writer process is to transfer the contents of the redo log buffer to disk. Whenever

you make a change to a database table (whether an insertion, update, or deletion), Oracle writes the

committed and uncommitted changes to a redo log buffer (memory buffer). The LGWR process then

transfers these changes from the redo log buffer to the redo log files on disk. The log writer writes a

commit record to the redo log buffer and writes it to the redo log on disk immediately, whenever a

user commits a transaction.

The log writer writes all redo log buffer entries to the redo logs under the following circumstances:

• At commit

• When one-third full

• When there is 1 MB of redo

• Every three seconds

• Before DBWn writes it ensures that the log writer has already completed writing all redo records for the changed data from the log buffer to the redo logs on disk. This is called the write-ahead protocol.

•the log writer writes a commit record to the redo log following the committing of each transaction.

•The redo log files, as you learned earlier, are vital during the recovery of an Oracle database from a lost or damaged disk.

Checkpoint:

The checkpoint process, or CKPT, helps to reduce the amount of time required for instance recovery. CKPT updates the header of the control file and the datafiles to reflect the last successful SCN (System Change Number). A checkpoint occurs automatically every time a redo log file switch occurs. The checkpoint process is charged with telling the database writer process when to write the dirty data in the memory buffers to disk. After telling the database writer process to write the changed data, the CKPT process updates the datafile headers and the control file to record the checkpoint details, including the time when the checkpoint was performed. The purpose of the checkpoint process is to synchronize the buffer cache information with the information on the database disks.

Each checkpoint record consists of a list of all active transactions and the address of the most recent log record for those transactions.

Responsible for:

• Signaling DBWn at checkpoints

• Updating datafile headers with checkpoint information

• Updating control files with checkpoint information

•  Writing a checkpoint record to the redo log file

• Flushing the contents of the redo log buffers to the redo log files

•  Flushing the contents of the database buffer cache to disk.

Process Monitor [PMON]:

When user processes fail, the process monitor process cleans up after them, ensuring that the database frees up the resources that the dead processes were using. For example, when a user process dies while holding certain table locks, the PMON process releases those locks so other users can use the tables without any interference from the dead process. In addition, the PMON process restarts failed server processes (in a shared server architecture) and dispatcher processes. The PMON process sleeps most of the time, waking up at regular intervals to see if it is needed. Other processes will also wake up the PMON process if necessary.

The PMON process automatically performs dynamic service registration. When you create a

new database instance, the PMON process registers the instance information with the listener, which is the entity that manages requests for database connections. This dynamic service registration eliminates the need to register the new service information in the listener.ora file, which is the configuration file for the listener.

Cleans up after failed processes by:

• Rolling back the transaction

• Releasing locks

• Releasing other resources

• Restarting dead dispatchers

System Monitor[SMON]:

The system monitor process, as its name indicates, performs system-monitoring tasks for the Oracle

instance,

In the case of a system crash or instance failure, due to a power outage or CPU failure, the SMON process performs crash recovery by applying the entries in the online redo log files to the datafiles. In addition, temporary segments in all tablespaces are purged during system restart.

Responsibilities:

• Instance recovery

                – Rolls forward changes in redo logs

                – Opens database for user access

                – Rolls back uncommitted transactions

• Coalesces free space

• Deallocates temporary segments

Archiver:

The archiver process is used when the system is being operated in an archivelog mode—that is, the

changes logged to the redo log files are being saved and not being overwritten by new changes. If you run your database in the no archivelog mode, Oracle will overwrite the redo log files with new redo log records. When you choose to run the instance in an archivelog mode, no such overwriting can take place—each filled log will be saved or archived in a special location.

• Optional background process

• Automatically archives online redo logs when ARCHIVELOG mode is set

• Preserves the record of all changes made to the database.

                                                ORACLE DATABASE

An Oracle database:

• Is a collection of data that is treated as a unit

• Consists of three file types

The general purpose of a database is to store and retrieve related information. An Oracle

database has a logical and a physical structure. The physical structure of the database is the

set of operating system files in the database. An Oracle database consists of three file types.

• Data files containing the actual data in the database

• Online redo log files containing a record of changes made to the database to enable recovery of the data in case of failures

• Control files containing information necessary to maintain and verify database integrity

Other Key File Structures

The Oracle server also uses other files that are not part of the database:

• The parameter file defines the characteristics of an Oracle instance. For example, it contains parameters that size some of the memory structures in the SGA.

• The password file authenticates users privileged to start up and shut down an Oracle instance.

• Archived redo log files are offline copies of the online redo log files that may be necessary to recover from media failures.

Functioning of Oracle Architecture:

Logical Structure

• Dictates how the physical space of a database is used

• Hierarchy consisting of tablespaces, segments, extents, and blocks

The datafiles in an Oracle database are grouped together into one or more tablespaces. Within

each tablespace, the logical database structures, such as tables and indexes, are segments that are

further subdivided into extents and blocks. This logical subdivision of storage allows Oracle to

have more efficient control over disk space usage. Figure shows the relationship between the

logical storage structures in a database.