Clarity

Expand all | Collapse all

Performance issue - Timesheets not available

  • 1.  Performance issue - Timesheets not available

    Posted 04-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 04-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 04-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 04-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 04-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 04-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 04-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 04-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 04-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 04-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 04-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 04-16-2015 08:16 AM

    Good catch

     

    NJ



  • 13.  Re: Performance issue - Timesheets not available

    Posted 04-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 04-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

    Posted 04-16-2015 08:48 AM

    Can you share the concat function ?

     

    NJ



  • 16.  Re: Performance issue - Timesheets not available

    Posted 04-16-2015 09:24 AM

    Yes of course:

     

    <xsl:variable name="lock_dateTime">

              <xsl:text xml:lang="en">Locked:</xsl:text>

              <!--Vantive 99650, added time zone arg...-->

              <xsl:value-of select="concat(' ',date:format-date-time(@locked_since, /data/header/userSession/@formattingLocale,/data/header/userSession/@timezone, true()))"/>

            </xsl:variable>

            <image src="Lock" type="gif" highlight="true">

              <tip value="{$lock_dateTime}"/>

     

    <xsl:variable name="excelAction">

          <xsl:value-of select="concat('npt.gridExcelExport&amp;',substring-after(/data/header/action/@url,'&amp;'))"/>

        </xsl:variable>

     

    In the meantime I've tried to execute the above query with exactly timesheet ID instead of variables ( :V)

    I've got syntax error... I've found that in some rows there are syntax error like "Inv estment" instead of "Investment"

    or 'Parse _ID" instead of "Parse_ID". Could this blank characters be created during creating AWR reports?
    Because I've this query from it.

     

    However after all repairs on it, It's still executing for about 5 minutes without any result...

    If you could tell me how can I find this query (where it's used in Clarity) it would be helpful..

     

    Thanks a lot

     

    Matej



  • 17.  Re: Performance issue - Timesheets not available

    Posted 04-29-2015 05:00 AM

    Hi All,

     

    We've tried to adjust above query which caused most performance problems, but got stuck in /*+ materialize */ function which didn't work in .xbl fil  so we have to got back to OOTB solution.

     

    We've identified that after rebuild indexes (PRTASK_WBS1 ,  PRTASK_PARENT, PRTASK_GANTT - advised by CA supporter) Timesheet performance was much better.

    The question is how this could help and why? It means that after rebuilded it worked, but when opened new Time periods, performance got worse because of not indexed.

    Furthermore, rebuilding indexes is not a permanent solution.


    Any advice regarding this is highly appreciated. Thanks


    Matej



  • 18.  Re: Performance issue - Timesheets not available

    Posted 04-29-2015 06:19 AM

    Hi Matej,

     

    Database maintenance is the key for the any app for work, that's the reason gathering database stats and rebuilding the index definitely helps.

     

    Ref: https://blogs.oracle.com/sysdba/entry/when_to_rebuild_index

     

    Also you can find this information while reading through the AWR report which tables are getting more fragmented and necessary maintenance can be done. Hope this help Matej.

     

    Regards

    Suman Pramanik



  • 19.  Re: Performance issue - Timesheets not available

    Posted 04-29-2015 07:37 AM

    Hi Suman,

     

    Thanks for this valuable information. However I'm not sure what includes "database stats". Could you please clarify it more deeply?

    In the meantime I've asked DB admins if they know something about it.

     

    Is there any possibility that automatically indexing is switched off on DB level? It seems like that because we've not had this issue before (many years) and this automatization

    should ensure that new records are indexed as well. Thanks a lot

     

    Matej



  • 20.  Re: Performance issue - Timesheets not available

    Posted 04-29-2015 07:54 AM

    "database stats" is database statistics ; the database itself is VERY CLEVER, it uses these statistics about itself (number of rows in tables, distribution of data values in the tables etc) in order to work out the most efficient way of accessing the data. However the stats themselves need to be "gathered" every now and then so that the best information is available to the database's query-optimiser. In Oracle there is a job that should be run periodically to recalculate statistics (but one of the out-of-the-box Clarity admin jobs will run that database task for you though, so Clarity tries to self-manage all this). But Suman is absolutley right ; the database is the most complicated bit of technology in your application stack and it needs maintenance to make sure it is running optimally - its easy to cripple the database and thus cripple your application. Not recalculating statistics will, over time, degrade you database performance, often in sudden and unexpected ways.

     

    If you have an index on a table it will update as data gets put into the table - this is all normal behaviour. However indexes (and data generally) can get fragmented over time (split over different areas of a disk - just like de-fragmenting your PC hard drive) and this can affect the performance of indexes. So you can think of rebuilding indexes as being like de-fraging your hard drive.

     

    (and if your DB admins don't know anything about "database-stats", I would question exactly what they did know about! )



  • 21.  Re: Performance issue - Timesheets not available

    Posted 04-29-2015 08:00 AM

    If your implementation is the one that I think it is, you must have a lot of old timesheets. Do you ever delete old timeperiods?



  • 22.  Re: Performance issue - Timesheets not available

    Posted 04-29-2015 07:48 AM

    Hi Matej,

     

    The index es are there and it is supposed to do the indexing job but sometimes due to the volume of data and to control the fragmentation level you need to alter the index so that the execution plan is back to normal. Here are the information on database stats

     

    https://docs.oracle.com/database/121/TGDBA/gather_stats.htm#TGDBA168

     

    Now if the execution plan is ok then the query execution will be fine and there won't be delay but again to maintain the database there are some tasks needs to be done regularly to keep healthy one i.t. stats gathering, checking I/O thorugh put.

     

    I will talk about it more on tomorrow performance tuning session, please try attending the same

     

    Regards

    Suman Pramanik



  • 23.  Re: Performance issue - Timesheets not available

    Posted 04-29-2015 08:13 AM

    Hi Dave, Suman,

     

    OK, I've tried to understand all this new information. As I understood, it's completely work for DB admins. I hope they doing their jobs well because we haven;t problems like that

    and I cannot control them as I don't know pretty much about DBA's work...

     

    I meant if there are some other actions which need to be keep when rebuilding indexes. If only DB statistics I will let know DB admins, but suppose they are aware about it.

    Only recommendations they have regarding this issue is to optimize query which is behind TS access ( I've posted it above). We've tried to adjust this query which caused most performance problems, but got stuck in /*+ materialize */ function which didn't work in .xbl fil so we have to got back to OOTB solution.

     

    Also as I said we have done rebuilding indexes in past 2 weeks 2 times but it's not permanent solution and can't do it like this.

     

    @Andrew: Hmm, I think a very good hint. You are right - we've never deleted any Timesheets and using Clarity from 2008...so many old TS are there. What Suman, Dave you think about this thought from Andrew? Thanks

     

    Matej



  • 24.  Re: Performance issue - Timesheets not available

    Posted 04-29-2015 08:14 AM

    and you have a LOT of users as well, as I recall.



  • 25.  Re: Performance issue - Timesheets not available

    Posted 04-29-2015 08:18 AM

    I know of a few instances with lots of users (thousands) and lots (pushing 10 years) of timeperiods - and am not aware of specific issues with that.



  • 26.  Re: Performance issue - Timesheets not available

    Posted 04-29-2015 08:23 AM

    I think they have >10k. 



  • 27.  Re: Performance issue - Timesheets not available

    Posted 04-29-2015 08:21 AM

    Hi Matej,

     

    I am with Andrew, you need to do clean up not only time period but audits,process instance, notifications etc.

     

    Now the reason altering indexing didn't work is after you alter any database table you need to compute statistics and this is inevitable and DBA's should be aware of.

     

    And what you tried is not a very good solution solution, you were trying to pass hint which did not work but hints have very occurred performance when the database versions are upgraded.

     

    So any of the query you write it's ok to allow oracle to choose plan rather tweaking and using hint.

     

    I am not against using hint but it should be used judiciously.

     

    Hope after the table stats are gathered you will have better performance.

     

    Regards

    Suman Pramanik



  • 28.  Re: Performance issue - Timesheets not available

    Posted 04-29-2015 08:41 AM

    Hi All,

     

    We have about 3300 users... so Andrew, I have to disappoint you

    I've checked old TS and it begins from March 2008. For Time scale March 2008 - December 2012 we have about 322k TS.

     

    Could you please provide me a few steps how can we cleanup through frontend all these old TS?

     

    We have scheduled job for cleanup Job logs - every job older than 10 days is deleted from log.

    I've checked notifications, we have about 10K notifications last used in 2008 so I assume I can delete them

    Processes are deleted as well and we have now less than 50 processes running. 

     

    Finally as you said, before rebuilding Indexes it has to be gathered DB statistics, right? Thanks a lot

     

    Matej



  • 29.  Re: Performance issue - Timesheets not available

    Posted 04-29-2015 08:48 AM

    Hi Matej,

     

    You need to go under administration -> Time period and start closing and deactivating those.

     

    Second once you rebuild the index after that gather table level statistics and then run oracle table analyze job probably every night and one a month gather system level statistics.

     

    Looking forward to hear back.

     

    Regards

    Suman Pramanik



  • 30.  Re: Performance issue - Timesheets not available

    Posted 04-29-2015 08:59 AM

    Hi Suman,

     

    So many manual deactivations right?

     

    I've just send DB admins question regarding DB statistics, so we will see.

    Oracle table analyze job is needed to run immediately after rebuilding indexes? We have scheduled it to run every Sunday. Thanks

     

    This system level statistics you also mentioned can be gathered on Clarity level? What is it about? Thanks for all answers...

     

    Matej



  • 31.  Re: Performance issue - Timesheets not available

    Posted 04-29-2015 09:45 AM

    Hi Matej,

     

    Yes unfortunately you need to do manually as DB updates is a big no. The job Oracle table  collect the stats at schema level however the reason I asked to do at table stats as it would be quick as you have huge amount of data on timesheet table.Check the clb_notification table also.

     

    Regards

    Suman Pramanik



  • 32.  Re: Performance issue - Timesheets not available

    Posted 04-29-2015 08:58 AM

    One instance I have has >3000 users, all doing weekly timesheets back to 2005, no specific timesheet-related performance issues (and we have never purged timesheets/timeperiods). Currently 2.2M records on the timesheet table, 5M timeentry records*.

     

    I'm not saying that this would not help, just that I think there would be other database/housekeeping related issues that would be more pressing than purging timeperiods.

     

    (* - The raw numbers here don't mean much, I could have 2M timesheets in a system sized to cope with 10M just fine, you could have 100k timesheets in a system sized to cope with 50k and the system is grinding to a halt - the key here is how well your database is sized/optimised to cope with it all - there are lots of discussions on here about "housekeeping" that would discuss this (and the webcast tomorrow of course )



  • 33.  Re: Performance issue - Timesheets not available

    Posted 04-29-2015 09:08 AM

    Hi Dave,

     

    Hmm, interesting and we would like to also do things like you

     

    Hope our DB admins can somehow think up about this issue because they didn't contribute so much work there so far...

     

    Nevertheless, I'd like to note that respective part of the SQL code which caused most problems retrieves structure of tasks form projects.

    As on some project there are many tasks and also structure could be more deep, it takes too long.

     

    On your machines this structure doesn't need to be so complicated, also not such an amount of users is accessing TS at the same time, So for you it’s working fine, but if heavily load on the system, it's not so good. That's why I suggest to optimize the query precisely this one part with Tasks structure (TASKMS sub-query). This is also confirmed by DB admins to optimize the query ...

     

    This is all what I know from them and what we could able to identify. But however asked form DB stats and will see. Thanks a lot fro your help, I will post any news asap.

     

    Matej



  • 34.  Re: Performance issue - Timesheets not available

    Posted 04-29-2015 09:17 AM

    ^ exactly ; so your problems sound related to projects/tasks rather than timesheets/timeperiods.

     

    Its not a simple thing this - lots of different factor to consider.

     

    You are doing the right thing though, engaging your DBAs to tell you where the problems are, then working out why you have the problems and what you can do (at database and/or application level) to fix them.



  • 35.  Re: Performance issue - Timesheets not available

    Posted 04-29-2015 09:56 AM

    Yes I hope that. Even indexes which are rebuilded are connected to task and wbs structures so I assume it is connected somehow to these...

     

    I've just got response from DB admins:

    They said they gather stats after rebuilding indexes (used command: alter index <INDEX NAME> rebuild online;)

     

    and for stats:

    exec dbms_stats.gather_index_stats(ownname => 'SCHEMA_NAME', 'INDEX_NAME', cascade => TRUE, estimate_percent=>100 );

    So what is the next action when I know DB statistics are done? Thanks


    Matej



  • 36.  Re: Performance issue - Timesheets not available

    Posted 04-29-2015 10:11 AM

    Hi Matej,

     

    That is all is required, I am providing some information which can help better maintenance.

     

     

    Check for tables which are fragmented (i.e Data is much lower then High Water Mark),so that we can target those segments (tables) for recreation (NB: Statistics must be gathered prior to executing this command):

     

    SELECT TABLE_NAME , (BLOCKS *8192 / 1024/1024 ) - (NUM_ROWS*AVG_ROW_LEN/1024/1024)
    "Data lower than HWM in MB"
    FROM DBA_TABLES WHERE UPPER(owner) =UPPER('&OWNER') order by 2 desc;

     

    o If more than 250MB is being wasted then investigate why this is and take remedial action
    o If we can reduce fragmentation levels then it will speed up full table scans and fast full index scan operations
    o Remedial action could be to execute the following:

     

    SELECT 'ALTER TABLE '||table_NAME||' ENABLE ROW MOVEMENT;'

    FROM DBA_tables

    WHERE OWNER = '<CLARITY_SCHEMA_NAME>'

    GO

     

     

    SELECT 'ALTER TABLE '||table_NAME||' SHRINK SPACE;'

    FROM DBA_tables

    WHERE OWNER = '<CLARITY_SCHEMA_NAME>'

     

     

    SELECT 'ALTER INDEX '||INDEX_NAME||' REBUILD COMPUTE STATISTICS PCTFREE 50;'

    FROM DBA_INDEXES

    WHERE OWNER like '<CLARITY_SCHEMA_NAME>' and

    index_name not like 'SYS%'

     

     

    exec DBMS_STATS.GATHER_SCHEMA_STATS (ownname => '<CLARITY_SCHEMA_NAME>', estimate_percent => 50, cascade => true, degree => 4, method_opt=> 'FOR ALL COLUMNS SIZE 1');


     

     

    This needs to be done to have the optimum performance.

     

    Please check how the performance goes.

     

    Regards

    Suman Pramanik



  • 37.  Re: Performance issue - Timesheets not available

    Posted 04-29-2015 11:12 AM

    Hi Suman,

     

    Thanks for sharing this info. I've just forwarder it to our DB admins. Will let you know.

     

    I'd like to point again at the beginning of this issue, which may lost in the meantime:

     

    AWR reports from DB admins shows us that some queries consumes most DB resources so then DB and whole Clarity got stuck (because they are still executing in background).

    The whole query is mentioned above in my post. The one part of this query (Sub-query Taskms) causes this. We've executed execution plan (which I can fully send to you) but can share a screen. As you can see it's a quite much Rows and Costs there, so we still think that the root cause of this whole issue is somehow connected to PRTASk table and

    PRTASK_WBS1 ,  PRTASK_PARENT, PRTASK_GANTT indexes as well. Thanks


    Execution_plan.JPG




  • 38.  Re: Performance issue - Timesheets not available

    Posted 04-29-2015 11:23 AM

    Hi Matej,

     

    I am not saying that it cannot be but with such a huge data we might need to add further index to do the optimization, however having said that even clean up is important. Also share what all indexes you have on PRTask table.


    Regards

    Suman Pramanik



  • 39.  Re: Performance issue - Timesheets not available

    Posted 04-30-2015 02:27 AM

    Hi Suman,

     

    OK, Then let me know how this new index can be set up.

    I went through indexes and we have all these these with PRTASK prefix: (but we've rebuilt only mentioned 3 PRTASK_WBS1 ,  PRTASK_PARENT, PRTASK_GANTT):

     

    PRTASK_indexes.JPG



  • 40.  Re: Performance issue - Timesheets not available

    Posted 04-30-2015 03:04 AM

    Hi Matej,

     

    If you go to the table definition of PRTASK you will find list of indexes so you need to rebuild all of them.

     

    Regards

    Suman Pramanik



  • 41.  Re: Performance issue - Timesheets not available

    Posted 04-30-2015 03:27 AM

    Hi Suman,

     

    I've rebuilt only those 3 indexes which were recommended from your colleague (Chandrani Tankala) so I did it.

    When I've looked over PRTASK table there are less indexes mapped on this:

     

    PRTASK_indexes.JPG

    So always when rebuilding indexes all these other have to be re-indexed as well? Thanks

     

    Matej



  • 42.  Re: Performance issue - Timesheets not available

    Posted 04-30-2015 05:30 AM

    Hi Suman,

     

    So all PRTASK indexes mentioned above were rebuilt. Execution SQL plan is now much better and also TS accessibility has improved.

    We know that indexing is a root-cause but why? Why it worked before when we didn't changed anything. I assume when new periods or TS will be created, this issue appear very soon again...

     

    Is there any automatic indexing option what we can check? Thanks

     

    Matej



  • 43.  Re: Performance issue - Timesheets not available

    Posted 04-30-2015 06:24 AM

    Hi Matej,

     

    I am glad to hear that it improved, as I earlier said when there are huge number of transaction there is always a need to gather stats, do the reindex or use row movement or shrink space. These are remedial action for the database and are required on the huge volume of transaction.

     

    Indexes are always automatic but sometime optimizer chooses a different plan and that's where the performance is bad so the DBA needs to take these actions to overcome.

     

    Hope this helps.

     

    Regards

    Suman Pramanik



  • 44.  Re: Performance issue - Timesheets not available

    Posted 04-30-2015 07:07 AM

    Hi Suman,

     

    But this only time limited workaround - not the solution. We've never had this issue bofer and not changed anything with indexes nor DB admins.

    Response from DBA's is following:

     

    There is no issue with the index rebuilding and stats gathering job.
    Please have look on below index rebuild job, the job running every weekend Saturday , 25th April last ran and completed successfully. Next run date 02-MAY.
    Log file attached, we can see all the indexes has been rebuild on date 25th April. And the statistics gathering job is running everyday with out any issue.


    But still for some reason there is something which causes that new TS/periods are not automatically indexed, only manually and then it's OK.
    Thanks

     

    Matej



  • 45.  Re: Performance issue - Timesheets not available

    Posted 04-30-2015 07:22 AM

    Hi Matej,

     

    As I mentioned before also database maintenance is the key and these are part of database maintenance. Yes you might not have run into the same and as the data grows you see the problem. Also housekeeping also plays a important part. We need to delete the old time sheets.

     

    Assuming the query is bad then even with indexing it shouldn't work but it works better.

     

    I am sorry I don't have any further recommendations apart from the one said in this thread.

     

    Regards

    Suman Pramanik



  • 46.  Re: Performance issue - Timesheets not available

    Posted 04-30-2015 08:10 AM

    matej256 wrote:

     

    But still for some reason there is something which causes that new TS/periods are not automatically indexed, only manually and then it's OK.

     

    This is not how the database works - when data is put into a table it is added to indexes automatically.

     

    Rebuilding indexes should only ever need to be done infrequently, and really only then for (very rare) corruption or possible "fragmentation" issues (as described earlier).

     

    Your DBAs are telling you that everything is OK as far as indexes/stats are on your database but you think you still have issues - what you need to do is isolate that query that is "running slow", then sit with your DBAs and get them to explain why it is running slow on "new data" compared to "old data" (or whatever you symptoms really are) and get them to explain what they can do about it.

     

    (i.e. its not really an application issue, its a database one)



  • 47.  Re: Performance issue - Timesheets not available