More no of ILM jobs are executing then JOB LIMIT value of DBA_ILMPARAMETERS (Doc ID 2280207.1)

Last updated on JUNE 30, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 12.1.0.2 and later
Information in this document applies to any platform.

Symptoms

SQL> select * from DBA_ILMPARAMETERS;

NAME                                           VALUE
-------------------------------------------------------------------------------------------------------------------------------- ----------
ENABLED                                      1
RETENTION TIME                          30
JOB LIMIT                                     2   =>>>>>>>>>>> No restrict by value
EXECUTION MODE                        2
EXECUTION INTERVAL                  15
TBS PERCENT USED                      85
TBS PERCENT FREE                       25
POLICY TIME                                1

Below result shows ILM jobs are created more then value specified of JOB LIMIT parameter

SQL> select count(*) from
(select t.task_id, t.task_owner, t.state,e.policy_name, e.object_owner, e.object_name,e.SELECTED_FOR_EXECUTION ,r.job_name,r.job_state,r.start_time,r.completion_time,r.statistics
from dba_ilmtasks t,dba_ilmevaluationdetails e,dba_ilmresults r
where t.task_id=e.task_id(+)
and t.task_id=r.task_id(+)
and e.job_name = r.job_name(+)
and t.task_id = :task_id);

COUNT(*)
----------
382

 

Changes

Test case perform to verify this

SQL> exec dbms_ilm_admin.customize_ilm( dbms_ilm_admin.policy_time, dbms_ilm_admin.ilm_policy_in_seconds)

PL/SQL procedure successfully completed.

SQL> select * from DBA_ILMPARAMETERS;

NAME VALUE
-------------------------------------------------------------------------------------------------------------------------------- ----------
ENABLED 1
RETENTION TIME 30
JOB LIMIT 2
EXECUTION MODE 2
EXECUTION INTERVAL 15
TBS PERCENT USED 85
TBS PERCENT FREE 25
POLICY TIME 1

8 rows selected.

SQL> drop user tc cascade;

User dropped.

SQL> alter system set resumable_timeout=900;

System altered.

SQL> grant connect, create table to tc identified by tc;

Grant succeeded.

SQL> alter user tc quota 1G on users;

User altered.

SQL> create table tc.ilm_test
partition by range(created) interval (numtodsinterval(1, 'day'))
 (
 partition p2015 values less than(to_date('20160101', 'yyyymmdd'))
 --, partition p2016 values less than(to_date('20170101', 'yyyymmdd'))
 )
 storage (initial 1M)
 as
 select date '2015-01-01'+level-1 created
 from dual
 connect by level<=sysdate-date'2015-01-01';

Table created.

SQL> ALTER SYSTEM SET HEAT_MAP = ON;

System altered.

SQL> alter table tc.ilm_test ilm add policy row store compress basic segment after 2 day of no modification;

Table altered.

SQL> begin
for part_rec in (select table_owner, table_name, partition_name
from dba_tab_partitions
where table_owner = 'TC'
and table_name = 'ILM_TEST'
order by partition_position desc
offset 1 row)
loop
DBMS_ILM_ADMIN.SET_HEAT_MAP_TABLE (owner => part_rec.table_owner,
tablename => part_rec.table_name,
partition => part_rec.partition_name,
access_date => sysdate-4,
segment_access_summary => dbms_ilm_admin.HEAT_MAP_SEG_READ);
end loop;
end;
/

PL/SQL procedure successfully completed.

SQL> exec dbms_ilm.flush_all_segments

PL/SQL procedure successfully completed.

SQL> alter user tc quota 0 on users;

User altered.

SQL> var task_id number
SQL> exec dbms_ilm.execute_ilm( :task_id, dbms_ilm.SCOPE_DATABASE)

PL/SQL procedure successfully completed.

SQL> exec dbms_lock.sleep( 30 )

PL/SQL procedure successfully completed.

SQL> col task_owner for a10
col state for a10
SQL> SQL> col policy_name for a11
SQL> col object_owner for a12
SQL> col object_name for a12
SQL> col subobject_name for a12
SQL> col object_type for a15
SQL> col comments for a10
SQL> col job_name for a12
SQL> col job_state for a22
SQL> col selected_for_execution for a25
SQL> col statistics for a20
SQL> col start_time for a26
SQL> col completion_time like start_time

SQL> SELECT *
FROM dba_ilmevaluationdetails
WHERE task_id = :task_id
;

TASK_ID POLICY_NAME OBJECT_OWNER OBJECT_NAME SUBOBJECT_NA OBJECT_TYPE SELECTED_FOR_EXECUTION JOB_NAME COMMENTS
---------- ----------- ------------ ------------ ------------ --------------- ------------------------- ------------ ----------
21 P21 TC ILM_TEST P2015 TABLE PARTITION SELECTED FOR EXECUTION ILMJOB306
21 P21 TC ILM_TEST SYS_P1000 TABLE PARTITION SELECTED FOR EXECUTION ILMJOB664
21 P21 TC ILM_TEST SYS_P1001 TABLE PARTITION SELECTED FOR EXECUTION ILMJOB374
21 P21 TC ILM_TEST SYS_P1002 TABLE PARTITION SELECTED FOR EXECUTION ILMJOB376
21 P21 TC ILM_TEST SYS_P1003 TABLE PARTITION SELECTED FOR EXECUTION ILMJOB88
21 P21 TC ILM_TEST SYS_P1004 TABLE PARTITION SELECTED FOR EXECUTION ILMJOB90
21 P21 TC ILM_TEST SYS_P1005 TABLE PARTITION SELECTED FOR EXECUTION ILMJOB760
21 P21 TC ILM_TEST SYS_P1006 TABLE PARTITION SELECTED FOR EXECUTION ILMJOB378
21 P21 TC ILM_TEST SYS_P1007 TABLE PARTITION SELECTED FOR EXECUTION ILMJOB462

SQL> select count(*) from
(select t.task_id, t.task_owner, t.state,e.policy_name, e.object_owner, e.object_name,e.SELECTED_FOR_EXECUTION ,r.job_name,r.job_state,r.start_time,r.completion_time,r.statistics
from dba_ilmtasks t,dba_ilmevaluationdetails e,dba_ilmresults r
where t.task_id=e.task_id(+)
and t.task_id=r.task_id(+)
and e.job_name = r.job_name(+)
and t.task_id = :task_id);

COUNT(*)
----------
382

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms