Blog - comments

Hi Stephane,

In fact at dbi services we have professional material ;-)

Samsung SSD 840 PRO Series

Gregory Steulet

I have a write throughput 10x lower than your test (is it the SSD?)

stephane (ebu)
You obtained :Running the test with following options: Number of threads: 64 Random number generator...
stephane (ebu)

I wrote a benchmark based on your own. I'm so far from your result (which is very good for a laptop)

stephane (ebu)
Hi Stephane,It depends, you can find the stats for MySQL 5.6 below:sysbench 0.5: multi-threaded sys...
Gregory Steulet
Blog Pierre Sicot Oracle Database 12c: Information Lifecycle Management

dbi services Blog

Welcome to the dbi services Blog! This IT blog focuses on database, middleware, and OS technologies such as Oracle, Microsoft SQL Server & SharePoint, EMC Documentum, MySQL, PostgreSQL, Sybase, Unix/Linux, etc. The dbi services blog represents the view of our consultants, not necessarily that of dbi services. Feel free to comment on our blog postings.

  • Home
    Home This is where you can find all the blog posts throughout the site.
  • Categories
    Categories Displays a list of categories from this blog.
  • Tags
    Tags Displays a list of tags that have been used in the blog.
  • Bloggers
    Bloggers Search for your favorite blogger from this site.
Posted by on in Technology Survey

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.
  • Low cost storage tier to manage the old data rarely accessed.
  • Online archive storage tier to manager data seldom accessed, which can be low cost storage tier.
  • Offline archive storage tier to manage data removed from the database which can be stored on a tape.


The central advantages of the online archive storage tier are:

  • Data are always online with faster access in comparison to tape.
  • The storage cost is low.
  • The application can be used to access the data immediately - no need to restore from a tape.


Information Lifecycle Management (ILM) allows us to use policies at the row, segment, and table level with "create" and "alter" SQL statements.

The syntax of the ILM clause is:


ILM_clause := ILM {[ADD | DELETE | DELETE_ALL | ENABLE_ALL | DISABLE_ALL] policy_name POLICY_CLAUSE} | ILM_tracking_clause


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.

The syntax of the compression_clause is:


compression_clause := { COMPRESS FOR { OLTP [INPLACE] | QUERY {LOW | HIGH} } | ARCHIVE {LOW | HIGH}}}


The ILM storage tiering policies are only available at the segment level.

The syntax of the tiering_clause is:


tiering_clause := TIER TO tablespace_name


The ILM tracking clause syntax is:


ILM_tracking_clause ::= ILM {ENABLE | DISABLE} ACTIVITY TRACKING ILM_activity_clauseILM_activity_clause ::= { SEGMENT ACCESS | (WRITE TIME | CREATE TIME | READ TIME)} 


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. 



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.

Rate this blog entry:

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.


  • Guest
    internet shop Friday, 15 August 2014

    thanks like it

  • Guest
    Shahid Tuesday, 16 December 2014

    What are the licenses required in order to implement ILM?
    Partition, Advanced compression, Lifecycle management pack.... anything else??

  • Guest
    pierre Tuesday, 16 December 2014

    Hello Sahid,

    I think that for ILM you need the partitionong option and the Advanced Compression option.
    According to Oracle documentation:
    "Oracle Advanced Compression provides comprehensive data compression and Information Lifecycle Management (ILM) capabilities for all types of data: structured/unstructured, backup, network, archive, and Data Guard Redo Transport traffic. To facilitate automated ILM, Heat Map and Automatic Data Optimization enable organizations to maintain system-generated data usage statistics, at the row and segment levels, and to set policies that implement data compression/movement automatically."


Leave your comment

Guest Sunday, 02 August 2015
AddThis Social Bookmark Button
Deutsch (DE-CH-AT)   French (Fr)


Contact us now!

Send us your request!

Our workshops

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

Expert insight from insiders!

Fixed Price Services

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

A safe investment: our IT services at fixed prices!

Your flexible SLA

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

ISO 20000 certified & freely customizable!

dbi services Newsletter