Details: Automatic Index Optimization

This page provides more detailed information about Automatic Data Optimization policies for indexes, extending existing ADO functionality for tables to provide segment movement, compression and optimization capability on indexes.

The slide shows how to set up the different steps between Heat Map and Automatic Data Optimization (ADO) to automate the movement of a segment to another tablespace and/or the compression of blocks or a segment depending on certain conditions defined in ADO policies.

Oracle Database 21c allows ADO policies for indexes, extending existing Automatic Data Optimization (ADO) functionality for tables to provide segment movement, compression and optimization capability on indexes. The optimization process includes actions such as compressing, shrinking, or rebuilding indexes. When the OPTIMIZE clause is specified, Oracle automatically determines which action is optimal for the index and implements that action as part of the optimization process. You do not have to specify which action is taken.

1. The first operation for the DBA is to enable Heat Map, tracking the activity on blocks and segments. Heat Map activates system-generated statistics collection, such as segment access or modification.

2. Real-time statistics are collected in memory (V$HEAT_MAP_SEGMENT view) and regularly flushed by scheduled DBMS_SCHEDULER jobs to the persistent HEAT_MAP_STAT$ table. The persistent data is visible by using the DBA_HEAT_MAP_SEG_HISTOGRAM view.

3. The next operation for the DBA is to create ADO policies on indexes as default ADO behavior on tablespaces.

4. The next step for the DBA is to schedule when ADO policy evaluation must happen if the default scheduling does not match the business requirements. ADO policy evaluation relies on Heat Map statistics. MMON evaluates row-level policies periodically and start jobs to compress whichever blocks qualify. Segment-level policies are evaluated and executed only during the maintenance window.

5. The DBA can view ADO execution results by using the DBA_ILMEVALUATIONDETAILS and DBA_ILMRESULTS views.

6. Finally, the DBA can verify whether the segment moved to another tablespace and is therefore stored on the tablespace defined in the ADO policy, and or if blocks of the index got compressed viewing the COMPRESSION_STAT$ table.