Heat Map and Automatic Data Optimization

Heat Map and Automatic Data Optimization: Heat Map and Automatic Data Optimization (ADO) can be used to implement your Information Lifecycle Management (ILM) strategy, along with Partitioning, Advanced Compression, and Hybrid Columnar Compression.

usefull links:
*youtube
Manage Automatic Data Optimization (ADO) with Enterprise Manager Cloud Control 12c
Exploring Oracle 12c’s Automatic Database Optimization (ADO) Features:

*oracle by example
Setting Up Compression Tiering for Automatic Data Optimization
Setting Up Storage Tiering Automatic Data Optimization

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 and row and segment 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 table HEAT_MAP_STAT$. 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 segments or groups of segments or 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 then view ADO execution results by using the DBA_ILMEVALUATIONDETAILS and DBA_ILMRESULTS views.
6. Finally, the DBA can verify if 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 the COMPRESSION_STAT$ table.

Defining Automatic Actions

define the action and the level of automatic execution:
• The action executed (see possible actions in the first column of the table in the slide):
– Compression and which type of compression
– Data movement to other storage tier
– Both when defining two policies on the same segment
• The possible levels of execution:
– ROW: Row-level ADO policies can only be created based on modification time.
– SEGMENT: Segment-level ADO policies can apply to tables or partitions.
– GROUP: Group-level ADO policies indicate that the table’s SecureFiles LOBs are
compressed as well. Global indexes are maintained. An ADO policy can be specified for a table with the GROUP keyword that is part of the POLICY clause for compression. If the table becomes eligible for an ADO action at any time, the same ADO action would be performed on all the SecureFiles LOBs of the table as well. For example, if the ADO action is compression, the dependent objects like SecureFiles LOBs would be compressed at compression levels corresponding to a default mapping between heap segment compression levels and those of SecureFiles LOBs. Similar semantics hold for the GROUP keyword for ADO policies on table partitions.
– TABLESPACE: A DEFAULT ADO policy defined on a tablespace applies to all segments that will be created in the tablespace.

A few reminders about compression: Compression can occur while data is being inserted, updated, or bulk-loaded into a table.
• ROW STORE COMPRESS BASIC or ADVANCED is used for rows inserted without using direct-path insert and updated rows, using the Advanced Compression option (ACO). ROW STORE COMPRESS ADVANCED is the new syntax used with the “Advanced Row Compression” feature, new name for the old OLTP Table Compression feature part of ACO. ROW STORE COMPRESS ADVANCED on the heap table maps to LOW for SecureFiles LOB segments when the GROUP keyword is used.
• COLUMN STORE COMPRESS FOR QUERY LOW or HIGH provides a higher level of compression than ROW STORE compression. It works well when load performance is critical, frequent queries are run against the table, and no normal DML is expected. Column Store is a feature commonly referred to as Columnar Compression or just Columnar or HCC. COLUMN STORE COMPRESS FOR QUERY LOW/QUERY HIGH on a heap table maps to MEDIUM for SecureFiles LOB segments.
• COLUMN STORE COMPRESS FOR ARCHIVE LOW/ARCHIVE HIGH compression provides the highest level of compression and works well for infrequently accessed data, mostly for read-only data. It enables HCC. COLUMN STORE COMPRESS FOR ARCHIVE LOW/ARCHIVE HIGH on a heap table maps to MEDIUM for SecureFiles LOB segments.

Assumptions
The environment is prepared beforehand; that is, installed an Oracle database 12c non-CDB orcl. The ILM new features are not supported in a multitenant container database (CDB). Any attempt to enable this feature will raise user exceptions.