Systems Global Area SGA

1
Architecture of an Oracle Database
User 1
Server CPU
Systems Global Area (SGA)
Server Process PGA 1
User 1s Process
Server Process PGA 2
User 2
. . .
User 2s Process
Instance
Process Monitor (PMON)
System Monitor (SMON)
Database Writer (DBWO)
Log Writer (LGWR)
Check point (CKPT)
Archiver (ARCO)
Database
Undo
Parameter File
System
Data
Redo Log 1
Archived Redo Logs
Control File
Index
Tools
Password File
Redo Log 2
Redo Log 3
Temp
. . .
2
Understanding Oracle Architecture Components

• NAU/CBA

3
Objectives

• Upon completion of this lesson you should
• Understand the architecture of the Oracle server
  and be able to describe its main components
• Be able to describe how users connect to an
  Oracle database and how Oracle supports
  client-server and N-tiered systems
• Be able to describe how the Oracle server
  operates in the processing of
• Queries
• DML statements
• Commits

4
The Password and Parameter files

• Password file
• allows access to Oracle when database instance
  is down
• Parameter file
• Starts up instance allocates SGA space
• Creates (1st run) or connects to control file
• Its parameter values control SGA space is used
  and affect how and when processes do their work

Parameter File
Password File
5
The Database

• The Control File
• Manages interactions of the Oracle instance with
  all of the other files of the database
• Maintains information on the status of the
  database files and coordinating information
  required for recovery in case of failure
• Is connected to an instance as soon as the
  instance is allocated by the parameter file
• If the only database file available when an
  instance is opened in NOMOUNT mode

Database
Undo
System
Data
Redo Log 1
Control File
Index
Tools
Redo Log 2
Redo Log 3
Temp
. . .
6
The Database

• The System Tablespace
• Contains data dictionary information (metadata)
• Is the default location for any objects created
  by users. Dont allow this the system
  tablespace should contain only metadata
• Is the only tablespace which must be created at
  the time a database is created
• Must always be on-line when the database is open

Database
System
Rollback
Data
Redo Log 1
Control File
Index
Tools
Redo Log 2
Redo Log 3
Temp
. . .
7
The Database

• Non-System Tablespaces
• Created as needed to enhance performance
• All user created objects should be stored in this
  type of tablespace
• One or more temporary tablespaces should be
  created to store scratch information during large
  sorts
• Tablespaces are logical structures a tablespace
  may span multiple physical files
• Tablespaces may be accessed randomly

Data
Database
Undo
Redo Log 1
System
Index
Control File
Tools
Redo Log 2
Redo Log 3
Temp
. . .
8
The Database

• Redo and Undo Files
• Redo log files
• Contain before and after images of data that have
  changed
• Are written ( thus must be read) sequentially
• Must have at least 2 redo log files in a group
• Written to in a circular fashion
• Used for recovery of entire system
• Undo Tablespace
• (Formerly Rollback)
• Consists of segments which are written to in a
  circular fashion

Redo Log 1
Database
Undo
System
Data
Control File
Index
Tools
Redo Log 3
Redo Log 2
Temp
. . .

• But can be retrieved randomly
• Used for failures involving a single user or a
  single process including simple rollbacks
• of unwanted changes

9
The Database Instance

• Resides in the server CPU
• Consists of the SGA and a set of background
  processes
• The SGA consists of memory structures configured
  by the parameter file and managed by server
  processes and the background processes
• Background processes are Oracle software elements
  which perform operations to maintain the instance
  and database.

As the name implies, background processes
normally do their work automatically as needed
without being explicitly invoked by the user
10
The SGA Shared Pool
Shared Pool
Systems Global Area (SGA)

• Library Cache
• Contains instructions (SQL statements) and their
  parsed execution plans needed to perform the work
  of the instructions
• Also called the shared SQL area
• Dictionary Cache
• Consists of data dictionary information needed
  for current processing
• As new entries in these areas the least recently
  used (LRU) entry is removed

11
The SGA
Shared Pool
Systems Global Area (SGA)

• Data Buffer Cache
• Consists of blocks of data retrieved from
  tablespaces as needed for processing
• All changes to database data occur here
• If a block is changed, both the before and after
  versions are initially kept in memory
• Least recently used (LRU) blocks are replaced
  when we run out of space
• Before replacing modified blocks
• must write to their tablespace in secondary
  storage and if necessary write before version to
  rollback tablespace

12
The SGA
Shared Pool
Systems Global Area (SGA)

• Redo Log Buffer
• Records before and after images of data in data
  buffer cache as it is modified (only changed
  bytes recorded)
• The data in this buffer is flushed to the redo
  log files of the database
• Whenever there is a commit
• When the buffer becomes more than 1/3rd full
• After a specified time period has elapsed
• Emptying of redo log buffer to redo log files
  must be complete before a user is notified that
  their transactions has been committed.
• Optional SGA structures
• Java Pool for Java code
• Large Pool

13
Background Processes

• Process monitor (PMON)
• Cleans up after failed user processes
• Releases locks held by process
• Reclaims memory and other resources allocated to
  the failed process
• Performs rollback of any unfinished transactions
  for the process
• System Monitor (SMON)
• Performs automatic recovery (from online redo
  logs) in case of system failure
• Responsible for automatic reclaiming of free
  space and de-allocation of temporary segments

Process Monitor (PMON)
System Monitor (SMON)
14
Background Processes

• Log Writer
• Flushes Redo Log Buffer by writing its contents
  to a Redo Log File
• When current redo log file is full a checkpoint
  is initiated and log writer switches to the next
  Redo Log File
• Switch occurs only if the prior checkpoint for
  the next Redo Log File has been completed

Log Writer (LGWR)
15
Background Processes

• Checkpoint
• Initiated by log writer at log switch
• Writes a checkpoint number to the redo log file
• Coordinates the process of seeing that all
  transactions completed (or rolled back) prior to
  the checkpoint time have their effects written
  back to secondary storage
• Writes a checkpoint number to the header of each
  tablespace and to the control file once step 3 is
  completed
• Also initiated by elapsed time or of
  transactions exceeding specified parameters

22
22
22
22
22
Check Point (CKPT)
16
Database Writer

• Writes dirty buffers from the data buffer cache
  to the database (including undo tablespace(s) )
  when
• Directed by the checkpoint process
• of free buffers gets too low
• When activated, makes copy of the buffer list and
  writes out all buffers dirty at that time

Buffer Status Free buffer data unchanged and
not in use Pinned buffer data in current
use Dirty buffer data has been changed
but not in current use
Database Writer (DBWR)
17
Archiver

• If archiving is turned on the archiver process
  writes from redo log files to off-line archival
  storage (disk or tape)
• Begins writing when a log file becomes inactive
• Must complete writing to archive before that redo
  log file is used again by log writer system hangs
  if this process is not completed

Archiver (ARCH)
Redo Log 1
Redo Log 2
Redo Log 3
Archived Redo Logs
18
The Oracle Server
Application/networkserver
Server
Users
19
Connecting to a Database
Serverprocess
Client
Server
Userprocess
User
20
Server Process

• Runs on the server machine (host)
• Services a single user process in the dedicated
  server configuration
• Uses an exclusive PGA
• Includes the Oracle Program Interface (OPI)
• Processes calls generated by the client
• Returns results to the client

21
Program Global Area (PGA)

• Is not shared
• Written to only by server process
• Contains
• Sort area
• Session Information
• Cursor state
• Stack space

Server Process PGA 1
22
Query Processing

• Parse
• Search for identical statement in Library Cache
• Check syntax, object names, and privileges
• Lock objects used during parse
• (Create and store execution plan or use execution
  plan for identical statement if found in step 1)
• Execute Identify rows selected (prepare to
  retrieve them to data buffer cache if necessary)
• Fetch Return rows to the user process

23
Query Processing Example
1
2
5
6
1. User requests SELECT wage from EMP
WHERE name Jones
3
4
2. Search Library cache for identical
statement
3. Retrieve metadata, check syntax, etc.,
lock objects create store execution
plan.
4. Retrieve data from tablespaces to Data
Buffer Cache, if needed
5. Identify and retrieve to server process
data to meet users request
6. Return result to user wage
850
24
DML Statement Processing
1
5
3
4
1. User sends SQL statement UPDATE EMP SET
wage 925 WHERE name Jones
2
2. Data to be modified retrieved to data
buffer cache (if necessary) and
locks acquired
3. Write before and after image of changes to
redo log buffer
4. Copy block in data buffer area modify 1
copy keep old copy for rollback
5. Inform user of result 1
Record Updated
25
COMMIT Processing
1
2
4
5

• 1. User process requests a
• commit

2. Server Process sends a COMMIT to the Redo Log
Buffer, a System Change Number (SCN) is assigned
3
3. Log writer writes all new entries in redo log
buffer to redo log file
5. Undo data blocks associated with the
transaction are released
4. User is informed that transaction has been
committed
26
Summary

• You should now understand the basic elements of
  the Oracle server architecture
• The Oracle database consists of
• Control file
• Database tablespaces
• System tablespace
• Rollback tablespace(s)
• Temporary tablespace(s)
• Data, index, tablespaces as needed to support
  processing
• Redo log files
• The Password file and Parameter file
• Offline archive files (optional but highly
  recommended)

27
SUMMARY (Continued)

• The Oracle Instance contains
• Systems Global Area (SGA)
• Shared Pool
• Library Cache
• Dictionary cache
• Database Buffer Cache
• Redo Log File
• Server Processes (usually 1 per user session)
• The server process has a Program Global Area
  (PGA) and
• Connects to a user process which performs some
  database related functions on the client machine
• Background Processes
• LGWR, PMON, SMON, CKPT, DBWO, ARCO

28
SUMMARY (Continued)

• Query Processing consists of three steps
• Parse (if identical statement not in Library
  Cache),
• Execute, and
• Fetch
• DML Statement Processing
• Locks records to be updated
• Writes before and after images to redo log
• Maintains linked current and rollback data blocks
• Commit processing
• Ensures that redo log information for a
  transaction is written to secondary storage (redo
  log files) before telling a user their
  transaction is committed
• Marks rollback data blocks as free for reuse