R
Rob Fitzpatrick
Guest
More on Type II: Performance Type II areas are composed of clusters of blocks. Cluster size is 8, 64, or 512 blocks and is specified at the storage area level. Type II areas are extended a cluster at a time (rather than a block at a time as with Type I). This minimizes the number of extend operations for variable extents and makes data more contiguous on disk, improving sequential I/O. In a Type I area, a storage object's data can reside in any block in the area. In Type II, a storage object comprises a chain of clusters. This allows object-based utilities (e.g. tablemove, idxmove, idxbuild record scan, etc.) to be more efficient. In Type I areas, records from different tables may be interleaved within the same block. In the worst case, a scan of a 1000-record table could require 1000 RM blocks to be read into the buffer pool, potentially forcing other useful data to be paged out. Whereas in Type II areas, each block (and all blocks in that block’s cluster) contains data from only one storage object. This increases buffer hit percentage and the ratio of logical to physical I/O versus Type I and improves application performance. A full table scan of a table in a Type II area can be performed via ABL without an index, using the TABLE-SCAN record phrase. This is the fastest way to scan a full table (although the records are not ordered). Reliability In Type II areas every block contains a checksum which is verified every time the block is read, meaning corruption can be detected and reported in the DB log. Availability The second phase of index rebuild (data scan/key build phase) for tables in Type II areas can be multi-threaded with the -datascanthreads n parameter (subject to certain conditions ). Depending on factors such as available cores, available RAM, and area size, multi-threading can significantly reduce the overall index rebuild time, which means reduced downtime. Flexibility When using Transparent Data Encryption with Type II areas, data can be encrypted at the level of individual storage objects (tables, indexes, LOB columns). In Type I areas, you can only encrypt an entire area. When using Alternate Buffer Pool with Type II areas, you can assign areas or individual storage objects to the ABP. With Type I you can only assign an entire area to ABP. In Type II areas there is one RM chain (a linked list of RM blocks with available space for record storage) per table, and thus individually-tunable create and toss limits per table as well. This allows DBAs to better tune the storage to the characteristics of the data, fill blocks more efficiently, and limit record fragmentation. In Type I areas there is a single RM chain, and create and toss limits, per area. Access to new features When using OpenEdge Auditing, the audit data and index areas must be Type II. When using Transparent Data Encryption, the encryption policy area must be Type II. When using Multi-tenant Tables, multi-tenant tables (table partitions) must be in Type II areas. When using Table Partitioning, table partitions will have to be in Type II areas. If you have your data in Type II areas now and you decide to use these and other new database features that require Type II areas then you can access them immediately. If your data is still in Type I areas you won't have access to these features until you dump and load. It is safe to say that all new major RDBMS enhancements will function only (or at least, function best) with Type II storage. This is by no means a comprehensive list of Type II advantages. In short, there is no scenario where Type I areas are more beneficial than Type II. Also, as the schema area must be Type I, you should ensure that you have no application objects stored there.
Continue reading...
Continue reading...