Introduction

This section will provide a basic understanding of ORACLE including the concepts and terminology of the ORACLE Server. It is important that you read through this section to familiarize yourself with the concepts and terminology to be used throughout this article..

Architect

There are two main components of Oracle database –– instance and database itself. An instance consists of some memory structures and the background processes, whereas a database refers to the disk resources. 


 
The memory structures and background processes constitute an instance. The memory structure itself consists of System Global Area (SGA), Program Global Area (PGA), and an optional area –Software Area Code.
In the other hand, the mandatory background processes are Database Writer (DBWn), Log Writer (LGWR), Checkpoint (CKPT), System Monitor (SMON), and Process Monitor (PMON). And another optional background processes are Archiver (ARCn), Recoverer (RECO), etc.
 


System Global Area

A system global area (SGA) is a group of shared memory structures that is used to store incoming data and control information (Initialization, parameter files) for one Oracle database instance. If multiple users are concurrently connected to the same instance, then the data in the instance's SGA is shared among the users. Consequently, the SGA is sometimes called the shared global area. Oracle automatically allocates memory for an SGA when you start an instance, and the operating system reclaims the memory when you shut down the instance. Each instance has its own SGA. The SGA is read/write. All users connected to a multiple-process database instance can read and write information contained within the instance's SGA.

   • Buffer Cache 
   • Shared Pool 
   • Redo Log Buffer 
   • Large Pool 
   • Java Pool

Buffer Cache

Buffer cache is used to stores the copies of data block that retrieved from data files. That is, when user retrieves data from database, the data will be stored in buffer cache. When a block is read by Oracle, it places this block into the buffer cache, because there is a chance that this block is needed again. Reading a block from the buffer cache is less costly (in terms of time) than reading it from the disk. Its size can be manipulated via DB_CACHE_SIZE parameter.

Different pools within the buffer cache

Keep pool


The keep pool's purpose is to take small objects that should always be cached. Its size can be manipulated via db_keep_cache_size. Also tables can be put into keep pool using following query

alter table table_name storage (buffer_pool keep);

Objects are identified those are small and have frequent full-table scans and which have more than 80% of their data blocks in the buffer.

Recycle pool

It’s designed for those objects that will be accessed once, such as a full table scan on a large object, or objects that have many DML statements that won’t be referenced again by the application. Blocks loaded in Recycle Buffer pool are immediate removed when they are not being used.

Default pool

The DEFAULT buffer pool is really the area of the buffer cache that isn’t being used by the KEEP buffer pool or the RECYCLE buffer pool. All other objects not directed specifically at the KEEP buffer pool or the RECYCLE buffer pool will remain in the DEFAULT buffer pool

Shared Pool

Shared pool is broken mainly into two small part memories 
   
   • Library Cache 
   • Dictionary Cache

Library Cache

The Library Cache is a piece of memory that Oracle uses in order to store SQL Statements. Whenever a Process issues an SQL Statement, the text of the Statement goes into the Library Cache where the statement is parsed an validated. For example if following statement is issued

insert into city (name, abbr) values ('Geneva', 'GE');

Library Cache checks if there is a table named city having the columns name and abbr. As an additional task, the Library Cache also checks if the user's privileges are sufficient to execute the statement. In a similar way, the Library Cache also caches PL/SQL Statements and Objects. The Library Cache was introduced because parsing (and validating and checking privileges) is expensive in terms of CPU cycles. It is managed by a Least Recently Used (LRU) algorithm and also enables the sharing those statements among users. It also contains shared and private Sql areas.

Dictionary Cache

Much of the data dictionary information is kept in the dictionary cache, because Oracle constantly accesses the data dictionary during database operation to validate user access and to verify the state of schema objects. Data Dictionary contains definitions of all schema objects in the database, names of all tables and views in the database, names and datatypes of columns in database tables, privileges and roles of all Oracle users, Integrity constraint information etc. USER_,DBA_,ALL_

Redo Log Buffer

Each Oracle database has a redo log. This redo log records all changes made in datafiles. The redo log makes it possible to replay SQL statements. Before Oracle changes data in a datafile it writes these changes to the redo log. That redo entry is stored in redo log buffer before it is written into the redo log files. If something happens to one of the datafiles, a backed up datafile can be restored and the redo, that was written since, replayed, which brings the datafile to the state it had before it became unavailable. when a transaction commits, the redo log buffer must be flushed out.

Large Pool

Large pool is an optional area of memory in the SGA. It is used to relieves the burden place on the shared pool. Large Pool plays an important role in Oracle Database Tuning since the allocation of the memory for the components otherwise is done from the shared pool. Usage of a Large Pool Area allows the shared pool to primarily cache SQL and avoid the overhead caused by shrinking the SQL Cache Area. The large pool size can be set by LARGE_POOL_SIZE parameter in init.ora initialization parameter file.

Java Pool

As its name, Java pool is used to services parsing of the Java commands. Its size can be set by JAVA_POOL_SIZE parameter in init.ora initialization parameter file.

Program Global Area

The PGA memory which is allocated for each session. The PGA (Program or Process Global Area) is a memory area that stores data and control information for a single process. The PGA is used to process SQL statements and to hold logon and other session information. PGA memory is divided into the following areas: 

   • Session Memory 
   • Private SQL Area

Session Memory

Session memory is the memory allocated to hold a session's variables (logon information) and other information related to the session. For a shared server, the session memory is shared and not private.

Private SQL Area

The private SQL area contains data such as bind variable values, query execution state information (For example, for a full table scan, this area contains information on the progress of the scan), query execution work areas and Sort-based operations like order by, group-by etc. Each session that issues a SQL statement has a private SQL area. Each user that submits the same SQL statement has his or her own private SQL area that uses a single shared SQL area. Thus, many private SQL areas can be associated with the same shared SQL area.

Software Area Code

Software area code is a location in memory where the Oracle application software resides.

Oracle Background Processes

When the Oracle server starts, multiple processes are started on the server (those run in background) to perform various functions as part of the Oracle instance. These processes communicate with various areas of the SGA. Following are the main background processes: • DBWn • LGWR • CKPT • SMON • PMON • ARCn Background processes can be listed with the help of following query.

SELECT NAME, description FROM v$bgprocess ORDER BY NAME, description

Database Writer (DBWn)

New and modified data is stored in buffers in the database buffer cache, which are marked as dirty buffers. The database writer writes modified blocks from the database buffer cache to the files on disk allowing subsequent SELECT statements and other DML statements access to those buffers in the buffer cache. Oracle allows a maximum of 20 database writer processes.

Log Writer (LGWR)

The Log Writer process writes the buffers in the SGA’s redo log buffer out to disk to the redo log files. The Log Writer process must be able to write redo log buffers fast enough to make sure that there is room in the redo log buffer for entries from new transactions. By writing all changes to the database to the redo logfiles, the changes made to the database can be recovered by reissuing the commands in the logs if an instance failure occurs. Log Writer writes under a variety of conditions: when a user issues a COMMIT, when the redo log buffer is one-third full, when DBWn writes dirty buffers, or every three seconds.

Checkpoint (CKPT)

The purpose of a checkpoint is to ensure that all of the buffers in the Database Buffer Cache that were modified prior to a point in time have been written to the data files. This point in time (called the checkpoint position) is where database recovery is to begin in the event of an instance failure. DBWn will already have written all of the buffers in the Database Buffer Cache that were modified prior to that point in time.

System Monitor (SMON)

If there is a system crash, the SMON process will apply the changes in the redo log files (saved to disk previously by the LGWR process) to the datafiles the next time the instance is started. This ensures that no committed transactions are lost because of the system crash.

Process Monitor (PMON)

The background process PMON cleans up after failed/Killed processes by 

   • Cleaning up the cache 
   • Rolling back the user’s current transaction 
   • Releasing all currently held table or row locks 
   • Freeing other resources currently reserved by the user

Archiver(ARCn)

Task of Archiver is to automatically archive online redo logs so as to prevent them from being overwritten. The process of switching from one online redo log file to another is called a log switch. The ARCn process initiates backing up, or archiving, of the filled log group at every log switch or when the log files are full. It automatically archives the online redo log file before the log can be reused, so all of the changes made to the database are preserved.

Database

The database refers to disk resources, and is broken into following two main structures 

   • Logical structures 
   • Physical structures

Logical structures

Oracle database is divided into smaller logical units to manage, store, and retrieve data efficiently. The logical units are 

   • Tablespace 
   • Segment 
   • Extent 
   • Data block



Tablespace

A tablespace is a logical group of data files in a database. A database typically contains at least one tablespace, and usually two or more. Within the database, the tablespace plays a role similar to that of a folder on the hard drive of a computer. One or more datafiles are explicitly created for each tablespace to physically store the data of all logical structures in a tablespace.

Some database programs, such as Oracle, automatically create a tablespace called SYSTEM that contains general information about the structure and contents of the database. A small database can be entirely contained in the SYSTEM tablespace, but in most cases, user data is placed in other tablespaces.

The files in a tablespace usually share a common characteristic. For example, a database for a wholesale distributor might consist of tablespaces entitled ACCOUNTING, ORDERS, SHIPPING, SERVICE, and SYSTEM. Each tablespace might contain only one data file, or thousands of files, or anything in between. Tablespaces can be created, deleted, and merged, just as can the folders on the hard drive of a computer.

Segment

A segment is a set of extents that contains all the data for a specific logical storage structure within a tablespace. For example, for each table, Oracle allocates one or more extents to form that table's data segment, and for each index, Oracle allocates one or more extents to form its index segment. It is further divided into: 

   • Data Segments 
   • Index Segments 
   • Temporary Segments 
   • Rollback Segments

Data Segments

A single data segment in an Oracle database holds all of the data for a table/tables.

Index Segments

Each index has an index segment that stores all of its data.

Rollback segment

A database contains one or more rollback segments to temporarily store "undo" information. This is where other, concurrent sessions read the data when they access the changed tables before the transactions are committed.

Temporary Segment

Oracle often requires temporary workspace for intermediate stages of SQL statement parsing and execution. Oracle automatically allocates this disk space called a temporary segment. Statements those sometimes require the use of a temporary segment are SELECT ... ORDER BY, SELECT DISTINCT ..., SELECT ... GROUP BY, SELECT ... UNION, SELECT ... INTERSECT, SELECT ... MINUS etc.

Extent

An extent is a unit of database storage space allocation made up of a number of contiguous data blocks obtained in a single allocation, used to store a specific type of information. One or more extents in turn make up a segment. When the existing space in a segment is completely used, Oracle allocates a new extent for the segment.

When Extents Are Allocated

When you create a table, Oracle allocates to the table's data segment an initial extent of a specified number of data blocks. Although no rows have been inserted yet, the Oracle data blocks that correspond to the initial extent are reserved for that table's rows.

If the data blocks of a segment's initial extent become full and more space is required to hold new data, Oracle automatically allocates an incremental extent for that segment. An incremental extent is a subsequent extent of the same or greater size than the previously allocated extent in that segment.

Extent

Oracle looks for free space to allocate to a new extent by first determining a candidate datafile in the tablespace and then searching the datafile's bitmap for the required number of adjacent free blocks. If that datafile does not have enough adjacent free space, then Oracle looks in another datafile.

Datablock

Oracle manages the storage space in the datafiles of a database in units called data blocks. A data block is the smallest unit of data used by a database. In contrast, at the physical, operating system level, all data is stored in bytes. Each operating system has a block size. Oracle requests data in multiples of Oracle data blocks, not operating system blocks. The standard block size is specified by the DB_BLOCK_SIZE initialization parameter.



Figure 4 : Header (Common and Variable)

The header contains general block information, such as the block address and the type of segment (for example, data or index).

Table Directory

This portion of the data block contains information about the table having rows in this block.

Row Directory

This portion of the data block contains information about the actual rows in the block (including addresses for each row piece in the row data area).

After the space has been allocated in the row directory of a data block's overhead, this space is not reclaimed when the row is deleted. Therefore, a block that is currently empty but had up to 50 rows at one time continues to have 100 bytes allocated in the header for the row directory. Oracle reuses this space only when new rows are inserted in the block.

Overhead

The data block header, table directory, and row directory are referred to collectively as overhead. Some block overhead is fixed in size; the total block overhead size is variable. On average, the fixed and variable portions of data block overhead total 84 to 107 bytes.

Row Data

This portion of the data block contains table or index data.

Free Space

Free space is allocated for insertion of new rows and for updates to rows that require additional space. Free space can also hold transaction entries. A transaction entry is required in a block for each INSERT, UPDATE, DELETE, and SELECT...FOR UPDATE statement accessing one or more rows in the block. The space required for transaction entries is approximately 23 bytes.

Physical Structures

The physical structures are structures of an Oracle database that are not directly manipulated by users. The physical structure consists of 

   • Datafiles 
   • Control Files 
   • Redo Log Files 
   • Archive Log Files 
   • Parameter Files 
   • Alert and Trace Log Files

Datafiles

The datafiles in a database contain all of the database data that the users of the database save and retrieve. Database datafiles are physical files stored on disk. Each datafile belongs to only one tablespace; a tablespace can have many datafiles associated with it.

Controlfiles

The control file maintains information about the physical structure of the entire database. It stores the name of the database, the names and locations of the tablespaces in the database, the locations of the redo log files, information about the last backup of each tablespace in the database, and much more. Because of the importance of this file, it is recommended that a copy of the control file reside on at least three different physical disks. As with the redo log files, Oracle keeps all copies of the control file in synch automatically.

Redo Log Files

The redo log files facilitate the Oracle mechanism to recover from an instance failure or a media failure. When any changes are made to the database, such as updates to data or creating or dropping database objects, the changes are recorded to the redo log files first. A database has at least two redo log files, and it is recommended that multiple copies of the redo log files be stored on different disks. (Oracle automatically keeps the multiple copies in synch.) If the instance fails, any changed database blocks that were not yet written to the datafiles are retrieved from the redo log files and written to the datafiles when the instance is started again.

Parameter file

The parameter file (sometimes called init.ora) contains configuration information for the database to use at startup time. The parameter file you configure how much RAM the database is going to use, where to find the control files, where to write trace files, and a whole host of other information. In most cases the database will not start without a parameter file.

Alert & Trace Log Files

The alert log file (also referred to as the ALERT.LOG) is a chronological log of messages and errors written out by an Oracle Database. Typical messages found in this file is: database startup, shutdown, log switches, space errors, ALTER SYSTEM commands etc. This file should constantly be monitored to detect unexpected messages and corruptions.

Oracle will automatically create a new alert log file whenever the old one is deleted.

When one of the Oracle background processes (such as dbwr, lgwr, pmon, smon and so on ) encounter an exception, they will write a trace file.

Archive Log Files

When Oracle DBA archive your redo log, you write redo log files to another medium location prior to their being overwritten. This location is called the archive log. Oracle DBA can archive to multiple locations, including a standby database.

These copies of redo log files extend the amount of redo information that can be saved and used for recovery. Archiving can be either enabled or disabled for the database, but Oracle recommends Oracle DBA enable archiving. The status of a log group that is actively being archived is ACTIVE.

References

   • http://www.dba-oracle.com/concepts/shared_pool_concepts.htm 
   • http://www.dbapool.com/articles/021306.html 
   • http://www.adp-gmbh.ch/ora/concepts/cache.html 
   • http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/memory.htm

About This Article

Copyrights 2017, www.expertsupdates.com