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

In this lesson, we're going
to look at the Program Global

Area, or PGA.

Again, the PGA is
the second type

of memory cache available
in the Oracle Instance.

So, again, an instance
is composed of background

processes and memory caches.

And this is the second
type of memory cache.

The important thing to
remember about the PGA

is that it's for private memory.

So whereas the SGA is for
shared memory between users,

the PGA is for private
memory that's not shared--

things like sorting
or session variables.

Sorting can be one of the most
expensive operations that's

done in a database
because it involves

reading a large amount of
memory, large amount of data,

and then moving through
all of that data

and putting it into some
kind of ordered set.

So a sort is done when we use
a query that does something

like an order-by clause.

We may order by
first name in a table

so we see all of the employees
and the employee table,

for instance, alphabetically.

So, sorting can be a
very expensive operation.

For that reason, it was decided
when Oracle was being created,

to not have those type of
operations occur in the SGA.

Rather, that we needed
some kind of private area

for users to be able to do
those type of operations.

And it also holds
session variables

for statements where you
have some variable that

gets passed to a piece of code.

So there's two ways
that this has been done.

The old way, which is
going to be limited

by the number of users that
we actually have connecting

to the database, or the new way
that's done, and has been done

since Oracle 9i, that uses
an actual pool of memory

to more efficiently
manage how this is done.

So the easiest way to
see how this operates

is to talk a little bit about
the way it used to be done.

So in the old way of private
management of memory,

Oracle would establish
a certain amount or size

of a slice of memory
that each user would get.

So a user would connect in and
say the database parameters

indicated that one
megabyte of memory

would be given to a user for
their private operations.

So any sorting,
session variables,

hash values in a join,
any of those things

would get one
megabyte of memory.

And then another
user would connect in

and they would get a
megabyte of memory.

And so on and so forth.

So we can see some
of the problems here.

The first is, not every user
that connects into a database

necessarily is going to
do any sorting operations.

So one megabyte of memory might
actually be too much for them,

so we're essentially
wasting memory.

The next thing is, what
about a user who needs

more than a meg of memory?

So, they're going to
do a lot of sorting.

Well, what occurs
in that situation

is that Oracle can't
contain all of the data

in the one megabyte
of memory and so it

has to write it out to disk,
into temporary files on disk.

And that's a very,
very slow operation

because all of those
one megabyte pieces

are written down to disk,
then they're all reassembled

and presented back to the user.

So not being able to do the
entire operation in memory

can be a true
performance problem.

And the last
problem with this is

that we're completely
constrained

by the number of
users that we have.

So we say, how much
memory should we

give for private
use in our database?

We say that we have
x amount of memory,

the operating system
uses a portion of that,

the SGA uses a portion
of that, and then

we have to define the
amount for the PGA--

the private area.

Well, in this case,
the amount of size

that we have available
to us is going

to be completely dependent on
the number of users we have.

So let's say we
have one megabyte

slices that are given to
each user for private memory.

If we have 500 megabytes
total available to us

for private memory, that means
we can only have 500 users.

500 users, one meg,
equals 500 meg.

But what if we have
more than 500 users?

Well, then we actually have to
make the slice size smaller,

and that gives everybody
less private memory.

The thing that really
compounds this problem is we

don't know that all 500
of those people or more

need a megabyte for sorting.

So the entire process
of doing private memory,

prior to Oracle 9i,
was very inefficient.

That's why Oracle came up
with the new way in Oracle 9i

called the PGA, the
Program Global Area.

So the PGA is actually
a pool of memory.

Rather than a slice of memory,
we establish an overall pool.

So we say we have 500
meg available to us

for private memory.

So we've established
1,000 meg for the SGA

and we have 500 meg for the PGA.

Now, when a user connects
in, it's Oracle in the PGA

that decides how big of a
slice of memory to give.

Then, if the user needs more--

that is to say the sort
requires more memory--

Oracle can dynamically add more
memory to their private memory,

and even more if needed.

When another user comes
into the database,

they're given a piece
of memory, as well.

But let's say they don't
require very much private memory

for sorting because they're not
going to do any sort operation.

And then Oracle can just be
free not to allocate anymore

memory to that.

So on the whole, the PGA is
able to more efficiently manage

the memory for Oracle users
in the private sphere.