Oracle database server is combination of instance(Memory component) and database(Storage component). Initial set of database files created during database creation. When instance starts SGA gets allocated and background process starts. Database comprises of physical and logical structure.
Shared Global Area - SGA
SGA is a dynamic pool which consists of various memory components. It contains control and data information for oracle database instance. SGA is shared by server process as well as background processes. SGA memory allocation and deallocation is done in terms of granules(contiguous memory) except for redolog buffer. Components of SGA are as follows
- Database Buffer Cache
- Redo Log Buffer
- Shared Pool
- Large Pool
- Java Pool
- Streams Pool
- Fixed SGA
I have posted another article with detailed information on process architecture
Database Buffer cache is the memory component that stores copies of data blocks that are read from data files. A buffer is a memory that temporarily caches a current or recently used data blocks. This component takes care of the following
- Optimize the physical I/O
- Keep frequently used blocks in buffer cache
- Write least frequently used blocks to disk
Buffers in the buffer cache has different states
- Unused :- The buffer is available for use
- Clean :- Buffers that were previously used but now it has read-consistent block
- Dirty : These are modified buffers that are not yet written to the disk
Allocation and de-allocation of buffers work on LRU(Least recently used) basis. Frequently used buffers are called hot buffer and cold buffer are those that are not used recently. Lets say when a query executes if server process can find the required data in buffer cache its called cache hit. If the data is not available in buffer cache its cache miss. Physical read happens when data block is copied from data file to memory.
Buffer cache has different pools
- Default cache :- Defaults location for caching the blocks
- Keep cache :- Frequently used blocks as usually kept here, to avoid aging out of blocks from cache
- Recycle cache :- Infrequently used blocks are kept here, or to phase it out of cache from consuming memory
- Cache to keep non standard blocks (2k, 4k, 16k, 32k blocks) :- This pool keeps blocks that are not standard block size
Redo log buffer is a circular buffer having changed vectors to the database. These changes are made through DML or DDL operations. It is the smallest memory component in SGA. Log writer process writes changed vectors from redolog buffer to online redolog files.
Shared Pool caches various control information such as parsed SQL, PL/SQL code, system parameters, and data dictionary information. Shared pool has different components
- Library cache :- It stores executable SQL and PL/SQL code, along with control structures such as locks and library cache handles.
- Data dictionary cache :- It holds metadata of tables and reference information about the database.
- Result Cache :- This component contains the SQL query result cache and PL/SQL function result cache. In simple words it stores the result of processed SQL statement
- Reserved pool :- is a shared pool component that Oracle Database can use to allocate large contiguous chunks of memory. This pool can be used to Allocate memory from the shared pool which is performed in chunks. It will allow large object(over 5 KB) to be loaded into the cache without needing a single contiguous area.
Large pool is an optional memory area intended for memory allocations that are larger than is appropriate for the shared pool.
Java Pool is used along with JVM component.
Streams Pool is used if Oracle streams is configured or used. This pool is exclusively for the use of Oracle streams.
Fixed SGA is used for internal housekeeping tasks like keeping information about locks etc.
Background Process
Process Monitor - PMON (Mandatory/Instance Critical) :- PMON is a background process that monitors all other background processes Performs process recovery for sessions terminated abnormally. It registers instance information to listener
System Monitor - SMON (Mandatory/Instance Critical) :- SMON is a background process responsible for instance recovery and clean up duties. It recovers transactions that were skipped during instance recovery due to file-read or tablespace offline errors. SMON cleans unused temporary segment from temp (temp tablespace)
Log Writer - LGWR (Mandatory/Instance Critical) :- LGWR is a background process writes changed vectors from buffer cache(redolog buffer) to online redolog files. Write happens from redolog buffer to redolog group in the following conditions
- when transaction is comitted
- when log switch occurs
- every 3 seconds
- 1/3rd of redolog buffer is full
- redolog buffer contains 1MB of data
Database Writer - DBWn 0-9 (Mandatory/Instance Critical) :- DBWR process writes dirty buffers in buffer cache to disk or datafiles. Writing happens in the following conditions
- Clean reusable buffers is not available in cache after checking threshold number of blocks
- To advance the check point, this is the position in redolog file where recovery of the instance starts
Check Point - CKPT (Mandatory/Instance Critical) :- CKPT is a background process which is responsible for
- Updating latest SCN to control file and data file headers
- Writing dirty buffers from buffer cache to disk
Memory Monitor - MMON (New in Oracle 10g) :- MMON collects the statistics required for AWR reports
Memory Monitor Light - MMNL (New in Oracle 10g) :- MMNL writes the statistics collected to the disk and statistics are used for ASH reports as well
Memory Manager - MMAN (New in Oracle 10g) :- MMAN background process manages the dynamic resizing of SGA memory components depending on load for database
Diagnostic Capture Process - DIAG (New in Oracle 11g) :- DIAG process executes oradebug commands and performs diagnostic dumps requested by other processes or termination of instance. Alertlog file and all trace files will be written to a single location
Recoverer - RECO :- Its a background process run on distributed database environment
General Task Execution process - GENo (New in Oracle 11g) :- This process performs required task for DMLs and SQL. GENo process handles the processes that are blocking other processes
Diagnosability Process 0 - DIA0 (New in Oracle 11g) :- DIA0 is responsible for hang detection and deadlock resolution
Virtual Keeper of Time Process - VKTM (New in Oracle 11g) :- VKTM acts as a time publisher for an Oracle instance. VKTM publishes two sets of time: a wall clock time using a seconds interval and a higher resolution time (which is not wall clock time) for interval measurements
Process Spawner - PSPO :- Process Spawner spawns oracle background process after initial startup of the instance. It was introduced in Oracle 10g. This process creates and manages other oracle background processes
Job Queue Coordinator Process - CJQO :- This process selects jobs that need to be run from the data dictionary and spawns job queue slave processes (Jnnn) to run the jobs. JQ0 is automatically started and stopped as needed by Oracle Scheduler. CJQ0 starts only as many job queue processes as required by the number of jobs to run and available resources
Space Management Coordinator - SMCO :-This background process is responsible for coordinating the execution of various space management tasks, including proactive space allocation and space reclamation. SMCO dynamically spawns slave processes (Wnnn) to implement these tasks.
Archiver - ARCH (Optional) :- Archiver is the background process that copies exact copy of the online redolog file to the disk (destination provided in the parameter file log_archive_dest_1). This background process run only when the database is in archivelog mode Click Here - How to enable or Disable archivelog mode
Recovery Writer - RVWR (Optional) (New in Oracle 10g) :- RVWR background process runs if database is in flashback mode. It writes flashback data to the flashback logs in the fast recovery area(FRA). FRA destination parameter db_recovery_file_dest is set in database pfile Click Here - How to enable or disable flashback mode
Change Tracking Writer - CTWR (Optional)(New in Oracle 10g) :- CTWR process writes changed block information from buffer to the change tracking file. Block change tracking is enabled to improve the performance of RMAN incremental backup
Database resource manager - DBRM (Optional) :- Database resource manager takes care of setting plans to users and all other database resource management activities. It controls the use of computing resources
Virtual Scheduler for Resource Manager - VKRM (Optional) :- VKRM process Serves as centralized scheduler for Resource Manager activity.CPU scheduling for all managed Oracle processes is handled by VKRM. The process schedules managed processes in according to an active resource plan.
Logical Structure
Data Blocks :- Its the smallest unit of space allocation in the data files and also minimum unit of I/O for database. Default block size for the database is set in initialization parameter DB_BLOCK_SIZE. The standard block size of the database is 4KB or 8KB, most of the databases use 8K as its default block size. We can create the tablespace with non standard block size like 16KB, 32KB etc
Space allocation in data blocks
Top most section of the block holds block header information, data fills up from bottom up
- PCTUSED : Its the parameter specified at the block level. PCTUSED is the minimum percentage of the block that can be used when new data gets added during insert operation
- PCTFREE : Its the parameter specified to reserve the space in the block for future update
Extents :- It comprises of group of data blocks which is the default unit of space allocation. When a datafile is added and even though no data is inserted, database allocates initial extent for data segment during segment creation. When the initial extent is full, database allocates incremental extent/next extent for this segment.
De-allocation of extents
Extents belonging to the user segment returned only if object is dropped via DROP command. Example :- Lets say, if 1000 rows of table sample_emp are deleted that doesn't claim data blocks to be used by other objects in the tablespace. Drop or truncating table will help reclaim space, another way to do so is defragmenting table
Segments :- It is a group of extents containing all the data for logical storage(table, index etc) within a tablespace. There are different kinds of segments
- User segments
- Temporary segments
- Undo segments
User segment stores the data for one user object in the form of table, index etc. When user inserts first row to a table, oracle database creates segments to that table.
Temporary segment gets allocated during query processing(like sorting operation). Temporary tables and their indexes consumes temporary segments which is session specific.
Undo segment: Oracle maintains record of previous image of data when it gets changed. Record of the previous data stored is undo data. Undo segment gets allocated to consume undo data
Tablespace :- Its the logical space where all segment is contained. Database contains SYSTEM, SYSAUX, UNDO, TEMP and user created tablespaces.
Permanent tablespace or user created tablespace contains user data stored in the form of tables or indexes, usually its application data
System tablespace contains data dictionary information and metadata information for user created objects
Undo tablespace is locally managed tablespace that stores undo data
Temporary tablespace contains session specific temporary data used for sorting operations. It also contains session specific data stored in temporary tables and indexes
Physical Structure
Datafiles :- Data gets stored in the datafiles under the tablespaces. Tablespace has one or more datafiles. User data spans across multiple datafiles
Control file :- Its the most important file which is required for the database to run, which is in binary format. Control file information such as
- Database name
- Database creation time stamp
- Data files, online redo log files, and archived redo log files location
- Latest SCN information
- Tablespace information
- RMAN backups information etc
Redolog File :- This is the file where LGWR writes the database changes from redolog buffer to redolog files
Archivelog File :- Its the exact copy of the redolog file which is used in case of instance recovery
Parameter file :- It contains parameters required for database initialization
No comments:
Post a Comment