Database Data Blocks


Remember, the data block is the minimum unit of I/O in the database instance. Each database data block consists of one or more operating system blocks, and the block size of a tablespace is set at the time of tablespace creation. DB_BLOCK_SIZE is the default block size for all database data blocks.

Blocks can be managed using either automatic segment space management or manual space management.

Automatic Segment Space Management

In automatic segment space management (ASSM), the job of managing free space is taken care of within the database segments themselves. Tracking of the segment's free and used space is done using bitmaps (which I'll explain in a moment) rather than using FREELISTS. ASSM allows for more ease of management, better space utilization, and better performance for concurrent Insert operations.

FREELISTS are the constructs that Oracle uses internally to determine whether any free space is available in any given block that can be used for inserts. When this information is stored in the data dictionary, it takes a call to that view to determine where each insert should be placed any time there is an insert operation. With ASSM, and having all the information stored in a bitmap for any given data block, Oracle can save a query to the data dictionary and can determine when the insert happens which blocks contain sufficient space for the inserted data to be held.

The only situation where automatic space management is not applicable is in the case of a table that contains LOBs.

ASSM helps to better utilize space in the database with all objects, particularly those with greatly varying row sizes, utilizing available space more efficiently. Furthermore, because ASSM can make runtime adjustments to variations in concurrent accesses, concurrency handling is improved.

Bitmap segments inside the data and index segments contain bitmaps, which describe the status of each block in the segment with regard to available space in the blocks. This map is located in a separate set of blocks in the segment known as bitmapped blocks (BMBs). Whenever you insert a new row, the server searches this map for an available block that has sufficient space to hold the new data. As the amount of available space inside the blocks changes, the new states are reflected in the bitmap.

ASSM can be enabled only at the tablespace level, for what is referred to as locally managed tablespaces. After the tablespace is created, the specifications apply to all the segments that get created in that tablespace. A sample creation script would be similar to the following:

 CREATE TABLESPACE userdata02 DATAFILE '/mydatabases/mydb1/userdata02.dbf' SIZE 100 M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 32K SEGMENT SPACE MANAGEMENT AUTO; 

The creation of the bitmapped segments is inferred by the SEGMENT SPACE MANAGEMENT AUTO clause of the CREATE TABLESPACE command. When this happens, it cannot subsequently be altered by specifications of PCTUSED, FREELIST, or FREELIST GROUPS parameters.


Heap tables (typically simply called tables), indexes, and index-organized tables can all be bitmap managed.


Manual Segment Space Management

Manual segment space management tells Oracle that you intend to use the free lists for managing the free space within segments and allows you to configure the storage parameters manually. These parameters include PCTFREE, PCTUSED, and FREELIST. This used to be the only way that tablespaces and their storage could be managed.

With this method, you have considerably more control. You can control how the block space is configured and used. You can, further, control when block space is made available.

 CREATE TABLESPACE userdata02 DATAFILE '/mydatabases/mydb1/userdata02.dbf' SIZE 100 M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 32K SEGMENT SPACE MANAGEMENT MANUAL; 

If not specified as AUTO, segment space management is MANUAL by default.


The next section explains how space within a block is managed for a data segment that is dictionary managed and has a setting of PCTFREE=10 and PCTUSED=40.

Block Space Usage

Rows are inserted into the block whenever necessary until the freespace in the block is equal to or less than 10% (it may not be possible to have a transaction stop at exactly 10% free space). This block is now no longer made available for inserts (rows at this point are taking up at least 90% of the available data area or 100% PCTFREE plus or minus).

The space that remains can be used for row size expansion from updates. If a column that was null now contains data, or a column that was varchar2 (60) had 20 characters but now has 55, these would go toward filling up the extra 10% of the data block.

If rows are deleted, and enough rows are deleted from the block, or if the rows decrease in size as a result of other updates, the block's utilization may fall below 90%. However, this only means that there is additional space for updates, not that any more inserts will occur to that block. Inserts will not occur until the utilization of that block falls below the value of PCTUSED, which in this case is 40%.

If utilization does fall below 40%, the block is again made available for inserts. As rows are inserted into the block, utilization again increases, and the vicious circle starts again.

Data Block Layout

The block contains a header, free space, and data:

  • Header Contains the address of the data block, a table directory, row directory, and transaction slots used whenever a transaction, or transactions, make changes to rows in the block. These headers grow from the top of the block down. The block header can be further broken down into the following subsections

    • Common and variable header Contains the general block information, its address, and type of segment to which it belongs.

    • Table directory Contains information about the table that has its rows in the given block.

    • Row directory Contains information about the rows in the block including the addresses for each row piece in the row data portion of the data block.

  • Free space Found in the middle of the block, between the header and the data space. This allows the header space to grow down when necessary and the data space to grow up when necessary. The free space in a data block is contiguous when the block is first defined, but with deletions and updates, may become fragmented. The free space in the data block are automatically coalesced by Oracle Server whenever it becomes necessary.

  • Data space When data is inserted into the segment to which the extent belongs, the data is inserted into the corresponding extent database blocks from the bottom up.



    Oracle 9i Fundamentals I Exam Cram 2
    Oracle 9i Fundamentals I Exam Cram 2
    ISBN: 0789732653
    EAN: 2147483647
    Year: 2004
    Pages: 244
    Authors: April Wells

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net