An Oracle database server consists of a database and an instance. Although, it’s quite common among the practitioners to use the terms instance and database interchangeably, they are different but related entities. The term database refers to the physical storage of information and instance refers to the memory structures and several background processes running on the server that provides access to the information in the database. |
Every Oracle database server running Oracle database is associated with an Oracle instance. When a database is started on the database server, Oracle allocates a memory area called System Global Area (SGA) and starts one or more Oracle background processes. This combination of memory structures and background processes is called an Oracle Instance. An instance can be part of one and only database, however multiple instances can be part of the same database when using RAC (Real Application Clusters).
|
1.1. System Global Area (SGA) |
Also known as Shared Global Area, is a group of shared memory structures. The SGA is not really one large chunk of memory – it’s made up of various components of memory structures. All the processes of an instance – system processes and user processes share the database and control information stored in the SGA.
|
SGA has five major components - they are:
|
›› Shared Pool [SHARED_POOL_SIZE]
›› Database Buffer Cache [DB_CACHE_SIZE]
›› Log Buffer [LOG_BUFFER]
›› Large Pool [LARGE_POOL_SIZE]
›› Java Pool [JAVA_POOL_SIZE]
|
Alternatively, the above memory structures can automatically and dynamically be sized in 10g or 11g if any one of the following parameters is set. This will allow Oracle distribute the optimal memory among various SGA components.
|
›› Automatic Shared Memory Management (ASSM): 10g [SGA_TARGET]
›› Automatic Memory Management (AMM): 11g [MEMORY_TARGET]
|
Shared Pool is used to store most recently executed SQL statements and most recently used data definitions. Consists of two memory structures:
|
- Stores and shares most recently used SQL and PL/SQL statements
- Stores and shares execution plan and parsed information
- Consists of two sub-caches: Shared SQL & Shared PL/SQL
- Under-sizing this cache can drastically affect the overall database performance
|
- Stores most recently used database definitions
- Stores metadata information about most recently used object information like tables, indexes, users, privileges etc.
- During the parse phase, the server process looks at the Data Dictionary cache for information to resolve object names and validate access
- Under-sizing this cache can drastically affect the overall database performance.
|
1.1.2. Database Buffer Cache
|
When a query is processed, the Oracle server process looks in the Database Buffer Cache for any blocks it needs. If the block is not found in the Database Buffer Cache, then it reads the block from the data file and places a copy in the Database Buffer Cache. Subsequent requests for the same block may not require physical reads thereby improving performance. Managed by LRU algorithm to age out buffers that have not been accessed recently to make room for new blocks.
Consists of independent sub-caches:
|
The redo log buffer caches redo information until it is written to the Online Redo log files. 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 to the redo logs on disk. The primary purpose of caching this redo information is for recovery purpose.
|
Provides memory allocation for various I/O server processes, backup and recovery (RMAN) and also provides session memory where shared servers are configured. It’s used to relieve burden on the Shared Pool in the shared server architecture.
|
Provides memory allocation for Java objects and Java execution, including data in the JVM in the database. This memory is essential for database upgrade, migration and ORA patch updates.
|
1.2. Program Global Area (PGA) |
By contrast, the Program Global Area (PGA), unlike SGA, consists of private session memory and SQL area used to store data and control information for every single server process or background process. PGA is allocated when a process is created and de-allocated when the process is terminated. PGA is generally used to store session specific information and for memory intensive operations such as sorting SQL results and managing special joins called “hash” joins.
|
1.3. Mandatory Background Processes |
Background processes consolidate functions that would otherwise be handled by multiple database programs running for each user process. To maximize performance and accommodate many users, Oracle uses a multiprocessing background processes to provide increased parallelism and reliability.
Following are the mandatory background processes:
|
›› Database Writer (DBWn)
›› Log Writer (LGWr)
›› Database Writer (CKPT)
›› Database Writer (SMON)
›› Process Monitor (PMON)
|
1.3.1. 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 DBWn processes to handle the I/O load to multiple datafiles – hence the notation DBWn. The DB_WRITER_PROCESSES initialization parameter specifies the number of DBWn processes.
DBWn flushes the 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)
- If Oracle needs to read blocks requested by the 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 fashion minimizes the performance impact of losing them from the buffer cache.
|
- When taking TABLESPACES offline
- When dropping or truncating a table
- When placing TABLESPACES in READ ONLY mode
|
Performs sequential writes from the Redo Log Buffer to the online redo log files under one of the following situations:
|
- When transaction commit occurs
- When one-third of Redo Log Buffer is full
- When Redo Log Buffer changes reaches 1 MB
- Before DBWn writes modified blocks to the data files
|
1.3.3. System Monitor (SMON)
|
Maintains overall health and safety of an Instance. SMON performs crash recovery when instance is started after a failure and coordinates and performs recovery for a failed instance.
|
- Rolling forward data that has not been written out in the data files however recorded in the online redo log files
- Recovers all committed transactions
- Rolling back uncommitted transactions
- Defragments database by coalescing free spaces as part of the space maintenance functions
- De-allocates temporary segments
|
1.3.4. 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.
|
When checkpoint occurs, Oracle must update all the headers of the datafiles to record the details of the checkpoint information. A checkpoint keeps the database buffer cache and the database datafiles synchronized.
|
Archiver, though, not a mandatory process but a critical process in a production environment to recover a database after the loss of a disk. As members in one of the online redo log groups are filled, the Oracle server begins writing to the next available group. This process of switching is called a log switch. ARCn initiates archiving at every log switch. ARCn is active only when the RDBMS is operated in ARCHIVELOG mode.
In NOARCHIVELOG mode, the Online Redo Log files are overwritten each time a log switch occurs. However, LGWr doesn’t overwrite an Online Redo Log file group until the CKPT for that group is complete. This ensures that committed data can however be recovered if there is an instance crash.
|
In Oracle, the term database refers to the physical storage of information. However, Oracle database architecture has a physical and logical structure that make up the database.
|
The physical structure of the database consists of three types of operating system files, also known as database files – the actual storage for database information.
|
- One or more Data files – you can have as few as one data file or as many as hundreds of data files.
- The number of data files that can be configured is limited by the Oracle parameter MAXDATAFILES
- Two or more Redo log files – Redo log files hold information used for recovery in the event of a system failure. This information is used in the event of a system failure to reapply changes that have been made and committed but that might not have been made to the data files. The number of Online Redo Log files that can be configured is limited by the Oracle parameter MAXLOGFILES
- One or more Control files – Control files contain information used to start an Instance, such as the location of data files and redo log files and also key information about the contents and state of the database including checkpoint information, log history, current log sequence information, archive log information and RMAN backup metadata.
|
Logical structures include Tablespace, Segment, Extents and Blocks. Since, it is logical, it’s visible only inside the Oracle database server.
|
- Tablespace: All of the data stored within an Oracle database must reside in a tablespace. Tablespace is a logical structure; hence we cannot look at it at the operating system level. Each tablespace is composed of one or more physical structure called datafiles.
- Segment: The database schema (collection of user objects) consists of segments like tables, indexes, views, clusters, stored procedures, triggers and so on. A segment is a set of extents and can span across multiple datafiles.
- Extents: Each extent is a logical composition of multiple data blocks. Extents cannot span across multiple datafiles.
- Blocks: At the finest level of granularity, Oracle Database stores data in data blocks. It should ideally be in multiple of OS blocks.
|
Last modified: Jul 17, 2012 12:30 IST
|
|
No comments:
Post a Comment