Oracle Memory

Muchake Brian
Phone: 0701178573
Email: bmuchake@cis.mak.ac.ug, bmuchake@gmail.com

Do not Keep Company With Worthless People

Psalms 26:11
Oracle Instance -Review
 Previously, oracle instance was defined as a single unit that serves the
function of caching data and performing routine operations required for
transactional processing via a set of background processes.
 But on a deeper analysis the oracle instance is actually more complex.
System Global Area (SGA)
System Global Area
 The System Global Area (SGA) is the first part of the oracle instance that
is use to cache data and metadata in memory.
 The SGA is a group of shared memory structures known as individual SGA
components which are used to cache user-generated data, that is, rows
from data-based tables, as well as various other control information that
the oracle instance requires.
 Note: The SGA is the name used to represent a collection of various
caches in memory. The buffer cache, shared pool, and others are all
components of the single SGA.
System Global Area [Cont’d]
System Global Area [Cont’d]
 While each of these individual caches serves its own unique purpose,
luckily for system administrators, they don’t need to manually size each
of the components individually.
 When configuring the database, a couple of parameters are used to
determine the overall size of the SGA as a whole.
 These parameters are, (i) SGA_MAX_SIZE, and (ii) SGA_TARGET.
 We set the maximum size for the entire SGA, say 64 gigabytes, and its up
to oracle to determine how much of the total SGA it should allocate for
each individual memory component based on demand as well as memory
availability.
System Global Area [Cont’d]
 We can of course override automatic sizing if we prefer.
Shared Pool
 In the oracle context, the shared pool is the most important area inside
the SGA that is used to cache non-user data. (i.e. stuff other than the
actual database rows from the actual tables).
 There are various other database constructs that, if shared among users,
would speed up the performance of various database activities.
 In order to that the shared pool, in itself, is divided into yet another
subset of caches, each with its own dedicated purpose. The main ones
are the oracle data dictionary cache, and the library cache.
 The data dictionary cache is a collection of oracle maintained database
tables and views containing reference information about the database
structure and its users. Basically metadata about the database. Think of
it as the place to cache control information that is essential for the oracle
database to operate.
Shared Pool [Cont’d]
 The oracle database accesses the oracle dictionary frequently.
Specifically, every time a user executes a sequel query, there is a lot of
metadata that is required for a sequel query to execute properly
Metadata such as referential integrity information, table definitions and
structure, annexing information, and so on.
 Now, while this data is also stored on this as part of the oracle system
tablespace, oracle also caches this data into memory to improve
performance. That is where the data dictionary cache comes into play.
 The Library Cache is another area inside a shared pool. Oracle uses the
library cache to store the cached information about each sequel
statement that is executed. This is important because when you execute
a sequel statement the oracle database has a lot of work it has to do.
Shared Pool [Cont’d]
 For example before begin executing the sequel statement itself, it has to
parse the statement, create what is known as an execution plan, which is
basically the way the oracle database determines the best way to retrieve
the data required by the sequel statement, such as which indexes to use.
 This parsing process can take quite a lot of time. Well usually merely
seconds per sequel statement, but when a number of users execute a lot
of sequel statements in parallel, it can amount to a lot of CPU work for a
database.
 Oracle being an efficient database and all, will try to cache the parsed
state of the sequel statement in memory so that these two users are
executing the same sequel statement.
 For sequel parsing will only have to occur once. Only when the first users
executed a sequel statement.
Shared Pool [Cont’d]
 The first time a sequel statement is executed is known as a hard parse,
and it is considered the most resource demanding parse that can occur
for any given sequel statement.
 Subsequent executions of the same sequel statement, assuming it
identical, even they originate from different users, can use the already
parsed presentation of the sequel statement stored in memory inside the
library cache. This is known as a soft parse and is considered a much
lighter and far less demanding, sequel parse event.
 The second user will be able to use the already parsed version of the
sequel statement that has been stored in memory. This improves
database performance when a sequel statement goes to a soft parse,
compared to a hard parse.
Shared Pool [Cont’d]
 Especially when you consider a production database with hundreds or
thousands of sequel executions every second. It adds up to a lot of time.
 There are some additional caches inside the shared pool, but they are not
as important as the two discussed above.
Database Buffer Cache
 Alongside the shared pool, buffer cache is one of the most important
components of the SGA.
 Unlike the shared pool which caches parsed SQL statements, and
database method data, the buffer cache is responsible for actually
caching database user data. Rows from tables.
 When a user runs an SQL statement, if the data is read from disk,
performance will suffer, as disks, no matter how fast they are, will always
be slower than RAM, than memory.
 Thus if the user can get the data required for the SQL statement directly
from memory, the performance will increase tremendously.
 That is why caching data in memory is very important, and for that
specific purpose we have the buffer cache area inside the SGA.
Database Buffer Cache [Cont’d]
 The buffer cache caches frequently accessed database data into memory,
so that performance can benefit.
 Its important to note that the buffer cache actually caches blocks of data
instead of individual rows. The way that oracle stores our data physically
on disk is different compared to how we logically structure our database.
 For us we work with tables and rows but oracle stores it as chunks of
sequential data known as oracle blocks. Each oracle block contains one
or more rows for any given table.
 Each time a use session accesses a row, the entire block is read and
cached in the buffer cache.
 By default an oracle database block is eight kilobytes in size, but this can
be changed.
Database Buffer Cache [Cont’d]
 Note: Oracle is smart enough to make sure blocks which are loaded into
the buffer cache always satisfy the database read consistency model.
 All users who are concurrently connected to the database share and
access a single database buffer cache, having access to a single unified
view of all cache database data.
 Also oracle automatically manages which data is cached into the buffer
cache.
 Given that RAM is a limited resource, oracle makes sure that only the
most frequently read data from the disk will the cached into memory, and
data that is seldom accessed will eventually be aged out of the buffer
caches so that it will make room for more frequently accessed data.
 The buffer cache is divided into subcomponents including the keep pool,
recycle pool, as well as others.
Database Buffer Cache [Cont’d]
 The keep pool is a component inside the buffer cache which allows a
database administrator to pin certain sets of data into memory and make
sure that they never age out of the cache, even if they are seldom
accessed.
 Recycle pool is a reusable data buffer for transient data blocks. Transient
data blocks are blocks that are read as parts of large table full table
scans and are not likely to be needed again soon.
Database Buffer Cache [Cont’d]
Redo Log Buffer
 The Redo Log Buffer is another essential component of the SGA.
 The Redo Log Buffer is a circular buffer that stores information pertaining
the changes made to the database.
 The Redo Log Buffer is specifically designed to store something known as
redo entries. Redo entries are small records that reflect any changes
made to the database as part of transactions or database structure
changes, also known as DML-data modification language commands and
DDL-Data definition language command.
 Consider Redo entries as database change vectors. Redo entries are used
for database recovery when and if needed.
Redo Log Buffer [Cont’d]
 These Redo entries contain the information necessary to reconstruct or
redo, hence the name, changes that have occurred to the database
incase the database failed midway during a transaction.
 The Redo entries are considerably smaller compared to the actual data
blocks which are stored in the buffer cache.
 The Redo Log entries in the Redo Log Buffer are also written on a
periodic basis to a set of files in our oracle database storage known as
redo logs.
 When a user modifies data in the database, the changes to the rows in
the database are applied both to the actual rows themselves which are
cached in the buffer cache, but also special redo entries are generated
and stored inside the redo log buffer. Remember these are database
change vectors.
Redo Log Buffer [Cont’d]
 For each row that is modified, and your redo entry is created inside the
Redo Log buffer, because redo entries contain just the changes they are
much smaller and can be written to disk at a much faster rate compared
to writing the contents of entire database blocks from the buffer cache.
 So when a user commits in order to ensure the transaction is safe and
secure on our database storage, not just containing memory which is
very risky.
 The redo entries for that specific transaction will be written to disc
synchronously and immediately when the user issued the commit
command. Immediately after the user gets a notification that the
transaction has been stored, and thus its now safe on our database.
 It is sufficient for oracle to have the redo entries for a transaction stored
on a disc for it to be consistent.
Redo Log Buffer [Cont’d]
 No need to wait for all of the database blocks from the buffer cache
containing the actual rows modified by the transaction to be written to
disc, as well.
 This can be written asynchronously in the background.
 Thus when users modify data, both the actual data blocks are modified
inside the buffer cache but also redo entries are generated into the redo
log buffer.
 When a user decides to commit a transaction and thus store it
permanently on the disc, all that oracle has to do is to make sure the
redo entries from the redo log are written to the disc.
 These are small sets of data as they only represent the vector changes
made to the rows and not the entire rows themselves.
Redo Log Buffer [Cont’d]
 The actual modified database blocks in the buffer cache will also be
written to disc, but asynchronously as they are much bigger and slower.
 If the database fails immediately after a transaction has completed, so
that only the redo entries for the transaction has been stored on disc, the
oracle database can use these redo entries to reconstruct the transaction
and ensure no data will be lost.
Other SGA Pools and the PGA
Large Pool
 Large pool contain memory used by special oracle features. Such as
distributed transactions, shared server processes, and oracle backup and
recovery operations.
Java Pool
 Java pool is used to store all session specific java code and data inside a
specific java machine (JVM) and a streams pool which provides memory
for oracle streams processes.
PGA
 Each time a user session needs a slice of private memory it gets its own
PGA also known as the private global area.
 The PGA is used by clients connecting to the database as a sort of
staging or- temporary in memory area containing various run time
information about sequel cursors as well as memory used for heavier
sequel operations such as ordered by, group by, joins
 So each user session gets its own dedicated PGA and the contents of that
PGA exists solely for the duration of that session.
 When a client disconnects, that specific PGA gets cleared.