Original subtitles

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