Heat Map and Automatic Data Optimization (ADO)
Oracle Database 12c allows for activity tracking with Heat Map. This tool provides the ability to track and mark data as it goes through life cycle changes (see my previous blog on Information Lifecycle Management). Data accesses are tracked at the segment-level (table, index, etc.). Data modifications tracked at block and segment-level. The block-level and segment-level statistics are collected in memory and then stored in tables in the SYSAUX tablespace. Automatic Data Optimization, or ADO, allows the creation of policies that use these Heat Map statistics to compress and move data based on your specified requirements. ADO automatically evaluates and executes policies that perform compression and storage tiering actions. ADO is dependent on Heat Map, and will not work unless Heat Map is enabled.
The components of Heat Map and Automatic Data Optimization are shown below:
The key to Automatic Data Optimization is the defined ADO policies. ADO policies allow the specification of conditions under which row, partition, table access or modification, need to be satisfied before a compression or a move action is performed. For example, when partition rows are not updated for three days, automatic compression based on the defined policy is performed. This eliminates the need for the application to make explicit changes. When policies are set at tablespace level, the table, partition, and subpartitions, stored in that tablespace, are automatically compressed and/or moved to a lower-cost tablespace based on the policies.
In summary, ADO allows the setting of policies that enforce data flows automatically, with minimal intervention:
– Automatically compresses data
– Automatically move segments
The ADO policies are based on data classification levels. You can classify each row of a table based on its operational relevance by using the row-level data classification. Modifications tracked at row-level. Row-level statistics are managed in-line as part of the work done when a process modifies data. At segment-level, access and modifications are tracked:
– Last access time
– Fetch-by-rowid tracking for index range scan or fetch by ROWID
– Full-table-scan tracking
– Last modification time
Segment-level activity tracking information is automatically flushed from memory to the SYSAUX tablespace for persistence by scheduled DBMS_SCHEDULER jobs that, by default, run hourly. Segment-level statistics are guaranteed to checkpoint to on-disk statistics tables.
Heat Map and ADO
The chart below, and explained after the chart, shows how to set up the different steps between Heat Map and ADO. The process shown automates the movement of a segment to another tablespace and or the compression of blocks or a segment depending on conditions in ADO policies.
1. The first operation for the DBA is to enable Heat Map
ALTER SYSTEM SET heat_map = ON;
– This parameter is dynamic
2. Heat Map activates system-generated statistics collection:
• Real-time statistics are collected in memory using V$HEAT_MAP_SEGMENT
view and regularly flushed to the persistent table HEAT_MAP_STAT$
• The persistent data is visible using DBA_HEAT_MAP_SEG_HISTOGRAM
3. The next operation for the DBA is to create ADO policies on segments or groups of segments or as default ADO behavior on tablespaces.
More on defining policies in
the next posting.
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 utilizes the Heat Map statistics
– MMON evaluates row-level policies periodically and starts jobs to compress
the blocks that qualify
5. Segment-level policies are evaluated and executed only during the maintenance window.
6. The DBA can then view ADO execution results using –
DBA_ILMEVALUATIONDETAILS and DBA_ILMRESULTS views
Finally, the DBA can verify that the segment moved and is therefore stored on the tablespace defined in the ADO policy and/or if blocks or the segment got compressed viewing COMPRESSION_STAT$ table
In the next posting, we will look at defining policies and include some examples. We will then wrap this series by monitoring and managing ADO.
In future postings, we will initially concentrate on new features in Oracle Database 12c.