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:
CREATE INDEX idx_LA_TIME on EVENT12(LAST_ACCESS_TIME);
And please set Initial parameter, optimizer_mode = RULE.