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

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

that goes against the database.

And so 4K works well for
this because we're not

bringing more data into
cache than we really need.

So small transactions lends to
a small database block size.

Let's skip over 8 and go to 16K.

16K is more appropriate for
things like data warehousing,

dss, and where we're using
binary large objects.

So why would that be?

Well, a larger block
size of the 16K range

is ideal for data warehousing
because of the way

that we use a database
when it's a data warehouse.

So a data warehouse
is going to really

be very opposite of the OLTP.

So, instead of a lot of
small discrete transactions,

we tend to have a fewer
number of very, very

large transactions.

So data warehouse and
decision support systems

are going to query
large amounts of data

and then come up with some
kind of aggregate or report

from that.

And, because of that, because
we're selecting that much data,

a 16K is a good block
size because we'll

tend to use fewer blocks.

So, if we were using a 4K
block size in a data warehouse,

that's four times
more blocks that we

have to bring into cache.

So it's more effective
generally to use 16K

for data warehousing.

And the other type is
binary large objects.

So that will occur if
we're storing things

like binary data
in the database.

So if we're storing video files,
image files in some cases.

Documents.

Document management systems
are all the rage these days.

And, rather than store
all of those documents

out in a file system,
Oracle allows you

to store it in the database.

So you might have
PDFs or Word documents

that are actually stored in the
database as a pod of a database

row.

But, because they're larger
than a typical var car 2

string, or a date,
or a number, it's

often advantageous
to use a larger block

size along the 16K range.

32K is the largest.

And, again, that's the
other end of the spectrum

and you don't see that as often.

But, where you do,
it will tend to be

things like data warehousing
and decision support systems.

So that leaves us with 8K.

To go back to it again, 8K
is the default block size,

and we said that that is
generally a pretty good choice.

It splits the middle as far
as what's available to us.

And it's very good
for hybrid systems.

So, these days, hybrid
systems are very common.

And so what's a hybrid system?

Well, a hybrid system may have
some characteristics of an OLTP

and some characteristics of
a data warehousing or ETL

kind of load heavy database
where lots of data is loaded.

So, a lot of times,
during business hours,

the database may function as
a transactional processing

system.

And at night data
will be loaded.

And so it's good to have
a block size that kind of

splits those two and makes
the best of both worlds

available to us.