Monday, October 25, 2010

Engineering Dynamic Optimal Storage with Oracle ASM

The possibility of customizing storage performance is no longer left to the many products which have the capability to dynamically reorganize most dynamic or active data block onto hot volume sectors, i.e., those sectors whose band width and overall response time entices better I/O performance, usually supporting enterprise applications.

In the case of Oracle databases most active blocks are normally determined by segments associated with logical objects, such as tables. Indexes or materialized views. Furthermore, the capability of aligning storage performance with more dynamic objects is quite relevant to ASM technology, and a core value in Exadata’s database machine elasticity. ASM unique’s capability to perform rebalancing operations at the ASM file level and based on its unique extend indexing allows for an optimal rebalancing not only on conventional rebalancing operations but on a smart implementation engineered on the basis on Intelligent Data Placement.

The key idea is to be able to categorize segments associated with logical objects both by size (top-down) and in relation to their level of utilization, in particular, with respect to top most queries and database statistics. Both a size indicator and a level of usage index can be implemented. Furthermore a compound index can be finalized by finding the product of both size indicator and index value, which can be then subject to a mathematical norm, whose outcome will allow an Oracle DBA or Architect to categorize that segment as a candidate for Mirror Hot sector or Mirror Cold, otherwise. Three levels can be implemented with the highest level being applicable to the Mirror Hot sectors, and the lowest to the Mirror cold sectors.

[Compund Index] = [Size Indicator] x [Usage Level Index]

Based on the compound index, an object with a high compound index value should use the best slots in the hot sectors, which actually used the best band width and head speed in the physical disks building the ASM drives.

This is done by creating a custom template and applying to appropriate ASM datafile and subsequently determining relevance to logical objects. The SQL API is shown in the example below:

ALTER DISKGROUP adndata1 ADD TEMPLATE datafile_hot

ALTER DISKGROUP adndata1 MODIFY FILE '+data/adn3/datafile/tools.255.765689507'

Consistently, tables can be associated with tablespaces that have been designed on the basis of ASM data files, which have already been set as mirror hot or mirror cold templates, accordingly. These maintenance activities can occur as a baseline, for instance, when the database is created (first loaded), migrated, or upgraded accordingly, and systematically when a relevant event occurs. For instance, a partitioned table, using partition pruning, will require to change the most active partition due to a partition interval or time range issue, which will make a different or new partition the most dynamic segment, which in turn may require to be set in a mirror hot sector for optimal performance. In general, business algorithms can be customized, implemented, and set for periodic ASM storage maintenance in order to attain optimal performance.

Concluding Remarks

With Oracle11g Release 2, enterprise storage can now benefit of ASM elasticity, and the ability to customize storage optimal performance for a specific database, by engineering ASM production maintenance practices that optimize object allocation onto hot sectors, which use tablespaces associated with ASM data files utilizing MIRRORHOT templates.