Original subtitles

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.