Download - Memory Structure of Oracle
-
8/3/2019 Memory Structure of Oracle
1/15
MemoryStructure
of
Oracle
-
8/3/2019 Memory Structure 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
-
8/3/2019 Memory Structure of Oracle
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
-
8/3/2019 Memory Structure of Oracle
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.
-
8/3/2019 Memory Structure of Oracle
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.
-
8/3/2019 Memory Structure of Oracle
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
-
8/3/2019 Memory Structure of Oracle
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/3/2019 Memory Structure of Oracle
8/15
PMON SMON CKPT DBWR LGWR
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
-
8/3/2019 Memory Structure of Oracle
9/15
PMON SMON CKPT DBWR LGWR
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.
-
8/3/2019 Memory Structure of Oracle
10/15
PMON SMON CKPT DBWR LGWR
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
Update fromemp set sal =2000 wheredeptno = 10;
Scott 1
Scott 2
Select *from emp;
20 & 30
10
-
8/3/2019 Memory Structure of Oracle
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
Update fromemp set sal =2000 wheredeptno = 10;
PMON
Undo
Segment
Releasing Other resources.
Releasing Locks.
Rolling back the transaction.
-
8/3/2019 Memory Structure of Oracle
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.
-
8/3/2019 Memory Structure of Oracle
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.
**************************************************
-
8/3/2019 Memory Structure of Oracle
14/15
Tuning SQL statement
Select empno, ename from emp
Where deptno = 10;
Converted in to ASCII text.
**************************************************
Select empno, ename from emp
Where deptno = 20;
Converted in to ASCII text.
**************************************************
Select empno, ename from emp
Where deptno = &deptno;
Converted in to ASCII text.
*************************************************
1
2
3
-
8/3/2019 Memory Structure of Oracle
15/15
Questions