All language subtitles for [SubtitleTools.com] The Instance - System Global Area - Learning Oracle 12c [Video]

In this lesson, we're going
to take a look at the System

Global Area, or SGA.

The SGA is a pod of
the Oracle instance.

So the instance is composed of
background processes and memory

caches.

And the System Global
Area is the main cache

that we think of when
we think of memory areas

that Oracle uses.

It's been said in Oracle
that everything is a cache,

or everything is in cache.

That's why the caches can be
so important to processing

in Oracle because operations
don't occur on disk,

they happen in memory.

Volatile storage, such as RAM,
is so much faster, usually

an order of magnitude faster,
than non-volatile storage

like disk.

So the SGA is the
System Global Area,

or it could also be thought
of as the shared global area

because the SGA represents the
shared memory used by Oracle.

And shared memory is absolutely
crucial for a database system.

The reason is that you have
many different users that

access a database.

If all of those
users are completely

siloed from each
other, they don't

get the benefit of being able
to share anything in memory.

That's why shared memory is
important to an enterprise

system, because it
allows one user to be

able to benefit from the
work that was already

done by another user.

The first portion of the SGA
is the database buffer cache.

In Oracle, database
blocks are read from disk

into buffers in memory,
and those buffers

form the database buffer cache.

So all of the data that's
read from the database

into memory to become accessible
to other users, all of that

is written into the
database buffer cache.

That makes it a very, very
crucial portion of the SGA.

Users can require the
same data at times.

So if a user does a
query, they perform

a query that brings employee
information into the SGA,

and it stays in
the database buffer

cache, the reason it's
held in the buffer

cache is that another user
might execute a query that

requires the same data.

So rather than another process
reading the data again,

that data is already
in memory, it's

already available for the second
user that came in as well.

This is a crucial component
of how Oracle manages

a high performance system.

The database buffer cache
keeps data in the cache,

based on what's called
the LRU algorithm.

LRU stands for
Least Recently Used.

So this algorithm defines
the rules or the conditions

for which the database
writer process will

write the buffers back out
of memory down to disk.

And it's not the oldest
blocks in memory--

it's the least
recently used blocks.

The second portion of the System
Global Area is the shared pool.

So let's look at the
concept of parsing.

We execute this Select
statement that you see here--

Select first name
from employees.

Let's think about
what actually happens

when we execute this query.

It has to be parsed,
which means it

has to be broken down
into a machine language

that the computer
can understand.

It has to be checked for
syntactical correctness.

It has to be checked
for accuracy.

So does the employees
table exist?

Does the first name column
exist in the employee's table?

Does the user have
access to read

that table, do they have
the proper permissions.

So all of those
things have to happen.

But one of the most
important things

that happens during a
parse is that the statement

is broken down and
Oracle decides on what's

called an execution plan.

What's the best way for Oracle
to bring that data from disk

into memory and make
it available to users.

Should it read every
row in the table?

Should it use an index?

Should it use a partition?

All of those things go
into the execution plan.

So it turns out that just
executing a simple SQL

statement can be an
expensive operation.

There's a lot of work
that goes into it.

And so Oracle is
designed, again,

as a high performance system
to store the execution

plans of SQL statements
from different users,

and to store them in what's
called the shared pool.

The specific portion
of the shared pool

that stores this information
is called the library cache.

So it will store the shared SQL.

The second portion
of the shared pool

is called the Data
Dictionary cache,

which stores database metadata.

So anytime, a SQL
statement is executed.

Information is needed
from the data dictionary

in the database.

And so, again, as a
high performance system,

rather than reread all of that
data dictionary information,

it is stored in memory as well.

The next memory area of
the System Global Area

is the log buffer.

So the log writer process
will write any changes

that occur in the database to
memory first, then from memory

onto disk.

The memory area that is written
to by the log writer process

is called the log buffer.

So it is the small area
that holds information

before it's written to disk.

And there's three
conditions that

can occur that caused the
log buffer to be flushed out

to disk.

Anytime a commit occurs, any
time the log buffer becomes 1/3

full, or on a mandatory
checkpoint when Oracle forces

data buffers out of
memory onto disk.

The large pool is a
specialized memory area

in the System Global Area that
was added in Oracle version 9i.

And really it kind
of underscores

the benefits of caching.

The large pool was designed to
take some work from the shared

pool.

It's used in the shared
server architecture where,

rather than give every user a
dedicated server process when

they connect in as the dedicated
server model it operates,

the shared server
model is going to spawn

a fixed number of pipes into
the database, if you will,

and every user that comes
in competes for those pipes.

And it can be an efficient
use of resources in situations

where you don't have a
lot of computing resources

to satisfy your users.

The large pool is
also used as caching

for backup and recovery.

So as backups and
recoveries occur,

the large pool can be used
as an added memory area

to speed that process.

And the last of the pools
in the System Global Area

are the Streams pool
and the Java pool.

These are very specialized
pools that you will not

use unless you use the
products associated with them.

So the Streams pool will
support Oracle streams,

which is Oracle's
latest product to do

replication between
geographically

separated locations.

The Java pool is
a memory area that

can be used to
instantiate Java objects.

So if you use
Java-stored procedures

in the database, stored code
of Java, those types of objects

can benefit from the
memory of the Java pool.