CA PIM(SAM) r12.9 SP2: CPU utilization of oracle.exe becomes 98% by PIM service

Document ID : KB000098908
Last Modified Date : 31/07/2018
Show Technical Document Details
Customer met CPU 100% problem . At that time, oracle.exe is using 98% of CPU.
Problem was occurred not start up PIM but it occurred for a while after start.
If the problem is occurred, it should restart to solve it.
OS: Windows all
Product: CA Privileged Identity Manager r12.9 SP2 for Enterprise Management
  User store: Embedded or ActiveDirectory
ORACLE support find following SQL is running long time and using CPU.

update tasksession12 set tasksession12.state =2 where tasksession12.state not in(128,2,32,256) and environmentid=:1 
and tasksession12.tasksessionid in( SELECT tasksessionid FROM (SELECT tasksessionid, count(*) AS TasksCount 
FROM event12 WHERE state in (128,2,32,256) and last_access_time < :2 group by tasksessionid) event1 
WHERE TasksCount in (select count(*) from event12 where last_access_time < :3 group by tasksessionid)) 

This SQL execute for MonitorFailTasks.

ORACLE support claimed this statement does not use correct index.
So, it takes long time and consume CPU.
This performance problem is caused by ORACLE SQL behavior.
ORACLE SQL does not use index with 'not in()'. So, CA should add index at last_access_time in event12 table.
And also, should work with Rule base Optimizer to use correct index.

so please create index as following:
And please set Initial parameter, optimizer_mode = RULE.