Oracle Architecture

Oracle Architecture There are two terms that are used with Oracle • •

Database - A collection of physical operating system files Instance - A set of Oracle processes and a SGA

These two are very closely related but a database can be mounted and opened by many instances. An instance may mount and open only a single database at any one point in time. The File Structure The are five types of files that make up a database • • • • • •

Parameter Files - These files tells Oracle were to find the control files. Also they detail how big the meory area will be, etc Data Files - These hold the tables indexes and all other segments Redo Log Files - Our transaction logs Control Files - Details the location of data and log files and other relevant information about their state. Temp Files - used for disk-based sorting and temporary storage Password Files - Used to authenticate users logining into the database.

Parameter Files The parameter file for Oracle is the commonly know file init.ora (or init.ora). The file itself is a very simple plain text file (on oracle 9i is can also be a binary file) and details information regarding block size, location of the control files, etc. On a windows server the file can be found in [ORACLE_HOME]/admin/<sid>/pfile but can be in any location by using the "startup pfile = " . Sometimes a parameter may start with a underscore this normally refers to a undocumented parameter and is normally only requested by Oracle support. Data Files Normally there are at least two data files, one for System data and the other for User data. •

Segments - are database objects, a table, a index, rollback segments. Every object that consumes space is a segment. Segments themselves consist of one or more extents.

•

Extents - are a contiguous allocation of space in a file. Extents, in turn, consist of blocks

•

Blocks - are the smallest unit of space allocation in Oracle. Blocks normally are 2KB, 4KB or 8KB in size but can be larger.

The relationship between segments, extents and blocks looks like this

Oracle Architecture

Every block within the Oracle database will be the same size and all block generally have the same format, which looks something like the below, note that the block size will normally be 2KB, 4KB or 8KB.

Header

contains information regarding the type of block (a table block, index block, etc), transaction information regarding active and past transactions on the block and the address (location) of the block on the disk

Table Directory

contains information about the tables that store rows in this block

Row Directory Block overhead

contains information describing the rows that are to be found on the block. This is an array of pointers to where the rows are to be found in the data portion of the block. The three above pieces are know as the Block Overhead and are used by Oracle to manage the block itself.

A tablespace is a container which holds segments. Each and every segment belongs to exactly one tablespace. Segments never cross tablespace boundaries. A tablespace itself has one or more files associated with it. An extent will be contained entirely within one data file. So in summary the Oracle hierarchy is as follows: • •

A database is made up of one or more tablespaces A tablespace is made up of one or more data files, a tablespace contains segments

Oracle Architecture • • •

A segment (table, index, etc) is made up of one or more extents. A segment exists in a tablespace but may have data in many data files within a tablespace. An extent is a continuous set of blocks on a disk. An extent is in a single tablespace and is always in a single file within that tablespace. A block is the smallest unit of allocation in the database. A block is the smallest unit of i/o used by the database.

Temp Files Oracle will use temorary files to store results of a large sort operation when there is insufficient memory to hold all of it in RAM. Temporary files never have redo generaed for them, although they have UNDO generated. Temporary data files never need to be backed up. Control Files The control files contain a directory of the other files Oracle needs. The control files tell the instance where the database and online redo log files are. The control files also tell Oracle about checkpoints that have taken place, the name of the database, archive redo log history, etc. Control files should be duplicated across different disks as it make recovery much harder without them. Redo Log Files Redo log files are used for recover purposes only. There are two types of redo log files online and archive. There are at least 2 online redo log files and they are used in a circular fashion when one fills up oracle will switch to the next log file. Archive Redo log When a redo log file fills up and before it is used again the file is archived for safe keeping, this archive file with other redo log files can recover a database to any point in time. It is best practice to turn on ARCHIVELOG mode. The Memory Stucture There are five memory stuctures that make up the System Global Area (SGA). The SGA will store many internal data structures that all processes need access to, cache data from disk, cache redo data before writing to disk, hold parsed SQL plans and so on.

SGA

Oracle Architecture

The shared pool consists of the following areas: Library cache includes the shared SQL area, private SQL areas, PL/SQL procedures and packages the control structures such as locks and library cache handles Shared Pool Dictionary cache is a collection of database tables and views containing information about the database, its structures and users. Buffers for parallel execution messages and control structures Use the parameter SHARED_POOL_SIZE in the init.ora file to adjust This area holds copies of read data blocks from the datafiles. The buffers in the cache contain two lists, the write list and the least used list (LRU). The write list holds dirty buffers which contain modified data not yet written to disk. The least used list holds free buffers (no useful data) , pinned buffers (being accessed) and dirty buffers that have not yet been moved to the write list. This pool is broken down into three pools, recycle (goal here is to age out a block as soon as it is no longer needed), keep (goal is to keep warm/hot block in the pool for as long as possible) and default buffer pool. Buffer cache

The size is deteremnied by the following in the system paramenter file: DB_2K_CACHE_SIZE DB_4K_CACHE_SIZE DB_8K_CACHE_SIZE DB_16K_CACHE_SIZE DB_32K_CACHE_SIZE

Use the parameter DB_BLOCK_BUFFERS in the init.ora file to adjust The redo buffer is where data that needs to be written to the online redo logs will be cached temporarily before it is written to disk. However the contents of this area are flushed:

Redo buffer

• • •

Every three seconds Whenever someone commits When its gets one third full or contains 1MB of cached redo log data.

These entries contain necessary information to reconstruct/redo changes by the INSERT, UPDATE, DELETE, CREATE, ALTER and DROP commands. Use the parameter LOG_BUFFER in the init.ora file to adjust Large Pool This is an optional memory area that provide large areas of memory for: • • •

MTS - to allocate the UGA region in the SGA Parallel execution of statements - to allow for the allocation of inter-processing message buffers, used to coordinate the parallel query servers. Backup - for RMAN disk I/O buffers

The large pool is basically a non-cached version of the shared pool.

Oracle Architecture

Parallel execution message buffers. Use the parameter LARGE_POOL_SIZE in the init.ora file to adjust used to execute java code within the database. Java Pool Use the parameter JAVA_POOL_SIZE in the init.ora file to adjust Display information regarding the SGA Memory area

Memory location

SGA SGA (detailed)

sql> show sga; use table v$sgastat;

Buffer cache

SGA

Display memory allocation

SGA

Display the redo buffer

SGA

Library cache Dictionary cache

Displaying the information

shared pool shared pool

use table v$bh; compute sum of bytes on pool break on pool skip 1 select pool, name bytes from v$sgastat order by pool, name select * from v$sga where name = 'Redo Buffers' use table v$librarycache; use table v$rowcache;

PGA and UGA If you have MTS configured then the UGA must be stored in a memory stucture that everyone has access to and this would be the SGA. However if you are using a dedicated server connection then the UGA becomes part of the PGA Each process connected to the database requires its own area of memeory this is know as the Program Global Area (PGA). This area stores variables, arrays and other information that do not need to be shared with other processes.

Oracle Architecture

Session Information

Stack space

PGA in an instance running without the multi-threaded server (named Shared Server in Oracle9i) requires additional memory for the user's session, such as private SQL areas and other information. If the instance is running the multi-threaded server, this extra memory is not in the PGA, but is instead allocated in the SGA (the Shared Pool). The memory allocated to hold a sessions variables, arrays, etc and other information relating to the session. However for a shared server the session memory is shared and not private

Display information regarding the PGA Session information

v$sesstat, v$statname select a.name, b.name from v$statname a, v$mystat b Display PGA and UGA usage where a.statistic# = b.statistic# and a.name like '%ga %' alter the following parameters Tunning the PGA/UGA Shrink the PGA area

SORT_AREA_SIZE (PGA) SORT_AREA_RETAINED_SIZE (UGA) > exec dbms_session.free_unused_user_memory;

Oracle Background Processes There could be upto 11 server process depending on the server configuration, Windows will only have one process called Oracle, this process will have one thread for each of the below processes.

Process Monitor System Monitor

Responsible for cleaning up after abnormally terminated connections. Responsible for monitoring other server processes and restarting them if PMON necessary Registers the instance with the listener. SMON Temporary space cleanup Crash recovery apon restart Coalescing free space Recovering transactions active against unavailable files

Oracle Architecture

Distributed database recovery Checkpoint process Database block writer

Log writer

Archive process Block server process Lock monitor process Lock manager daemon Lock process

Instance recovery of failed node in OPS (Oracle parallel server) Cleans up OJB$ (Low Level data dictionary) Shrinks rollback segments Offlines rollback segments Recovers transactions that are left in a prepared state because of a crash or loss RECO of connection during a two-phase commit. It does not do the checkpoint but assists with the checkpointing process by CKPT updating the file headers of the data files. Responsible for writing dirty blocks to disk, it writes the dirty blocks from the DBWn buffer. Responsible for flushing to disk the contents of the redo log buffer located in the SGA. It does this: LGWR

• •

Every 3 seconds Whenever you commit

•

When the redo log buffer is a third full or contains 1 Mb of bufferd data

Copy online redo log file to another location when LGWR fills up, these log files would be used to perform media recovery. Used in OPS and keeps each servers SGA in the clusters consistent with each BSP other. Used in OPS and monitors all instances in a cluster to detect a failure of an LMON instance. Used in OPS and controls the global locks and global resources for the block LMD buffer cache in a clustered environment. Used in OPS and is the same as the LMD daemon but handles requests for all LCKn global resources other than database block buffers ARCn