Clarity

 View Only
Expand all | Collapse all

Performance issue - Timesheets not available

  • 1.  Performance issue - Timesheets not available

    Posted Apr 16, 2015 04:16 AM

    Hi All,

     

    We're currently facing some issue with Timesheets - from yesterday accessing them it lasts pretty long time (some minutes after clicking on TS icon).

    Today also we got some errors when trying to access TS - Fatal errors.

     

    We've requested AWR and ASH (DB reports) from DB admin and they showed about 78% of DB CPU time spent on Timesheet loading by app3 service engine.

     

    Looking into app3 logs there are some error we can't identify. Could you please help on this? Example of error:

     

    FATAL 2015-04-15 09:37:06,596 [http-bio-29082-exec-1262] web.VXSLServer (clarity:weilerer:76346079__42C122DD-515D-414C-A0D3-7990EDA32497:timeadmin.selectTimesheetTask) Transformation fatal error:A sequence of more than one item is not allowed as the 12th argument of concat(); SystemID: file:/home/clarity/niku/clarity/META-INF/projmgr/vxsl/commonTemplates.xsl; Line#: 1048575; Column#: -1

    ; SystemID: file:/home/clarity/niku/clarity/META-INF/projmgr/vxsl/commonTemplates.xsl; Line#: 1048575; Column#: -1

    net.sf.saxon.trans.DynamicError: A sequence of more than one item is not allowed as the 12th argument of concat()

    at net.sf.saxon.expr.ComputedExpression.typeError(ComputedExpression.java:622)

    at net.sf.saxon.expr.SingletonAtomizer.evaluateItem(SingletonAtomizer.java:109)

    at net.sf.saxon.functions.Concat.evaluateItem(Concat.java:38)

    at net.sf.saxon.expr.ExpressionTool.eagerEvaluate(ExpressionTool.java:296)

    at net.sf.saxon.expr.ExpressionTool.lazyEvaluate(ExpressionTool.java:244)

    at net.sf.saxon.expr.LetExpression.eval(LetExpression.java:163)

     

    OR

     

    ERROR 2015-04-15 09:36:59,503 [http-bio-29082-exec-1263] niku.union (clarity:weilerer:76346079__42C122DD-515D-414C-A0D3-7990EDA32497:timeadmin.selectTimesheetTask)

    com.niku.union.web.WebException: net.sf.saxon.trans.DynamicError: A sequence of more than one item is not allowed as the 12th argument of concat()

      at com.niku.union.web.VXSLServer.transform(VXSLServer.java:166)

      at com.niku.union.web.VXSLServer.transform(VXSLServer.java:94)

      at com.niku.union.web.XMLPageProcessor.transformDataToView(XMLPageProcessor.java:677)

      at com.niku.union.web.XMLPageProcessor.processPortletXSL(XMLPageProcessor.java:862)

      at com.niku.union.web.XMLPageProcessor.processPage(XMLPageProcessor.java:193)

      at com.niku.union.web.XMLPageProcessor.processPage(XMLPageProcessor.java:83)

    I've checked for this error through communities page but found only 2 threads which didn't help me.

     

    I've checked mentioned file (commonTemplates.xsl) and found concat function but don't know how to fix it...even we don't have changed this file...

     

    Both reports and example of app3 log I have but can't attach there as files.

     

    Thanks

     

    Matej



  • 2.  Re: Performance issue - Timesheets not available

    Posted Apr 16, 2015 05:17 AM

    Have you stopped and restarted app3?  (sorry, an obvious statement but often fixes "weird things")



  • 3.  Re: Performance issue - Timesheets not available

    Posted Apr 16, 2015 07:46 AM

    I think it's not the solution but tried it . Bad thing is that it didn't helped...



  • 4.  Re: Performance issue - Timesheets not available

    Posted Apr 16, 2015 06:13 AM

    For the mentioned task, can you check the wbs sequence for the tasks on the project in the db ?

     

    NJ



  • 5.  Re: Performance issue - Timesheets not available

    Posted Apr 16, 2015 07:47 AM

    What do you mean wbs sequence ? Do you know the name of the column which I can search for?



  • 6.  Re: Performance issue - Timesheets not available

    Posted Apr 16, 2015 07:50 AM

    Check the below 2 for the task on the project -

     

    PRTASK.PRWBSSEQUENCE

    PRTASK.PRWBSLEVEL

     

    NJ



  • 7.  Re: Performance issue - Timesheets not available

    Posted Apr 16, 2015 07:53 AM

    Thanks, but both are null: (for TS with Fatal error)

     

    wbs sequence.JPG



  • 8.  Re: Performance issue - Timesheets not available

    Posted Apr 16, 2015 07:59 AM

    Can you open the schedule in OWB and MSP, save it back and then see if it still shows null ?

     

    NJ



  • 9.  Re: Performance issue - Timesheets not available

    Posted Apr 16, 2015 08:08 AM

    We are not using MSP nor OWB. What does this 2 values mean? Should something be there? Thanks



  • 10.  Re: Performance issue - Timesheets not available

    Posted Apr 16, 2015 08:15 AM

    PRTASK.PRWBSSEQUENCE

    This is the sequence of the task in the WBS list

     

    PRTASK.PRWBSLEVEL

    This is the level at which the task is created - 1 is summary level, 2nd level is sub-task / activity / whatever WBS you follow, and so on

     

    If you are not using MSP /  OWB, can you indent / outdent the task, save the schedule and then see if the issue still persists ?

     

    NJ



  • 11.  Re: Performance issue - Timesheets not available

    Posted Apr 16, 2015 08:14 AM

    Just in case you didn;t realise it; your screenshot is showing PRMSPWBSSEQUENCE and PRMSPWBSLEVEL rather than PRWBSSEQUENCE and PRWBSLEVEL (different columns)



  • 12.  Re: Performance issue - Timesheets not available

    Posted Apr 16, 2015 08:16 AM

    Good catch

     

    NJ



  • 13.  Re: Performance issue - Timesheets not available

    Posted Apr 16, 2015 08:19 AM

    Sorry guys, my fault

     

    So the good columns are:

     

    wbs sequence.JPG



  • 14.  Re: Performance issue - Timesheets not available

    Posted Apr 16, 2015 08:45 AM

    May I ask something next?

     

    From our DB team I’ve received another AWR and ASH reports. They show us that the most DB resources (almost 95%) consumes this query:

     

    timeadmin.jpg

     

    It’s standard TS query used for viewing Timesheets or not?

    SELECT X.TE_TYPE , X.ID , TE.USER_LOV1 , TE.USER_LOV2 , (SELECT V.NAME FROM CMN_LOOKUPS_V V WHERE V.LOOKUP_TYPE = 'PRTIMEENTRY_USER_LOV1' AND V.LOOKUP_CODE = TE.USER_LOV1 AND LANGUAGE_CODE = :v0 ) USER_LOV1_NAME , (SELECT V.NAME FROM CMN_LOOKUPS_V V WHERE V.LOOKUP_TYPE = 'PRTIMEENTRY_USER_LOV2' AND V.LOOKUP_CODE = TE.USER_LOV2 AND LANGUAGE_CODE = :v1 ) USER_LOV2_NAME , ROLE.ID AS ROLE_ID , ROLE.FULL_NAME AS ROLE_NAME , CC.PRNAME AS CC_NAME , CC.PREXTERNALID AS CC_ID , TC.PRNAME AS TC_NAME , TC.PREXTERNALID AS TC_ID , TE.PRASSIGNMENTID AS ASSIGN_ID , TE.INCIDENT_ID , A.PRESTSUM AS ASSIGN_ETC , A.PRPENDESTSUM AS ASSIGN_PENDETC , A.PRESTPATTERN AS ASSIGN_LOADPATTERN , A.PREXTENSION AS PREXTENSION , X.ASSIGN_START , X.ASSIGN_FINISH , DETAILS.USAGE_SUM AS ASSIGN_BASELINE , A.PRACTSUM AS ASSIGN_ACTSUM , X.TASK_ID , NVL(X.TASK_NAME, CC.PRNAME) AS TASK_NAME , X.TASK_SHORTNAME , X.TASK_EXTID , X.TASK_WBS_LEVEL , X.TASK_WBS_SEQUENCE , X.PROJ_ID , X.PROJ_DESC , X.PROJ_NAME , X.PROJ_EXTID , X.INV ESTMENT_CODE , X.CATEGORY_NAME , TE.PRACTCURVE , (SELECT 1 FROM DUAL WHERE EXISTS (SELECT PRID FROM PRNOTE WHERE PRTABLENAME='PRTimeEntry' AND PRRECORDID=TE.PRID) ) AS HAS_NOTES , PT1.PARENT_ID AS PARENT_TASK_ID , PT1.PARENT_NAME AS PARENT_TASK_NAME , PT1.PARENT_XID AS PARENT_TASK_EXTID , PT1.PHASE_ID AS PHASE_TASK_ID , PT1.PHASE_NAME AS PHASE_NAME , PT1.PHASE_XID AS PHASE_EXTID FROM PRTIMEENTRY TE LEFT OUTER JOIN PRCHARGECODE CC ON TE.PRCHARGECODEID=CC.PRID LEFT OUTER JOIN PRTYPECODE TC ON TE.PRTYPECODEID=TC.PRID LEFT OUTER JOIN PRASSIGNMENT A ON TE.PRASSIGNMENTID=A.PRID LEFT OUTER JOIN SRM_RESOURCES ROLE ON TE.ROLE_ID=ROLE.ID LEFT OUTER JOIN PRJ_BASELINE_DETAILS DETAILS ON A.PRID = DETAILS.OBJECT_ID AND DETAILS.OBJECT_TYPE = 'ASSIGNMENT' AND DETAILS.IS_CURRENT = 1 LEFT OUTER JOIN (SELECT TASKMS.PRID, TASKMS.PRPROJECTID , PARENTTASK.PRNAME AS PARENT_NAME, PARENTTASK.PRID AS PARENT_ID, PARENTTASK.PREXTERNALID AS PARENT_XID , PHASETASK.PRNAME AS PHASE_NAME, PHASETASK.PRID AS PHASE _ID, PHASETASK.PREXTERNALID AS PHASE_XID FROM PRTASK PARENTTASK, PRTASK PHASETASK , (SELECT T1.PRID, MAX(T2.PRWBSSEQUENCE) AS parentSeq, MAX(T3.PRWBSSEQUENCE) AS phaseSeq, T1.PRPROJECTID FROM PRTASK T1, PRTASK T2, PRTASK T3, PRASSIGNMENT, PRTIMEENTRY TE2 WHERE PRASSIGNMENT.PRRESOURCEID = 5846858 AND TE2.PRTIMESHEETID = 6199406 AND PRASSIGNMENT.PRID = TE2.PRASSIGNMENTID AND PRASSIGNMENT.PRTASKID = T1.PRID AND T2.PRPROJECTID = T1.PRPROJECTID AND T2.PRWBSLEVEL < T1.PRWBSLEVEL AND T2.PRWBSSEQUENCE < T1.PRWBSSEQUENCE AND T3.PRPROJECTID = T1.PRPROJECTID AND T3.PRWBSSEQUENCE < T1.PRWBSSEQUENCE AND T3.PRWBSLEVEL = 1 GROUP BY T1.PRID, T1.PRPROJECTID ) TASKMS WHERE TASKMS.PRPROJECTID = PARENTTASK.PRPROJECTID AND TASKMS.parentSeq = PARENTTASK.PRWBSSEQUENCE AND TASKMS.PRPROJECTID = PHASETASK.PRPROJECTID AND TASKMS.phaseSeq = PHASETASK.PRWBSSEQUENCE ) PT1 ON A.PRTASKID = PT1.PRID , (( SELECT CASE WHEN INV.ODF_OBJECT_CODE='project' THEN 'DIRECT' ELSE 'INVESTMENT' END AS TE_TYPE , TE.PRID AS ID , NVL(A.PRSTART, PRTASK.PRSTART) AS ASSIGN_START , NVL(A.PRFINISH, PRTASK.PRFINISH) AS ASSIGN_FINISH , PRTASK.PRID AS TASK_ID , PRTASK.PRNAME TASK_NAME , PRTASK.PRSHORTNAME AS TASK_SHORTNAME , PRTASK.PREXTERNALID AS TASK_EXTID , PRTASK.PRWBSLEVEL AS TASK_WBS_LEVEL , PRTASK.PRWBSSEQUENCE AS TASK_WBS_SEQUENCE , INV.ID AS PROJ_ID , INV.DESCRIPTION AS PROJ_DESC , INV.NAME AS PROJ_NAME , INV.CODE AS PROJ_EXTID , NLS_UPPER(INV.ODF_OBJECT_CODE) AS INVESTMENT_CODE , '' AS CATEGORY_NAME FROM PRASSIGNMENT A, PRTASK, INV_INVESTMENTS INV, PRTIMEENTRY TE WHERE TE.PRTIMESHEETID= :v2 AND TE.PRASSIGNMENTID=A.PRID AND A.PRTASKID=PRTASK.PRID AND INV.ID = PRTASK.PRPROJECTID ) UNION ( SELECT 'INDIRECT' AS TE_TYPE , TE.PRID AS ID , CAST(NULL AS DATE) AS ASSIGN_START , CAST(NULL AS DATE) AS ASSIGN_FINISH , CAST(NULL AS NUMERIC) AS TASK_ID , '' AS TASK_NAME , '' AS TASK_SHORTNAME , '' AS TASK_EXTID , CAST(NULL AS NUMERIC) AS TASK_WBS_LEVEL , CAST(NULL AS NUMERIC) AS TASK_WBS_SEQUENCE , C AST(NULL AS NUMERIC) AS PROJ_ID , '' AS PROJ_DESC , '' AS PROJ_NAME , '' AS PROJ_EXTID , '' AS INVESTMENT_CODE , '' AS CATEGOR

    Y_NAME FROM PRTIMEENTRY TE WHERE TE.PRTIMESHEETID= :v3 AND TE.PRASSIGNMENTID IS NULL AND TE.INCIDENT_ID IS NULL ) UNION ( SELECT 'INCIDENT' AS TE_TYPE , TE.PRID AS ID , I.START_DATE AS ASSIGN_START , I.RESOLUTION_DATE AS ASSIGN_FINISH , CAST(NULL AS NUMERIC) AS TASK_ID , I.SUBJECT AS TASK_NAME , '' AS TASK_SHORTNAME , I.INCIDENT_CODE AS TASK_EXTID , CAST(NULL AS NUMERIC) AS TASK_WBS_LEVEL , CAST(NULL AS NUMERIC) AS TASK_WBS_SEQUENCE , INV.ID AS PROJ_ID , INV.DESCRIPTION AS PROJ_DESC , INV.NAME AS PROJ_NAME , INV.CODE AS PROJ_EXTID , NLS_UPPER(INV.ODF_OBJECT_CODE) AS INVESTMENT_CODE , C.NAME AS CATEGORY_NAME FROM IMM_INCIDENTS I, INV_INVESTMENTS INV, PRTIMEENTRY TE, IMM_CATEGORIES C WHERE TE.PRTIMESHEETID= :v4 AND TE.PRASSIGNMENTID IS NULL AND TE.INCIDENT_ID = I.ID AND TE.INCIDENT_INVESTMENT_ID = INV.ID AND I.CATEGORY_ID = C.ID )) X WHERE TE.PRTIMESHEETID= :v 5 AND TE.PRID = X.ID ORDER BY proj_name asc, TASK_WBS_SEQUENCE asc, TE.PRID asc



  • 15.  Re: Performance issue - Timesheets not available