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:
Define WHEN the policy takes effect (a metric is computed based on statistics like):
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:
There are four possible levels of execution:
Compression can occur while data is being inserted, updated, or bulk-loaded into a table:
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:
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.
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.
