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

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.