Clarity

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

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

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

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

NJ

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

Check the below 2 for the task on the project -

PRTASK.PRWBSSEQUENCE

PRTASK.PRWBSLEVEL

NJ

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

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

NJ

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

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

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

Good catch

NJ

Sorry guys, my fault

So the good columns are:

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:

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

Can you share the concat function ?

NJ

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

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

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

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

"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! )

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?

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

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

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

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.

I think they have >10k. 

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

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

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

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 )

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

^ 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.

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

Hi Matej,

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

This needs to be done to have the optimum performance.

Please check how the performance goes.

Regards

Suman Pramanik

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

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

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):

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

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:

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

Matej

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

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

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

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

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)

Hi Dave,

We've isolated this query at the very beginning of this issue:

(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 = 5884803    And Te2.Prtimesheetid = 6242774 

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))

This part (Sub-query) of the whole query(which is listed early above) caused performance problems. It's due to fact it's every-time executed when comparing And

conditions (many executions on same PRtask table). We've done SQL Execution plan and there were many many executions, hash joins, full scans on PRTask table and so on...

(However, after rebuilt indexes, it dramatically went down).

We've tried to adjust this query with WITH clause at the beggining of the whole query to not execute it so many times. Performance was really much better, but then appeared some users with TS for which this WITH clause got Null value, so it finished with Error. So we went back to OOTB solution. Furthermore, our DB admins also identified this query as not so well-optimized and

causing this performance issue (when user clicked on his TS, this query was still executing in the background so the whole Clarity was slow).

About indexes, we're also familiar with your opinion it's needed rarely, when changing table structure or indexes are corrupted. That's why this issue is strange when rebuilding of indexes helped us,

but we don;t know why...

Thanks

Matej

Hi Matej,

Rebuilding index does help and if the query is not optimized it will never work with rebuilding index. Benefits of rebuilding index are below

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

Regards

Suman Pramanik

Hi Suman,

Yes I know about this blog, but actually I still don't know why it's helped and we had to done it several times....

Anyway, in the meantime our DB admins implemented AMM (Automatic Memory Management) function on our DB,

and spread PGA memory to 2 GB. After these actions it's running fine and we are still monitoring it.

Thanks for your help so far.

Matej

Hi Matej,

From what i understood the rebuilding of indexing was done not for all the indexes but to few and i asked to do for all the indexes and I also suggested to do a ENABLE ROW MOVEMENT & SHRINK SPACE.

That also should have helped. I am happy to hear that the performance is good and I also did recommend  AMM (Automatic Memory Management) in my performance tuning webinar also.

Have a great weekend.

Regards

Suman Pramanik

Hi Suman,

OK

Is that command ENABLE ROW MOVEMENT & SHRINK SPACE. a part of AMM or not?

If not maybe we can do it also.

Thanks

Matej

Hi Matej,

Not its part of AMM, these are extreme situation where we need to perform these.

Regards

Suman Pramanik

Hi Suman, so after a while I'm back there.

As our monthly closing is coming (till 2 PM CET today - it's time to when users have to submit their Timesheets), we have again issues with Timesheets, the same as before.

In recent 2 weeks it was, suprisingly working fine, today it's broken. My only assumption is that many users is working with TS, but it is really the case for Clarity?

DB has been optimized - implemented AMM and spread PGA memory from 600MB to 3GB. And also rebuilt all PRTASK indexes. but as I said before, rebuilding is not for me solution but only temporary workaround which I hoped will keep longer but unfortunately not.

From AWR database report I can see that most of DB resources and DB time (80-90%) is used for opening Timesheets.

If you have some new suggestions for me regarding this, please let me know. Thanks

Hi Matej,

What is the count of CLB_NOTIFICATIONS table?

Regards

Suman Pramanik

Hi Suman,

It's about 40000:

We have in the meantime found out that there are quite a lot of user sessions in DB which are blocking each other. Means once one finishes, the next comes.

Is there any best practice when some kind of closing (Timehseets e.g.) is coming then a limited count of users can work with TS? Because I can imagine when

e.g. 20-30 users at once are accessing TS (with the query above, which is not fast we think), then populating, saving and so on in the same time....

EDIT: we've performed restart of Clarity to clean-up user sessions.

But however they stayed the same after restart! Is it normal?.

Did not restart erase the user session? I thought it did. I use table cmn_sessions table and this query I've found on CA forum:

Select

cmn_sec_users.last_name, cmn_sec_users.first_name Name,

cmn_sec_users.user_name Userid,

Count(user_id) Number_sessions

from

cmn_sessions,

cmn_sec_users

where

cmn_sec_users.id = cmn_sessions.user_id

Group By Cmn_Sec_Users.Last_Name, Cmn_Sec_Users.First_Name, Cmn_Sec_Users.User_Name

Order By Number_Sessions Desc

Currently we have about 1500 user sessions (have 3000 users in Clarity), and Process admin contains about 200 sessions.

Thanks a lot

Matej

The implementation I was thinking of dated back to at least 2003 and had something like 10k users then. It must be a different one.