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

In this lesson, we're going to
study the concept of a database

block in Oracle.

So a database block is
the smallest atomic unit

of space in an Oracle database.

If you've ever heard the
concept of an operating system

block or a file
system block, this

is sort of analogous to that.

So, when Oracle stores
space or reads space,

it never reads less than
the size of an Oracle block.

So, even if it only needs one
row that's maybe 100 bytes,

it still reads the
entire block into memory.

So database blocks
are stored on disk

in the form of extents and
segments and then data files.

But the blocks are the
smallest part of that.

And so a database block is
read into a memory buffer

or a buffer in the cache.

So we have something
like the SGA

and the buffer cache
that's present there.

And so the database
blocks are actually

read into those buffers.

The database blocks size is
defined by the DB block size

parameter in the database.

And this is defined at creation.

And, once the database is
created with a certain block

size, it cannot be changed.

So your only option if you
want to change the block

size of the entire database
is to export all of the data

out, recreate the database
with the new block size,

and then import
the data back in.

So the database
block sizes are going

to match the buffer sizes
because a block fits perfectly

into a buffer, if you will.

So if we had an 8K block
size for the database,

that means our buffers are
going to be 8K as well.

And, since the block size cannot
be changed after creation,

it's important that we know how
to properly size our database

block, so what block size is
available to us and what is

most advisable for
our certain situation.

So, to look at the anatomy
of a database block a little,

let's say this is
one database block.

Database block consists of
header information, free space,

and used space.

So the header
information is going

to be a small amount that's
used for the block that

will have information,
such as something called

the DBA or the
Data Block Address,

where it's located
physically on disk,

and some other information
about columns and tables

that are within the block.

The next section
is the free space.

So a database block will
have space that's used

and space that's not.

And so the free
space will be what

we constitute as the open and
available space for new rows.

The used space
will be the actual

rose on disk within the block.

So, if we see here, we could
look at this as a row of data.

And so the used space is going
to be filled with row data,

and then the free space
will be available for use.

So this line actually moves
within every database block.

As a block begins to fill
up with more and more rows

of data, this free space
gets less and less.

So we said that this
sizing of a database block

was very important because
it can't be changed.

So what are our possibilities?

Well, we can have a database
block size between 2 kilobytes

and 32 kilobytes.

So how do we decide
what is best?

Well, let's start with
the middle which is 8K.

So the sizing possibilities are
2K to 32k in a doubling order,

if you will.

2, 4, 8, 16, and 32.

Those are the five possibilities
for our block size.

The default is 8K.

And, for most situations,
that's going to be a good size.

Let's look at the top end.

So the smallest block
possibility is 2K.

2K will not be used
all that often.

You won't generally see
it being used because it's

a very small block size.

The next is more
common and that is 4K.

So a 4K block size is going
to be ideal for situations

where the database does
something like, OLTP,

a lot of online
transaction processing.

So this is many small
discrete transactions.

So we could think of an OLTP as
something like an order entry

system, maybe an online
store of some kind

because you go and
you have a page that

shows you the possible
things that you can purchase,

then you create a shopping cart.

And, once you filled all that
information about your order,

you click Submit and that's
a single discrete transaction