Oracle Database Fundamentals

A minimal Oracle configuration will have a system tablespace and at least 2 redo log groups

Processes

PMON Process

Process Monitor (mandatory process) - reclaims database resources no longer needed by a user like cleaning up terminated connections and rolling back uncommitted transactions

SMON Process

System Monitor (mandatory process) - reclaims database resources no longer needed by a user like performing automatic instance recovery and reclaiming space used by temporary segments no longer used

DBWR Process

Database Writer (mandatory process) - manages the database buffer cache - it will write modified data blocks back to disk so free buffers can be found (data must go into the database buffer cache before it is accessed)

LGWR Process

Log Writer (mandatory process) - writes information in redo log buffer to disk - written to in a circular fashion so that must be at least 2 - does log switch when active or current log becomes filled

CKPT Process

Checkpointer (optional) - DBWR writes all dirty buffers in the database buffer cache to disk - guarantees that all modified data blocks are written to disk

ARCH Process

Archiver (optional) - copies inactive online redo log files to archive log files, or archives (archive stream). It should then be copied to a designated storage device - 'ALTER SYSTEM ARCHIVE LOG LIST' gives status of whether it is running or not

RECO Process

Recoverer process (optional) - resolves failures involving a distributed transaction

LCKn Process

Lock process (optional) - performs inter-instance locking in a parallel server system

Qnnn Process

Parallel Query process (optional) - provides parallel query, parallel index creation, parallel data loading, and parallel Create Table As Select (CTAS) capabilities

SNPn Process

Snapshot process (optional) - performs automatic refreshes of snapshots

LMON Process

(optional) - used to handle instance deaths and associated recovery for lock management

LMDn Process

(optional) - used to handle remote lock requets that originate from a different instance

Parameters

Initial

MinExtents

NEXT specifies the size of the next extent to be allocated. Oracle updates NEXT each time it allocates an extent. The following formula is what version 7 follows - size of n extent:
NEXT(n+1) = NEXT (nth) * (1.0 + PCTINCREASE/100)

Setting the value of NEXT specifies the size of the next extent allocated (rounded up to multiple of five database blocks). Also depending on the free extent found, it may choose to pick up the whole extent if breaking it in two results in a free extent < 5 blocks. so the size of the extent might be off as much as 9 blocks.

PctIncrease

Definitions

Rollback Segments

Used for read consistency purposes. It takes more bytes to store information on an update or delete, than an insert operation. To rollback an insert just have to store the rowid that you need to delete, but to rollback a delete you need to store all the information in the record so you can insert it again (an update depends on how much of the row is updated). It isn't a good idea to set PCTINCREASE for rollback segments - also it is optimal to have the same size extents for your rollback segments. Oracle determines the rollback segmen to use by assigning to rbs which has least number of active txns or if two or more rbs have the same "least number of active txns" , then assign to the one which is after the last one used. This ensures that undo is kept for a longer time. Do following queries to see if have roolback segment contention and if you need to add more (also add one if know have a large transaction coming up):

# of waits since instance startup: select * from v$waitstat where operation = 'buffer bust waits' and class = 'undo segment header';

if number of waits: select class, count from v$waitstat where class in ('system undo header', 'system undo block', 'undo header', 'undo block');
is greater than 1% of total requests: select sum(value) from v$sysstat where name in ('db_block_gets', 'consistent gets');
then increase the number of rollback segments

find out the max extents for a rollback segment: select SEGMENT_NAME, STATUS, MAX_EXTENTS from dba_rollback_segs;
This will tell you the MAX_EXTENTS that the rollback segment was created with; however, the first block of each segment contains an extent map for each extent. The maximum number of extents allowed is therefore a function of the database block size of each extent map entry. This is a common extent mapping table:

DATA BLOCK SIZE                  GREATEST MAXEXTENT VALUE
	512                              25
	1K                               57
	2K                               121
	4K                               249
	8K                               505

Thereby, the MAX_EXTENTS value in dba_rollback_segs may not be accurate because the MAX_EXTENTS cannot exceed the GREATEST MAXEXTENT VALUE.

Redo Logs

Used for recovery - contain both the redo and undo changes needed to make your database consistent. Also store information needed to roll back uncommitted transactions. During instance recovery Oracle will roll forward applying the redo and then roll back applying the undo. When inserting into a table with an index, there is considerably more undo created than when inserting into a table without an index. If redo logs are multiplexed then the same redo log information is written to multiple redo log files (recommended that they be multiplexed or hardware mirrored - placed on raw disks using RAID 0+1 which does mirroring and fine grain striping - introduces parallelism to the disk writes) - they should never go on same disk as archive log files. You need atleast 2 redo log groups and it's recommended to have atleast 2 members in each group on separate disk drives. Type 'archive log list' at SQLDBA prompt for information redo logs and archival information. In init.ora change log_archive_start paramater to enable/disable automatic archiving or manually adjust it with command 'log archive start/stop'.

Views associated with redo log information:

v$log - log file informatio nfrom control file
- combination of the thread# and sequence# distinguishes a log file and an archive file
v$log_history - archived log names for all logs in the log history
v$logfile - information about all redo logs
v$loghist - log history from the control file
v$database -database information from the control file

Free Lists

A list of blocks that can be reused by that segment. When deletes occur and the data in a block becomes less than the specified pctused, this block is put on a free list. Every time a block is added, it is added to the HEAD (beginning) of the free list.

free space search algorithm (V6.0.35 and higher):

Search for free space on its own transaction free list (Trfl). If found goto step 8 else goto step 2.
Search for free space on the process freelist. If found goto step 8. If not found goto step 3.
Search Master freelist. If space found copy a chunk of it to Prfl and goto step 2 else goto step 4.
Search for committed Txfls. If no committed Txfl space found, goto step 5. Else: Copy space from the first committed Txfl to Master freelist, goto step 3.
Can High Water Mark be bumped up? If no, goto step 6. If yes, bump up by some number and copy space to Prfl and goto 2.
Go to data dict. Search fet$ and allocate an extent to segment and goto 4. If no space in fet$ goto 7.
Error. No more space in tablespace.
Use the space.

SCN (System Commit Number)

A timing mechanism Oracle uses to guarantee ordering of transactions and to enable correct recovery from failure (like a logical clock). They are used for guaranteeing read-consistency, and checkpointing. Vital area for tuning Parallel Server (they cost 2 DLM calls) - for non-OPS it uses the SGA so it is very quick.

they are obtained automatically at commit
can't increase more than 16,000 times a second and they use 48bits
must be monitonically increasing
must be able to jump-adjust the SCN from any instance - the instance with the lower SCN will catch up so their SCNs correspond
the paramter 'max_commit_propogation_delay' controls the exact time-lag between updating a SCN on one instance and the time the other instance sees that change in its wn cached value

Operations

Update Operation

acquire database blocks into the database buffer cache
acquire rollback blocks into the database buffer cache
place exclusive row locks on rows that about to change
store a record to identify before and after images in the redo log buffer (not entire record - just changed data)
save rollback data into a rollback segment block
apply changes to the data blocks

Recorded in the data block when a dml occurs

Pin the current version of the block in cache.
Generate the undo
Write the undo information to a redo record in the redo buffers
Write the undo information to a block in the rollback segment
Change the block-record the undo information.
Write the change to a redo record in the redo buffers
Write the change to the block in the buffer cache
LGWR writes out redo buffers at some later time. DBWR writes out the changed blocks at some later time. LGWR always writes out redo records before DBWR can write out the changed block.

Commit Operation

user issues commit
a commit record for the user's transaction is placed in the redo log buffer
LGWR flushes the redo log buffer to the current log group
the user is notified that the transaction has been committed
resource locks are released on data and rollback blocks
DBWR will eventually write database blocks to disk (this includes rollback segments and index blocks)

At commit time, Oracle also updates the transaction table and records the SCN for that transaction.

SQL compiler steps

The SQL compiler parses the SQL, examines the SQL for syntax errors, then converts the SQL parse tree into an internal representation (IBM, which invented SQL, calls the internal format QGM, or Query Graph Model).
The compiler examines the reformatted SQL to ensure that executing the statement won't violate referential integrity, and notes whether the database engine should process a constraint or trigger for the SQL.
The compiler rewrites the SQL statement, replacing view references with actual column names and transforming the SQL for processing by the optimizer. The transformation eliminates redundant joins, adds implied predicates and converts Intersect clauses to Exists subqueries.
The optimizer itself uses cost-based algorithms to determine the most efficient execution method for the SQL. The optimizer finds the best join order, for example, and decides whether the execution of the SQL statement will be CPU- or I/O-bound.
The compiler "remembers" the essence of the SQL for later comparison with and optimization of subsequent SQL statements and delivers the compiled, optimized SQL statement to the database engine. The engine accesses the data and sends a response to the client.