11

Oracle Architectural Components

Oracle Architectural Components

1-1-22

ObjectivesObjectives

• Listing the structures involved in connecting a user to an Oracle server

• Listing the stages in processing a query

• Listing the stages in processing a DML statement

• Listing the stages in processing COMMITS

• Listing the structures involved in connecting a user to an Oracle server

• Listing the stages in processing a query

• Listing the stages in processing a DML statement

• Listing the stages in processing COMMITS

1-1-33

The Oracle ServerThe Oracle Server

Server

Application/networkserver

Users

Oracle server

1-1-44

Connecting to a DatabaseConnecting to a Database

UserUser

ClientClient ServerServerServerServer

processprocess

Oracle serverUserUserprocessprocess

1-1-55

User ProcessUser Process

• Runs on the client machine

• Is spawned when a tool or an application is invoked

• Runs the tool or application (SQL*Plus, Server Manager, Oracle Enterprise Manager, Developer/2000)

• Includes the User Program Interface (UPI)

• Generates calls to the Oracle server

• Runs on the client machine

• Is spawned when a tool or an application is invoked

• Runs the tool or application (SQL*Plus, Server Manager, Oracle Enterprise Manager, Developer/2000)

• Includes the User Program Interface (UPI)

• Generates calls to the Oracle server

1-1-66

Server ProcessServer 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

• 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

1-1-77

Oracle InstanceOracle Instance

Background processes

An Oracle instance:

• Is a means to access an Oracle database

• Always opens one and only one database

An Oracle instance:

• Is a means to access an Oracle database

• Always opens one and only one database

Instance

SGA

1-1-88

Oracle DatabaseOracle Database

Control files

Redo logfiles

Data filesPasswordfile

Parameterfile

Archivedlog files

Database

1-1-99

Database

Other Key Physical StructuresOther Key Physical Structures

Archivedlog files

Passwordfile

Parameterfile

1-1-1010

Processing a QueryProcessing a Query

• Parse:Parse:

- Search for identical statement- Search for identical statement

-Check syntax, object names, privilegesCheck syntax, object names, privileges

- Lock objects used during parseLock objects used during parse

-Create and store execution planCreate and store execution plan

• Execute: identify rows selectedExecute: identify rows selected

• Fetch: return rows to user processFetch: return rows to user process

1-1-1111

UserUserprocessprocess

ServerServerprocessprocess

Processing a Query (cont.)Processing a Query (cont.)

ParseParse

StatementStatement

OKOK

HandleHandle

ExecuteExecute

OKOK

HandleHandle

FetchFetch

ResultsResults

SELECT *

FROM emp

ORDER BY ename;

SELECT *

FROM emp

ORDER BY ename;

1-1-1212

The Shared PoolThe Shared Pool

• Size defined by SHARED_POOL_SIZE

• Library cache contains statement text, parsed code, and an execution plan

• Data dictionary cache contains table and column definitions and privileges

• Size defined by SHARED_POOL_SIZE

• Library cache contains statement text, parsed code, and an execution plan

• Data dictionary cache contains table and column definitions and privileges

Shared pool

Librarycache

Datadictionary

cache

1-1-1313

Database Buffer CacheDatabase Buffer Cache

• Number of buffers defined by DB_BLOCK_BUFFERS

• Size of a buffer based on DB_BLOCK_SIZE

• Stores the most recently used blocks

• Number of buffers defined by DB_BLOCK_BUFFERS

• Size of a buffer based on DB_BLOCK_SIZE

• Stores the most recently used blocks

1-1-1414

Program Global Area (PGA)Program Global Area (PGA)

• Not shared and not writable

• Contains

– Sort area

– Session information

– Cursor state

– Stack space

• Not shared and not writable

• Contains

– Sort area

– Session information

– Cursor state

– Stack space

PGAServerServerprocessprocess

1-1-1515

Processing a DML StatementProcessing a DML Statement

1.1. If data blocks not in the buffer, server If data blocks not in the buffer, server process reads them into the bufferprocess reads them into the buffer

2.2. Server process places locks on rows to Server process places locks on rows to be modifiedbe modified

3.3. The redo log buffer is modified with the The redo log buffer is modified with the changed valueschanged values

4.4. The data blocks are changedThe data blocks are changed

5.5. Before image is recorded in the rollback Before image is recorded in the rollback blockblock

1-1-1616

Instance

SGA Shared pool

Librarycache

Redo logbuffer

Database buffercache

Datadictionary

cache

UPDATE emp

SET sal=sal*1.1

WHERE empno=7369

UPDATE emp

SET sal=sal*1.1

WHERE empno=7369

Processing a DML Statement (cont.)Processing a DML Statement (cont.)

1

ServerServerprocessprocess

Control files

Redo logfiles

Data files

Database

2

3

4

5

1-1-1717

Rollback Segment Rollback Segment

DML DML statementstatement

Old Old imageimage

New New imageimage

Rollback Rollback segmentsegment

TableTable

1-1-1818

Redo Log BufferRedo Log Buffer

• Size defined by LOG_BUFFER

• Records changes made through the instance

• Used sequentially

• Circular buffer

• Size defined by LOG_BUFFER

• Records changes made through the instance

• Used sequentially

• Circular buffer

1-1-1919

Other Instance ProcessesOther Instance Processes• Other required processesOther required processes

- - Database Write (DBW0)Database Write (DBW0)

- Log Writer (LGWR)- Log Writer (LGWR)

- Process Monitor (PMON)- Process Monitor (PMON)

- System Monitor (SMON)- System Monitor (SMON)

- Checkpoint (CKPT)- Checkpoint (CKPT)

• Archive process (ARC0) is optional; is Archive process (ARC0) is optional; is used in production databaseused in production database

1-1-2020

Instance

SGA Shared pool

DBWR

Database buffercache

Database Writer (DBWR)Database Writer (DBWR)

Control files

Redo logfiles

Data files

DBW0 writes DBW0 writes when:when:• There are many There are many dirty buffersdirty buffers

• There are few There are few free buffersfree buffers

• Timeout occursTimeout occurs

• Checkpoint Checkpoint occursoccurs

1-1-2121

Instance

SGA Shared pool

Redo logbuffer

LGWR

Log Writer (LGWR)Log Writer (LGWR)

Control files

Redo logfiles

Data files

LGWR writes LGWR writes when:when:• There is a commitThere is a commit

• The redo log buffer The redo log buffer is one-third fullis one-third full

• There is more than There is more than 1MB of redo1MB of redo

• Before DBW0 writesBefore DBW0 writes

1-1-2222

COMMIT ProcessingCOMMIT Processing

1.1. Server process places a commit record Server process places a commit record and system change number (SCN) in and system change number (SCN) in redo log buffer.redo log buffer.

2.2. LGWR writes redo log to redo log files.LGWR writes redo log to redo log files.

3.3. User is informed COMMIT is completeUser is informed COMMIT is complete

4.4. Server process records trans. Is Server process records trans. Is complete and locks can be released.complete and locks can be released.

1-1-2323

Shared pool

Redo logbuffer

LGWR

Control files

Redo logfiles

Data files

Database

COMMIT Processing (cont.)COMMIT Processing (cont.)

ServerServerprocessprocess

1

2UserUser

processprocess

3

Database buffercache4

SGA

Instance

1-1-2424

SMON: System MonitorSMON: System Monitor

• Automatically recovers the instanceAutomatically recovers the instance

-Rolls forward changes in redo logsRolls forward changes in redo logs

-Opens the database for user accessOpens the database for user access

-Rolls back uncommitted transactionsRolls back uncommitted transactions

• Coalesces free spacesCoalesces free spaces

• Deallocates temporary segmentsDeallocates temporary segments

1-1-2525

PMON: Process MonitorPMON: Process Monitor

Cleans up after failed processes by:Cleans up after failed processes by:

• Rolling back the transactionRolling back the transaction

• Releasing locksReleasing locks

• Releasing other resourcesReleasing other resources

1-1-2626

ArchivingArchiving• Database archive modeDatabase archive mode

- NOARCHIVELOG for databases that do - NOARCHIVELOG for databases that do not require recovery after disk failurenot require recovery after disk failure

- ARCHIVELOG mode for production- ARCHIVELOG mode for production

•ARC0 processARC0 process

• Automatically archives online redo Automatically archives online redo log fileslog files

• Preserves the record of all changes Preserves the record of all changes made to the databasemade to the database

1-1-2727

Instance

SGA Shared pool

DBWR LGWR

Database

Control files

Data files Redo logfiles

UserUserprocessprocess

ServerServerprocessprocess

PGA

SummarySummary

Passwordfile

Archivedlog files

Parameterfile

1-1-2828

Summary (cont.)Summary (cont.)You should have learned:You should have learned:

• Explain the SGA, Instance, memory Explain the SGA, Instance, memory structuresstructures

• Explain the database filesExplain the database files

• Explain the primary background processes Explain the primary background processes (DBW0, LGWR, CKPT, PMON, SMON, ARC0)(DBW0, LGWR, CKPT, PMON, SMON, ARC0)

•Explain SQL processing stepsExplain SQL processing steps

•Explain the COMMIT processExplain the COMMIT process