Subscribe

Share

Database, SQL and PL/SQL

Manage the Information Lifecycle

Use Heat Map and Automatic Data Optimization in Oracle Database 12c to take your data’s temperature and manage storage efficiently.

By Anita Mukundan

January/February 2015

Information lifecycle management (ILM) identifies information in a database by usage frequency and assigns different types of storage and different levels of compression, based on the lifecycle stage of that information.

This column discusses two new ILM features introduced in Oracle Database 12c: Heat Map and Automatic Data Optimization. The column also includes sample questions of the type you may encounter when taking the Upgrade Oracle9i/10g/11g OCA to Oracle Database 12c OCP (1Z1-067) or Upgrade to Oracle Database 12c (1Z0-060) exams.


Heat Map and Automatic Data Optimization

Heat Map and Automatic Data Optimization are two new features of the Oracle Advanced Compression option for Oracle Database 12c. Heat Map automatically tracks data modification and query time stamps, providing details of how data is being used. It collects statistical information about the usage frequency or “heat” of a segment, such as a table or a partition. Figure 1 illustrates how Heat Map can classify data.


o15ocp-f1
 

Figure 1: Information lifecycle status and Heat Map temperatures

The information collected by Heat Map is then used by Automatic Data Optimization to move and compress data according to user-defined policies. For example, colder or infrequently accessed data may be compressed more and moved to a low-cost storage device.

The HEAT_MAP initialization parameter enables and disables both Heat Map and Automatic Data Optimization. Heat Map can be enabled at the system or session level. At the segment level, Heat Map tracks the time stamps of the most recent modification and query of each table and partition in a database. At the block level, Heat Map tracks only the most recent modification time stamp.

For Automatic Data Optimization, Heat Map must be enabled at the system level. Conditions in Automatic Data Optimization policies are not limited to Heat Map data; you can also create customized conditions by using PL/SQL functions with your own logic to determine when to move or compress data. Automatic Data Optimization policies can be specified at different levels, including row, segment, and tablespace.


In your session, you successfully execute the command
SQL>ALTER SESSION SET HEAT_MAP = ON;

Which two actions will be automatically supported in your session?

a. Creation of Automatic Data Optimization policies based on Heat Map statistics

b. Creation of Automatic Data Optimization policies based on user-defined conditions

c. Tracking of segment-level reads and writes

d. Tracking of block-level reads and writes

e. Tracking of index lookups and full-table scans

The correct answers are C and E.

Answer C is correct because enabling Heat Map causes the database to track read and write access of segments. Answer E is correct because enabling Heat Map causes the database to collect statistics on all reads performed on objects in a session. Answers A and B are incorrect because Heat Map must be enabled at the system level to allow usage of Automatic Data Optimization policies. Answer D is incorrect because Heat Map tracks only writes but not reads at the block level.

You can alter a table to add more Automatic Data Optimization policies or to enable, disable, or delete existing policies. You can add policies to an entire table or a table partition. Policies are evaluated and executed automatically in the background during the maintenance window. However Automatic Data Optimization policies can also be evaluated and executed anytime by a DBA, manually or via a script.

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

Which statement is true about Automatic Data Optimization?

a. A single Automatic Data Optimization policy must be applied across all the partitions of a table.

b. Automatic Data Optimization policies are evaluated and executed only during the maintenance window.

c. Automatic Data Optimization policies are always based on Heat Map data.

d. A segment-level Automatic Data Optimization policy can be executed multiple times.

The correct answer is D.

Answer D is correct because although a segment-level policy is disabled by default after its first successful execution, it can be explicitly re-enabled. Answer A is incorrect because different Automatic Data Optimization policies can be applied to individual partitions of a table. Answer B is incorrect because a policy can also be executed at any time with scripts or manually by DBAs. Answer C is incorrect because customized conditions in Automatic Data Optimization policies can be created with PL/SQL functions.


Automatic Data Optimization and Storage Tiering

Multiple storage tiers enable organizations to store hot (active), warm (less active), and cold (historical and archive) data on the appropriate high-performance, high-capacity, or low-cost/low-performance storage. Figure 2 shows typical storage tiers for different data.


o15ocp-f2
 

Figure 2: From most- to least-active data and different storage tiers

Automatic Data Optimization storage tiering policies enable automatic data movement to a lower-cost storage tier or a higher-performance storage tier. These policies are applicable only at the segment level and cannot be specified at the row level.

The ALTER TABLE statement

SQL> ALTER TABLE sales 
MODIFY PARTITION qtr1
ILM ADD POLICY TIER TO 
low_cost_tbs;

enables the movement of the QTR1 partition of the SALES table to the LOW_COST_TBS tablespace when the source tablespace that contains the QTR1 partition has reached a capacity threshold.

The threshold for activating tiering policies is based on two parameters:

  • TBS PERCENT USED
  • TBS PERCENT FREE

Both values can be controlled by the DBMS_ILM_ADMIN package.

TBS PERCENT USED and TBS PERCENT FREE default to 85 and 25, respectively. Hence, whenever the source tablespace’s usage percentage goes beyond 85 percent, any tiering policy specified on its objects will be executed and objects will be moved to the target tablespace until the source tablespace becomes at least 25 percent free. Note that it is possible to add a custom condition to tiering policies to enable movement of data based on conditions other than how full the tablespace is.

The Q1_2014 partition of the SALES table is currently stored in the SALES_TBS tablespace, along with several other segments. The COMPLETED_2014_SALES_TBS tablespace exists in your database.

You successfully execute the command

SQL>ALTER TABLE sales 
MODIFY PARTITION q1_2014
ILM ADD POLICY TIER TO 
completed_2014_sales_tbs;

How will this affect the storage of the Q1_2014 partition?

a. It will remain in the SALES_TBS tablespace as long as at least the TBS PERCENT FREE value of space is available in this tablespace.

b. It can be moved out of the SALES_TBS tablespace when this tablespace has exceeded the TBS PERCENT USED value.

c. It can remain in the SALES_TBS tablespace as long as its free space does not exceed the TBS PERCENT USED value and will become READ ONLY in the target tablespace.

d. It will be moved out of the SALES_TBS tablespace when this tablespace has less than the TBS PERCENT FREE value and will become READ ONLY in the target tablespace.

The correct answer is B.

Answer B is correct because when the source tablespace’s usage percentage goes beyond the TBS PERCENT USED value, the storage tiering policy specified for the segments residing in it will be executed.

Answers A, C, and D are incorrect because the TBS PERCENT USED value (not TBS PERCENT FREE) triggers the movement of segments from the source tablespace to the target tablespace until the source tablespace reaches at least the TBS PERCENT FREE value. In addition, the READ ONLY option must be explicitly specified for the target tablespace.


Automatic Data Optimization Policy Limitations

Here are some considerations to take into account when using Automatic Data Optimization policies in ILM strategies:

  • Multiple policies can be applied to a segment, provided they do not have conflicting conditions.
  • A table-level policy overrides a tablespace-level policy.
  • A table-level policy is inherited at the partition level.
  • Automatic Data Optimization policies do not apply to multitenant container databases (CDBs).
  • Automatic Data Optimization storage tiering does not check for storage space in a target tablespace.

Which two answers are factors that should be taken into account when creating Automatic Data Optimization policies?

a. Multiple Automatic Data Optimization policies with distinct conditions can be applied to a segment.

b. A tablespace-level policy overrides a table-level policy.

c. Automatic Data Optimization policies for storage tiering execute only after ensuring that the target tablespace has sufficient percentage based on TBS PERCENT FREE.

d. Compression policies can be created at both the segment and row levels.

e. Automatic Data Optimization policies must be defined separately for each partition in a range-partitioned table.

The correct answers are A and D.

Answer A is correct because segments can have several nonconflicting Automatic Data Optimization policies. Answer D is correct because it is possible to create compression policies at the segment and row levels.

Answer B is incorrect because a table-level policy overrides a tablespace-level policy. Answer C is incorrect because Automatic Data Optimization does not perform checks for storage space in a target tablespace when using storage tiering. Answer E is incorrect because a table-level policy is inherited at the partition level in a partitioned table.


Conclusion

The Heat Map and Automatic Data Optimization features in Oracle Database 12c introduce automatic classification of data, automatic data compression, and movement across storage tiers. This enables comprehensive and automated ILM solutions that minimize costs while maximizing database performance.

Next Steps

LEARN more about
 the Oracle Certification Program
 ILM, Heat Map, and Automatic Data Optimization
 Oracle Advanced Compression

 READ Inside OCP columns

 

Photography by Meric Dagli, Unsplash