Oracle database block is the smallest unit of storage in which oracle can store the data.
Different OS platforms vary in size of OS block like Linux has OS block size as 1kb and solaris has OS block size 512 bytes. But Oracle does not consider OS block size as standard Blocksize for database. Oracle takes multiples of OS block size to create one block of Oracle . for example - In oracle 11g, 8 blocks of Linux OS are used to create one block of Oracle.
Oracle creates bigger blocks than the OS so that It can perform I/O operation more efficiently. Suppose If multi block read count is set to 8 then in one fetch Oracle retrieves 8 blocks from disk that means 64kb of data. In dataware house Generally multi block read count value is set to a high value like 32 because there most of the SQL statements are SELECT commands.
Oracle can have different block sizes in the same database as 2k,4k,8k,16k and 32k depending on requirement.
In Oracle Block we have different parts like-
1.Variable Header- This part of block contains information about block like address of the block.
2.Table Directory- This part contains information about the table whose data is stored in this block.
3.Row directory-This part contains the address of the stored row in data block.
4.Row data-Actual row are stored in this part of the block
5.Free space-This part of the block is reserved for the future updates of the exiting rows.
If PCT USED is set to 40% and PCT FREE is set to 20% then to insert a new row in block more than 60% of the block should be free.
Different OS platforms vary in size of OS block like Linux has OS block size as 1kb and solaris has OS block size 512 bytes. But Oracle does not consider OS block size as standard Blocksize for database. Oracle takes multiples of OS block size to create one block of Oracle . for example - In oracle 11g, 8 blocks of Linux OS are used to create one block of Oracle.
Oracle creates bigger blocks than the OS so that It can perform I/O operation more efficiently. Suppose If multi block read count is set to 8 then in one fetch Oracle retrieves 8 blocks from disk that means 64kb of data. In dataware house Generally multi block read count value is set to a high value like 32 because there most of the SQL statements are SELECT commands.
Oracle can have different block sizes in the same database as 2k,4k,8k,16k and 32k depending on requirement.
In Oracle Block we have different parts like-
1.Variable Header- This part of block contains information about block like address of the block.
2.Table Directory- This part contains information about the table whose data is stored in this block.
3.Row directory-This part contains the address of the stored row in data block.
4.Row data-Actual row are stored in this part of the block
5.Free space-This part of the block is reserved for the future updates of the exiting rows.
If PCT USED is set to 40% and PCT FREE is set to 20% then to insert a new row in block more than 60% of the block should be free.
Good one sir.....! :)
ReplyDeletethanx srinu sir
Delete