Similar to the existing Oracle capability of Automatic Data Optimization (ADO) for data segments, the Oracle Database 20c capability of Automatic Data Optimization for Indexes achieves ILM on indexes by enabling organizations to set policies that automatically optimize indexes (ADO for Indexes only is only available at the segment level).
In the example below, as with ADO for data segments, the existing heat map framework is used to collect activity statistics on the index as it goes through lifecycle changes. The OPTIMIZE clause enables ADO to optimize the index whenever the policy condition is met.
ALTER INDEX orders_idx ILM ADD POLICY
OPTIMIZE AFTER 3 DAYS OF NO MODIFICATION;
When the optimize clause is specified, Oracle Database automatically determines which action is optimal for the index and implements that action as part of the optimize clause, you do not have to specify which action is taken. The actions include:
Compress: Compresses portions of the key values in an index segment
Shrink: Merges the contents of index blocks where possible to free blocks for reuse
Rebuild: Rebuilds index to improve space usage and access speed
When the index ADO policy qualifies for execution, the database automatically determines which index optimization to implement (shrink, compress or rebuild index). In the example above, the ADO policy automatically optimizes the index based on the recommendation of the database when the policy condition (no modification for 3 days) is met and the policy is executed.
ADO-based storage tiering (Tier To) is not based upon the ADO condition clause (i.e. after “x” days of NO MODIFICATION) as is compression tiering and instead, is based upon tablespace space pressure. The justification for making storage tiering dependent on “space pressure” is exactly as you might imagine, the belief that users will want to keep as much data as possible on their high performance (and most expensive) storage tier, and not move data to a lower performance storage tier until it is absolutely required. The exception to the storage pressure requirement are storage tiering policies with the ‘READ ONLY’ option, these are triggered by a heat-map based condition clause.
The value for the ADO parameter TBS_PERCENT_USED specifies the percentage of the tablespace quota when a tablespace is considered full. The value for TBS_PERCENT_FREE specifies the targeted free percentage for the tablespace. When the percentage of the tablespace quota reaches the value of TBS_PERCENT_USED, ADO begins to move segments so that percent free of the tablespace quota approaches the value of TBS_PERCENT_FREE. This action by ADO is a best effort and not a guarantee.
You can set ILM ADO parameters with the CUSTOMIZE_ILM procedure in the DBMS_ILM_ADMIN PL/SQL package, for example:
BEGIN
DBMS_ILM_ADMIN.CUSTOMIZE_ILM(DBMS_ILM_ADMIN.TBS_PERCENT_USED,85):
DBMS_ILM_ADMIN.CUSTOMIZE_ILM(DBMS_ILM_ADMIN.TBS_PERCENT_FREE,25):
END;
In this example, when a tablespace reaches the fullness threshold (85%) defined by the user, the database will automatically move the coldest table/partition(s) in the tablespace to the target tablespace until the tablespace quota has at least 25 percent free. Of course, this only applies to tables and partitions that have a “TIER TO” ADO policy defined (see examples below). This frees up space on your tier 1 storage (ACTIVE tier) for the segments that would truly benefit from the performance while moving colder segments, that don’t need Tier 1 performance, to lower cost Tier 2 storage (LESS ACTIVE/COLD Tier).
For example:
ALTER TABLE orders ILM ADD POLICY TIER TO lessactivetbs;
ALTER INDEX orders_idx ILM ADD POLICY TIER TO lessactivetbs;
In this simple TIER TO example, Oracle Database automatically evaluated the ADO policies (and tablespace fullness) to determine when data and/or index segments are eligible to move to a different tablespace. This ensures data accessibility and performance, while reducing the storage footprint even further – with no additional burden placed on database administrators or storage management staff.
Similar to ADO data segment storage tiering ADO index segment storage tiering also operates at the segment level, so when an ADO policy implements storage tiering the entire segment is moved and this movement is one direction, meaning that ADO storage tiering is meant to move colder segments from high performance storage to slower, lower cost storage. If an ADO index optimization policy, and a storage tiering policy both qualify for execution, the database will execute both operations in a single segment reorganization step (similar to a data segment).
The Heat Map and Automatic Data Optimization (data and indexes) features of Advanced Compression, along with Oracle Database, provide comprehensive and automated ILM capabilities that minimize costs while maximizing performance. In combination with its comprehensive compression features, Oracle Database provides an ideal platform for implementing Information Lifecycle Management for all of your database data