OLTP – ONLINE TRANSACTIONAL PROCESSING
OLAP – ONLINE ANALYTICAL PROCESSING
CLIENT- APPLICATION SERVER -SERVER ---- THREE TIER Architecture
ORACLE DATABASE SERVER / HOST MACHINE
Physical Machine (node)
CPU, Memory, Storage
User Request Database Listener
USER
SQL
Oracle S/W Oracle RDBMS Engine
Network (LAN)
ORACLE_HOME DB
( Files )
Instance Production
Production DBA
Operating System ( Linux )
ORACLE DATABASE SERVER
DATABASE
INSTANCE
Collection of 3
files
SGA (SYSTEM GLOBAL AREA / SHARED GLOBAL BACKGROUND
AREA) PROCESS 1.Controlfile
2.Redo log file
3.Datafile
MANDATORY OPTIONAL
MEMORY MEMORY
COMPONENTS COMPONENTS
1.JAVA POOL(8i)
1. DATABASE BLOCK BUFFERS (OR )
2.LARGE POOL(8i) Mandatory Optional
DB_CACHE
3.STREAMS POOL(10g) Background Background
2. REDO LOG BUFFER CACHE Process Process
3. SHARED POOL
1.SMON 1.RECO
2.PMON 2.CJQO
3.LGWR 3.OPQO
4.DBWO
DATA DICTIONARY 5.ARCH
LIBRARY CACHE
CACHE 6.CKPT
TYPE MIN/MAX CARRIERS MULTIPLEX
SIZE
OF FILE
THE CRUCIAL
MIN 1
1.CONTROL FILES INFORMATION OF KB/MB Y
MAX 8
[3] DATABASE
Y
2.REDO 2 KB/MB
LOG LATEST TX
FILES
ACTUAL DATA.
3.DATAFILES 1.Upto 9i TABLES/INDEX N
GB/TB
2.From 10g
ADDITIONAL BACKGROUND PROCESSES FROM 10g
MMON – Memory Monitor Gathers database statistics
MMAN – Memory Manager used for tuning of SGA components
MMNL – Manageability Light weight Captures users session history
Physical Database Structure
The files that constitute an Oracle database are organized into the following:
Control files: Contain data about the database itself (that is, physical
database structure information). These files are critical to the database.
Without them, you cannot open data files to access the data within the
database.
Data files: Contain the user or application data of the database
Online redo log files: Allow for instance recovery of the database. If the
database crashes and does not lose any data files, then the instance can
recover the database with the information in these files.
The following additional files are important to the successful running of the
database:
Parameter file: Is used to define how the instance is configured when it
starts up
Password file: Allows users to connect remotely to the database and
perform administrative tasks
Backup files: Are used for database recovery. You typically restore a backup
file when a media failure or user error has damaged or deleted the original
file.
Archive log files: Contain an ongoing history of the data changes (redo) that
are generated by the instance. Using these files and a backup of the
database, you can recover a lost data file. That is, archive logs enable the
recovery of restored data files.
DATABASE STRUCTURE
Stores current SCN in
Redo Log Control Files
BUFFER Buffer
CACHE
W.S
Control Files
DBWR LGWR
Log Member States
UNUSED
Updates
Header CURRENT
with
SCN
ACTIVE
Data Files (.dbf ) INACTIVE
Log Seq # 1 Log Seq # 2
Redo Log Group # 1 Redo Log Group # 2
Stores current SCN in
Redo Log Control Files
BUFFER Buffer
CACHE
W.S
Control Files
CKPT
DBWR LGWR
Log Member States
UNUSED
Updates
Header CURRENT
with
SCN
ACTIVE
Data Files (.dbf ) INACTIVE
Log Seq # 1 Log Seq # 2
Redo Log Group # 1 Redo Log Group # 2
Stores current SCN in
Redo Log Control Files
BUFFER Buffer
CACHE
W.S
Control Files
CKPT
DBWR LGWR
Log Member States
Log switches UNUSED
Updates
Header CURRENT
with
SCN
ACTIVE
Data Files (.dbf ) INACTIVE
Log Seq # 1 Log Seq # 2
Redo Log Group # 1 Redo Log Group # 2
Stores current SCN in
Redo Log Control Files
BUFFER Buffer
CACHE
Control Files
CKPT
DBWR LGWR
Log Member States
Log switches UNUSED
Updates
Header CURRENT
with
SCN
ACTIVE
Data Files (.dbf ) ARCH
Log Seq # 1 Log Seq # 2 INACTIVE
Redo Log Group # 1 Redo Log Group # 2
Archive
Stores current SCN in
Redo Log Control Files
BUFFER Buffer
CACHE
Control Files
CKPT
DBWR LGWR
Log Member States
UNUSED
Updates
Header CURRENT
with
SCN
ARCH ACTIVE
Data Files (.dbf ) INACTIVE
Log Seq # 1 Log Seq # 2
Redo Log Group # 1 Redo Log Group # 2
Archive
Stores current SCN in
Redo Log Control Files
BUFFER Buffer
CACHE
Control Files
CKPT
DBWR LGWR
Log Member States
Log switches UNUSED
Updates
Header CURRENT
with
SCN
ACTIVE
Data Files (.dbf ) INACTIVE
Log Seq # 3 Log Seq # 2
Redo Log Group # 1 Redo Log Group # 2
Stores current SCN in
Redo Log Control Files
BUFFER Buffer
CACHE
Control Files
CKPT
DBWR LGWR
Log Member States
Log switches UNUSED
Updates
Header CURRENT
with
SCN
ACTIVE
Data Files (.dbf ) ARCH INACTIVE
Log Seq # 3 Log Seq # 4
Redo Log Group # 1 Redo Log Group # 2
Archive
Stores current SCN in
Redo Log Control Files
BUFFER Buffer
CACHE
Control Files
CKPT
DBWR LGWR
Log Member States
UNUSED
Updates
Header CURRENT
with
SCN
ARCH ACTIVE
Data Files (.dbf ) INACTIVE
Log Seq # 3 Log Seq # 4
Redo Log Group # 1 Redo Log Group # 2
Archive
Stores current SCN in
Redo Log Control Files
BUFFER Buffer
CACHE
Control Files
CKPT
DBWR LGWR
Log Member States
UNUSED
Updates
Header CURRENT
with
SCN
ARCH ACTIVE
Data Files (.dbf ) INACTIVE
Log Seq # 5 Log Seq # 4
Redo Log Group # 1 Redo Log Group # 2
Archive
Oracle Architecture with File System
How the Oracle Database Works
The following example describes the most basic level of operations that the
Oracle database performs. This illustrates an Oracle configuration where
the user and associated server processes are on separate computers
(connected through a network).
STEPS
1. An instance has started on the computer running Oracle (often
called the host or database server).
2. A computer running an application (a local computer or client
workstation) runs the application in a user process. The client
application attempts to establish a connection to the instance by
using the Oracle Net Services driver.
3. The instance detects the connection request from the application and
connects to a server process on behalf of the user process.
4.The user updates a row.
5. The server process receives the statement and checks if it is already in
the shared pool of the SGA. If a shared SQL area is found, the server
process checks the user’s access privileges to the requested data, and the
previously existing shared SQL area is used to process the statement. If
not, then a new shared SQL area is allocated for the statement, so that it
can be parsed and processed.
6. The server process retrieves any necessary data values from the actual
data file (table) or from data blocks that are stored in the SGA.
7. The server process modifies the table data in the SGA.
8. When the transaction is committed, the LGWR process immediately
records the transaction in the redo log file.
9. The DBWn process writes modified blocks to the disk when doing so is
efficient.
10. The server process sends a success or error message across the
network to the application.
Processing SQL
The following steps show how Oracle processes SQL
1. Statement is passed to Oracle for processing
2. Before it is placed in the library cache, a hash value is computed that
represent s a number of characteristics of the SQL.
3. Oracle compares the computed hash value against those values in a hash table where it
maintains for SQL statements already in the cache.
4. If a match is found, the new SQL statement is thrown away and the one sitting in the
cache is executed on its behalf.
5. In no match is found, further processing is done on the new SQL statement, an entry is
made in the library cache hash table for newly arrived code, and it is placed in the
library cache.
6. There are 3 stages of SQL processing – parse, execute and fetch
• During parsing, Oracle server checks the syntax and validates
table, column names against data dictionary
• Determines whether user has privilege to execute the statement
• Determines optimal execution plans for statement
• Finds a shared SQL area for the statement
• In execution stage, for UPDATE and DELETE statement, Oracle locks the affected rows,
looks for data blocks in DB buffer cache, if found, executes becomes faster, if not then
Oracle has to read from physical data files to buffer cache. For SELECT and INSERT
statements, locking is not necessary.
• During fetch operation, rows are fetched to user process.
File system
Base Tables Performance
created by Oracle Related Tables
at the time of
database creation
SYS Table SYSAUX
space Table space
Redo log file members
TEMP
Tablespace
Group-1
Control
USER USER Group-2
Files
DATA INDEX
Tablespace Tablespace
Group-3
UNDO
Tablespace
6, 7, 8, 9i Only one table space is required System Table space
10g/11g System and System Aux table spaces are required
PARSE ----- EXECUTE----FETCH
SQL Statistics
User Oracle + EP
User Program Server
Process Process Intstance
Interface
Valid Parse
SQL
Statement
Library
Data Block Cache
Buffer
Fetch
~ ~ SP
I/O Data
Dictionary
Cache
BT
UserData01.dbf RLB system01.dbf
d1.dbf d1.dbf
EP
UserData02.dbf
d1.dbf
LOG WRITER Details
SMON
Library
Data Block Cache
Buffer ICR -
Instance
DBWR ~ ~
Data
SP
Crash
Recovery
Dictionary
Cache
BT
UserData01.dbf RLB system01.dbf
d1.dbf d1.dbf
LGWR
Log
Switch
TX - 1 TX – 3 Continued
TX - 2 TX - 4
TX - 3 TX - 5
1 2
Redo Log
Files
Oracle Architecture
Transaction Example - Update
UPDATE table
SET user = ‘RAJESH’
WHERE id = 12345
Oracle Architecture
Transaction Example - Update
Oracle Architecture
Transaction Example - Update
Oracle Architecture
Transaction Example - Update
Oracle Architecture
Transaction Example - Update
Oracle Architecture
Transaction Example - Update
Oracle Architecture
Transaction Example - Update
1 ROW UPDATED
Oracle Architecture
Transaction Example - Update
COMMIT
Oracle Architecture
Transaction Example - Update
COMMIT
SUCCESSFUL
Oracle Architecture
Transaction Example - Update
Oracle Architecture
Transaction Example - Update
