Performance issue - Application takes more time to get work object details data

Hi ,

Application Report definition are takes more time to get the data from pega work and index tables, but when the same database query executed from SQL developer works fast.

Not sure, where the bottleneck.

Report definition using application connection id

when I run in SQL developer, using different id for db connection

Report definition are pointing to exposed column, not blob

Pega version: 7.2.1 , DB: oracle , server: web sphere


Keep up to date on this post and subscribe to comments

August 21, 2019 - 3:13am


Have you tried tracer or performance analyser to check where the time is spent?

August 21, 2019 - 5:05am

Please try DB Tracer to analyze the time taken (getting connection, executing query, etc.)
Based on the findings you can either narrow down the issue or get hints on what to do next.

Also check the Pega alert log to see if you have any alerts related to connection pooling.

August 21, 2019 - 10:19am
Response to MAHI302446

Tried DB trace, it shows high time in preparedStatement in SQL

August 21, 2019 - 3:29pm

When you executed the query using Oracle SQL developer, did you use the same attribute values in the where clause, as it was when you executed that report def from PRPC?

BTW, does it say 'PreparedStatement' or 'PreparedStatementQuery'?

August 22, 2019 - 12:25pm
Response to N.SenSharma

1. yes, same attribute element withe same set of where clause 

2. TB tracer shows - preparedStatement under operation column

August 22, 2019 - 5:57pm
Response to JohnPaulRaja,C

I was referring to the value used in where clause.

Can you please get the generated query, and update it with the actual values used in query/report def, and get the cost/explain plan of that query from your DBA or Oracle SQL developer?

August 26, 2019 - 10:27am
Response to N.SenSharma

Working with DBA, will update the status shortly