For example, the following SQL statement enables Heat Map tracking for the database instance.

ALTER SYSTEM SET HEAT_MAP = ON;

When Heat Map is enabled, all accesses are tracked by the in-memory activity tracking module. Objects in the SYSTEM and SYSAUX tablespaces are not tracked.

The following SQL statement disables heat map tracking.

ALTER SYSTEM SET HEAT_MAP = OFF;

When Heat Map is disabled, accesses are not tracked by the in-memory activity tracking module. The default value for the HEAT_MAP initialization parameter is OFF.

The HEAT_MAP initialization parameter also enables and disables Automatic Data Optimization (ADO). For ADO, Heat Map must be enabled at the system level.

Example 5-1 shows examples of information provided by heat map views. The V$HEAT_MAP_SEGMENT view displays real-time segment access information. The ALL_, DBA_, and USER_HEAT_MAP_SEGMENT views display the latest segment access time for all segments visible to the user. The ALL_, DBA_, and USER_HEAT_MAP_SEG_HISTOGRAM views display segment access information for all segments visible to the user. The DBA_HEATMAP_TOP_OBJECTS view displays heat map information for the top most active objects. The DBA_HEATMAP_TOP_TABLESPACES view displays heat map information for the top most active tablespaces.

DBMS_HEAT_MAP includes one set of APIs that externalize heat maps at various levels of storage such as block, extent, segment, object, and tablespace; and a second set of APIs that externalize the heat maps materialized by the background process for the top tablespaces.

Example 5-2 shows examples of the use of DBMS_HEAT_MAP package subprograms.

ADO Policies for Tables

The ILM clauses of the SQL CREATE and ALTER TABLE statements enable you to create, delete, enable or disable a policy for ADO. An ILM policy clause determines the compression or storage tiering policy and contains additional clauses, such as the AFTER and ON clauses to specify the condition when a policy action should occur. When you create a table, you can add a new policy for ADO. You can alter the table to add more policies or to enable, disable, or delete existing policies. You can add policies to an entire table or a partition of a table. You can specify only one condition type for an AFTER clause when adding an ADO policies to a table or partition of a table. ILM ADO policies are given a system-generated name, such P1, P2, and so on to Pn.

A segment level policy executes only one time. After the policy executes successfully, it is disabled and is not evaluated again. However, you can explicitly enable the policy again. A row level policy continues to execute and is not disabled after a successful execution.

The scope of an ADO policy can be specified for a group of related objects or at the level of a segment or row, using the keywords GROUP, ROW, or SEGMENT.

The default mappings for compression that can be applied to group policies are:

• COMPRESS ADVANCED on a heap table maps to standard compression for indexes and LOW for LOB segments.

• COMPRESS FOR QUERY LOW/QUERY HIGH on a heap table maps to standard compression for indexes and MEDIUM for LOB segments.

• COMPRESS FOR ARCHIVE LOW/ARCHIVE HIGH on a heap table maps to standard compression for indexes and HIGH for LOB segments.

The compression mapping cannot be changed. GROUP can only be applied to segment level policies. The storage tiering policies are applicable only at the segment level and cannot be specified at the row level.

ADO Policies for Indexes

ADO policies for indexes enable the compression and optimization for indexes using the existing Automatic Data Optimization (ADO) framework.

You can add an ADO index policy with the ILM clause of the ALTER INDEX or CREATE INDEX SQL statement. An ADO index policy is given a system-generated name, such as P1, P2, ... Pnn.

For example, you can add an ADO policy when the index is created.

CREATE TABLE product_sales 
   (PRODUCT_ID NUMBER NOT NULL,
    CUSTOMER_ID NUMBER NOT NULL, 
    TIME_ID DATE NOT NULL, 
    CHANNEL_ID NUMBER NOT NULL,
    PROMO_ID NUMBER,
    QUANTITY_SOLD NUMBER(10,2) NOT NULL);

CREATE INDEX prod_id_idx ON product_sales(product_id) ILM ADD POLICY OPTIMIZE AFTER 7 DAYS OF NO MODIFICATION;

SELECT POLICY_NAME, POLICY_TYPE, ENABLED FROM USER_ILMPOLICIES;
POLICY_NAME             POLICY_TYPE    ENA
---------------------   -------------  ---
P21                     DATA MOVEMENT  YES

You can add an ADO policy to an existing index.

ALTER INDEX hr.emp_id_idx ILM ADD POLICY SEGMENT TIER TO LOW_COST_TBS;

ALTER INDEX hr.emp_id_idx ILM ADD POLICY OPTIMIZE AFTER 3 DAYS OF NO ACCESS;

The OPTIMIZE clause enables ADO to optimize the index whenever the policy condition is met. The optimization process includes actions such as compressing, shrinking, or rebuilding indexes.

• 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 an index to improve space usage and access speed

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.

When administering ADO policies for indexes, you cannot manually disable policies. An ADO policy for indexes executes only one time. After the policy executes successfully, the policy is disabled and is not evaluated again.

You can delete one policy at a time with the ILM clause of ALTER INDEX SQL statement. For example:

ALTER INDEX prod_id_idx ILM DELETE POLICY p21;

Modifying an ILM ADO policy at the index partition level is not supported. An ADO policy modified at the index level is cascaded to all partitions.

ADO Policies for In-Memory Column Store

Automatic Data Optimization (ADO) supports the In-Memory Column Store (IM column store) with the INMEMORY, INMEMORY MECOMPRESS, and NO INMEMORY policy types.

• To enable objects for population in the In-Memory Column Store, include INMEMORY in the ADD POLICY clause.

• To increase the compression level on objects in an IM column store, include INMEMORY MEMCOMPRESS in the ADD POLICY clause.

• To explicitly evict objects that benefit the least from the IM column store, include NO INMEMORY in the ADD POLICY clause. For example:

The following is an example of the use the NO INMEMORY clause to evict objects from the IM column store.

ALTER TABLE sales_2015 ILM ADD POLICY NO INMEMORY 
      AFTER 7 DAYS OF NO ACCESS;

An ADO policy with an In-Memory Column Store clause can only be a segment level policy. The USER/DBA_ILMDATAMOVEMENTPOLICIES and V$HEAT_MAP_SEGMENT views include information about ADO policies for the In-Memory Column Store.

Customizing ADO Policies

You can customize policies with the ON PL/SQL_function option which provides the ability to determine when the policy should be executed. The ON PL/SQL_function option is available only with segment level policies. For example:

CREATE OR REPLACE FUNCTION my_custom_ado_rules (objn IN NUMBER) RETURN BOOLEAN;

ALTER TABLE sales_custom ILM ADD POLICY COMPRESS ADVANCED SEGMENT
      ON my_custom_ado_rules;

The SQL statement in Example 5-3 creates a table and adds an ILM policy.

The SQL statements in Example 5-4 provide examples of adding ILM policies to a partition of the sales table.

You can disable or delete ILM policies for ADO as shown in the SQL statements in Example 5-5. At times you may need to remove existing ILM policies if those policies conflict with a new policy that you want to add.

In combination with the row-level compression tiering policy, you have fine-grained control over how the data in your database is stored and managed.

Example 5-6 illustrates how to create policies for ADO to enforce a compression and storage tiering policy on the sales_ado table, reflecting the following business requirements:

1. Bulk Load Data

2. Run OLTP workloads

3. After six months with no updates, compress for Archive High

4. Move to low cost storage

An HCC row level policy can be defined on any table regardless of the compression type of the table. Rows from cold blocks can be compressed with HCC when there is DML activity on other parts of the segment.

With HCC policies on non-HCC tables, there may be row movement during updates if the row is in a HCC compression unit (CU). Also, similar to other use cases of row movement, index maintenance is necessary to update index entries that referenced the moved row.

Row-level policies are supported in Oracle Database 12c Release 1 (12.1): however, the database must be at 12.2 compatibility or greater to use HCC row-level compression policies.

Various ILM ADO parameters are described in Table 5-2.

For the values of the TBS_PERCENT* parameters, ADO makes a best effort, but not a guarantee. When the percentage of the tablespace quota reaches the value of TBS_PERCENT_USED, ADO begins to move data so that percent free of the tablespace quota approaches the value of TBS_PERCENT_FREE. As an example, assume that TBS_PERCENT_USED is set to 85 and TBS_PERCENT_FREE is set to 25, and that a tablespace becomes 90 percent full. ADO then initiates actions to move data so that the tablespace quota has at least 25 percent free, which can also be interpreted as less than 75 percent used of the tablespace quota.

You can display the parameters with the DBA_ILMPARAMETERS view. For example, the following query displays the values of the ADO-related parameters.

SQL> SELECT NAME, VALUE FROM DBA_ILMPARAMETERS;

---------------------------------------------------------------- ----------
ENABLED                                                                   1
RETENTION TIME                                                           30
JOB LIMIT                                                                 2
EXECUTION MODE                                                            2
EXECUTION INTERVAL                                                       15
TBS PERCENT USED                                                         85
TBS PERCENT FREE                                                         25
POLICY TIME                                                               0
ABSOLUTE JOB LIMIT                                                       10
DEGREE OF PARALLELISM                                                     4
...

With the PL/SQL DBMS_ILM and DBMS_ILM_ADMIN packages, you can manage ILM activities for ADO so that they do not negatively impact important production workloads. Database compatibility must be set to a minimum of 12.0 to use these packages.

The EXECUTE_ILM procedure of the DBMS_ILM package creates and schedules jobs to enforce policies for ADO. The EXECUTE_ILM() procedure provides this functionality, regardless of any previously-scheduled ILM jobs. All jobs are created and scheduled to run immediately; however, whether they are run immediately depends on the number of jobs queued with the scheduler.

You can use the EXECUTE_ILM procedure if you want more control when ILM jobs are performed, and do not want to wait until the next maintenance window.

The STOP_ILM procedure of the DBMS_ILM package stops all jobs, all running jobs, jobs based on a task Id, or a specific job.

The CUSTOMIZE_ILM procedure in the DBMS_ILM_ADMIN PL/SQL package enables you to customize settings for ADO, as shown in Example 5-9.

For example, you can set the values for the TBS_PERCENT_USED and TBS_PERCENT_FREE ILM parameters or set the ABS_JOBLIMIT ILM parameter. TBS_PERCENT_USED and TBS_PERCENT_FREE determine when data is moved based on tablespace quotas and ABS_JOBLIMIT sets the absolute number of concurrent ADO jobs.

You can also recreate objects with policies using the DBMS_METADATA PL/SQL package.

• The DBA/USER_ILMDATAMOVEMENTPOLICIES view displays information specific to data movement related attributes of an ILM policy for ADO.

• The DBA/USER_ILMTASKS view displays the task Ids of the procedure EXECUTE_ILM. Every time a user invokes the procedure EXECUTE_ILM, a task Id is returned to track this particular invocation. A task Id is also generated to track periodic internal ILM tasks by the database. This view contains information about all ILM tasks for ADO.

• The DBA/USER_ILMEVALUATIONDETAILS view displays details on policies considered for a particular task. It also shows the name of the job that executes the policy in case the policy was selected for evaluation. In case the policy was not executed, this view also provides a reason.

• The DBA/USER_ILMOBJECTS view displays all the objects and policies for ADO in the database. Many objects inherit policies through their parent objects or because they were created in a particular tablespace. This view provides a mapping between the policies and objects. In the case of an inherited policy, this view also indicates the level from which policy is inherited.

• The DBA/USER_ILMPOLICIES view displays details about all the policies for ADO in the database.

• The DBA/USER_ILMRESULTS view displays information about data movement-related jobs for ADO in the database.

• The DBA_ILMPARAMETERS view displays information about ADO-related parameters.