Information Lifecycle Management (ILM) is a set of processes and policies for managing data from creation to archival or deletion. Automatic Data Optimization (ADO) creates policies, and automates actions based on those policies, to implement your ILM strategy. ADO uses Heat Map, which tracks data access patterns.
Note: This chapter assumes that you are familiar with the basic concepts of ILM, ADO, and Heap Map. For more background, see Oracle Database VLDB and Partitioning Guide.
ADO manages the IM column store through ADO policies. You can only create an ADO policy with an INMEMORY clause at the segment level.
The database treats an ADO policy like an attribute of an object. ADO policies are at the database level, not the instance level.
ADO有如下策略
Oracle Database supports the following types of ADO policies for Database In-Memory:
- INMEMORY policy
This policy marks objects with the INMEMORY attribute, enabling them for population in the IM column store.
- Recompression policy
This policy changes the compression level on an INMEMORY object.
- NO INMEMORY policy
This policy removes an object from the IM column store and removes its INMEMORY attribute.
ADO根据以下数据来判断使用哪个策略：
Oracle Database supports the following criteria to determine when policies apply:
- A specified number of days since the object was modified
Obtain this value from the column SEGMENT_WRITE_TIME in the DBA_HEAT_MAP_SEGMENT view.
- A specified number of days since the object was accessed
This value is the greater value in the columns SEGMENT_WRITE_TIME, FULL_SCAN, and LOOKUP_SCAN in the DBA_HEAT_MAP_SEGMENT view.
- A specified number of days since the object was created
Obtain this value from the CREATED column in DBA_OBJECTS.
- A user-defined function returns a Boolean value
ADO manages the IM column store as a new data tier.
You can create policies to evict objects from the IM column store when they are being accessed less often, and populate objects when they are being accessed more often and would improve query performance. ADO manages the IM column store using Heat Map statistics.
2.1 Purpose of INMEMORY Policies
In many databases, segments undergo heavy modification after creation. To maximize performance, ADO can populate these segments in the IM column store when write activity subsides. For example, if you add a partition to a table every day, then you can create a policy that populates the sales_2016_d100 partition one day after creation:
ALTER TABLE sales MODIFY PARTITION sales_2016_d100
ILM ADD POLICY SET INMEMORY MEMCOMPRESS FOR QUERY
PRIORITY HIGH
AFTER 1 DAYS OF CREATION
Similarly, you may know that write activity on a table subsides two months after creation, and want to populate this object when this time condition is met:
ALTER TABLE 2016_ski_sales
ILM ADD POLICY SET INMEMORY MEMCOMPRESS FOR QUERY
PRIORITY CRITICAL
AFTER 60 DAYS OF CREATION
The preceding policy causes all existing and new partitions of the 2016_ski_sales table to inherit the policy. When the segment qualifies for the policy, the database marks every partition independently with the specified INMEMORY clause. If the segment already has an INMEMORY policy, then the database ignores the new policy.
2.2 Purpose of Recompression Policies
You may want to compress data in the IM column store based on access patterns. For example, you may want to change a segment from DML compression to query compression 2 days after DML activity on the segment has ceased:
ALTER TABLE lineorders
ILM ADD POLICY MODIFY INMEMORY MEMCOMPRESS FOR QUERY HIGH
AFTER 2 DAYS OF NO MODIFICATION
If the object is not populated in IM column store, then this policy only changes the compression attribute. If the object is populated in the IM column store, then ADO repopulates the object using the new compression level. The database ignores the policy if the segment does not already have the INMEMORY attribute.
2.3 Purpose of NO INMEMORY Policies
To optimize space in the IM column store, you may want to evict inactive segments using a NO INMEMORY policy. This policy is also useful for preventing population of inactive segments by infrequent queries. For example, if reports on a specific sales partition run frequently during the year, but typically not every week, then you may want to may want to evict this partition after a week of no access:
ALTER TABLE sales MODIFY PARTITION sales_2015_q1
ILM ADD POLICY NO INMEMORY AFTER 7 DAYS OF NO ACCESS;
If the sales table for 1998 is rarely queried, then you may want to evict after 1 day of no access:
ALTER TABLE sales_1998
ILM ADD POLICY NO INMEMORY AFTER 1 DAYS OF NO ACCESS;
Queries of an evicted segment are never blocked. The database can always access the data through the traditional buffer cache mechanism.
When enabled, Heat Map automatically discovers data access patterns. ADO uses the Heat Map data to implement user-defined policies at the database level.
Heat Map automatically tracks usage information at the row and segment levels. At the row level, Heat Map tracks data modification times, and then aggregates these times to the block level. At the segment level, Heat Map tracks times for modifications, full table scans, and index lookups.
When an IM column store is enabled, Heat Map tracks access patterns for columnar data. For example, the sales table may be “hot,” whereas the locations table may be “cold.” The ADO algorithms work the same way for columnar data as for row-based data.
The database periodically writes Heat Map data to the data dictionary. The database exposes Heat Map data in data dictionary views. For example, to obtain the read and write time for In-Memory objects, query the ALL_HEAT_MAP_SEGMENT view.
3.2 How Policy Evaluation Works
The policy evaluation for IM column store policies uses the same infrastructure as the evaluation of other ADO policies. The database evaluates and executes policies automatically during the maintenance window.
The database evaluates policies using Heat Map statistics, which are stored in the data dictionary. Setting INMEMORY attributes is mostly a metadata operation, and thus minimally affects performance.
ADO uses the Job Scheduler to perform population. The In-Memory Coordinator Process (IMCO) performs the population.
Enable Heat Map using the HEAT_MAP initialization parameter. Control ADO through a SQL and PL/SQL interface.
4.1 ILM Clause in DDL Statements
No new SQL statements are required to create In-Memory policies, but the ILM clause has new options. The following table describes SQL options for ADO and the IM column store.
Table 4-3 ILM Clause for ADO and the IM Column Store
| Clause | Description | Examples |
| SET INMEMORY | Sets the INMEMORY attribute for the object | ALTER TABLE sh.sales ILM ADD POLICY SET INMEMORY MEMCOMPRESS FOR QUERY LOW PRIORITY HIGH SEGMENT AFTER 30 DAYS OF CREATION; |
| MODIFY INMEMORY | Modifies the compression level for the object | ALTER TABLE sh.customers ILM ADD POLICY MODIFY INMEMORY MEMCOMPRESS FOR QUERY HIGH PRIORITY CRITICAL SEGMENT AFTER 30 DAYS OF CREATION; |
| NO INMEMORY | Sets the NO INMEMORY attribute for the object | ALTER TABLE sh.products ILM ADD POLICY NO INMEMORY SEGMENT AFTER 30 DAYS OF CREATION; |
4.2 Initialization Parameters
The following table describes initialization parameters that are relevant for ADO and the IM column store.
Table 4-4 Initialization Parameters for ADO and the IM Column Store
| Initialization Parameter | Description |
| COMPATIBLE | Specifies the release with which the database must maintain compatibility. For ADO to manage the IM column store, set this parameter to 12.2.0 or higher. |
| HEAT_MAP | Enables both the Heat Map and ADO features. For ADO to manage the IM column store, set this parameter to ON. |
| INMEMORY_SIZE | Enables the IM column store. This parameter must be set to a nonzero value. |
4.3 PL/SQL Packages
The following table describes PL/SQL packages that are relevant for ADO and the IM column store.
Table 4-5 PL/SQL Packages for ADO and the IM Column Store
| Package | Description |
| DBMS_HEATMAP | Displays detailed Heat Map data at the tablespace, segment, object, extent, and block levels. |
| DBMS_ILM | Implements ILM strategies using ADO policies. |
| DBMS_ILM_ADMIN | Customizes ADO policy execution. |
4.4 V$ and Data Dictionary Views
The following table describes views that are relevant for ADO and the IM column store.
Table 4-6 Views for ADO and the IM Column Store
| View | Description |
| DBA_HEAT_MAP_SEG_HISTOGRAM | Displays segment access information for all segments visible to the user. |
| DBA_HEAT_MAP_SEGMENT | Displays the latest segment access time for all segments visible to the user. |
| DBA_HEATMAP_TOP_OBJECTS | Displays heat map information for the top 10000 objects by default. |
| DBA_HEATMAP_TOP_TABLESPACES | Displays heat map information for the top 10000 tablespaces. |
| DBA_ILMDATAMOVEMENTPOLICIES | Displays information specific to data movement-related attributes of an ADO policy in a database. The action_type column describes policies related to the IM column store. Possible values are COMPRESSION, STORAGE, EVICT, and ANNOTATE. |
| V$HEAT_MAP_SEGMENT | Displays real-time segment access information. |
- Creating an ADO Policy for the IM Column Store
You can use ADO policies to set, modify, or remove the INMEMORY clause for objects based on Heat Map statistics.
To create an ADO IM column store policy, specify the ILM ADD POLICY clause in an ALTER TABLE statement, followed by one of the following subclauses:
- SET INMEMORY ... SEGMENT
This option is useful when you want to mark segments with the INMEMORY attribute only when DML activity subsides.
- MODIFY INMEMORY ... MEMCOMPRESS ... SEGMENT
Storing data uncompressed or at the MEMCOMPRESS FOR DML level is appropriate when it is frequently modified. The alternative compression levels are more suited for queries. If the activity on a segment transitions from mostly writes to mostly reads, then you can use the MODIFY clause to apply a different compression method.
- NO INMEMORY ... SEGMENT
This option is useful when access to a segment decreases with time (it becomes “cold”), and to prevent population of this segment as a result of random access.
Prerequisites
Before you can use an ADO IM column store policy, you must meet the following prerequisites:
- Enable the IM column store for the database by setting the INMEMORY_SIZE initialization parameter to a nonzero value and restarting the database.
- The HEAT_MAP initialization parameter must be set to ON.
Heat Map provides data access tracking at the segment-level and data modification tracking at the segment and row level.
- The COMPATIBLE initialization parameter must be set to 12.2.0 or higher.
Example 4-16 Creating an Eviction Policy
In this example, you create a policy specifying that oe.order_items table is evicted from the IM column store if it has not been accessed in three days. An ADO IM column store policy must be a segment-level policy.
ALTER TABLE oe.order_items ILM ADD POLICY
NO INMEMORY SEGMENT
AFTER 3 DAYS OF NO ACCESS;
Example 4-17 Executing an ILM Policy Using DBMS_ILM
You can also evaluate and executes policies manually. Thus, you can programmatically decide when you want an object compressed or tiered. The following example manually executes an ADO task for sh.sales:
DECLARE
v_executonid NUMBER;
BEGIN
DBMS_ILM.EXECUTE_ILM ( owner => 'SH',
object_name => 'SALES',
execution_mode => DBMS_ILM.ILM_EXECUTION_OFFLINE,
task_id => v_executionid);
END;
/
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
