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

Compression policies

• 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

Custom Policies

• 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 :

  SQL> 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);

Enable/Disable Policies

• 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.