2 Replies Latest reply: Jan 14, 2013 2:40 AM by Nicolas.Gasparotto RSS

    PeopleSoft portal - Login takes so much time

    user410911
      Hi All,

      we are facing slowness issue while login and after that also poor response. We took trace and got the below stuff

      [.....]
      97 (3008)      1-3120783 18.24.48 414.363950 Cur#1.15897.DGEPUAT RC=0 Dur=0.000197 COM Stmt=SELECT VERSION FROM PSVERSION WHERE OBJECTTYPENAME = 'SYS'
      PSAPPSRV.15897 (3008)      1-3120784 18.24.48 0.000936 Cur#1.15897.DGEPUAT RC=0 Dur=0.000001 Commit
      PSAPPSRV.15897 (3008)      1-3120785 18.24.48 0.000124 Cur#1.15897.DGEPUAT RC=0 Dur=0.000064 COM Stmt=SELECT MSGNODENAME FROM PSMSGNODEDEFN WHERE VERSION > :1 UNION SELECT MSGNODENAME FROM PSMSGNODEDEL WHERE VERSION > :2
      PSAPPSRV.15897 (3008)      1-3120786 18.24.48 0.000017 Cur#1.15897.DGEPUAT RC=0 Dur=0.000001 Bind-1 type=8 length=4 value=214748355
      PSAPPSRV.15897 (3008)      1-3120787 18.24.48 0.000006 Cur#1.15897.DGEPUAT RC=0 Dur=0.000001 Bind-2 type=8 length=4 value=214748355
      PSAPPSRV.15897 (3008)      1-3120788 18.24.48 0.000821 Cur#1.15897.DGEPUAT RC=0 Dur=0.000178 COM Stmt=SELECT CLASSID FROM PSCLASSDEFN WHERE VERSION > :1 UNION SELECT CLASSID FROM PSCLASSDEL WHERE VERSION > :2
      PSAPPSRV.15897 (3008)      1-3120789 18.24.48 0.000014 Cur#1.15897.DGEPUAT RC=0 Dur=0.000000 Bind-1 type=8 length=4 value=214748355
      PSAPPSRV.15897 (3008)      1-3120790 18.24.48 0.000005 Cur#1.15897.DGEPUAT RC=0 Dur=0.000000 Bind-2 type=8 length=4 value=214748355
      PSAPPSRV.15897 (3008)      1-3120791 18.24.48 0.000492 Cur#1.15897.DGEPUAT RC=0 Dur=0.000042 COM Stmt=SELECT OPRID FROM PSOPRDEFN WHERE VERSION > :1
      PSAPPSRV.15897 (3008)      1-3120792 18.24.48 0.000007 Cur#1.15897.DGEPUAT RC=0 Dur=0.000000 Bind-1 type=8 length=4 value=214748355
      PSAPPSRV.15897 (3008)      1-3120793 18.24.48 0.276860 Cur#1.15897.DGEPUAT RC=0 Dur=0.000089 COM Stmt=SELECT URL_ID FROM PSURLDEFN WHERE VERSION > :1 UNION SELECT URL_ID FROM PSURLDEL WHERE VERSION > :2
      PSAPPSRV.15897 (3008)      1-3120794 18.24.48 0.000013 Cur#1.15897.DGEPUAT RC=0 Dur=0.000000 Bind-1 type=8 length=4 value=214748355
      PSAPPSRV.15897 (3008)      1-3120795 18.24.48 0.000005 Cur#1.15897.DGEPUAT RC=0 Dur=0.000001 Bind-2 type=8 length=4 value=214748355
      PSAPPSRV.15897 (3008)      1-3120796 18.24.48 0.000857 Cur#1.15897.DGEPUAT RC=0 Dur=0.000050 COM Stmt=SELECT PORTAL_NAME FROM PSPRDMDEFN WHERE VERSION > :1 UNION SELECT PORTAL_NAME FROM PSPRDMDEL WHERE VERSION > :2
      PSAPPSRV.15897 (3008)      1-3120797 18.24.48 0.000007 Cur#1.15897.DGEPUAT RC=0 Dur=0.000000 Bind-1 type=8 length=4 value=214748355
      PSAPPSRV.15897 (3008)      1-3120798 18.24.48 0.000006 Cur#1.15897.DGEPUAT RC=0 Dur=0.000001 Bind-2 type=8 length=4 value=214748355
      PSAPPSRV.15897 (3008)      1-3120799 18.24.48 0.000538 Cur#1.15897.DGEPUAT RC=0 Dur=0.000059 COM Stmt=SELECT PORTAL_NAME, PORTAL_REFTYPE, PORTAL_OBJNAME FROM PSPRSMDEFN WHERE VERSION > :1 UNION SELECT PORTAL_NAME, PORTAL_REFTYPE, PORTAL_OBJNAME FROM PSPRSMDEL WHERE VERSION > :2
      PSAPPSRV.15897 (3008)      1-3120800 18.24.48 0.000006 Cur#1.15897.DGEPUAT RC=0 Dur=0.000001 Bind-1 type=8 length=4 value=214748355
      PSAPPSRV.15897 (3008)      1-3120801 18.24.48 0.000005 Cur#1.15897.DGEPUAT RC=0 Dur=0.000000 Bind-2 type=8 length=4 value=214748355
      PSAPPSRV.15897 (3008)      1-3120802 18.24.48 0.000628 Cur#1.15897.DGEPUAT RC=0 Dur=0.000061 COM Stmt=SELECT PORTAL_NAME, OPRID, PORTAL_REFTYPE, PORTAL_LABEL FROM PSPRUFDEFN WHERE VERSION > :1 UNION SELECT PORTAL_NAME, OPRID, PORTAL_REFTYPE, PORTAL_LABEL FROM PSPRUFDEL WHERE VERSION > :2
      PSAPPSRV.15897 (3008)      1-3120803 18.24.48 0.000007 Cur#1.15897.DGEPUAT RC=0 Dur=0.000000 Bind-1 type=8 length=4 value=214748355
      PSAPPSRV.15897 (3008)      1-3120804 18.24.48 0.000005 Cur#1.15897.DGEPUAT RC=0 Dur=0.000001 Bind-2 type=8 length=4 value=214748355
      PSAPPSRV.15897 (3008)      1-3120805 18.24.48 0.000573 Cur#1.15897.DGEPUAT RC=0 Dur=0.000061 COM Stmt=SELECT ROLENAME FROM PSROLEDEFN WHERE VERSION > :1 UNION SELECT ROLENAME FROM PSROLEDEL WHERE VERSION > :2
      PSAPPSRV.15897 (3008)      1-3120806 18.24.48 0.000006 Cur#1.15897.DGEPUAT RC=0 Dur=0.000001 Bind-1 type=8 length=4 value=214748355
      PSAPPSRV.15897 (3008)      1-3120807 18.24.48 0.000006 Cur#1.15897.DGEPUAT RC=0 Dur=0.000001 Bind-2 type=8 length=4 value=214748355
      PSAPPSRV.15897 (3008)      1-3120808 18.24.48 0.000454 Cur#1.15897.DGEPUAT RC=0 Dur=0.000001 Commit
      PSAPPSRV.15897 (3008)      1-3120809 18.24.48 0.000111 Cur#1.15897.DGEPUAT RC=0 Dur=0.000040 COM Stmt=SELECT VERSION FROM PSVERSION WHERE OBJECTTYPENAME = 'SYS'
      PSAPPSRV.15897 (3008)      1-3120810 18.24.48 0.000803 Cur#1.15897.DGEPUAT RC=0 Dur=0.000080 COM Stmt=SELECT VERSION, EMPLID, EMAILID, LANGUAGE_CD, CURRENCY_CD, OPERPSWD, ENCRYPTED, SYMBOLICID, OPRCLASS, ROWSECCLASS, MULTILANG, PTALLOWSWITCHUSER, TO_CHAR(LASTPSWDCHANGE,'YYYY-MM-DD'), ACCTLOCK, PRCSPRFLCLS, DEFAULTNAVHP, LASTUPDDTTM, LASTUPDOPRID, FAILEDLOGINS, OPRDEFNDESC, EXPENT, OPRTYPE FROM PSOPRDEFN WHERE OPRID = :1
      PSAPPSRV.15897 (3008)      1-3120811 18.24.48 0.000009 Cur#1.15897.DGEPUAT RC=0 Dur=0.000000 Bind-1 type=2 length=7 value=1386959
      PSAPPSRV.15897 (3008)      1-3120812 18.24.48 0.001191 Cur#1.15897.DGEPUAT RC=0 Dur=0.000187 COM Stmt=SELECT COUNT (*) FROM PSROLEUSER WHERE ROLEUSER = :1
      PSAPPSRV.15897 (3008)      1-3120813 18.24.48 0.000016 Cur#1.15897.DGEPUAT RC=0 Dur=0.000001 Bind-1 type=2 length=7 value=1386959
      PSAPPSRV.15897 (3008)      1-3120814 18.24.48 0.000637 Cur#1.15897.DGEPUAT RC=0 Dur=0.000046 COM Stmt=SELECT ROLENAME, DYNAMIC_SW FROM PSROLEUSER WHERE ROLEUSER = :1 ORDER BY ROLENAME
      PSAPPSRV.15897 (3008)      1-3120815 18.24.48 0.000006 Cur#1.15897.DGEPUAT RC=0 Dur=0.000001 Bind-1 type=2 length=7 value=1386959
      PSAPPSRV.15897 (3008)      1-3120816 18.24.48 0.000698 Cur#1.15897.DGEPUAT RC=0 Dur=0.000063 COM Stmt=SELECT COUNT (DISTINCT CLASSID) FROM PSROLEUSER A, PSROLECLASS B, PSROLEDEFN C WHERE A.ROLEUSER = :1 AND A.ROLENAME = B.ROLENAME AND B.ROLENAME = C.ROLENAME AND A.ROLENAME = C.ROLENAME AND C.ROLESTATUS = 'A'
      PSAPPSRV.15897 (3008)      1-3120817 18.24.48 0.000006 Cur#1.15897.DGEPUAT RC=0 Dur=0.000000 Bind-1 type=2 length=7 value=1386959
      PSAPPSRV.15897 (3008)      1-3120818 18.24.48 0.000931 Cur#1.15897.DGEPUAT RC=0 Dur=0.000063 COM Stmt=SELECT DISTINCT CLASSID FROM PSROLEUSER A, PSROLECLASS B, PSROLEDEFN C WHERE A.ROLEUSER = :1 AND A.ROLENAME = B.ROLENAME AND B.ROLENAME = C.ROLENAME AND A.ROLENAME = C.ROLENAME AND C.ROLESTATUS = 'A'
      PSAPPSRV.15897 (3008)      1-3120819 18.24.48 0.000007 Cur#1.15897.DGEPUAT RC=0 Dur=0.000000 Bind-1 type=2 length=7 value=1386959
      PSAPPSRV.15897 (3008)      1-3120820 18.24.48 0.001069 Cur#1.15897.DGEPUAT RC=0 Dur=0.000148 COM Stmt=SELECT COUNT(*) FROM PSUSEROPTNDEFN
      PSAPPSRV.15897 (3008)      1-3120821 18.24.48 0.000543 Cur#1.15897.DGEPUAT RC=0 Dur=0.000047 COM Stmt=SELECT OPTN_CATEGORY_LVL, OPTN_CATEGORY_GRP, OPTN_CATEGORY, USEROPTN, USER_OPTION_VALUE FROM PSUSEROPTNDEFN
      PSAPPSRV.15897 (3008)      1-3120822 18.24.48 0.000982 Cur#1.15897.DGEPUAT RC=0 Dur=0.000152 COM Stmt=SELECT OPTN_CATEGORY_LVL, USEROPTN, USER_OPTION_VALUE FROM PSLOCALEOPTNDFN WHERE LOCALECD = :1
      PSAPPSRV.15897 (3008)      1-3120823 18.24.48 0.000007 Cur#1.15897.DGEPUAT RC=0 Dur=0.000000 Bind-1 type=2 length=5 value=en-us
      PSAPPSRV.15897 (3008)      1-3120824 18.24.48 0.000678 Cur#1.15897.DGEPUAT RC=0 Dur=0.000046 COM Stmt=SELECT OPTN_CATEGORY_LVL, USEROPTN, USER_OPTION_VALUE FROM PSUSERPRSNLOPTN WHERE OPRID = :1
      PSAPPSRV.15897 (3008)      1-3120825 18.24.48 0.000007 Cur#1.15897.DGEPUAT RC=0 Dur=0.000000 Bind-1 type=2 length=7 value=1386959
      PSAPPSRV.15897 (3008)      1-3120826 18.24.48 0.000709 Cur#1.15897.DGEPUAT RC=0 Dur=0.000152 COM Stmt=SELECT 'X' FROM PSROLEUSER WHERE ROLEUSER = :1 AND ROLENAME = 'PeopleSoft Administrator'
      PSAPPSRV.15897 (3008)      1-3120827 18.24.48 0.000007 Cur#1.15897.DGEPUAT RC=0 Dur=0.000001 Bind-1 type=2 length=7 value=1386959
      PSAPPSRV.15897 (3008)      1-3120828 18.24.48 0.000531 Cur#1.15897.DGEPUAT RC=0 Dur=0.000051 COM Stmt=SELECT 'X' FROM PSROLEUSER A, PSROLEDEFN B WHERE A.ROLEUSER = :1 AND A.ROLENAME = B.ROLENAME AND B.ALLOWNOTIFY ='Y' AND B.ROLESTATUS = 'A'
      PSAPPSRV.15897 (3008)      1-3120829 18.24.48 0.000006 Cur#1.15897.DGEPUAT RC=0 Dur=0.000000 Bind-1 type=2 length=7 value=1386959
      PSAPPSRV.15897 (3008)      1-3120830 18.24.48 0.001041 Cur#1.15897.DGEPUAT RC=0 Dur=0.000039 COM Stmt=SELECT VERSION FROM PSVERSION WHERE OBJECTTYPENAME = 'SYS'
      PSAPPSRV.15897 (3008)      1-3120831 18.24.48 0.000587 Cur#1.15897.DGEPUAT RC=0 Dur=0.000000 Commit
      PSAPPSRV.15897 (3008)      1-3120832 18.24.48 0.001391 Cur#1.15897.DGEPUAT RC=0 Dur=0.000325 Commit
      PSAPPSRV.15712 (3156)       1-2496374 18.24.48 1168.683602 Cur#1.15712.DGEPUAT RC=0 Dur=0.000083 COM Stmt=SELECT VERSION FROM PSVERSION WHERE OBJECTTYPENAME = 'SYS'
      PSAPPSRV.15712 (3156)      1-2496375 18.24.48 0.000762 Cur#1.15712.DGEPUAT RC=0 Dur=0.000000 Commit
      PSAPPSRV.15712 (3156)      1-2496376 18.24.48 0.000101 Cur#1.15712.DGEPUAT RC=0 Dur=0.000054 COM Stmt=SELECT MSGNODENAME FROM PSMSGNODEDEFN WHERE VERSION > :1 UNION SELECT MSGNODENAME FROM PSMSGNODEDEL WHERE VERSION > :2
      PSAPPSRV.15712 (3156)      1-2496377 18.24.48 0.000010 Cur#1.15712.DGEPUAT RC=0 Dur=0.000000 Bind-1 type=8 length=4 value=214748355
      PSAPPSRV.15712 (3156)      1-2496378 18.24.48 0.000005 Cur#1.15712.DGEPUAT RC=0 Dur=0.000000 Bind-2 type=8 length=4 value=214748355
      PSAPPSRV.15712 (3156)      1-2496379 18.24.48 0.001013 Cur#1.15712.DGEPUAT RC=0 Dur=0.000205 COM Stmt=SELECT CLASSID FROM PSCLASSDEFN WHERE VERSION > :1 UNION SELECT CLASSID FROM PSCLASSDEL WHERE VERSION > :2
      PSAPPSRV.15712 (3156)      1-2496380 18.24.48 0.000017 Cur#1.15712.DGEPUAT RC=0 Dur=0.000001 Bind-1 type=8 length=4 value=214748355
      PSAPPSRV.15712 (3156)      1-2496381 18.24.48 0.000006 Cur#1.15712.DGEPUAT RC=0 Dur=0.000001 Bind-2 type=8 length=4 value=214748355
      PSAPPSRV.15712 (3156)      1-2496382 18.24.48 0.000714 Cur#1.15712.DGEPUAT RC=0 Dur=0.000146 COM Stmt=SELECT OPRID FROM PSOPRDEFN WHERE VERSION > :1
      PSAPPSRV.15712 (3156)      1-2496383 18.24.48 0.000007 Cur#1.15712.DGEPUAT RC=0 Dur=0.000001 Bind-1 type=8 length=4 value=214748355
      PSAPPSRV.15712 (3156)      1-2496384 18.24.48 0.273570 Cur#1.15712.DGEPUAT RC=0 Dur=0.000181 COM Stmt=SELECT URL_ID FROM PSURLDEFN WHERE VERSION > :1 UNION SELECT URL_ID FROM PSURLDEL WHERE VERSION > :2
      PSAPPSRV.15712 (3156)      1-2496385 18.24.49 0.000014 Cur#1.15712.DGEPUAT RC=0 Dur=0.000000 Bind-1 type=8 length=4 value=214748355
      PSAPPSRV.15712 (3156)      1-2496386 18.24.49 0.000005 Cur#1.15712.DGEPUAT RC=0 Dur=0.000001 Bind-2 type=8 length=4 value=214748355
      PSAPPSRV.15712 (3156)      1-2496387 18.24.49 0.001103 Cur#1.15712.DGEPUAT RC=0 Dur=0.000176 COM Stmt=SELECT PORTAL_NAME FROM PSPRDMDEFN WHERE VERSION > :1 UNION SELECT PORTAL_NAME FROM PSPRDMDEL WHERE VERSION > :2
      PSAPPSRV.15712 (3156)      1-2496388 18.24.49 0.000016 Cur#1.15712.DGEPUAT RC=0 Dur=0.000000 Bind-1 type=8 length=4 value=214748355
      PSAPPSRV.15712 (3156)      1-2496389 18.24.49 0.000006 Cur#1.15712.DGEPUAT RC=0 Dur=0.000001 Bind-2 type=8 length=4 value=214748355
      PSAPPSRV.15712 (3156)      1-2496390 18.24.49 0.000623 Cur#1.15712.DGEPUAT RC=0 Dur=0.000062 COM Stmt=SELECT PORTAL_NAME, PORTAL_REFTYPE, PORTAL_OBJNAME FROM PSPRSMDEFN WHERE VERSION > :1 UNION SELECT PORTAL_NAME, PORTAL_REFTYPE, PORTAL_OBJNAME FROM PSPRSMDEL WHERE VERSION > :2
      PSAPPSRV.15712 (3156)      1-2496391 18.24.49 0.000007 Cur#1.15712.DGEPUAT RC=0 Dur=0.000000 Bind-1 type=8 length=4 value=214748355
      PSAPPSRV.15712 (3156)      1-2496392 18.24.49 0.000006 Cur#1.15712.DGEPUAT RC=0 Dur=0.000001 Bind-2 type=8 length=4 value=214748355
      PSAPPSRV.15712 (3156)      1-2496393 18.24.49 0.000772 Cur#1.15712.DGEPUAT RC=0 Dur=0.000173 COM Stmt=SELECT PORTAL_NAME, OPRID, PORTAL_REFTYPE, PORTAL_LABEL FROM PSPRUFDEFN WHERE VERSION > :1 UNION SELECT PORTAL_NAME, OPRID, PORTAL_REFTYPE, PORTAL_LABEL FROM PSPRUFDEL WHERE VERSION > :2
      PSAPPSRV.15712 (3156)      1-2496394 18.24.49 0.000008 Cur#1.15712.DGEPUAT RC=0 Dur=0.000001 Bind-1 type=8 length=4 value=214748355
      PSAPPSRV.15712 (3156)      1-2496395 18.24.49 0.000005 Cur#1.15712.DGEPUAT RC=0 Dur=0.000000 Bind-2 type=8 length=4 value=214748355
      PSAPPSRV.15712 (3156)      1-2496396 18.24.49 0.000720 Cur#1.15712.DGEPUAT RC=0 Dur=0.000174 COM Stmt=SELECT ROLENAME FROM PSROLEDEFN WHERE VERSION > :1 UNION SELECT ROLENAME FROM PSROLEDEL WHERE VERSION > :2
      PSAPPSRV.15712 (3156)      1-2496397 18.24.49 0.000007 Cur#1.15712.DGEPUAT RC=0 Dur=0.000001 Bind-1 type=8 length=4 value=214748355
      PSAPPSRV.15712 (3156)      1-2496398 18.24.49 0.000005 Cur#1.15712.DGEPUAT RC=0 Dur=0.000000 Bind-2 type=8 length=4 value=214748355
      PSAPPSRV.15712 (3156)      1-2496399 18.24.49 0.000526 Cur#1.15712.DGEPUAT RC=0 Dur=0.000001 Commit
      PSAPPSRV.15712 (3156)      1-2496400 18.24.49 0.001204 Cur#1.15712.DGEPUAT RC=0 Dur=0.000335 Commit
      PSAPPSRV.15821 (2983)       1-2288290 18.24.49  102.495154 Cur#1.15821.DGEPUAT RC=0 Dur=0.000192 COM Stmt=SELECT VERSION FROM PSVERSION WHERE OBJECTTYPENAME = 'SYS'
      PSAPPSRV.15821 (2983)      1-2288291 18.24.49 0.000898 Cur#1.15821.DGEPUAT RC=0 Dur=0.000001 Commit
      [......]

      these lines are repeatedly captured in the trace files. Not sure whether version causing this issue??

      Any idea on this issue? how to analyze and debug this issue?

      Thanks,
      Hari.A
        • 1. Re: PeopleSoft portal - PSOPRDEFN, PSVERSION,PSLOCK versions out of sync
          user410911
          Hi All,

          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