|René Nyffenegger's collection of things on the web|
René Nyffenegger on Oracle - Most wanted - Feedback
Free used and unused blocks
If an insert statement is executed, Oracle tries to insert the row in a free block. If it doesn't find a free block, it tries to insert it in an unused block.
See also dbms_space.free_blocks
pctfree controls the threshold when a free block becomes a non-free block.
A block that has never been written to is a unused block. Unused blocks are beyond the high water mark.
The number of unused blocks can be found with dbms_space.unused_space.
A block that has been written to is a used blocks. Used blocks are below the high water mark.
A free block is also a used block; but a used block is not always a free block.
Getting the count of used and free blocks
With free blocks and unused blocks it is possible to get the count of used and free blocks, respectively, within a segment.
Size of db blocks
Since Oracle 10g, it's possible to define multiple block sizes for a database. However, one of the sizes is the standard size. The size of the (standard) database block is determined by the db_block_size init parameter. This standard block size is the block size of blocks in the system tablepsace.
Interested transaction list (ITL)
Each ITL uses 24 bytes overhead.
All blocks have a header that consists of:
The redo log address (RBA) plays an important role. . To be finished.
Checking integrity of blocks
The integrity of database blocks can be checked with dbverify [dbv] (the database verify tool) or dbms_dbverify.
Not all bytes in a datablock actually store inserted (or selectable) data. Some bytes are also used for Oracle to organize the data so that it can be found again. These (additional) data are called overhead.
The following applies for tables:
The overhead consists of
fixed header + transaction header + table directory + row directory = block header.
The size of the block header is 57 bytes.
The size of the transaction header is dependant on the initrans parameter.
The table directory is used to find the start position of each row (or row directory).
The size of the table directory is 4 bytes*number of tables. Number of tables is important for cluster tables. For other tables, it's 1 (eg 4 bytes).
The row directory uses 2 bytes per stored row.
For non cluster tables, the row header is 3 bytes. Each stored row has one row header. One byte is used to store flags, one byte to indicate if the row is locked (for example because it's updated but not commited), and one byte for the column count.
Each column within a row needs at least 1 byte indicating the size of the data in the column. For varchar2's longer than 250 bytes, 3 bytes are used.
When calculating the amount of storable data in a block, the pctfree must also be considered.
Blocks might become corrupt. Information about corrupt blocks is recorded in the control files.
to be finished...
Checking block data integrity in memory
Oracle allows to check the integrity of data within a database block before it is written from memory (that is: buffer cache) to the harddisk by setting the parameter db_block_checksum to full.
Database block address
Each block in the database is uniquely identified with the database block address (dba).
With x$bh, it is possible to query some information about db_blocks in the buffer cache.