X

Database Storage Optimization best practices, tips and tricks and guidance from Database Compression Product Management

ADO Example – Automating Compression Tiering for Information Lifecycle Management

Gregg Christman
Product Manager

In this blog we’re going to explore what
exactly Automatic Data Optimization (ADO) is and the role ADO plays in both
database compression tiering and storage tiering. The
use of ADO requires the Advanced Compression option.

What
does ADO provide for your organization?

ADO allows your
organization to create policies that automate data compression and data
movement and to implement the tiering of compression and storage. ADO policies
are specified at the segment or row level for tables and table partitions.
Oracle Database periodically evaluates ADO policies, and uses the information
collected by Heat Map to determine which policies to execute. Policies will be evaluated and executed
automatically, in the background, during the maintenance window. ADO policies
can also be evaluated and executed anytime by a DBA, manually or via a script (
the DBMS_ILM package supports immediate evaluation or
execution of ADO related tasks, see here).

Let’s look at
some examples, using both Advanced Row Compression and Hybrid Columnar
Compression, as part of an ADO solution for data compression tiering (we’ll
look at using ADO for storage tiering in the next blog).

ADO Policy
Conditions

ADO policies
specify what conditions (of data access as tracked by Heat Map) will initiate
an ADO operation – conditions such as no access, or no modification or creation
time
– and when the policy will take effect – for example, after “n” days or
months or years. Organization can also create custom ADO conditions based upon
their specific business rules.

ADO INSIGHT: ADO “conditions” cannot be mixed. For
example, if the first ADO condition, on a table/partition, uses the condition
“no modification”, then the other conditions used for the same table/partition
must use the same condition type.

In our example, we have a table named
“orders” that was initially created without any compression. We have turned on
Heat Map and are tracking the usage of this table over time. It is the
intention, of our organization, to wait until the majority of the post-load
activities, that are performed initially on the table, complete and then the
table will be compressed, using Advanced Row Compression, without moving the table
(meaning the table will be compressed in place). Once the tables cools down,
and begins to be primarily used for reports/queries, we’ll then compress the
table with HCC Query High. When the table has become “cold”, and is only
occasionally queried (used for reporting purposes), we’ll then compress it even
further with HCC Archive High. The example uses the ADO condition “no
modification”.

ADO Compression Tiering: Advanced Row
Compression

The ADO policy below enables Advanced
Row Compression, and since we specified “row” versus “segment” level
compression, the tables’ blocks will be individually compressed when all the
rows on the block meet the ADO compression policy that is specified (that being
AFTER 2 DAYS OF NO MODIFICATION)

ALTER TABLE
orders ILM ADD POLICY

ROW STORE COMPRESS ADVANCED ROW

AFTER 2 DAYS OF NO MODIFICATION;

This policy allows the post-load
activity to subside on the table before compression is implemented. For
organizations with SLA’s around the load times, this allows the table to be
created and populated as quickly as possible, before implementing compression. When
using Advanced Row Compression, compression can be specified at the “row” level
or the “segment” level. Row level allows the table to be compressed in place,
block-by-block, as all the rows on a block meet the ADO policy condition. When
using either Advanced Row or Hybrid Columnar Compression, tables/partitions can
also be compressed at the segment level, which means the table/partition is rebuilt using compression.

ADO Compression Tiering: Hybrid Columnar
Compression (HCC Query High)

The next policy, that was specified by
the DBA, will be automatically enforced by the database (at the segment level)
when Heat Map determines there has been no data modifications for 90 days – the
policy changes the compression level of the table to a higher level of
compression (HCC Query High) when the data is being used primarily for
queries/reporting.

ALTER TABLE
orders ILM ADD POLICY

COLUMN STORE COMPRESS FOR QUERY HIGH SEGMENT

AFTER 90 DAYS OF NO MODIFICATION;

Changing the compression from
Advanced Row Compression, to Hybrid Columnar Compression, occurs
during a maintenance window after the specified ADO policy criteria has been
met.

ADO Compression Tiering: Hybrid Columnar
Compression (HCC Archive High)

When this table further “cools
down” additional storage and performance gains (we’ll talk about moving the data to
Tier 2 storage in the next blog) can also realized when ADO automatically
compresses the data to the highest level possible (HCC Archive High) with
Oracle. In this example, this data is still needed for query purposes, but is no
longer being actively modified and only occasionally queried or used for
reporting. This cold/historic data is an ideal candidate for HCC Archive High compression.

After 180 days of no modification
being made to the data, this ADO policy will be applied.

ALTER TABLE
orders ILM ADD POLICY

COLUMN STORE COMPRESS FOR ARCHIVE HIGH SEGMENT

AFTER 180 DAYS OF NO MODIFICATION;

With the final ADO compression
tiering policy criteria being satisfied, the data is now compressed to the HCC
Archive High level and could also be moved to lower cost storage (Tier 2). This
allows active data to remain on higher performance tiers and also allows the historic
data, which remains online, to still be accessed by applications as needed and
ensures a smaller footprint for the historic data.

In this simple ILM example Oracle
Database, using Heat Map, automatically evaluated the ADO policies to determine
when the table was eligible to be moved to a higher compression level and then
implemented the specified compression automatically, with no additional burden
placed on database administrators or storage management staff. Although this
example uses a table, data partitioning could also have been used and all the
ADO policies specified could have been enforced at the partition-level.

This example uses the “best
practice” approach of compressing using both Advanced Row Compression and
Hybrid Columnar Compression. Advanced Row Compression (as well as Heat Map and
ADO) requires the Advanced Compression option. While HCC doesn’t require the
Advanced Compression option, it does have other requirements, please see here
for a blog discussion of HCC. While compression tiering best practice does
include the use of HCC, if an organization doesn’t have access to HCC, then
they would use only Advanced Row Compression in their ADO policies.

But for now the database storage
optimization adventure continues in the next blog, in which we will discuss how
Automatic Data Optimization automates database storage tiering with Oracle
Database.

Join the discussion

Comments ( 5 )
  • guest Saturday, March 19, 2016

    Thanks for this blog post , I wonder if all this ILM activities occuring during maintenance window are transparent .

    In case of moving partition to different storage tier (tablespace) is that online operation ? What about global indexes and so on ?

    Regards

    GG


  • guest Wednesday, March 23, 2016

    In regards to moving a partition to a different tablespace (storage tier), the Online Move Partition capability can be used to ensure that DML operations can run uninterrupted, on the partition or subpartition, that is being moved.

    The decision to use online partition move (among other ADO decisions) is dictated by an ADO env setting. The ADO env setting can be changed using the procedure 'customize_ilm' of the package DBMS_ILM_ADMIN package. The procedure 'customize_ilm' takes in an ADO env parameter and a value to set to the parameter as arguments. The parameter for controlling whether data movement is online/offline is EXECUTION_MODE and users can set the values ILM_EXECUTION_OFFLINE or ILM_EXECUTION_ONLINE (documented in the dbms_ilm_admin) package.

    The online mode is the default mode for ADO operations. Local and global indexes would be transparently maintained during the move. If the user chooses to use the OFFLINE setting, ADO uses the 'UPDATE INDEXES' clause to update all indexes for partitioned tables. However it is possible that global indexes are invalid for non-partitioned tables. ADO makes a best effort attempt to schedule jobs for rebuilding these indexes.


  • guest Wednesday, March 23, 2016

    Thank You for the explanation, I have to check how this works for normal (nonpartitioned) heap table, does Oracle use somekind of online redefinition like dbms_redefinition package ?

    Regards

    GG


  • Gregg Christman Wednesday, March 23, 2016

    For non-partitioned tables, ADO segment level movement is achieved using DBMS_REDEFINITION or an 'alter table move' based on whether the user chooses "ONLINE". In the (default) case where ADO uses DBMS_REDEFINITION, the indexes would be maintained and would be always available. In the case where the ADO env is set to perform operations OFFLINE, the ADO compression/storage tiering operations for non-partitioned tables is done in 2 phases

    1. Compress/move the table

    2. Create jobs for maintaining global indexes

    Global indexes are usable at the end of ADO operations (completion of step 2). Please note that there would be a brief period (after completion of step 1, before completion of step 2) where global indexes would be unavailable. ADO makes a best effort attempt to schedule jobs for rebuilding these indexes.


  • guest Friday, March 25, 2016

    Thank You for all detailed explanations.

    Regards

    GG


Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.