Oracle Architecture Part 3

[embedplusvideo height=”356″ width=”584″ standard=”″ vars=”ytid=L_2TfSHotsY&width=584&height=356&start=&stop=&rs=w&hd=0&autoplay=0&react=1&chapters=&notes=” id=”ep6822″ /]

Watch the rest of this series:

Part 1

Part 2

Part 4


Initilisation params for the Buffer cache:

  • db_cache_size
  • db_block_size
  • It is very helpfull if the buffer block size is a multiple of your operating system block size.

The Buffer cache is controled with a Least Recently Used (LRU) algorithm which determines what stays in and what gets removed from the SGA.

The goal being to have the data that is accesses most frequently kept in the SGA so that it can be accessed fast.

Shared pool.

The purpose of the Shared pool is to reduce parsing

The Shared pool consists of 2 parts:

  • Library Cache
    • text of sql statements
    • meta code – compiled version of above text
    • execution plan that the optimiser will use
    • Views:
      • v$sqlarea
      • v$sql
  • Dictionary Cache
    • names of objects that have been touched recently
    • privalages and roles – access information

The Shared Pool also usesa  LRU algorithm.

Initialization parameter – shared_pool_size controls size of shared pool.


Java Pool

  • Initilization parameter – java_pool_size
  • large pool – optional
  • if using RMAN configure a large large pool
  • shared server uses this


Watch Part 4

Oracle Architecture Part 4

[embedplusvideo height=”328″ width=”584″ standard=”″ vars=”ytid=nNGLyWIPPTQ&width=584&height=328&start=&stop=&rs=w&hd=0&autoplay=0&react=1&chapters=&notes=” id=”ep8443″ /]

Watch the earlier parts first:

Part 1

Part 2

Part 3



Oracle Processes



Database Writer

  • Writes from the SGA to the data files
  • Does deffered writes
  • like a hotel room clerk
  • kicks blocks out to disk when new data needs free blocks



Log Writer

  • writes info to the current group of redo logs


System monitor

  • very important process
  • performs instance recovery
  • cleans up after sorts



Process monitor

  • Monitors Processes
  • detects – deadlock detected for example



Checkpoint process

one big reason for checkpoints is that commonly accessed
data may never get written out to disk. A checkpoint forces
this to get written.

Oracle Instance

– is the SGA and background processes


Oracle database and the Oracle instance combined is called the Oracle Server.

You can have multiple instances associated with the same database, this is called RAC.

Dedicated server process connects through the PGA.

Oracle Architecture Part 2

[embedplusvideo height=”328″ width=”584″ standard=”″ vars=”ytid=wmc9uxNW99E&width=584&height=328&start=&stop=&rs=w&hd=0&autoplay=0&react=1&chapters=&notes=” id=”ep5503″ /]

Check out part 1 first.


Online Redo Logs

  • Contains Redo and Undo info
  • DML
  • Inserts/Deletes
  • DDL – Create alter drop
  • Commits
  • Need at least 2 files/groups
  • Should be multiplexed each on different devices and controlers
  • When each file fils, next one is started and old one is archived (if in Archivelogmode)

An Oracle database is comprised of datafiles, control files
and redo logs.

The second part is the memory – the SGA (system global area
or shared global memory)

Database Buffer Cache

Comprised of Oracle data blocks

There are 4 main types of data blocks, which are the same as the 4 types of data mentioned earlier:

  • Data
  • Index
  • Rollback
  • Temporary

Part 3…

Oracle Architecture Part 1

[embedplusvideo height=”328″ width=”584″ standard=”″ vars=”ytid=addFo5FMzkg&width=584&height=328&start=&stop=&rs=w&hd=0&autoplay=0&react=1&chapters=&notes=” id=”ep3446″ /]

There are 3 essential elements of any database system

  • Datafiles – storage
  • Memory – in oracle its the SGA
  • Processes – To operate on the data files and memory


  • Datafiles
  • Contains data
  • Tables
  • Indexes
  • Undo segments (used to be rollback segments)
  • Temporary files
  • Oracle is easy as DIRT – Data, Index, Rolback, Temp
  • In Oracle you can now have around 65000 datafiles

Important views for the DBA

  • dba_data_files
  • v$dbfile
  • v$datafile

Control files.

Should have 3 identical control files on different disks and controllers

They contain information about the database

  • db name
  • created date
  • path to data files
  • check point info

Important Views

  • v$controlfile

Watch Part 2…