Original subtitles

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.