7/24/13 10:32 PM 4. A sample relational db server
A sample relational
A sample relational
database server
7/24/13 10:32 PM 4. A sample relational db server
Oracle system architecture
Oracle Instance
(software) Oracle Database(data) Transient memory Background processes Persistent memory
7/24/13 10:32 PM 4. A sample relational db server
Server process PGA Server process PGA Server process PGA Server process PGA Server process PGA Server process PGA . . . Background processes System Global Area (SGA)
Oracle system architecture
PMON Data buffer caches Redo log buffer Shared pool Large pool Java pool DBWR CKPT LGWR ARCH . . . Clients SMON
7/24/13 10:32 PM 4. A sample relational db server
Data buffer caches
DEFAULT pool
DEFAULT pool contains miscellaneous data blocks
KEEP pool
KEEP pool contains frequently accessed data blocks KEEP pool retains data blocks
RECYCLE pool
RECYCLE pool contains data blocks from full table scans RECYCLE pool eliminates data blocks as soon as they are no longer needed
2K 4K 8K 16K 32K
7/24/13 10:32 PM 4. A sample relational db server
Redo log buffer
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
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
7/24/13 10:32 PM 4. A sample relational db server
Redo log buffer
The modifications of data blocks are initially recorded in 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; these 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:
(1) every three seconds,
(2) whenever a transaction commits,
(3) when log writer process (LGWR) is is asked to switch log files,
7/24/13 10:32 PM 4. A sample relational db server
Redo log buffer
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 write can operate on the buffer concurrently with the transactions Long transactions writing a lot to a database benefit from a large redo log buffer
7/24/13 10:32 PM 4. A sample relational db server
Shared pool
LIBRARY cache contains parse trees and execution plans for SQL statements, PL/SQL procedures and packages and control structures such as locks and library cache handles
LIBRARY
cache DICTIONARYcache
DICTIONARY cache contains a collection of data dictionary relational tables containing information about the
structures of the database
Control structures
LRU strategy is used to control allocation and deallocation of memory in Shared pool
7/24/13 10:32 PM 4. A sample relational db server
Large pool
Large pool cache contains session memory for the shared server and XA interface (transactions interact with more than one database), I/O server processes, backup and
restore operations, and parallel execution message buffer
LARGE pool
Large pool is used to store large memory allocations Large pool is strongly recommended when using
7/24/13 10:32 PM 4. A sample relational db server
Java pool
Java pool is needed only with Java stored procedures,
Java Beans, or other options included in Jserver option of the system
JAVA pool
Java pool consists of transient memory used by the local Java Virtual Machine
7/24/13 10:32 PM 4. A sample relational db server
Process Global Area
Process Global Area (PGA) is a transient memory area that contains data and control information for a server process
SORT area Connection information Cursor state Stack space HASH area
Process Global Area (PGA) 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)
7/24/13 10:32 PM 4. A sample relational db server
Database files
UNI (database) STAFF (tablespace) STUDENT (tablespace) SYSTEM (tablespace) staff01.dbf (file) staff02.dbf (file) std01.dbf (file) std02.dbf (file) std03.dbf (file) system01.dbf (file) (data blocks) (extents) STAB(data seg) (data seg)SUBJ
SIDX (index seg)
ENROLMENT
(data seg) (data segment)SYS
STD (data seg) STAB (table) SUBJ (table) STD (table) SYS (table) ENROLMENT (table) SIDX (index)
7/24/13 10:32 PM 4. A sample relational db server
File system mechanisms
Operating 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 operation system
Automatic Storage Management (ASM): ASM is a file system
exclusively designed for a database; data is still stored in files; file system is ASM
Clustered file system: it is a traditional file system that is
shared by many nodes (computers) in a clustered environment.
7/24/13 10:32 PM 4. A sample relational db server
Parameter files
Parameter files tell the system where to find control files, trace files, and what are the initial values of certain system parameters, e.g. 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
7/24/13 10:32 PM 4. A sample relational db server
Online redo log files
The system needs at least two files in a group of redo log
files; it is recommended to mirror the redo log file groups on different disk drives
The system implements write-ahead logging algorithm that writes all old/new values in to the log files before
the modifications are performed in a database
Online redo log files are used for instance recovery after crash, media recovery after a data file restore from backup, standby database processing, redo log mining processes for information sharing
NOLOGGING attribute allows from the exclusion of the operations on some data objects, e.g. tablespaces, to be logged
7/24/13 10:32 PM 4. A sample relational db server
Online redo log files
Each redo log group consists of one or more redo log members where the individual redo log file members are the mirror images of each other
Redo log files are fixed in size and are used in a circular fashion
Switching from one group of log files to another triggers
checkpointing, i.e the systems writes all modified blocks in
7/24/13 10:32 PM 4. A sample relational db server
Archived log files
The archived log files are the copies of log files performed when the old contents of a cyclic redo log file is about to be overwritten
The log files are archived when the system runs in ARCHIVELOG mode
7/24/13 10:32 PM 4. A sample relational db server
Control files
The control files contain information about all files in a
database and are used to maintain internal consistency of the system
The control files are vital to startup of the system; it is recommended to mirror the control files on different disk drives
The control files also contain information about checkpoints, database name, timestamp when a database was created,
7/24/13 10:32 PM 4. A sample relational db server
Trace files
Trace file contain information written by the background processes when an internal error occurs or when the lower level tracing is turned on
There are two types of trace files:
(1) Trace files requested by database administrator; these are the results of enabling SQL_TRACE=TRUE; these containscontains
diagnostic information about user session diagnostic information about user session
(2) Trace files server generated in the responses to the system internal errors
7/24/13 10:32 PM 4. A sample relational db server
Alert file
Alert file (alert log) contains a history of a database: log file switches, internal errors, block corruption errors, deadlock errors, created tablespaces, tablespaces taken offline/online, shutdown and restart operations, administrative operations, errors of shared servers and dispatchers, and errors
7/24/13 10:32 PM 4. A sample relational db server
Background processes
DBWR0..DBWR19 Database writer processes
The database writer processes are responsible for writing the modified data blocks ("dirty blocks) from data buffer cache to persistent storage
LGWR Log writer process
The log writer process writes all redo entries that have been copied into log buffer since the last time it wrote
CKPT Checkpoint process
The checkpoint process updates the datafile headers and the control files to record the checkpoint event; the checkpoint process does not write the data blocks from data buffer
7/24/13 10:32 PM 4. A sample relational db server
Background processes
SMON System monitor process
The system monitor process is responsible for recovery, cleaning up temporary segments when no longer needed, and coalescing contiguous free extents
PMON Process monitor
The process monitor performs a recovery when a user
process fails, cleans up data buffer cache and releases the resources occupied the failed process
CJQ0..CJQn Job queue processes
The job queue processes run the scheduled users' jobs in batch processing
7/24/13 10:32 PM 4. A sample relational db server
Background processes
RECO Recoverer process
The recoverer process resolves the failures involving the distributed transactions
ARCH0..ARCH9 Archiever processes
The archiever processes copy the contents of redo log file to another persistent storage location after a log file switch has occurred
Dnnn Dispatcher processes
The dispatcher processes are the components of
multithreaded database server; dispatcher processes handle multiple connections between client processes and server processes
7/24/13 10:32 PM 4. A sample relational db server
Background processes
LMS Lock manager server process
The lock manager server process provides inter-instance resource management in Real Application Cluster option
QMN0..QMN9 Queue monitor processes
The queue monitor processes manage job queues in Advanced Queuing option
Snnn Server processes
The server processes handle the requests of the users connected to the instance; parse and run SQL, read data blocks; return the results
7/24/13 10:32 PM 4. A sample relational db server
Oracle process flows
. . . Server process PGA Server process PGA Server process PGA Server process PGA Server process PGA Server process PGA . . .
DBWR LGWR ARCH PMON SMON
Data buffer caches
Background processes System Global Area (SGA)
Redo log buffer Shared pool Large pool Java pool CKPT Clients SELECT * FROM EMPLOYEE WHERE employee_id = :1 FOR UPDATE OF salary
1 2 3 4 5 6
7/24/13 10:32 PM 4. A sample relational db server
Oracle process flows
. . . Server process PGA Server process PGA Server process PGA Server process PGA Server process PGA Server process PGA . . .
DBWR LGWR ARCH PMON SMON
Data buffer caches
Background processes System Global Area (SGA)
Redo log buffer Shared pool Large pool Java pool CKPT Clients 2 3 5 1 UNDO 4 EMPLOYEE 6 6 UDATE EMPLOYEE SET salary = :2 WHERE employee_id = :1;
7/24/13 10:32 PM 4. A sample relational db server
Oracle process flows
. . . Server process PGA Server process PGA Server process PGA Server process PGA Server process PGA Server process PGA . . .
DBWR LGWR ARCH PMON SMON
Data buffer caches
Background processes System Global Area (SGA)
Redo log buffer Shared pool Large pool Java pool CKPT Clients 1 2 3 5 UNDO tbs 4 EMPLOYEE 6 6 UDATE EMPLOYEE SET salary = :2 WHERE employee_id = :1; COMMIT; 7 7 8 8 8
7/24/13 10:32 PM 4. A sample relational db server
Typical performance problems
Memory Poor data buffer Large table scans, not enough RAM, cache hit ratio invalid table/index placement in buffer
caches Category Problem Caused by
Contention Redo log waits Incorrect size of redo logs, too small redo log buffer
Free list waits Bot enough lists with free data blocks Rollback waits Insufficient number of rollback
segments
I/O Disk contention Incorrect size of redo logs, too small redo log buffer
Slow access to data SYSTEM tablespace on frequently used dictionary device
Slow disk sorts Tablespace used for sorting on frequently used device
7/24/13 10:32 PM 4. A sample relational db server
Typical performance problems
Space Out of space conditions Poorly forecasted data volumes
Tablespace Invalid settings of object space size or fragmentation tablespace object setting, not using
locally managed tablespaces Category Problem Caused by
SQL Large join queries Overnormalized database design Object activity Chaining in tables Incorrect setting of PCTFREE,
PCTUSED, too small database block size
Rollback extension Incorrect size of rollback segments, not using auto-UNDO management
Many large table scans Incorrect indexing schema
Object fragmentation Incorrect initial sizing, not performing defragmentation
7/24/13 10:33 PM 4. A sample relational db server
