Question

Frequent PEGA0005 and PEGA0001 alerts in production

Frequent PEGA0001 and PEGA0005 alerts seen. Here is the alert log:

2019-10-10 04:39:49,538 GMT*8*PEGA0005*8772*500*1fef4948d56811eeb526d973abada0e7*NA*NA*H213133125E2F6AA8102D085910D5BFAE*sneha*ENT-TEST-Work*TEST:01.01.01*1aa0d871dbadcb4ca20033e0ee9d9444*Y*20*H213133125E2F6AA8102D085910D5BFAE*8155*http-10.138.142.69:8444-6*PS1__TABTHREAD1*com.pega.pegarules.data.internal.access.DatabasePreparedStatementImpl*www-TEST-cm-prd.lb.macbank|10.137.249.26*Activity=@baseclass.doUIAction*Rule-Obj-Activity:ApplicationSetup*RULE-OBJ-REPORT-DEFINITION PXRETRIEVEREPORTDATA #20160804T094403.091 GMT Step: 34 Circum: 0******pxRDBIOElapsed=8.78;pxDeclExprCtxFreeUseCount=2;pxRDBIOCount=1;pxRunStreamCount=4;pxTotalReqCPU=0.14;pxRunModelCount=7;pxRunWhenCount=100;pxRulesExecuted=168;pxOtherCount=9;pxDBInputBytes=2,376;pxTotalReqTime=8.93;pxActivityCount=31;pxAlertCount=1;pxOtherFromCacheCount=14;pxDeclarativeRulesInvokedCount=4;pxInteractions=1;pxLegacyRuleAPIUsedCount=1;pxRuleCount=11;pxInputBytes=706;pxDeclarativeRulesInvokedElapsed=0.00;pxRulesUsed=1,315;pxRuleFromCacheCount=11;pxOtherIOElapsed=0.01;pxTrackedPropertyChangesCount=19;pxOtherIOCount=2;*Embed-ReportContent*NA*Embed-ReportContent*NA*ReportingIndex-TEST-ExceptionAllocatedExceptions;doActivity Rule-Obj-Report-Definition;Java;RULE-OBJ-ACTIVITY RULE-OBJ-REPORT-DEFINITION PXRETRIEVEREPORTDATA #20160804T094403.091 GMT Step: 34 Circum: 0;doActivity Rule-Obj-Activity:pxRetrieveReportData;12 additional frames in stack;*ExceptionType=;VerificationStatus=;UniqueID=;CounterpartyAVOX=;Leg2CCY=;Buy_Sell=;CaseID=;UTI=;NotionalPrice=;ImportID=;ReportingParty=;Party1transactionID=;UPI=;PriorUniqueID=;StartDate=20191009T043940.635 GMT;ExoticExpiryDate=;CounterpartyDTCC=;Leg1NA=;ID=;Fwd_FixedPrice=;Referenceentity=;Assignee=;TradeParty2ID=;BusinessGroup=;pyReportClass=Index-TEST-Exception;OptionEffectiveDate=;ExerciseType=;Jurisdiction=;CollateralPortCode=;TradeP2Name=;PremReciever=;CaseStatus=;pyReportParamPageName=paramPageALLExceptions;WarningReason=;AssetClass=;ULInstrument=;Error_Reason_WarningCode=;Leg1TNQ=;Collateralized=;Datacentre=;SecurityType=;Fwd_FX_ValueDate=;DealNumber=;CreditByEquity=;ValuationStatus=;CopyAlways=true;StatementDate=;Party2transactionID=;Source=;ReferenceEntityRIC=;OpComID=;Leg2NA=;TradeDate=;OptionExpiryDate=;OptionType=;CounterpartyCCN=;CounterpartyLEI=;Leg1CCY=;TradeParty1ID=;DPAmount=;Leg2TNQ=;TerminationDate=;CPShortName=;pyRunType=FullResolve;pyPageName=ALLExceptions;retainUnusedJoins=;USI=;MsgType=;EndDate=20191010T043940.635 GMT;MTSDB=;Delegation=;FloatFixedR2=;OpCurrency=;pyReportName=AllocatedExceptions;SellerParty=;pyForEachCount=72;EffectiveDate=;OptionStyle=;FloatFixedR1=;*Database operation took more than the threshold of 500 ms: 8,772 ms SQL: SELECT DISTINCT CASE WHEN "PC0".ID IS NOT NULL THEN CAST ( SUBSTRING ("PC0".ID, CHARINDEX ('-', "PC0".ID) +1 ,LEN ("PC0".ID) ) AS INTEGER ) END AS "pyIntegerValue(1)" , "PC0".ID AS "ID" , "PC0".CaseID AS "CaseID" , "Case".CaseName AS "CaseName" , "PC0".TimeStamp AS "TimeStamp" , "PC0".ExceptionType AS "ExceptionType" , "PC0".MsgType AS "MsgType" , "PC0".AssetClass AS "AssetClass" , "PC0".DealNumber AS "DealNumber" , "PC0".UniqueID AS "UniqueID" , "PC0".ReportingObligation AS "ReportingObligation" , "PC0".Party1ReportObl AS "Party1ReportObl" , "PC0".Party2ReportObl AS "Party2ReportObl" , "PC0".Error_Reason_WarningCode AS "Error_Reason_WarningCode" , "PC0".WarningReason AS "WarningReason" , "PC0".StatementDate AS "StatementDate" , "PC0".Source AS "Source" , "PC0".CollateralPortfolioCode AS "CollateralPortfolioCode" , "PC0".Collateralized AS "Collateralized" , "PC0".UTI AS "UTI" , "PC0".USI AS "USI" , "PC0".Party1transactionID AS "Party1transactionID" , "PC0".Party2transactionID AS "Party2transactionID" , "PC0".PriorUniqueId AS "PriorUniqueId" , "PC0".ReportingParty AS "ReportingParty" , "PC0".ReconciliationDataCenter AS "ReconciliationDataCenter" , "PC0".UPI AS "UPI" , "PC0".TradeParty1ID AS "TradeParty1ID" , "PC0".TradeParty2ID AS "TradeParty2ID" , "PC0".TradeDate AS "TradeDate" , "PC0".EffectiveDate AS "EffectiveDate" , "PC0".OptionEffectiveDate AS "OptionEffectiveDate" , "PC0".TerminationDate AS "TerminationDate" , "PC0".OptionExpiryDate AS "OptionExpiryDate" , "PC0".ExoticExpiryDate AS "ExoticExpiryDate" , "PC0".Fwd_FX_ValueDate AS "Fwd_FX_ValueDate" , "PC0".NotionalPrice AS "NotionalPrice" , "PC0".Leg1CCY AS "Leg1CCY" , "PC0".Leg1NotionalAmount AS "Leg1NotionalAmount" , "PC0".Leg1TotalNotionalQuantity AS "Leg1TotalNotionalQuantity" , "PC0".Floating_FixedRate1 AS "Floating_FixedRate1" , "PC0".Fwd_FixedPrice AS "Fwd_FixedPrice" , "PC0".Leg2CCY AS "Leg2CCY" , "PC0".Leg2NotionalAmount AS "Leg2NotionalAmount" , "PC0".Leg2TotalNotionalQuantity AS "Leg2TotalNotionalQuantity" , "PC0".Floating_FixedRate2 AS "Floating_FixedRate2" , "PC0".VerificationStatus AS "VerificationStatus" , "PC0".UnderlyingInstrument AS "UnderlyingInstrument" , "PC0".ReferenceEntity AS "ReferenceEntity" , "PC0".ReferenceEntityRIC AS "ReferenceEntityRIC" , "PC0".CreditByEquity AS "CreditByEquity" , "PC0".SecurityType AS "SecurityType" , "PC0".SellerParty AS "SellerParty" , "PC0".Buy_Sell AS "Buy_Sell" , "PC0".OptionStyle AS "OptionStyle" , "PC0".OptionType AS "OptionType" , "PC0".OptionCurrency AS "OptionCurrency" , "PC0".DigitalPayoutAmount AS "DigitalPayoutAmount" , "PC0".OptionCommodityID AS "OptionCommodityID" , "PC0".ExerciseType AS "ExerciseType" , "PC0".PremiumReceiver AS "PremiumReceiver" , "PC0".ValuationStatus AS "ValuationStatus" , "PC0".CounterpartyShortName AS "CounterpartyShortName" , "PC0".TradeParty2Name AS "TradeParty2Name" , "PC0".CounterpartyLEI AS "CounterpartyLEI" , "PC0".CounterpartyAVOX AS "CounterpartyAVOX" , "PC0".CounterpartyDTCC AS "CounterpartyDTCC" , "PC0".CounterpartyCCN AS "CounterpartyCCN" , "PC0".Delegation AS "Delegation" , "PC0".MTSDatabase AS "MTSDatabase" , "PC0".pxInsIndexedKey AS "pxInsIndexedKey" , "PC0".pxCreateDateTime AS "pxCreateDateTime" , "PC0".CaseInsKey AS "CaseInsKey" , "PC0".Assignee AS "Assignee" , "PC0".UniqueExceptionID AS "UniqueExceptionID" FROM data.pr_Index_TEST_Exception "PC0" INNER JOIN data.pc_TEST_Work "Case" ON ( ( "PC0".CaseInsKey = "Case".pzInsKey ) AND "Case".pxObjClass = ? AND "PC0".pxObjClass = ? ) INNER JOIN data.pr_index_Jurisdictions "JU" ON ( ( "JU".pxInsIndexedKey = "PC0".pxInsIndexedKey ) AND "JU".pxObjClass = ? AND "PC0".pxObjClass = ? ) WHERE ( ( "PC0".CaseID IS NOT NULL ) AND "PC0".TimeStamp >= ? AND "PC0".TimeStamp <= ? ) ORDER BY 10 DESC*

2019-10-10 04:39:50,257 GMT*8*PEGA0001*9643*3000*1fef4948d56811eeb526d973abada0e7*NA*NA*H213133125E2F6AA8102D085910D5BFAE*sneha*ENT-TEST-Work*TEST:01.01.01*1aa0d871dbadcb4ca20033e0ee9d9444*Y*20*H213133125E2F6AA8102D085910D5BFAE*8156*http-10.138.142.69:8444-6*PS1__TABTHREAD1*com.pega.pegarules.session.internal.engineinterface.service.HttpAPI*www-TEST-cm-prd.lb.macbank|10.137.249.26*Activity=@baseclass.doUIAction*Rule-Obj-Activity:ApplicationSetup*@BASECLASS DOUIACTION #20151218T095648.007 GMT Step: 36 Circum: 0******pxRDBIOElapsed=8.79;pxDBOpExceedingThresholdCount=1;pxDeclExprCtxFreeUseCount=2;pxRDBIOCount=1;pxRunStreamCount=4;pxTotalReqCPU=0.85;pxRunModelCount=7;pxOutputBytes=1,307,744;pxRunWhenCount=2,432;pxRulesExecuted=2,511;pxOtherCount=9;pxDBInputBytes=2,376;pxTotalReqTime=9.64;pxActivityCount=31;pxAlertCount=2;pxOtherFromCacheCount=75;pxDeclarativeRulesInvokedCount=4;pxInteractions=1;pxLegacyRuleAPIUsedCount=1;pxRuleCount=72;pxInputBytes=706;pxDeclarativeRulesInvokedElapsed=0.00;pxRuleIOElapsed=0.00;pxRulesUsed=3,715;pxRuleFromCacheCount=72;pxOtherIOElapsed=0.01;pxTrackedPropertyChangesCount=19;pxOtherIOCount=2;*NA*NA*NA*NA*NA*contentID=6dce0fe0-0bf3-4429-871b-fc7886d4a67b;className=ENT-TEST-Work-Case-Exception;readOnly=false;pyDataTransformParams=[];tabIndex=2;label=AllocatedExceptions;preActivityParams=FilterType:AllocatedExceptions,FilterValuesPage:,FilterValuesClass:,DTReturnType:;portalThreadName=OpenPortal;prevContentID=88d0292e-17d9-4ddd-9c1a-7e474fd0068c;portalName=Manager;prevRecordkey=Data-Portal|pyDashboard7|undefined;pyActivity=@baseclass.doUIAction;harnessName=AllExceptions;action=display;model=pyDataTransformEQUALSSetDefValForAllocatedExcpns;preActivity=LoadCustomFilterDetails;dynamicContainerID=f9e64470-c4fa-4e63-b814-0a50f75d0647;primaryPageClass=Index-TEST-Exception;*HTTP interaction has exceeded the elapsed time alert threshold of 3000 ms: 9644 ms.*

Comments

Keep up to date on this post and subscribe to comments

October 10, 2019 - 7:20am

Please provide any details on cause of these alerts and resolution.

October 10, 2019 - 7:35am

Hello, 

Have you asked your DBA about this long query. Any advices on how to increase the speed of this request?

October 10, 2019 - 11:18pm

It is only a select query with Where condition as: WHERE ( ( "PC0".CaseID IS NOT NULL ) AND "PC0".TimeStamp >= ? AND "PC0".TimeStamp <= ? ) ORDER BY 10 DESC* only. There are 2 table joins but those are index tables. When the query is run individually in DB directly, it doesn't take more than .0 to 1 second. However through Pega report, it is giving alert PEGA0001 and PEGA0005 in every interaction.

Pega
October 24, 2019 - 8:13am

Hi,

You will have to fine-tune your database. Maybe creating indexes help.