All language subtitles for [SubtitleTools.com] Memory Management - Learning Oracle 12c [Video]

In this lesson, we'll be taking
a look at memory management.

Memory management
is very important

because memory is very
important to Oracle.

These Oracle caches all
have a particular job to do.

And the performance
of our system

is highly dependent
on whether or not

we have them sized correctly.

So Oracle has taken a
number of different methods

through the years
to manage memory--

all the way back
to the old days,

where we used manual
memory management.

And many people still do.

And then, in version
10g, Oracle came up

with automatic shared
memory management.

And in 11g, automatic
memory management.

So we have some
choices here in how

we manage our memory so
it's important to understand

how they work.

So the first type
of memory management

is known as manual
memory management.

And when you do an
installation of Oracle

and then the creation of a
database in the DBCA tool,

there's an option for
you to choose what type

of memory management you want.

When we manually
manage our memory,

that means we're doing
separate parameter settings

for the following parameters.

So the shared pool size--

the size of the shared pool.

The size of the
database buffer cache.

The size of the large pool.

And the size of the
Java and Stream pools.

And also the PGA--

the program global area.

So we're taking all
of the parameters

that we can manually
size and we're

taking that responsibility
on ourselves

to say that we're
deciding what we

want those parameters
to be-- those values.

And then the memory is
cut out in the operating

system for Oracle to
use in that fashion.

Many DBAs still use manual
memory management today

because they feel it's the most
efficient way to manage memory.

Because the other two methods
have somewhat of an overhead

involved because Oracle
is managing some portion

of the memory, to some degree.

In 10g, Oracle came out
with automatic shared memory

management.

And with automatic shared
memory management, or ASMM,

Oracle is managing just
the SGA dynamically.

So the only Memory
settings that we change

are the overall size of the
SGA and the size of the PGA.

So the PGA-- being the
program global area--

isn't associated with any
of the caches in the SGA.

And so the PGA stands
alone on its own,

anyway, also in manual
memory management.

However, as far as the SGA
goes, what's occurring here?

So we're setting a
parameter called sga_target

and that's going to be
the size of the SGA.

Within that sga_target
boundary, everything in the SGA

is automatically
managed by Oracle.

So it will decide on the
size of the shared pool,

and the size of the
database buffer cache,

and the large pool, and the
Java cache-- all of those,

it will decide as it sees fit.

And it has the
ability to do this

because it has processes,
like MMAN, under the hood that

are managing the memory.

So if Oracle senses that the
buffer cache needs more memory,

it can take away memory
from the shared pool

and give it to the buffer cache.

So all of the caches within
the sga_target are being moved

and sized constantly,
changing to adapt to whatever

your database needs are.

This is a good idea,
at least for a database

that is sort of dynamic
or a hybrid database.

So let's say that a database
is an OLTP transactional

system during the
day, but at night, it

does more decision support
and batch processing.

And a lot of databases
are like that.

Well, the needs of the SGA--

particularly the shared
pool and the buffer cache--

are different for those
types of operations.

So what are your options?

Well, if you're using
manual memory management,

you need to split
the difference.

So you make a compromise
between the two.

So a transactional system would
benefit from a larger shared

pool and less from
a buffer cache.

But a batch system
is going to need

a lot more in the buffer cache
and less in the shared pool.

So in manual memory management,
you have to make a compromise.

But with automatic
shared memory management,

you don't have to do that.

Because during the day, Oracle
will be automatically managing

the memory toward a
transactional system

and at night toward
a batch system.

So it has that flexibility.

Although, it does have
overhead associated

with it that can sometimes
affect performance.

In 11g, Oracle went
one step further

and decided to have a setting
to manage all of the memory

together.

And this is called AMM, or
automatic memory management.

And so the SGA and
the PGA together

are determined by
two parameters--

a memory_target and
a memory_max_target.

Oracle will manage all memory
sizing within those boundaries.

So if we give a
MEMORY_MAX_TARGET of 2 gig

and a MEMORY_TARGET
of some value less

than or equal to that--

that means Oracle has 2
gigabytes of memory to manage

and size the SGA, and the
PGA, and all the components

that they contain.

So to look at this
relationship a little bit,

we have two parameters in
Automatic Memory Management

that can be used to define this.

We say memory_max_target
and we set that to a value.

memory_max_target is
not a dynamic parameter,

it cannot be changed
while the database is up.

If we want to change
memory_max_target,

we have to change it in the SP
file and restart the database.

However, memory_target
is dynamic.

So within this boundary,
we can make memory_target

smaller or larger, as long as we
don't exceed memory_max_target.

And again, memory_target
is a dynamic parameter.

So normally, what
you'll see is DBAs

that size memory_target
to a certain value,

and memory_max_target
to a certain value,

and then memory_target to
a value that is less than

or equal to that boundary.

And that gives you the ability,
if you size it-- let's say,

that memory_max_target is 2
gigabytes and memory_target is

1.5--

that gives you the ability
to grow your memory,

should you need to do so.

So those are the three
types of memory management

available to us in Oracle.

And it's up to the
DBA to choose the best

one for his or her situation.