|
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 ManagementIn 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;
Manual Segment Space ManagementManual 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;
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 UsageRows 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 LayoutThe block contains a header, free space, and data:
|
|