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

In this lesson, we're going to
look at our next larger unit

of space, called the extent.

Now the extent used to be a
structure of great importance

to a DBA, but now because of
some changes that have occurred

in the Oracle architecture
that we'll discuss,

the extent is almost
something that's

not even considered anymore.

But it is something for us to
take a look at and understand

to have a more complete
understanding of how database

storage structures work.

So a database extent
is the next larger unit

of space from a block.

So an extent is
composed of blocks.

So we have an extent that's
composed of four 8K blocks.

So let's say our database
block size is 8K.

We have four of them, and
that gives us a 32K extent.

So that is the full extent
size of the four blocks

that form together
to become an extent.

Now the number of
blocks in the extent

is really dependent on
the size of the extent,

and that used to be something
that DBAs would control.

So if we specified
a 16K extent, we'd

have two 8K blocks instead.

And there was a certain
amount of rounding

that could go into
this, but generally DBAs

took a lot of time trying to
determine the proper extent

size.

So in structure, the
way an extent works

is that there is always
an initial extent, so

that first extent size.

Again, that was something
that an Oracle DBA would

calculate for any given table.

So a table would be
made up of extents,

which were made up of blocks.

So we would determine the
size of the initial extent,

and then we'd have
a next extent.

So when the initial
extent was full,

all of the blocks
inside of it were full,

it would extend
to a next extent,

and then another extent
once that was full.

And the extent size, at
least the next extent size,

could be changed on the fly.

So if a database
administrator saw

that there were three
extents in a table

and that it was filling up
these extents very quickly,

he or she could say, let's
just make the next extent size

larger.

So when the three
extents filled up,

and it was time
for another extent,

that one would be larger.

And then we even had the
ability-- and still do,

if we wish--

to put a percentage of
increase on the extent size.

So every subsequent extent
would be, say, 20% larger

than the previous one.

And this helps us slow
the amount of growth

that occurs in our extents.

The reason that we used to
have strategies like this

is because one of the DBAs most
common jobs in the old days,

if you will, was managing
the number of extents

in all of the tables.

So having a large number of
extents was seen as a bad thing

because it was seen as a sort of
fragmentation that would occur.

So you had more
and more extents.

There was nothing wrong with the
five extents that you see here,

but if the extents
grew to 500 in number,

700, 1,000 in number, then
that was seen as a performance

problem because during
a full table scan,

each of those extents
would have to be read.

And they would be
actually located

on different spots on
disk, and so it could

create performance problems.

So one of the DBA's
main tasks was

to reduce the number of extents.

So it was common practice
that if the number of extents

in a table grew
to 1,000, the DBA

would have to reorganize the
table, which basically meant

dumping all of the
data out, recreating

the table with a very
large initial extent--

probably if you
calculate it out,

it would be the total size
of all the extents together--

make that the initial extent,
and then import all the data

back in.

However, around
version 8i, Oracle

put out a paper called,
"How to Stop Defragmenting

and Start Living."

And the conclusion
of that paper was

that, after extensive testing,
advances in disk speeds

had actually made
this irrelevant.

So that if you just studied
the performance of a table

with a lot of extents versus
a table with a few amount

of extents, that you'd see that
these days, or at that point

in time, the performance hit
was not really considerable,

really was negligible, because
disk speeds had improved.

And there were new
technologies like RAID,

which actually were
moving the data around

beneath the Oracle layer.

But then in 9i, Oracle
came out with a feature

for extents that pretty much
solved the issue, even if you

didn't accept their
idea that having

a large number of extents
was a performance problem.

So prior to 9i, our only option
really in dealing with extents

was something called
dictionary managed extents

and what's called
dictionary managed tables.

And dictionary managed
extents were actually recorded

in the data dictionary.

So when Oracle needed
to find a certain extent

for a piece of data--
because remember

that extents contain blocks,
and blocks contain rows of data.

So if we needed a
particular row of data,

it would have to
find the extent.

So how would it find
the right extent?

Well it would actually
look in the underlying

tables of the data dictionary.

And that's fine for
small databases,

but it got to the point
where databases were so large

that any time an
extent was needed,

this huge amount of data
in the data dictionary

would have to be queried.

And that really did
impact performance.

So Oracle introduced
something in version 9i

called locally managed table
spaces, where Oracle actually

managed the extents.

So instead of storing that
extent information in the data

dictionary, it actually
stores that information

about free extents in the table
space in the data file itself.

It's actually stored in
something called a bitmap.

So the extent information
becomes a part of the data

file itself, and that is
much quicker to retrieve

in terms of performance.

So locally managed
table spaces with Oracle

managed extents, if you
will, allows Oracle to decide

how big to make the extents.

So rather than the DBA deciding,
I want an initial extent that

is this size, and then
a next extent that's

this size, and maybe a
percentage increase the next,

Oracle determines based
on its own algorithms

how big to make the extents,
and it controls that.

So right now, at
this point in time,

locally managed table spaces are
really by far the most common.

It's actually quite unusual
to see tablespaces that

are dictionary managed anymore.

There's almost no
reason for that

because time has
shown that Oracle

can manage the extents and
their sizing very effectively.