memory structure of oracle

8/3/2019 Memory Structure of Oracle

1/15

MemoryStructure

of

Oracle


2/15

Oracle Server

Oracle Server is the combination of Oracle Instanceand Oracle Database.

Oracle Instance is consist of SGA

(Shared Global Area) and some Backgroundprocesses.

Oracle Database consist of three file types,Data files, Control files, and Redo log files.

Oracle Server

Oracle Database Oracle Instance


3/15

Connection Processes

During connection to Oracle Server using SQL *Plus, Developer, Report,etc, a User Process is created.

User Process then connect to the Server Process. The Server Processcheck the user privileges and then connect to the Oracle Server.

Note: The User Process cannot directly interact with Oracle Server.

UserProcess

ServerProcess

Oracle Server


4/15

PMON SMON CKPT DBWR LGWR

Shared Pool

Library

Cache

Data Dict

Cache

Instance

SGA

DataFiles

ControlFiles

Redo logFiles

Database

Undo

Segment

Memory Structure and Processes

Database

Buffer

Cache

Redo log

Buffer

Cache

DBWR: Database writer is use to write

blocks in data files from DatabaseBuffer Cache.

LGWR: Log writer writes blocks in theRedo log files from Redo log BufferCache.

PMON: Process Monitor cleans up afterfailed processes.

SMON: System Monitor is use forInstance recovery.

CKPT: Check point is just like a buzzer,Check point force DBWR and LGWR to

write the data from caches to files.


5/15

Shared Pool

Library

Cache

Data Dict

Cache

Instance

SGA

DataFiles

ControlFiles

Redo logFiles

Database

Shared Pool

1. Library Cache stores informationabout the most recently used SQLand PL/SQL statements.

2. Data Dictionary Cache is a

collection of the most recent useddefinition in the database.


6/15

DBWR

Instance

SGA

DataFiles

Database

DatabaseBuffer

Cache

Database Buffer Cache

Database Buffer Cache stores the copyof the tables which are query by astatement.

DBWR process writes the modified

records in the Data files from DatabaseBuffer Cache.

Check point is just like a buzzer, Checkpoint force DBWR to write the data fromcache to files.

CKPT


7/15

LGWR

Instance

SGA

DataFiles

Redo logFiles

Database

Undo

Segment

Redo Log Buffer Cache

Database

Buffer

Cache

Redo log

Buffer

Cache

The Redo Log Buffer Cache records allchanges made to the Database BufferCache.

The main purpose of Redo Log Buffer isRecovery

LGWR process writes the records in theRedo log files from Redo Log BufferCache after every Commit, 3 seconds

Undo Segment stores changed records.After ROLLBACK, the previous data

come from undo segment to theDatabase buffer cache.


8/15


Shared Pool

Library

Cache

Data Dict

Cache

Instance

SGA

DataFiles

ControlFiles

Redo logFiles

Database

Undo

Segment

Process behind SQL statement

Database

Buffer

Cache

Redo log

Buffer

Cache

UserProcess

ServerProcess

Select * fromemp;

EmptableParse

Execute

Fetch

Phases

Check statement syntax.

Privileges checking.

Execution plan checking

Prepare Execution plan.

In case of selection,

simple execute.

In fetch stage Oraclecollect the rows and returnto the user process

Check statement syntax.

Privileges checking.

Execution plan checking

In fetch stage Oraclecollect the rows and returnto the user process


9/15


Shared Pool

Library

Cache

Data Dict

Cache

Instance

SGA

DataFiles

ControlFiles

Redo logFiles

Database

Undo

Segment

Process behind DML statement

Database

Buffer

Cache

Redo log

Buffer

Cache

UserProcess

ServerProcess

Emp Table

Update fromemp set sal =2000 wheredeptno = 10;

ROLLBACK;COMMIT;

Parse

Execute

Phases

Prepare execution plan.

In this case, Oraclelocks the rows which are

effected.


10/15


Shared Pool

Library

Cache

Data Dict

Cache

Instance

SGA

DataFiles

ControlFiles

Redo logFiles

Database

Undo

Segment

Read Consistency Mechanism

Database

Buffer

Cache

Redo log

Buffer

Cache

UserProcess

ServerProcess

Emp Table


Scott 1

Scott 2

Select *from emp;

20 & 30

10


11/15

SMON CKPT DBWR LGWR

Shared Pool

Library

Cache

Data Dict

Cache

Instance

SGA

DataFiles

ControlFiles

Redo logFiles

Database

Function of PMON Process

Database

Buffer

Cache

Redo log

Buffer

Cache

UserProcess

ServerProcess

Emp Table


PMON

Undo

Segment

Releasing Other resources.

Releasing Locks.

Rolling back the transaction.


12/15

SMON

DataFiles

CKPT DBWR LGWR

Shared Pool

Library

Cache

Data Dict

Cache

Instance

SGA

ControlFiles

Redo logFiles

Database

Function of SMON Process

Database

Buffer

Cache

Redo log

Buffer

Cache

UserProcess

ServerProcess

Emp Table

Commit;

PMON

Undo

Segment

SMON performs Instancerecovery when database is

reopened.


13/15

Tuning SQL statement

Oracle Server uses the Library cache to store SQLand PL/SQL statement.

To find whether a statement is already cached, theOracle server

Reduce the statement to the numeric value of

the ASCII text.

Select empno, ename, job, sal from emp;

Converted in to ASCII text.

**************************************************


14/15

Tuning SQL statement

Select empno, ename from emp

Where deptno = 10;


**************************************************


Where deptno = 20;


**************************************************


Where deptno = &deptno;


*************************************************

1

2

3


15/15

Questions

memory structure of oracle

Documents