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
Issue:
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.
Environment:
OS: Windows all
Product: CA Privileged Identity Manager r12.9 SP2 for Enterprise Management
  CDB: ORACLE 
  User store: Embedded or ActiveDirectory
Cause:
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.
Resolution:
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.