Oracle Database 12c: Information Lifecycle Management

More and more resources are used in companies to manage data. Most companies buy more and more storage systems because of poor resource utilization. Starting with Oracle 11g, Oracle has introduced Information Lifecycle Management (ILM) which can use different kinds of storage with varying performances in order to increase cost savings. In Oracle 12c, ILM now allows the automation of the data movement to different storage tiers and the compression at segment level. The idea behind ILM is that when a data is recent it is often accessed, but when the data is old it is not very much accessed. The old data can be moved to a low cost storage, and the most recent data may use high performance storage.

Storage types

Four types of storage tiers can be used:

Source: Oracle Documentation

High performance storage tier to be used by the frequently accessed data.

These ILM policies help us moving data between different tiers of storage. And a new feature has appeared in the Oracle 12c version: The ILM policies now allow to fix the compression level for each storage tier.

Segment level compression tiering

As already explained, ILM in Oracle 12c allows the automation of the data movement to different storage tiers and the compression at segment level. Thus, we can also define compression policies for each storage tier and decide when the data will be moved.

Let's create a table and enable activity tracking:

The compression policy is created:

Let's define a storage policy:

You can also disable the activity tracking:

You can see in this last example how data is compressed after some delay of inactivity and how it is automatically moved to a low cost storage tier.

Row level compression tiering

We can use row level compression in combination with segment level compression.

For example, we can compress the rows of the table trade for OLTP after 1 month of no modification by running the following SQL statement:

With the combination of the row level compression and segment level compression, the DBA has really an efficient way to know where his data is and how it is managed. In fact, because of the data volume reduction, the performance will become better and the size of the rman backups will also decrease.

Conclusion

The new ILM features - row and segment level compression tiering - allow a better control of the data in the database. These features are very interesting for automatically managing the database storage, optimizing its performance, and reducing the storage cost.

Pierre Sicot is Senior Consultant at dbi services. He has more than fifteen years of experience in Oracle database administration, engineering, and optimization. His specialty is the installation, migration, optimization, and security of Oracle databases in production environments. Prior to joining dbi services, Pierre was consultant at Trivadis in Lausanne. He also worked at ilem Group as a database administrator and also as a Consultant for different customers. Pierre holds a engineering diploma of ESME Sudria in France. His branch-related experience covers Energy, Banking, Public Administration, etc.