Online Tutorials & Training Materials | STechies.com
Register Login

Oracle ILM/ADO Parameter Setting

|| || 0

Oracle ILM/ADO Parameter Setting
Stechies

How to check or manage ILM/ADO parameter settings?

Solution

We can use the following SQL statement in order to find current ILM parameters:

SQL> SELECT NAME, VALUE FROM DBA_ILMPARAMETERS;

SAP has 2 additional parameters to be set. They are needed to limit the resources for the ILM background jobs when they run during the Scheduler Maintenance Window so that it does not to impact the throughput of a SAP system.

ABSOLUTE JOB LIMIT:

Maximum upto 2 ILM jobs performing block compression
SQL> exec dbms_ilm_admin.customize_ilm(DBMS_ILM_ADMIN.ABS_JOBLIMIT, 2);

DEGREE OF PARALLELISM:

No parallel execution of ilm jobs
SQL> exec dbms_ilm_admin.customize_ilm(DBMS_ILM_ADMIN.DEG_PARALLEL, 1);

The below table contains all the possible parameters which can be defined:

Name Description
EXECUTION_INTERVAL Determines the frequency with which ADO background evaluation occurs. Specified in minutes.
PURGE_INTERVAL Controls the amount of time ADO history should be maintained. Specified in days.
EXECUTION_MODE Controls whether ADO execution is online, offline. The value for this parameter should either be DBMS_ILM_ADMIN.ILM_EXECUTION_OFFLINE or DBMS_ILM_ADMIN.ILM_EXECUTION_ONLINE.
JOBLIMIT Controls the upper limit on number of ADO jobs at any time. The maximum number of concurrent ADO jobs is JOBLIMIT*(number of instances)*(number of CPUs per instance).
ENABLED Provides a way to turn background ADO off or on.
TBS_PERCENT_USED Decides when a tablespace is considered full. Specified as a percentage of tablespace quota.
TBS_PERCENT_FREE Decides the targeted tablespace storage through ADO actions as a percentage of tablespace quota.
DEG_PARALLEL Decides the degree of parallelism to be used for ADO jobs
POLICY_TIME Decides if ADO policies are treated as though they are specified in seconds rather than days. Can take value ILM_POLICY_IN_SECONDS (treat policy time in seconds) or ILM_POLICY_IN_DAYS (treat policy time in days - default).


Related Articles