ADO (Automatic Data Optimization) / ILM (Information Lifecycle Management) FAQs
Q-1) What are the additional license needed for ADO/ILM?
You can use ADO/ILM if the Advance Compressed Option is licensed by Oracle direct customers. ASFU customer can use ADO/ILM as it is included in the ASFU license.
Q-2) How heat map track the data?
The data is tracked by heat map from Segment level and Row level.
In the segment level the heat map track the time of last modification and access of tables and partitions(Update and Select) and in the Row level, the heat map tracks the modification times for individual rows which are aggregated to the block level (Update).
The heat map skips the internal access of system tasks such as statistics data gathering task, DDLs, table redefinitions.
Q-3) How can we evaluate or trigger ILM policy?
The oracle database by default evaluates row level policies by MMON in every 15 minutes and segment level policies during the Maintenance Window 22:00 everyday. During this evaluation, the information collected by Heat Map is used by Oracle for determining the operation which needed to be executed. With SAP installations the ILM policies should be triggered only automatically and not manually.
Q-4) Can we create our own policy?
The SAP only allows only those ILM policies which are defined by SWPM, therefore, we can't create other policies.
Q-5) How can we enable or disable Heat Map?
The heat map can be enabled or disabled at the system level. But it should only be enabled at system level for ADO purpose.
We can use the below query to enable/disable Heat Map at system level:
- SQL> alter system set heat_map=on scope=both;
- SQL> alter system set heat_map=off scope=both;
Q-6) How can we define policies?
We can define ILM policy at table/partition level and tablespace level. All the tables created after defining ILM policy at tablespace level will inherit the ILM policy from tablespace automatically.
Q-7) How to set or change ILM parameters?
Ans) We can change or set ILM parameter by CUSTOMIZE_ILM procedure of DBMS_ILM_ADMIN package.
for example:
In order to change ILM paramater
SQL> exec dbms_ilm_admin.customize_ilm(DBMS_ILM_ADMIN.PURGE_INTERVAL, 40);
Q-8) When the job log can be cleared?
Ans) The Scheduler by default automatically purges all rows in the job log and window log which are older than 30 days by PURGE_LOG job. The value can be changed by DBMS_SCHEDULER.SET_*_ATTRIBUTE. (It is set by LOG_HISTORY parameter.)
Q-9) How can we check the ILM policy setup of an object?
Ans) In order to check the ILM policy setup of an object the following dictionary views can be used:
- SQL> select * from DBA_ILMPOLICIES;
- SQL> select * from DBA_ILMDATAMOVEMENTPOLICIES;
- SQL> select * from DBA_ILMOBJECTS;
Note: If the policy is inherited tablespace we can find the tablespace name from INHERITED_FROM column of DBA_ILMOBJECTS view.
Q-10) How can we confirm a window and its resource plan configuration?
In order to confirm a window and its resource plan configuration we can get the details from following dictionary views:
- SQL> select * from DBA_SCHEDULER_WINDOWS;
- SQL> select * FROM DBA_RSRC_PLANS WHERE PLAN = 'DEFAULT_MAINTENANCE_PLAN';
Q-11) How do SWPM support ADO/ILM?
Ans) All table data are initially loaded by R3load into a tablespace with an ADO policy which will be compressed in Advanced Row Compression (OLTP) format. All LOB data will be compressed with a degree of LOW. All future added data will not be compressed immediately but delayed according to the defined ADO policy. This mechanism guarantees that online transactions or batch operations are not impacted at all when using database compression.
Note: If the database parameter _advanced_index_compression_options=16 is set then only indexes will be compressed.
Q-12) How can we check the result of a task/job?
Ans) Every compression activities defined by the ADO policies are performed by additional background process ora_jnnn_<SID> of a database instance. We can use the following command in order to check the result of a task/job.
SQL> select di.*, ds.*
from DBA_ILMRESULTS di, DBA_SCHEDULER_JOB_RUN_DETAILS ds where di.task_id = <task_id> and di.job_name = <job_name> and di.job_name = ds.job_name;
Q-13) Which kind of ILM policy can be defined?
There are two types of ILM policies which can be defined:
- Smart Compression tiering
- Storage tiering policies can be defined.
Smart Compression Tiering
Smart Compression tiering allows the organizations to compress different partitions of a table with different compression features, or even to implement Advanced Row Compression at the block level within a single table or partition. With Advanced Row Compression or with Hybrid Columnar Compression, all of the compression actions implemented by ADO are fully online.
For example:
Add a row-level compression policy after 30 days of no modifications on table TEST_ILM.
SQL> alter table TEST_ILM ilm add policy row store compress advanced row after 30 day of no modification;
Storage Tiering
Storage tiering allows organizations to move tables or partitions from one tablespace to another or to free up space on a more expensive storage tier for more important data.
For example:
Add storage tier policy to move table TEST_ILM to a different tablespace ILM_TBS after 60 days of no access.
SQL> alter table TEST_ILM ilm add policy tier to ILM_TBS read only segment after 60 day of no access