5 Replies Latest reply: Jan 15, 2013 11:28 AM by 985022 RSS

    PeopleSoft portal - PSOPRDEFN, PSVERSION,PSLOCK versions out of sync

    user410911
      Hi All,

      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?

      Thanks,
      Hari.A