C13946271 / Komatsu / Taking long time to upload large structured assembly to update

[tseki]

It takes long time(more than or around 30 minutes) when uploading a large structured assembly.

Debugged MethodContextMonitor log and it shows as follows.

2018-01-08 12:42:30,787 WARN [ajp-bio-8010-exec-28] wt.method.MethodContextMonitor.contexts.servletRequest orgadmin - 2018-01-08 12:07:48.151 +0900, 2tokjl;jc24khlr;3040;xi7pkc;439131, -, -, 2tokjl;jc24khlr;3040;xi7pkc;439130, orgadmin, 10.4.14.87, -, -, /com.ptc.windchill.uwgm.soap.impl.uwgm.BinaryRequestHandler/getCacheDescriptor?trxid=511114632101899.7&SessionId=proe@10.4.14.87_2018-01-08T12:07:03.367+09:0&action_id=Upload_20180108120747_511114632101899&ReqNum=7+com.ptc.core.command.server.delegate.entity.PrepareEntityCommandDelegate+com.ptc.core.command.server.delegate.entity.NewEntityCommandDelegate, 0 , 117, 2082.1107081, 1, 3.403E-4, 0.4524029, 2082.6339633

It shows that JDBC process consumed 2082.11 seconds out of 2082.633 overall elapsed time.

Hence asked and received statspack report for the other day after retesting.(because they did not properly captured statspack on 8th Jan)

Also received sql execution plan for the worst 3 sqls which took over 800 - 1800 seconds.

CPU time for them are only around 20 seconds but I don't think execution plan has any problem.

Also buffer gets and physical reads seem very large.

From the Buffer Pool Advisory in statspack report, doubled buffer pool size would reduce the DB time to 1/3.

But I'm not sure this can be a real solution.

Could you please give me your insights on this performance problem?

Is there any advice we can ask the customer? Or could this be a code problem?

Statspack report and system configuration collector result(performance category, excluded PTC_logs and Third_Party) are here.

http://internal.ptc.com/salesforce/attachments/cases/13/94/62/71/C13946271_Logs.zip

It includes followings.

Event_Management_0x.jpg: Event management page for upload which shows the timestamps

sp_1_11.lst: Statspack report

SCC_Perf_20180112(folder) : SCC result which includes gather_info data

SQL_Report_20180115(folder): sql detail report including execution plan

Thanks in advance!

Tsutomu Seki

[SergeyEfimov]

How many parts in your assembly?

Do you have family table components?

[tseki]

Thanks Sergey.

I heard that it has over 15k parts required.

I'm not sure if there's family table component in it.

Is there any difference with family table? If so, I can ask the customer.

Thanks!

[tseki]

Please note only one assembly file is being uploaded.

Steps to reproduce they follow is

Open this assembly with NO_MODEL form workspace, then open it and edit some axis and save.

Then upload/checkin it.

[tseki]

Addition to the current information, they have provided the today's Statspack report.

It can be downloaded from  http://internal.ptc.com/salesforce/attachments/cases/13/94/62/71/StatspackReport_20180117.zip.

I've asked if the issue is reproduced today and awaiting customer.

Looks like similar SQL took long time in it.

[tseki · Accepted solution]

I'm sorry for keeping this old topic opened.

wt.pom.inClauseBindOptimizationThreshold could help to resolve the customer case.

This customer used to not provide the outcome😕

Closing this old topic for now.

Thanks,

Tsutomu