We are facing performance issue while logging in to PeopleSoft portal
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?
I assume it's reagarding your other thread PeopleSoft portal - Login takes so much time
Not sure why you reposted once again. I locked that other thread to avoid confusion.
Have you tried to run the app engine VERSION to fix discrepencies ? You may want to check it before hands with SYSAUDIT report.
I was unable to change to title of the previous post.
I am trying to understand the relativity between the PSVERSION and PSOPRDEFN version fields.
How those values should be? should it be same or PSVERSION should be greater than PSOPRDEFN VERSION?
My case is like always PSOPRDEFN version > PSVERSION version.
I didnt run the Version ae. we have manually updated the version in PSLOCK/PSVERSION/PSOPRDEFN version as 1 for the objecttype UPM.
I also agree. Do not mess with the values. The version values should always be the same. If they are not, run the version app engine as directed in PeopleBooks.
There is a bug also with version and tools 8.49 where doing multiple imports can throw the version off. The workaround is to shut down pside between imports.
Version is used mainly by the app server cache to know what is a current data and what should be refreshed.
Check your SYSAUDITS. If any versions are off, then do a version AE and clear your app server cache.
Edited by: 982019 on Jan 15, 2013 9:28 AM