Oracle

Oracle Database
The Oracle relational database management system (RDBMS) provides an open, comprehensive, integrated approach to information management

Connecting to a Server

Client

Middle tier

Server

Multitier architecture shown

1-3

Copyright © 2007, Oracle. All rights reserved.

1-4

Copyright © 2007, Oracle. All rights reserved.

Oracle Database Architecture:Overview
Instance
SMON PMON RECO Others

Connecting to the Database
• Connection: Communication between a user process and an instance • Session: Specific connection of a user to an instance through a user process

SGA

Shared pool Library cache Data dictionary cache
LGWR ARCn

Database buffer cache PGA Server process
DBWn

Redo log buffer

CKPT

User

SQL> Select …

User processServer process

Session

User process Data files

Control files Database

Online redo log files

Archived log files Session

Connection

1-6

Copyright © 2007, Oracle. All rights reserved.

1-7

Copyright © 2007, Oracle. All rights reserved.

Interacting with an Oracle Database
User process Server process

Oracle Database Server Structures
Memory structures
Sharedpool Library cache
User process Server process

Instance SGA

Instance SGA Shared pool Library cache Data dictionary cache
ARCn RECO Others

Database buffer cache

Redo log buffer

Database buffer cache

Redo log buffer

Data dictionary cache

Processes
DBWn CKPT LGWR SMON PMON ARCn RECO Others

DBWn

CKPT

LGWR

SMON

PMON

User Database

Storage structures

Datafiles

Control files

Online redo log files

1-8

Copyright © 2007, Oracle. All rights reserved.

1 - 10

Copyright © 2007, Oracle. All rights reserved.

1

Oracle Database Memory Structures
Server process 1 PGA Server process 2 PGA

DB structures Memory - Process - Storage

Database Buffer Cache
• Is part of the SGA • Holds copies of data blocks that are read from datafiles • Is shared by all concurrent users

Background process

PGA

Shared SQL area Library cache Redo log buffer Database buffer cache Java pool Streams pool

Data dictionary cache Other

SGA Instance SGA Shared pool Library cache Data dictionary cache
ARCn RECO Others

Shared pool I/O buffer Response queue Free memory Request queue

Database buffer cache

Redo log buffer

DBWnCKPT

LGWR SMON

PMON

Large pool

1 - 11

Copyright © 2007, Oracle. All rights reserved.

1 - 13

Copyright © 2007, Oracle. All rights reserved.

Redo Log Buffer
• Is a circular buffer in the SGA • Holds information about changes made to the database • Contains redo entries that have the information to redo changes made by operations such as DML and DDL
Instance SGA Shared poolLibrary cache Data dictionary cache
RECO Others DBWn CKPT LGWR SMON

Shared Pool
• Is a portion of the SGA • Contains:
– Library cache
—

Shared SQL area

– Data dictionary cache – C t l structures Control t t
Instance SGA Shared pool Library cache Data dictionary cache
RECO Others

Database buffer cache

Shared SQL area

Redo log buffer

Data dictionary cache

Databasebuffer cache

Redo log buffer

DBWn

CKPT

LGWR

SMON

PMON ARCn

Library cache

Other

PMON ARCn

1 - 14

Copyright © 2007, Oracle. All rights reserved.

1 - 15

Copyright © 2007, Oracle. All rights reserved.

• A shared SQL area contains the parse tree and execution plan for a given SQL statement. • When a new SQL statement is parsed, Oracle Database allocates memory fromthe shared pool to store in the shared SQL area. • Oracle Database processes PL/SQL program units (procedures, functions, packages, anonymous blocks, and database triggers) in much the same way it processes individual SQL statements. Oracle Database allocates a private area to hold values specific to the session that runs the program unit, including local, global, and package variables (also...