Based on the trace file we found that OPRDEFN version caused this issue.
Given below are the sequence of sqls captured in the trace.
*1) SELECT VERSION FROM PSVERSION WHERE OBJECTTYPENAME = 'SYS';* version of SYS--240098081
*2) SELECT OPRID FROM PSOPRDEFN WHERE VERSION > :1;* Bind value passed in above condition--214748355
Not sure how the bind value 214748355 is captured in trace. We are unable to find this number in PSVERSION as well as PSLOCK records.
This OPRDEFN sql was fetching 64892 rows which was long time. After that its landing at the home page. As of now we have updated version as 1 for all those 64892 employees.
After updating again we have logged in with trace enabled. This time is so fast to land at home page since the below condition doesnt fetch any rows.
SELECT OPRID FROM PSOPRDEFN WHERE VERSION > :1;
But every login it is updating the version in OPRDEFN > the version of UPM object version of PSVERSION. Its happening like below
PSOPRDEFN.VERSION = PSVERSION.VERSION+1 +(there is no code present like this. Just to exp the issue I have written this line)+
Given below is the PSLOCK record value.
select version from sysadm.pslock where objecttypename in ('UPM','SYS');
Version value for SYS--237798232
Version value for UPM-- 1
Is this normal behavior? Any body else had faced this issue earlier?