Transient Memory Structures of Relational Database Server
Outline
Oracle system architecture
Oracle system architecture
Transient Memory Structures of Relational Database Server
Outline
Data buffer caches
- DEFAULT pool contains miscellaneous data blocks
- KEEP pool contains frequently accessed data blocks
- KEEP pool retains data blocks
- RECYCLE pool contains data blocks from full table scans
- RECYCLE pool eliminates data blocks as soon as they are no longer needed
- nK pools contain data blocks from nK tablespaces
Transient Memory Structures of Relational Database Server
Outline
Redo log buffer
- Redo log buffer is a circular buffer that contains information (redo entires) about changes made to the database by INSERT, UPDATE, DELETE, CREATE, ALTER, and DROP statements
- Log writer background process writes the contents of redo log buffer into a group of mirrored redo log files
- The files are cyclical such that when one is full log writer uses the next one
- The first file is overwritten only after it has been checkpointed or archived
- The modifications of data blocks are initially recorded in redo log buffer
Redo log buffer
- Then log writer process writes the contents of redo log buffer into one of the files from a circular group of redo log files
- Redo log files are used to recover the transactions when the system fails
- Redo log buffer is flushed into redo log file in one of the cases:
- automatically every three seconds,
- whenever a transaction commits,
- when log writer process (LGWR) is is asked to switch log files,
- when redo log buffer is 1/3 full
- Default size of redo log buffer is controlled by LOG_BUFFER parameter, it is max(256 kbytes, (128*number CPUs)) the smallest size is 256Kbytes
- The systems with many concurrent transactions would benefit from a larger redo log buffer because log writer can operate on the buffer concurrently with the transactions
- Long transactions writing a lot to a database benefit from a large redo log buffer
Transient Memory Structures of Relational Database Server
Outline
Shared pool
- LIBRARY cache contains the parse trees and the execution plans for SQL statements, PL/SQL procedures and packages and control structures such as locks and library cache handles
- DICTIONARY cache contains a collection of data dictionary relational tables containing information about the structures of the database
- LRU strategy is used to control allocation and deallocation
of memory in shared pool
Transient Memory Structures of Relational Database Server
Outline
Large pool and Java pool
- Large pool cache contains session memory for the shared server and XA interface (transactions that interact with more than one database), I/O server processes, backup and restore operations, and parallel execution message buffer
- Large pool is used to store large memory allocations
- Large large pool is strongly recommended when using multithreaded server
- Java pool consists of transient memory used by the local Java Virtual Machine
- Java pool is needed only with Java stored procedures,
- Java Beans, or other options included in Jserver option of the system
Transient Memory Structures of Relational Database Server
Outline
Process Global Area
- Process Global Area (PGA) is a transient memory area that contains data and control information for a server process
- Process Global Area consist of private SQL area (bind information and run-time memory structures), cursor areas, stack space, session memory, and SQL work areas (sorting, hashing, operations on bitmaps)
Transient Memory Structures of Relational Database Server
Outline
Database files
Transient Memory Structures of Relational Database Server
Outline
File system mechanisms
- Operating system file systems: these are the files that appear in a file system of a given operating system
- Raw partitions: these are very big sections of persistent storage without any sort of file system on it
- Raw partitions are not buffered - all I/O is direct without any buffering from operating system
- Automatic Storage Management (ASM): ASM is a file system exclusively designed for a database
- Automatic Storage Management stores data in file system organized in a different way from operating system file system
- Clustered file system: it is a traditional file system that is shared by many nodes (computers) in a clustered environment
Transient Memory Structures of Relational Database Server
Outline
Parameter files
- Parameter files tell the system where to find control files, trace files, and what are the initial values of certain system parameters, for example DB_NAME, SGA_MAX_SIZE, SORT_AREA_SIZE, etc
- A name of server parameter file (SPFILE) is spfile<ORACLE_SID>.ora
- A legacy parameter file has a name init<ORACLE_SID>.ora
- A sample contents of init<ORACLE_SID>.ora
db_block_size=8192 db_cache_size=100M open_cursors=100 background_dump_dest= /packages/csoracle/u02/app/oracle/admin/csci/bdump core_dump_dest=/packages/csoracle/u02/app/oracle/admin/csci/cdump timed_statistics=TRUE user_dump_dest=/packages/csoracle/u02/app/oracle/admin/csci/udump
Transient Memory Structures of Relational Database Server
Outline
Oracle process flows
Oracle process flows
Oracle process flows
References
- T. Kyte Expert Oracle Database Architecture 9i, 10g, and 11g Programming techniques and Solutions, Apress 2011, chapters 2, 3, 4
- G. Harrison Oracle Performance Survival Guide, Prentice Hall, 2010, chapter 2