Guide to Creating Automatic Data Optimization (ADO) Policies

Defining Automatic Data Optimization (ADO) Policies

In the previous posting, we discussed enabling Heat Map. The next step is to create the ADO policies. The policies:

Define WHICH condition fires an action; for example:

• Low data access
• No access
• No data modification (updates/deletes/inserts on row or DDL ALTER statement on a segment)
• Object/row creation

Define WHEN the policy takes effect (a metric is computed based on statistics like):

• Number of accesses by ROWID
• Number of full table scans
• In the time period specified by the policy
• For example, after n days or months or years of low or no data access AFTER 3 MONTHS OF LOW ACCESS

The next part of creating the ADO policy is to define the action and level of execution, for example:
Next, we define the automatic action and the level of execution. Specify the action to take (possible actions in the first column in the chart above).

Specify:

• Compression and which type of compression
• Data movement to another storage tier
• Both when defining two policies on the same segment

There are four possible levels of execution:

• ROW: Row-level ADO policies can only be created based on modification time
• SEGMENT: Segment-level ADO policies can apply to tables or partitions
• GROUP: Group-level ADO policies indicate that the table’s SecureFile LOBs are compressed while global indexes are maintained
• TABLESPACE: A default ADO policy defined on a tablespace applies to all segments created in the tablespace

Compression can occur while data is being inserted, updated, or bulk-loaded into a table:

• ROW STORE COMPRESS BASIC or ADVANCED is used for rows inserted without using direct-path insert and updated rows.  This option requires the Advanced Compression option (ACO).
• ROW STORE COMPRESS ADVANCED is the syntax used with the Advanced Row Compression feature.  Called OLTP Table Compression in Oracle 11g.  Requires the Advanced Compression option (ACO).
• COLUMN STORE COMPRESS FOR QUERY LOW or HIGH provides a higher level of compression than ROW STORE compression.  This is commonly referred to as Columnar Compression or HCC.  This option works well when load performance is critical, frequent queries are run against the table, and no normal DML is expected.
• COLUMN STORE COMPRESS FOR ARCHIVE LOW/ARCHIVE HIGH compression provides the highest level of compression, also a part of HCC.  Works well for infrequently accessed data, mostly for read-only data
• HCC requires Exadata, Sun ZFS Storage Appliance, or Pillar Axiom Storage System.

Examples of Creating Compression Policies:

This example sets a default tablespace-wide ADO policy. Segments stored in the tablespace inherit the automatic compression with level ROW STORE COMPRESS ADVANCED and occurs after 30 days of low access.

LOW ACCESS means when a policy condition relies on access time, a metric is computed based on statistics in the time period specified by the policy:

• Number of accesses by RowID
• Number of full table scans
• Number of DMLs

The metric would weight these statistics based on the source and target compression levels of the policy. If the target compression is ARCHIVE HIGH, the metric would give a very high weight to DMLs and row lookups as opposed to full table scans.

The following examples set a segment-level ADO policy. Segments stored in the tablespace inherit the automatic compression with level ROW STORE COMPRESS ADVANCED.

Creating Storage Tiering Policy

The second possible action is data movement to another storage tier. It may be to a lower-cost storage tier or a higher-performance storage tier. The only possible scope for data movement is at the segment level.

This example moves the partition P1 of the table TAB6 to the LOW_COST_STORAGE tablespace when the source tablespace has reached the fullness threshold:

The tiering fullness threshold depends on two parameters:

• Objects with tiering policy will be moved if the tablespace they reside in becomes TBS PERCENT USED full (defaulted to 85).
• Objects will be moved to the target tablespace until the source tablespace becomes TBS PERCENT FREE free (defaulted to 25).

Both values can be controlled via the DBMS_ILM_ADMIN.CUSTOMIZE_ILM procedure and displayed in the DBA_ILMPARAMETERS view.

Storage Tiering: Priority

The chart below is explained following the chart.

In this chart, TBS PERCENT USED and TBS PERCENT FREE defaulted to 85 and 25, respectively. Whenever the source tablespace’s usage percent goes beyond 85%, any tiering policy specified on its objects will be executed. All objects will be moved to the target tablespace. As defined here, the source tablespace should be at least 25% free.

If the first parameter is now set to 55%, the policy takes effect and the least recently accessed table will be moved first. In our example, the T1 table, accessed last week, moves first and moves to the target tablespace defined in the tiering policy.

The space used in the source tablespace is still above the TBS PERCENT USED value of 55%. Therefore another table needs to move to the target tablespace. T3, accessed yesterday, is moved to the target tablespace. Now there is enough space in the source tablespace to get a TBS PERCENT USED value below 55% and a TBS PERCENT FREE value above 25% Therefore, table T2 can remain in the source tablespace.

When a segment is moved to another tablespace, another option is to set the target tablespace to READ ONLY after the object is moved. This can be very beneficial during backup since Oracle Recovery Manager (RMAN) has the ability to skip read-only tablespaces.

Custom ADO policies can be created when more flexibility is needed to tailor an ADO policy. They utilize a user-created function to evaluate the policy. The PL/SQL function encapsulates any business logic required to make a decision about ADO on an object. The function returns a BOOLEAN value or a number. For example, if the value returned is TRUE, the policy will be executed. The rules are described in the function. These customized policies can be used only with segment-level policies.

For example, when the BEFORE_JAN_2005 table partition contains fewer rows than a defined threshold or a defined elapsed time after its creation, the segment can then be moved to an archive storage tier.

CUSTOM_ILM_FUNCTION is a user-created PL/SQL function.

Several policies can be applied on the same segment as long as they do not have conflicting conditions. Conflicts appear if all policies on the same object or action are not on the same statistic.

For example, all compression policies have to be applied on the same statistic, either access time, modification time or creation time. It would not make sense to have an Advanced Row compression policy to compress after 3 days of no modification, and also have a QUERY HIGH compression policy to compress after 3 days of no modification. What would make sense is to have an Advanced Row compression policy to compress after 1 day of creation, then have a QUERY HIGH compression policy to compress after 30 days of no modification, then have an ARCHIVE HIGH compression policy to compress after 180 days of no access.

In the next posting, we will wrap this series on Automatic Data Optimization by talking about monitoring and managing ADO.