Information Lifecycle Management
Before reading this flashcard I recommend you to read my article on ILM.
Automatic Data Optimization (ADO)
- Policy based data management
- Let you define policies at :
- Tablespace level
- Segment level
- Row level
- Policies can either compress data or move data to a different tablespace
- Policies let you define when, what and where to move data
- Heat Map collect statistics used to trigger ADO actions
- Heat Map collects statistics on data utilization like:
- When does this row was last updated
- When does this table was last updated
- Policies let you define what to do with :
- Active data
- Frequently accessed data
- Infrequently accessed data
- Dormant data
- Policies let you also define what to do with data when you encounter space pressure in a tablespace.
Views related to Information Lifecycle Management
- DBA_ILMDATAMOVEMENTPOLICIES view displays information specific to data movement related attributes of an ILM policy for ADO.
- DBA_ILMTASKS view displays the task Ids of the procedure
EXECUTE_ILM. This view contains information about all ILM tasks for ADO.
- The DBA_ILMEVALUATIONDETAILS view displays details on policies considered for a particular task. It also shows the name of the job that executes the policy in case the policy was selected for evaluation.
- The DBA_ILMOBJECTS view displays all the objects and policies for ADO in the database.
- The DBA_ILMPOLICIES view displays details about all the policies for ADO in the database.
- The DBA_ILMRESULTS view displays information about data movement-related jobs for ADO in the database.
- The DBA_ILMPARAMETERS view displays information about ADO-related parameters.
Enable/Disable Heat Map
- To enable Heat Map use : ALTER SYSTEM SET HEAT_MAP=ON;
- To disable Heat Map use : ALTER SYSTEM SET HEAT_MAP=OFF;
- By default Heat Map is disabled
- ROW STORE COMPRESS BASIC : Rows are compressed when inserted or updated with the Basic table compression.
- ROW STORE COMPRESS ADVANCED : This is for Advanced Row Compression
- COLUMN STORE COMPRESS FOR QUERY LOW/HIGH : This is Columnar Compression, it provides good performance and is used for frequently queried but not frequently updated data.
- COLUMN STORE COMPRESS FOR ARCHIVE LOW/HIGH : This is the highest level of compression which should be used for infrequently accessed or updated data
- You can create custom policies based on your business rules by creating custom PL/SQL function which will be executed to define if a policy should be executed or not.
- Custom PL/SQL functions return a boolean (TRUE or FALSE)
- On TRUE the policy executes
- On FALSE nothing happens
Evaluation and Execution of ILM Policies
- By default row-level ILM policies are evaluated every 15 minutes by MMON.
- You can customize the interval by execution the following procedure :
Oracle PL/SQL1SQL> EXEC DBMS_ILM_ADMIN.CUSTOMIZE_ILM(DBMS_ILM_ADMIN.EXECUTION_INTERVAL,5);
- You can manually evaluate an ILM policy by using :
SQL> var V_TASKID NUMER
SQL> EXEC DBMS_ILM.EXECUTE_ILM_TASK(owner=>'SCOTT', OBJECT_NAME=>'EMPLOYEE', TASK_ID=>:V_TASKID_NUMBER);
- To disable all Information Lifecycle Management policies on a table use the following:
- ALTER TABLE MY_TABLE ILM DISABLE_ALL;
- To enable all Information Lifecycle Management policies on a table use the following:
- ALTER TABLE MY_TABLE ILM ENABLE_ALL;
- To enable or disable a specific policy use :
- ALTER TABLE MY_TABLE ENABLE/DISABLE POLICY POLICY_NAME;
- To delete a policy on a table use:
- ALTER TABLE MY_TABLE DELETE POLICY POLICY_NAME;
ADO AND HEAT MAP ARE NOT SUPPORTED IN MULTITENANT DATABASES.