Support Article
Sql query performance issue generating scorecards
SA-19665
Summary
These queries perform poorly when generating scorecards for AES 7.1.7 on Pega 7.1.8
SELECT "PC0".PROBLEMCORRELATION AS "ProblemCorrelation", SUM("PC0".KPIVALUE) AS "pySummaryValue(1)" FROM PEGA.pegaam_alert "PC0" WHERE ( "PC0".PROBLEMCORRELATION IN (? , ? , ? , ? , ? , ? , ? , ? , ? ) AND "PC0".GENERATEDDATETIME > ? AND "PC0".GENERATEDDATETIME < ? ) AND "PC0".pxObjClass = ? GROUP BY "PC0".PROBLEMCORRELATION ORDER BY 1 ASC inserts: <y64_prod:Browser Time:4ef7b514751b6f3fdc125d46fed42a48> <y64_prod:Browser Time:6a397bf43e2d367b6ab69eefaa451040> <y64_prod:Browser Time:734f713b0f6836040dbb2a376576676> <y64_prod:Browser Time:a923e2c3cae9bd623ebf665e0bbde3b> <y64_prod:Browser Time:d224d685ac64fa7836d49f77b4912e9> <y64_prod:Browser Time:dc97fc16f074b38162aafdd274205927> <y64_prod:Browser Time:e8ad5ecee6b45d9e8ba192703f387f3> <y64_prod:Connect Total Time:7c7269abf2855c3529f84bb8933cc8ed> <y64_prod:DB Time:62573fb5f127cf6bda5199ec828c389> <2015-12-30 00:00:59.999> <2016-01-05 23:59:59.999> <PegaAES-Data-Alert> *
SELECT COUNT("PC0".MSGID) AS "pySummaryCount(1)" , COUNT(DISTINCT "PC0".FIRSTACTIVITY) AS "pySummaryCount(2)" FROM PEGA.pegaam_alert "PC0" WHERE ( CASE WHEN "PC0".GENERATEDDATETIME IS NOT NULL THEN "PC0".GENERATEDDATETIME+? END >= ? AND CASE WHEN "PC0".GENERATEDDATETIME IS NOT NULL THEN "PC0".GENERATEDDATETIME+? END <= ? ) AND "PC0".pxObjClass = ? inserts: <0.0> <2016-01-06 00:00:00.0> <0.0> <2016-01-06 00:00:00.998> <PegaAES-Data-Alert> *
Error Messages
2016-01-06 10:14:26,340 [ WebContainer : 30] [TABTHREAD0] [ AES:07.13] (l.access.ConnectionManagerImpl) ERROR your_url|your_ip cvome - Not returning connection 8 for database "pegadata" to the pool as it previously encountered the following error
User ID: cvome
Last SQL: SELECT "PC0".PROBLEMCORRELATION AS "ProblemCorrelation", SUM("PC0".KPIVALUE) AS "pySummaryValue(1)" FROM PEGA.pegaam_alert "PC0" WHERE ( "PC0".PROBLEMCORRELATION IN (? , ? , ? , ? , ? , ? , ? , ? , ? ) AND "PC0".GENERATEDDATETIME > ? AND "PC0".GENERATEDDATETIME < ? ) AND "PC0".pxObjClass = ? GROUP BY "PC0".PROBLEMCORRELATION ORDER BY 1 ASC
java.sql.SQLTimeoutException: ORA-01013: user requested cancel of current operation
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:884)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1167)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1281)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3584)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3685)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1376)
at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.pmiExecute(WSJdbcPreparedStatement.java:942)
at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.execute(WSJdbcPreparedStatement.java:618)
at com.pega.pegarules.data.internal.access.DatabasePreparedStatementImpl.execute(DatabasePreparedStatementImpl.java:581)
at com.pega.pegarules.data.internal.access.DatabaseImpl.list(DatabaseImpl.java:6139)
at com.pega.pegarules.data.internal.access.DatabaseImpl.list(DatabaseImpl.java:5916)
at com.pega.pegarules.data.internal.rd.queryexec.SqlQueryExecutor.executeQuery(SqlQueryExecutor.java:68)
at com.pega.pegarules.data.internal.rd.queryexec.SqlQueryExecutor.executeQuery(SqlQueryExecutor.java:43)
at com.pega.pegarules.data.internal.rd.SqlReportExecutor.executeQuery(SqlReportExecutor.java:284)
at com.pega.pegarules.data.internal.rd.SqlReportExecutor.executeMainReport(SqlReportExecutor.java:128)
at com.pega.pegarules.data.internal.rd.SqlReportExecutor.execute(SqlReportExecutor.java:90)
at com.pega.pegarules.data.internal.rd.SqlReportExecutor.execute(SqlReportExecutor.java:57)
at com.pega.pegarules.data.internal.PRDataProviderImpl.executeReport(PRDataProviderImpl.java:1229)
at com.pega.pegarules.session.internal.mgmt.Executable.executeReport(Executable.java:10787)
at com.pegarules.generated.definition.ra_action_urgencybasedkpis_a139b0efe304d4cb481c9263f3c962e1.perform(ra_action_urgencybasedkpis_a139b0efe304d4cb481c9263f3c962e1.java:50)
at com.pega.pegarules.session.internal.mgmt.Executable.doAction(Executable.java:3305)
at com.pegarules.generated.activity.ra_action_pxretrievereportdata_ea2365ec0714372ba5ea7e36c543cc9a.step30_circum0(ra_action_pxretrievereportdata_ea2365ec0714372ba5ea7e36c543cc9a.java:3411)
at com.pegarules.generated.activity.ra_action_pxretrievereportdata_ea2365ec0714372ba5ea7e36c543cc9a.perform(ra_action_pxretrievereportdata_ea2365ec0714372ba5ea7e36c543cc9a.java:637)
at com.pega.pegarules.session.internal.mgmt.Executable.doActivity(Executable.java:3500)
at com.pega.pegarules.session.internal.mgmt.Executable.invokeActivity(Executable.java:10521)
at com.pegarules.generated.activity.ra_action_getweeklyactiontop10byurgency_cf3071f6d35e5c4c68d057e56d530ddc.step5_circum0(ra_action_getweeklyactiontop10byurgency_cf3071f6d35e5c4c68d057e56d530ddc.java:741)
at com.pegarules.generated.activity.ra_action_getweeklyactiontop10byurgency_cf3071f6d35e5c4c68d057e56d530ddc.perform(ra_action_getweeklyactiontop10byurgency_cf3071f6d35e5c4c68d057e56d530ddc.java:161)
at com.pega.pegarules.session.internal.mgmt.Executable.doActivity(Executable.java:3500)
at com.pega.pegarules.session.internal.mgmt.Executable.invokeActivity(Executable.java:10521)
at com.pegarules.generated.activity.ra_action_getweeklyscorecarddata_404e8ad68c93ec3f8d5c16268c568818.step11_circum0(ra_action_getweeklyscorecarddata_404e8ad68c93ec3f8d5c16268c568818.java:1156)
at com.pegarules.generated.activity.ra_action_getweeklyscorecarddata_404e8ad68c93ec3f8d5c16268c568818.perform(ra_action_getweeklyscorecarddata_404e8ad68c93ec3f8d5c16268c568818.java:288)
at com.pega.pegarules.session.internal.mgmt.Executable.doActivity(Executable.java:3500)
at com.pega.pegarules.session.internal.mgmt.Executable.invokeActivity(Executable.java:10521)
at com.pegarules.generated.activity.ra_action_refreshscorecardtab_a8bfa455319b09c39559eb2fbfec439e.step12_circum0(ra_action_refreshscorecardtab_a8bfa455319b09c39559eb2fbfec439e.java:1225)
at com.pegarules.generated.activity.ra_action_refreshscorecardtab_a8bfa455319b09c39559eb2fbfec439e.perform(ra_action_refreshscorecardtab_a8bfa455319b09c39559eb2fbfec439e.java:267)
at com.pega.pegarules.session.internal.mgmt.Executable.doActivity(Executable.java:3500)
at com.pegarules.generated.html_section.ra_stream_aesdashboardinnercontainer_f086ad392fd38359269f026d1e6524f8.pzLayoutBodyWrapper_1(ra_stream_aesdashboardinnercontainer_f086ad392fd38359269f026d1e6524f8.java:882)
at com.pegarules.generated.html_section.ra_stream_aesdashboardinnercontainer_f086ad392fd38359269f026d1e6524f8.pzGroup_1(ra_stream_aesdashboardinnercontainer_f086ad392fd38359269f026d1e6524f8.java:1205)
at com.pegarules.generated.html_section.ra_stream_aesdashboardinnercontainer_f086ad392fd38359269f026d1e6524f8.pzSectionBody_1(ra_stream_aesdashboardinnercontainer_f086ad392fd38359269f026d1e6524f8.java:255)
at com.pegarules.generated.html_section.ra_stream_aesdashboardinnercontainer_f086ad392fd38359269f026d1e6524f8.execute(ra_stream_aesdashboardinnercontainer_f086ad392fd38359269f026d1e6524f8.java:100)
at com.pega.pegarules.session.internal.mgmt.Executable.getStream(Executable.java:4033)
at com.pega.pegarules.session.internal.mgmt.Executable.getStream(Executable.java:3861)
at com.pegarules.generated.html.ra_stream_reloadsection_001d5c66ba807400c0ecb484e9d2fe6f.execute(ra_stream_reloadsection_001d5c66ba807400c0ecb484e9d2fe6f.java:192)
at com.pega.pegarules.session.internal.mgmt.Executable.getStream(Executable.java:4033)
at com.pega.pegarules.session.internal.mgmt.Executable.getStream(Executable.java:3861)
at com.pega.pegarules.pub.runtime.AbstractActivity.showHtml(AbstractActivity.java:247)
at com.pegarules.generated.activity.ra_action_reloadsection_8d636d6ea3d6b9b680f77d0cd1f195dd.step8_circum0(ra_action_reloadsection_8d636d6ea3d6b9b680f77d0cd1f195dd.java:898)
at com.pegarules.generated.activity.ra_action_reloadsection_8d636d6ea3d6b9b680f77d0cd1f195dd.perform(ra_action_reloadsection_8d636d6ea3d6b9b680f77d0cd1f195dd.java:188)
at com.pega.pegarules.session.internal.mgmt.Executable.doActivity(Executable.java:3500)
at com.pega.pegarules.session.internal.mgmt.base.ThreadRunner.runActivitiesAlt(ThreadRunner.java:646)
at com.pega.pegarules.session.internal.mgmt.PRThreadImpl.runActivitiesAlt(PRThreadImpl.java:461)
at com.pega.pegarules.session.internal.engineinterface.service.HttpAPI.runActivities(HttpAPI.java:3322)
at com.pega.pegarules.session.external.engineinterface.service.EngineAPI.processRequestInner(EngineAPI.java:385)
at sun.reflect.GeneratedMethodAccessor66.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:37)
at java.lang.reflect.Method.invoke(Method.java:611)
at com.pega.pegarules.session.internal.PRSessionProviderImpl.performTargetActionWithLock(PRSessionProviderImpl.java:1270)
at com.pega.pegarules.session.internal.PRSessionProviderImpl.doWithRequestorLocked(PRSessionProviderImpl.java:1008)
at com.pega.pegarules.session.internal.PRSessionProviderImpl.doWithRequestorLocked(PRSessionProviderImpl.java:841)
at com.pega.pegarules.session.external.engineinterface.service.EngineAPI.processRequest(EngineAPI.java:331)
at com.pega.pegarules.session.internal.engineinterface.service.HttpAPI.invoke(HttpAPI.java:850)
at com.pega.pegarules.session.internal.engineinterface.etier.impl.EngineImpl._invokeEngine_privact(EngineImpl.java:315)
at com.pega.pegarules.session.internal.engineinterface.etier.impl.EngineImpl.invokeEngine(EngineImpl.java:263)
at com.pega.pegarules.session.internal.engineinterface.etier.ejb.EngineBean.invokeEngine(EngineBean.java:225)
at sun.reflect.GeneratedMethodAccessor50.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:37)
at java.lang.reflect.Method.invoke(Method.java:611)
at com.pega.pegarules.internal.bootstrap.PRBootstrap.invokeMethod(PRBootstrap.java:367)
at com.pega.pegarules.internal.bootstrap.PRBootstrap.invokeMethodPropagatingThrowable(PRBootstrap.java:408)
at com.pega.pegarules.boot.internal.extbridge.AppServerBridgeToPega.invokeMethodPropagatingThrowable(AppServerBridgeToPega.java:223)
at com.pega.pegarules.boot.internal.extbridge.AppServerBridgeToPega.invokeMethodPropagatingException(AppServerBridgeToPega.java:245)
at com.pega.pegarules.internal.etier.ejb.EngineBeanBoot.invokeEngine(EngineBeanBoot.java:168)
at com.pega.pegarules.internal.etier.interfaces.EJSLocalStatelessEngineBMT_f2439d86.invokeEngine(Unknown Source)
at com.pega.pegarules.priv.context.JNDIEnvironment.invokeEngineInner(JNDIEnvironment.java:278)
at com.pega.pegarules.priv.context.JNDIEnvironment.invokeEngine(JNDIEnvironment.java:223)
at com.pega.pegarules.web.impl.WebStandardImpl.makeEtierRequest(WebStandardImpl.java:574)
at com.pega.pegarules.web.impl.WebStandardImpl.doPost(WebStandardImpl.java:374)
at sun.reflect.GeneratedMethodAccessor63.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:37)
at java.lang.reflect.Method.invoke(Method.java:611)
at com.pega.pegarules.internal.bootstrap.PRBootstrap.invokeMethod(PRBootstrap.java:367)
at com.pega.pegarules.internal.bootstrap.PRBootstrap.invokeMethodPropagatingThrowable(PRBootstrap.java:408)
at com.pega.pegarules.boot.internal.extbridge.AppServerBridgeToPega.invokeMethodPropagatingThrowable(AppServerBridgeToPega.java:223)
at com.pega.pegarules.boot.internal.extbridge.AppServerBridgeToPega.invokeMethod(AppServerBridgeToPega.java:272)
at com.pega.pegarules.internal.web.servlet.WebStandardBoot.doPost(WebStandardBoot.java:121)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:738)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:831)
at com.ibm.ws.webcontainer.servlet.ServletWrapper.service(ServletWrapper.java:1694)
at com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:970)
at com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:508)
at com.ibm.ws.webcontainer.servlet.ServletWrapperImpl.handleRequest(ServletWrapperImpl.java:181)
at com.ibm.ws.webcontainer.servlet.CacheServletWrapper.handleRequest(CacheServletWrapper.java:91)
at com.ibm.ws.webcontainer.WebContainer.handleRequest(WebContainer.java:878)
at com.ibm.ws.webcontainer.WSWebContainer.handleRequest(WSWebContainer.java:1592)
at com.ibm.ws.webcontainer.channel.WCChannelLink.ready(WCChannelLink.java:191)
at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleDiscrimination(HttpInboundLink.java:454)
at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleNewRequest(HttpInboundLink.java:516)
at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.processRequest(HttpInboundLink.java:307)
at com.ibm.ws.http.channel.inbound.impl.HttpICLReadCallback.complete(HttpICLReadCallback.java:84)
at com.ibm.ws.ssl.channel.impl.SSLReadServiceContext$SSLReadCompletedCallback.complete(SSLReadServiceContext.java:1818)
at com.ibm.ws.tcp.channel.impl.AioReadCompletionListener.futureCompleted(AioReadCompletionListener.java:175)
at com.ibm.io.async.AbstractAsyncFuture.invokeCallback(AbstractAsyncFuture.java:217)
at com.ibm.io.async.AsyncChannelFuture.fireCompletionActions(AsyncChannelFuture.java:161)
at com.ibm.io.async.AsyncFuture.completed(AsyncFuture.java:138)
at com.ibm.io.async.ResultHandler.complete(ResultHandler.java:204)
at com.ibm.io.async.ResultHandler.runEventProcessingLoop(ResultHandler.java:775)
at com.ibm.io.async.ResultHandler$2.run(ResultHandler.java:905)
at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:1662)
Steps to Reproduce
Not Applicable
Root Cause
A defect in Pegasystems’ code or rules
Perfomance problems are caused by the generation of the scorecard
Resolution
Apply HFix-25910
Published February 14, 2016 - Updated December 2, 2021
Have a question? Get answers now.
Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.