I am starting Oracle 12c learning series, where I am planning to publish multiple articles on Oracle 12c new features. I hope these articles will be helpful to DBAs aspiring for OCP 12c certification and also others who are planning to implement these features in there databases.
This is a very long post about ADO – Automatic Data Optimization, which is one of the ILM strategy to manage aging data. So please be patient while reading this article. This article provides end-to-end details about ADO implementation, commands syntax etc.
Typical lifecycle of data
Lifecycle of data starts with insert statements. New data is inserted into the table. At this stage data is either kept in non-compressed form or is compressed by DBA. But compression for such active data is optimized for DMLs (example OLTP table compression). Compression has is less and has minimal impact on DMLs.
After a month, activity has subsided, although significant OLTP transactions are still carried out. At this stage, data in former most active partition stays in OLTP table compressed format and new partition is automatically created.
Two months down the line data is rarely modified and accessed rarely as well. At this stage, partition can be moved to low cost storage tier and at higher compression level like hybrid columnar compression (HCC).
After 1 year, data is considered dormant and is no longer accessed or updated. At this stage, data can be moved to low cost storage tier with highest level of HCC compression and may also be marked as read-only.
How (ADO) Automatic Data Optimization works
Automatic data optimization (ADO) is an ILM strategy which uses activity tracking by using heat map to determine if the data is still getting accessed on not.
Heat map provides ability to track and mark data as it goes through lifecycle changes. Heat map can track data at following level
- Data accesses at segment level
- Data modification at segment level and block level
These collected heat map statistics are stored in SYSAUX tablespace. These statistics provide “heat map” of hot and cold data based on how frequent the data is accessed or modified.
ADO allows you to create policies that uses heat map statistics to compress and move data when necessary. ADO automatically evaluates and executes policies that perform compression and storage tiring actions.
Following are the outline steps which can be used to implement and use ADO policies
1) Enable heat map at database level. Heap map tracks data access at segment level and data modification at segment level and row level
2) Create ADO policies.
You can create ADO policies at different scopes/level
a) Tablespace level
b) Group level
c) Segment level
d) Row level
You can also create ADO policies based on different operations to track.
a) Creation
b) Access
c) Modification
You can also provide condition when this policy takes effect
a) Based on time period. Ex: After 3 days, After 1 week, After 1 year
b) Tablespace usage above threshold
You can also specify different kind of action for ADO policy
a) Compression
b) Moving to different storage tier
c) Both compression + move
3) Evaluating and executing ADO policies. By default, segment level ADO policies are evaluated every day during maintenance window. DBA can also create custom schedule for evaluating ADO policies. Row level policies are evaluated by MMON every 15 mins.
4) Verify ADO execution details by using DBA_ILMEVALUATIONDETAILS and DBA_ILMRESULTS view.
5) Verify if segment is compressed or moved based on the action provided in executed policy.
We are going into the details of all steps above and see how we can enable ADO policies as per our business requirements
Step 1) Enabling Heat Map
Enabling and disabling heat map
The very first step before you can start using ADO is to enable activity tracking, which you can enable by setting the new initialization parameter HEAT_MAP
SQL> alter system set heat_map = on;
System altered.
Above parameter will enable activity tracking for entire instance. Enabling heat map will enable tracking both DML and access at segment level and store these details in relevant table in SYSAUX tablespace. This will not track any access details of segments in SYSTEM and SYSAUX tablespace.
*** Important thing to note here is that heat_map does not work with CDB databases. It works only for non-CDB databases. So you cannot use this feature with CDB databases
You can also enable heat map at session level using following command
SQL> alter session set heat_map = on;
Session altered.
You can turn off heat map by setting the parameter to off as below
SQL> alter system set heat_map = off;
System altered.
Checking heat map details
Once you enable heap map, you can check various details of activity tracking provided by heat map. You can check details about when segments were accessed recently, was it row ID access or full scan access or when was the recent DML performed on the segment.
V$HEAT_MAP_SEGMENT view
Tracking details of segments are available at real time in V$HEAP_MAP_SEGMENT view. As soon as session accesses some objects in database, its heat map information will be available immediately in this view.
Example, I just accessed one of the demo table (SALES) in my test database after enabling heat map and I can see following info
SQL> select object_name, SUBOBJECT_NAME, TRACK_TIME, SEGMENT_WRITE, SEGMENT_READ, FULL_SCAN, LOOKUP_SCAN, con_id from v$heat_map_segment order by 2;
OBJECT_NAME SUBOBJECT_ TRACK_TIM SEG SEG FUL LOO CON_ID
-------------------- ---------- --------- --- --- --- --- ----------
SALES P10 14-JUN-16 NO NO YES NO 0
SALES P11 14-JUN-16 NO NO YES NO 0
SALES P12 14-JUN-16 YES NO YES NO 0
SALES P2 14-JUN-16 NO NO YES NO 0
SALES P3 14-JUN-16 NO NO YES NO 0
SALES P4 14-JUN-16 NO NO YES NO 0
SALES P5 14-JUN-16 NO NO YES NO 0
SALES P6 14-JUN-16 NO NO YES NO 0
SALES P7 14-JUN-16 NO NO YES NO 0
SALES P8 14-JUN-16 NO NO YES NO 0
SALES P9 14-JUN-16 NO NO YES NO 0
SALES PM 14-JUN-16 NO NO YES NO 0
I_SALES_ITEM_YEAR 14-JUN-16 NO NO NO YES 0
SALES_ITEM 14-JUN-16 NO NO YES NO 0
14 rows selected.
If you see column FULL_SCAN is YES, it means the access was done using full scan.
If you see column SEGMENT_WRITE is YES, it means the modification was done to the table
TRACK_TIME is the timestamp when access or modification was done.
You can also see LOOKUP_SCAN column is YES for 1 row where object_name is the name of index. So if index is getting used in your select plan, it will show LOOKUP_SCAN as YES for such access.
SYS.HEAT_MAP_STAT$ and DBA_HEAT_MAP_SEG_HISTOGRAM
Data from V$HEAP_MAP_SEGMENT is persisted into SYS.HEAT_MAP_STAT$ table in SYSAUX tablespace by DBMS_SCHEDULER job at regular period of time. This data is then available via view DBA_HEAT_MAP_SEG_HISTOGRAM. Following show similar data when we query DBA_HEAT_MAP_SEG_HISTOGRAM
SQL> select object_name, subobject_name, track_time, SEGMENT_WRITE, full_scan, lookup_scan from DBA_HEAT_MAP_SEG_HISTOGRAM;
OBJECT_NAME SUBOBJECT_ TRACK_TIM SEG FUL LOO
-------------------- ---------- --------- --- --- ---
SALES P2 14-JUN-16 NO YES NO
SALES P3 14-JUN-16 NO YES NO
SALES P4 14-JUN-16 NO YES NO
SALES P5 14-JUN-16 NO YES NO
SALES P6 14-JUN-16 NO YES NO
SALES P7 14-JUN-16 NO YES NO
SALES P8 14-JUN-16 NO YES NO
SALES P9 14-JUN-16 NO YES NO
SALES P10 14-JUN-16 NO YES NO
SALES P11 14-JUN-16 NO YES NO
SALES P12 14-JUN-16 YES YES NO
SALES PM 14-JUN-16 NO YES NO
SALES_ITEM 14-JUN-16 NO YES NO
I_SALES_ITEM_YEAR 14-JUN-16 NO NO YES
14 rows selected.
DBA_HEATMAP_TOP_TABLESPACES
This view shows heat map information for top 100 tablespaces. It gives report at tablespace level instead of segment level. You can fine MIN, MAX and AVG read time, write time, full table access time and lookup access time for each tablespace.
DBMS_HEAT_MAP package
DBMS_HEAT_MAP packages can be used to get heat map statistics at even deeper level. The default access is only going to let you know heat map statistics at segment level. But if you want to go deeper, for example to check which blocks were accessed and at what time, you can use use subprograms in DBMS_HEAT_MAP package.
Following is a summary of the procedures and functions in DBMS_HEAT_MAP package:
- BLOCK_HEAT_MAP function Returns the last modification time for each block in a table segment
- EXTENT_HEAT_MAP function Returns the extent-level Heat Map statistics for a table segment
- OBJECT_HEAT_MAP procedure Shows the minimum, maximum, and average access times for all segments belonging to an object
- SEGMENT_HEAT_MAP procedure Shows the Heat Map attributes for a segment
- TABLESPACE_HEAT_MAP procedure Shows minimum, maximum, and average access times for all segments in a tablespace
Example, we can use DBMS_HEAT_MAP.BLOCK_HEAT_MAP to get heat map statistics of a block of a segment
Similarly, DBMS_HEAT_MAP.EXTENT_HEAT_MAP function provide heat map statistics information at extent level.
Step 2) Enabling ADO policies
Let’s move to the 2nd step of implementing ILM. Now that we have enabled heat map in step 1, we need to create policies at various levels to manage our data. The policy we create will use the heat map statistical data and take the required action that we define in the policy. There are 4 sections in creating ADO policy
Level at which you can create ADO policies
- Tablespace level – Default ADO policy can be defined at tablespace level and will be applicable to all the segments existing in that tablespace or new segments getting created in that tablespace.
- Group level – When we define group level ADO policy, if a table is eligible for specific ADO action defined in policy, the same action would be performed on all dependent objects. If action is compress and table has LOB columns, all secureFile LOBs will be compressed as well.
- Segment level – Applies to tables and table partitions. If no policy defined at segment level and we have a default policy at tablespace level, tablespace level policy applies to segment. But segment level policy overrides tablespace level policy.
- Row level – Can be created only for tracking modifications. We cannot have row level policy for creation and access operations.
Operations to track
- Creation – Action will be taken on specified time condition after creation of data
- Access – Action will be taken on specified time condition after access of data
- Modification – Action will be taken on specified time condition after modification of data
Condition when this policy takes effect
- Based on time period. Ex: After 3 days, After 1 week, After 1 year
- Tablespace usage above threshold
Action to take for ADO policy
- Compression
- Data Movement- Moving to different storage tier
- Both compression + move
You can create ILM policies during CREATE TABLE statement or you can add ILM policies on existing tables using ALTER TABLE statements. A segment can have multiple ILM policies defined on it.
Before we look into different example of creating ILM policies let’s take a look at various compression level available. Data can be compressed while its being inserted, updated or loaded into a table via bulk load.
We have following 4 compression levels available
This is basic level of compression and is used while inserting data into a table without using direct-path insert, using advanced compression option (ACO).
- ROW STORE COMPRESS ADVANCED
This a renamed syntax for the previous OLTP table compression feature that was part of ACO. ROW STORE COMPRESS ADVANCED on heap table maps to LOW compression for SecureFile LOB segments when GROUP level ADO policy is implemented.
- COLUMN STORE COMPRESS FOR QUERY LOW or HIGH
This provides Hybrid columnar compression (or HCC) and provides higher level compression then ROW STORE COMPRESS. It works well when load performance is critical and frequent queries are run against the data and no DMLs are expected. COLUMN STORE COMPRESS FOR QUERY LOW or HIGH maps to MEDIUM compression for SecureFile LOB segments when GROUP level ADO policy is implemented.
- COLUMN STORE COMPRESS FOR ARCHIVE LOW or HIGH
This provides Hybrid columnar compression (or HCC) and provides highest level of compression. It works well when data is accessed very infrequently and no DMLs are run against data. COLUMN STORE COMPRESS FOR ARCHIVE LOW or HIGH maps to MEDIUM compression for SecureFile LOB segments when GROUP level ADO policy is implemented.
Let’s look at creating ADO polices for compression action and Data movement action
ADO policies for Compression action
Following are the different examples of creating ADO compression policies
Tablespace level compression policy
ALTER TABLESPACE USERS DEFAULT ILM ADD POLICY
ROW STORE COMPRESS ADVANCED
SEGMENT AFTER 30 DAYS OF NO ACCESS
In the above colorful command, we can see various sections required in policy implementation. TABLESPACE in purple color tells that this is the default policy at tablespace level and applicable to segments in that tablespace as represented by SEGMENT keyword in blue.
- ILM ADD POLICY – This is required to add a policy to tablespace or segment
- ROW STORE COMPRESS ADVANCED – This is the compression action level we are using as this is compression based ILM policy
- SEGMENT – This represent that this policy is applicable to all segments in USERS tablespace.
- AFTER 30 DAYS OF – This represent condition when this policy will be eligible to take required action of compression.
- NO ACCESS – This represent operation to track. We have 3 different types of operations that we defined above – Access, modification, creation
So if alter tablespace USERS using above command and add ILM policy then if any segment in the tablespace (which has no other ILM policy set) is not accessed for more than 30 days then that segment will be compressed to ROW STORE COMPRESS ADVANCED
Group level compression policy
ALTER TABLE SALES ILM ADD POLICY
ROW STORE COMPRESS ADVANCED
GROUP AFTER 60 DAYS OF NO MODIFICATION
In the above example, we are implementing group level ILM policy. This policy will automatically compress the table SALES if no modification is done for 60 days. Since we are using GROUP level policy, dependent LOBs are compressed with LOW compression after 60 days of no modification.
ALTER TABLE SALES MODIFY PARTITION P1 ILM ADD POLICY
COLUMN STORE COMPRESS FOR ARCHIVE HIGH
GROUP AFTER 3 MONTHS OF CREATION
In this example, we are using GROUP level compression policy on a partition of a table. This policy will automatically compress PARTITION P1 after 3 months of creation and corresponding LOB will be compressed MEDIUM. This does highest level of compression using HCC. Global indexes are maintained
Segment level compression policy
ALTER TABLE SALES ILM ADD POLICY
COLUMN STORE COMPRESS FOR QUERY HIGH
SEGMENT AFTER 90 DAYS OF NO MODIFICATION
In this example, we are creating segment level compression policy on SALES table. This policy is going to automatically compress SALES table with COLUMN STORE COMPRESS FOR QUERY HIGH if it’s not modified for 90 days.
ALTER TABLE SALES ILM ADD POLICY
ROW STORE COMPRESS
SEGMENT AFTER 1 YEAR OF CREATION
This example implements basic level of compression (ROW STORE COMPRESS [BASIC]) on SALES table after 1 year of its creation. SALES table will be automatically compressed after 1 year of its creation
Row level compression policy
ALTER TABLE SALES ILM ADD POLICY
ROW STORE COMPRESS ADVANCED
ROW AFTER 30 DAYS OF NO MODIFICATION
Above policy will compress any block of the table SALES whose all rows are not modified from last 30 days. Even if 1 row in a block gets modified, it does not qualify for compression.
Important thing to note here is that row level compression polices can be created based on modification time only. They cannot be created based on creation time or access time. Also, only compression type available for row level compression policy is ROW STORE COMPRESS ADVANCED/BASIC. We cannot use columnar compression for row level policy.
ADO policies for Data movement action
This is another type of ADO policies that we can set at segment level only. We cannot set this at tablespace level or group level or any other level. Also, data movement happens at tablespace level. Meaning that we can move the table or partition to another tablespace based on low cost storage.
Data movement policy takes following format
ALTER TABLE <TABLE_NAME> ILM ADD POLICY
TIER TO <TABLESPACE_NAME>
[ CUSTOM ILM RULE | SEGMENT <CONDITION> <OPERATION> ]
TIER TO is a new clause and we specify which tablespace the segment should move if it satisfies he condition set. We can also provide custom ILM rules in the last section of the command instead of providing condition (example AFTER 30 DAYS, AFTER 1 YEAR etc) and operation (example CREATION, NO MODIFICATION, NO ACCESS etc).
Let’s take an example
In the above example, we replaced compression clause with “TIER TO” clause.
TIER TO – This clause provides tablespace name where segment should move after it satisfies the condition. In this example, ILM policy will automatically move SALES table to low_cost_storage tablespace after 60 days of creation.
If we don’t provide the last part of condition and operation, then policy will be created and data movement action will take place when tablespace is running out of space. Space threshold for tablespace is already set and we can see those values in DBA_ILMPARAMETERS table.
Following are tiring fullness threshold for tablespace
- TBS PERCENT USED (default 85): Objects with tiring policy will be moved out of tablespace if its TBS PERCENT USED full (default 85% full)
- TBS PERCENT FREE (default 25): Objects with tiring policy will continue to move out until source tablespace has TBS PERCENT FREE space (default 25% free).
You can see current values set by querying DBA_ILMPARAMETERS view. You can customize following parameters using DBMS_ILM_ADMIN package.
SQL> select * from dba_ilmparameters;
NAME VALUE
-------------------- ----------
ENABLED 1
RETENTION TIME 30
JOB LIMIT 2
EXECUTION MODE 2
EXECUTION INTERVAL 1
TBS PERCENT USED 90
TBS PERCENT FREE 15
POLICY TIME 0
8 rows selected.
Example, if we want to change TBS PERCENT USER to 90% from existing 85%, we can use following procedure
SQL> exec dbms_ilm_admin.customize_ilm(dbms_ilm_admin.tbs_percent_used,90);
PL/SQL procedure successfully completed.
We will check out other procedures and functions in DBMS_ILM_ADMIN package at later section in this chapter.
So if we set following policy, then SALES table will be moved out of current tablespace to low_cost_storage tablespace when SALES tablespace reaches 90% of fullness
ALTER TABLE SALES ILM ADD POLICY
TIER TO low_cost_storage
Also, if we set this policy on many tables in the same tablespace and if tablespace reaches the fullness threshold, then tables will be moved to different tablespace in the order of oldest accessed tables first.
Last thing to know about data movement policy is that they are executed only once in life time. Since they apply only at segment level, once the policy is executed on the segment and required movement takes place, the policy is disabled for that segment. This rule applies only for data movement policy and not for compression policy.
CUSTOM_ILM_RULES
Instead of having condition and operation in the simplified syntax provided by Oracle, you can have more complex algorithm about when the desired action for ILM policy should take place by specifying custom ILM rule. The syntax for custom ILM rule looks like below
ALTER TABLE SALES ILM ADD POLICY
<Compression clause> | <Data movement clause>
ON <CUSTOM_ILM_RULE>
A custom ILM rule should be a function which returns Boolean. You can implement complex logic inside the function and return Boolean after evaluating that complex logic. If return value is true, ILM policy takes action. If return value is false, ILM policy does not take action. This custom ILM rule can be used for both compression action as well as data movement action policies.
Implementing multiple policies on segment
A segment can have multiple ADO policies implemented on this. This logically makes sense as we want to have different action taken on data as it ages more and more. Example, we might go for basic level of compression after 1 week of data insertion, more advanced compression after a month of no creation or no modification and highest level of compression of moving to low cost storage after a month of no access or after a year of creation of data. So satisfy all these timelines and criteria it’s not unusual to have multiple ILM policies on a segment. But there are rules to follow.
Rule 1) Policy should be applied on same operation or statistics (creation, modification or access)
If you create a segment level policy on a table based on ACCESS, make sure that other policies are also created based on ACCESS operation only.
Example:
I create a policy on SALES table based on ACCESS
SQL> alter table demo_user.sales ilm add policy row store compress advanced segment after 2 days of no access;
Table altered.
Then I create another policy on same table based on ACCESS
SQL> alter table demo_user.sales ilm add policy column store compress for query high segment after 30 days of no access;
Table altered.
Now, If I tried to create 3rd policy based on creation or modification, I will get conflict
SQL> alter table demo_user.sales ilm add policy column store compress for archive high segment after 60 days of creation;
alter table demo_user.sales ilm add policy column store compress for archive high segment after 60 days of creation
*
ERROR at line 1:
ORA-38323: policy conflicts with policy 21
Rule 2) Compression level should increase with time
You should not reduce compression level as timeline condition increases. In the following example, I tried to set column level compression after 7 days of no access and row store compression (which is lower level compression then column level) after 30 days of no access. This causes conflicts
SQL> alter table demo_user.sales ilm add policy column store compress for query high segment after 7 days of no access;
Table altered.
SQL> alter table demo_user.sales ilm add policy row store compress advanced segment after 30 days of no access;
alter table demo_user.sales ilm add policy row store compress advanced segment after 30 days of no access
*
ERROR at line 1:
ORA-38325: policy conflicts with policy 23
Rule 3) Only 1 row level policy allowed per segment
You can have only 1 row level policy on a table. In following example, I tried to create 2 row level policies on same table and it didn’t allow
SQL> alter table demo_user.sales ilm add policy row store compress advanced row after 7 days of no modification;
Table altered.
SQL> alter table demo_user.sales ilm add policy row store compress advanced row after 30 days of no modification;
alter table demo_user.sales ilm add policy row store compress advanced row after 30 days of no modification
*
ERROR at line 1:
ORA-38325: policy conflicts with policy 24
Also remember that you can create only “no modification” operation policy at row level.
Rule 4) Segment level policy does not overwrite row level policy
Continuing with above example, after creating row level policy, we can still create segment level policy
SQL> alter table demo_user.sales ilm add policy column store compress for query high segment after 30 days of no modification;
Table altered.
Policy priority and policy Inheritance
As we discussed before, we can create policies at tablespace level, which serves as default policy for every segment created in that tablespace with no policy, segment level and even at partition level. Question remains as to which policy will actually take affect?
Again, this follows rules. Following are the simple rules to understand this
- Child level policy always overrides parent level policy for same action. By this logic, policies set at table partition will override the one set at table level if both has same actions and policies at table level will overrides the policy at tablespace level if both has same actions
- If no policies are defined at child level, inheritance takes place. Example, if no ILM policies are defined at partition level, table level policies are applied at partition level. Also, if no ILM policies are defined at table level, tablespace level policies are inherited at table level
- Inheritance is additive if policy actions are different. For example, if you have a compression policy at tablespace level after specified time condition and compression policy at segment level after some specified condition then total effect on the segment is the sum of effect of both policies – i.e. compression + data movement
SQL> select policy_name, object_name, subobject_name, object_type, inherited_from from dba_ilmobjects;
POLICY_NAM OBJECT_NAME SUBOBJECT_ OBJECT_TYPE INHERITED_FROM
---------- -------------------- ---------- ------------------------------ --------------------
P25 SALES P1 TABLE PARTITION TABLE
P25 SALES P10 TABLE PARTITION TABLE
P25 SALES P11 TABLE PARTITION TABLE
P25 SALES P12 TABLE PARTITION TABLE
P25 SALES P2 TABLE PARTITION TABLE
P25 SALES P3 TABLE PARTITION TABLE
P25 SALES P4 TABLE PARTITION TABLE
P25 SALES P5 TABLE PARTITION TABLE
P25 SALES P6 TABLE PARTITION TABLE
P25 SALES P7 TABLE PARTITION TABLE
P25 SALES P8 TABLE PARTITION TABLE
P25 SALES P9 TABLE PARTITION TABLE
P25 SALES PM TABLE PARTITION TABLE
P25 SALES TABLE POLICY NOT INHERITED
P41 SALES_ITEM TABLE TABLESPACE
15 rows selected.
We can check different policies implemented on different objects using DBA_ILMOBJECTS view.
In the above example, Policy P41 is applied on SALES_ITEM table as it exists in USERS tablespace and has no ILM policy defined on it. But since we have default ILM policy at tablespace level USERS, this table SALES_ITEM has inherited this policy from tablespace. In the column INHERITED_FROM, you can see value as TABLESPACE
Similarly, policy P25 applied to table and all its partition. But we create policy at table level only and not at partition level so all partitions of table inherit table level policy.
For object type TABLE and policy P25, we see “POLICY NO INHERITED” in INHERITED_FROM column. This is because we have created new policy at table level and it’s not getting inherited from any level.
Checking ILM policy details
We have couple of views to provide details about ILM policies that exists at various levels in database.
DBA_ILMPOLICIES
You can view all the policies configured in the database in this view
SQL> select * from dba_ilmpolicies;
POLICY_NAM POLICY_TYPE TABLESPACE ENABLE DELETED
---------- ------------- ---------- ------ -------
P24 DATA MOVEMENT YES NO
P25 DATA MOVEMENT YES NO
P41 DATA MOVEMENT USERS YES NO
Even compression policies are represented as data movement policies in this table. This is because during compression data movement happens.
DBA_ILMDATAMOVEMENTPOLICIES
SQL> select policy_name, action_type, compression_level, condition_type, condition_days from DBA_ILMDATAMOVEMENTPOLICIES;
POLICY_NAM ACTION_TYPE COMPRESSION_LEV CONDITION_TYPE CONDITION_DAYS
---------- ----------- --------------- ---------------------- --------------
P41 COMPRESSION ADVANCED LAST MODIFICATION TIME 60
P24 COMPRESSION ADVANCED LAST MODIFICATION TIME 7
P25 COMPRESSION QUERY HIGH LAST MODIFICATION TIME 30
Step 3) Evaluating and Executing ADO policies
We have 2 ways of evaluation and execution of ADO policies – scheduled in maintenance window, evaluating and executing it manually. Let’s look at both approaches
Scheduled in maintenance window
Row level policies are evaluated and executed every 15 mins by MMON background process. Segment level policies are evaluated and executed daily once during maintenance window using a scheduler job. This scheduler job uses DBMS_ILM.EXECUTE_ILM_TASK procedure to evaluate and execute ADO policies.
** Note that name of the package is DBMS_ILM as against DBMS_ILM_ADMIN, which is used for customizing ILM parameters and other admin related tasks. We will look into DBMS_ILM_ADMIN package in short while.
In DBMS_ILM.EXECUTE_ILM_TASK procedure, TASK_ID is an input parameter which is automatically generated during evaluation process, which also happens during scheduled maintenance window.
You can change the interval of evaluation and execution using DBMS_ILM_ADMIN.CUSTOMIZE_ILM procedure. We have seen this procedure previously when we were changing TBS PERCENT FREE and TBS PERCENT USED parameters. We can use same procedure to customize execution interval of ILM tasks.
SQL> exec dbms_ilm_admin.customize_ilm(dbms_ilm_admin.execution_interval, 1);
PL/SQL procedure successfully completed.
Manually executing ADO policies
For manually executing ADO policies, we have 2 options
- Directly execute ADO policies without doing any customization
- Preview the evaluation of ADO policies, make any desired changes to the task and then execute the task
You can directly execute ADO policies without doing any customization using DBMS_ILM.EXECUTE_ILM procedure. Note that this procedure is different than the one that runs in scheduled maintenance window (DBMS_ILM.EXECUTE_ILM_TASK). There are multiple definitions of this procedure and in simple definition, you can just pass owner and table name and all policies on that table will be executed.
You can also preview the evaluation result before you execute the task. You can do following steps to customize ILM tasks
- Evaluate set of ADO policies and preview the result. Check what all tables/policies will be executed
- Add/remove objects/subobjects from ILM tasks evaluation result.
- Execute the final customized evaluated ILM task
You can use following procedure in the same order to perform above steps of customizing ILM tasks
- Execute DBMS_ILM.PREVIEW_ILM procedure to preview ILM tasks for database or specific schema
- Execute DBMS_ILM.ADD_TO_ILM or DBMS_ILM.REMOVE_FROM_ILM to add or remove objects/subobjects from evaluated task
- Execute DBMS_ILM.EXECUTE_ILM_TASK to execute the above customized ILM task
Example:
var v_task_id number;
exec DBMS_ILM.PREVIEW_ILM(TASK_ID=> :v_task_id, ILM_SCOPE=>DBMS_ILM.SCOPE_SCHEMA);
let’s say above command generated task_id as 1234.
exec DBMS_ILM.ADD_TO_ILM(TASK_ID=>1234, OWN=>DEMO_USER’, OBJ_NAME=>’NEW_TABLE’);
exec DBMS_ILM.EXECUTE_ILM_TASK(TASK_ID=>1234);
You can also pass following 2 parameters to DBMS_ILM.EXECUTE_ILM_TASK
EXECUTION_MODE: ILM_EXECUTION_ONLINE value executes the task online and ILM_EXECUTION_OFFLINE executes the task offline.
EXECUTION_SCHEDULE: Only possible value for this parameter is DBMS_ILM.SCHEDULE_IMMEDIATE
Step 4) Checking execution details of ADO policies
You can check the details of ADO policies evaluation using following 3 views
- DBA_ILMEVALUATIONDETAILS
- DBA_ILMRESULTS
- DBA_ILMTASKS
DBA_ILMTASKS
Every task that is executed manually or automatically as scheduled will be recorded in DBA_ILMTASKS view. It stored creation time, start time and completion time of task and if task is completed, failed or Active
SQL> select TASK_ID, STATE, START_TIME, COMPLETION_TIME from DBA_ILMTASKS order by task_id;
TASK_ID STATE START_TIME COMPLETION_TIME
---------- --------- ------------------------------ ------------------------------
1 COMPLETED 15-JUN-16 10.19.24.976731 PM 15-JUN-16 10.19.24.976731 PM
2 COMPLETED 15-JUN-16 10.34.27.587548 PM 15-JUN-16 10.34.27.587548 PM
3 COMPLETED 15-JUN-16 10.49.30.158279 PM 15-JUN-16 10.49.30.158279 PM
4 COMPLETED 15-JUN-16 11.04.32.861197 PM 15-JUN-16 11.04.32.861197 PM
5 COMPLETED 15-JUN-16 11.19.35.192715 PM 15-JUN-16 11.19.35.192715 PM
DBA_ILMEVALUATIONDETAILS
This view can be used to see details on task execution after the evaluation of the ADO policies is completed. If the SELECTED_FOR_EXECUTION column in this view has the value “selected for execution,” then the policy has been selected for execution indeed and an ADO job will be executed to satisfy the ILM policy. However, a job may not execute if there’s a value other than “selected for execution” under the column SELECTED_FOR_EXECUTION.
The following values all mean that the ADO policy hasn’t successfully passed the evaluation:
- Precondition not satisfied
- Policy disabled
- Policy overruled
- Inherited policy overruled
- Job already exists
- No operation since last ILM action
- Target compression not higher than current
- Statistics not available
DBA_ILMRESULTS
Provide details about completed ILM tasks. It has a STATISTICS column which is a CLOB and provide Job specific statistics, such as space saved via compression etc
Enabling and disabling ADO policies
You can enable and or disable ADO policies at individual table level or at database level completely.
Enabling/disabling ADO policies
You can enable/disable individual ADO policy on a table
SQL> alter table demo_user.sales ilm disable policy P25;
Table altered.
SQL> alter table demo_user.sales ilm enable policy P25;
Table altered.
You can also enable/disable all policies on a table
SQL> alter table demo_user.sales ilm disable_all;
Table altered.
SQL> alter table demo_user.sales ilm enable_all;
Table altered.
Deleting ADO policies
You can delete individual policy on a table using policy name
SQL> alter table demo_user.sales ilm delete policy P24;
Table altered.
If you want to delete all policies on a table, you can use delete_all
SQL> alter table demo_user.sales ilm delete_all;
Table altered.
Enable/disable ILM
You can disable ILM completely in database using DBMS_ILM_ADMIN.DISABLE_ILM procedure. This disables ILM, but keep all policies intact. Those policies will not get executed until we enable ILM.
SQL> exec DBMS_ILM_ADMIN.DISABLE_ILM;
PL/SQL procedure successfully completed.
SQL> exec DBMS_ILM_ADMIN.ENABLE_ILM;
PL/SQL procedure successfully completed.
Hope this helps !!
Persistent Storage Solutions 