37. Kernal, Data Dictionary, Data blocks types in Database buffer cache

Kernal:

This is the heart of the Operating system. See the applications we have like Oracle is an application. Any applications doesn't have any permission to do anything with the hardware like the RAM, Hard Disk. That's where the Kernel come in, when the application needs to do anything to with the hardware it sends a system call to the kernel. Only kernel has the permission to do anything with the hardware. So kernel then does what the application needs with the hardware like save a file, update in the file of a memory.

Control File Creation:

Control file got created when the create database script run. To create the data files control file got needed to save the metadata of those files. So control files got created then.

Data Dictionary:

It got created with the running of catalog.sql. It contains some tables which are not accessible by sys user.

Views of Data Dictionary:

These views like dba_users, dba_tablespaces, dba_data_files, dba_entents etc all these static views these are data dictionary views which lets use see the data in the tables of the data dictionary which we don't have access to even as sys user.

Types of Data Blocks:

Free:

Free data block is the space of the memory that can be reused to load another data block. Sometimes a recycle data block can be replaced by a free data block.

Clean:

The data block which has just come to the database buffer cache are the clean data block. The clean data blocks are the blocks where no modifications have been made.

Dirty:

Dirty data blocks are those data blocks where modifications have been made through dml statements.

Recycle:

The clean data blocks which has been brought by select statements to th3 database buffer cache to display, after the display they still stay on the database buffer cache. They are not removed immediately. But if they stay and are not deleted there will come a time when the database buffer cache will be full and no new data block can come here. This will hamper the running of the database.

When this scenario happens the database runs some algorithms to determine which data block are less important. Now how does it determine that? Its similar to how we do that process in real life. For example if among many things we have to remove some of the things we remove the less important one i,e the one thing has been used least time. The algorithms does that in the same way.

The algorithm sees the touch count of the data blocks i,e how many times it has been taken by the server process to display in response to a select statement and compares it to how much time it has been in the data base buffer cache. Thats how it determine how much Improtant a data block. The less important data blocks are overwritten by the new arrived data blocks. The data blocks which are overwritten are called recycle data blocks.

Default:

The default blocks are the default ones which are present in the data base buffer cache.

Pinned:

We explained about how recycle block works as after some time and if it has few touch counts then it will overwritten by new data blocks. But there is a type of data block which cannot be overwritten or deleted. These data block is called pinned block.

The pin data block will not deleted and not be overwritten until the database shutdows.

Comparison of tablespace, extents, segments, data blocks with real world:

Now lets do a comparison of the real world to this hirerchy

Well then think of a how you reach a specific address in the real world.

First you need to go to the country where the address is located. Then you will got to the state where the address is located. Then you will go to the city where the address is from. Then you will reach the locality where the address is. Then you will reach the block of the address.

Now think this layer and compare it to the layer of the database.

Here the address block will be the oracle data block. Then extent will be the locality which is made from many address block or data block. The segment corresponds to the city which is made of many localities (extents). Then the city corresponds to the tablespace which is made of many cities (segments). The country will corresponds to the database which is made of many states (tablespaces).

So now that comparison is done we can figure out the way the server process can locate a data block. It will find out which tablespace (state) it contains, then it will reach the segment (city), then to extent (locality), data block (address block), it will do it the same we in real life will find an address.