Transcript INSYS365: Week 4, Class 1

INSYS365
Oracle Memory
Internals
7/18/2015
ISYS365, Oracle Memory
Internals
1
Oracle Memory
Structures

Oracle instance




7/18/2015
Have names
Memory buffers (SGA)
Background processes (Oracle
processes)
Parallel servers may share a
database
ISYS365, Oracle Memory
Internals
2
Oracle Memory
Structures

PGA (Program/Private/Process
Global Area)


Stores data and control information
for a single user process
Created when a user process connects
to database (e.g., SQL*Plus)



Stack space, containing contents of
variables and arrays that the session is
using
Session information
All this depends on whether you are
running a multi-threaded server
7/18/2015
ISYS365, Oracle Memory
Internals
3
Oracle Memory
Structures

SGA (System/Shared Global
Area)


Stores data that the user has
retrieved from the database or
that the user wants to stored in
the database. The importance
of this is due to the high cost of
disk I/O.
Contains




7/18/2015
Database buffer cache
Redo log buffer
Shared pool
Cursors
ISYS365, Oracle Memory
Internals
4
Oracle Memory
Structures

SGA (System/Shared Global
Area)

Contains

Database buffer cache





Redo log buffer

7/18/2015
1. Data blocks (written by DBWR
process). Buffer size and number
of buffers are specified in
INIT<SID>.ORA
2. Index blocks
3. Rollback segment blocks
4. Temporary segment blocks
Written to the Redo logfiles by
LGWR
ISYS365, Oracle Memory
Internals
5
Oracle Memory
Structures

SGA (System/Shared Global Area)

Contains

Shared pool




Cursors




7/18/2015
1. Data dictionary cache: most frequently
asked questions of the data dictionary
2. Library cache: PL/SQL statements and
its execution plan (e.g., whether indexes
will be used)
3. Control structures: locks, cache handles,
etc.
1. Parsed SQL statements (the parsed
representation of a SQL statement)
2. Execution plan (algorithm for executing
the statement)
3. Number of rows affected
4. In the case of a SELECT, the rows
returned
ISYS365, Oracle Memory
Internals
6