Question
Natwest
IN
Last activity: 25 Dec 2019 4:24 EST
Frequent PEGA0005 and PEGA0001 alerts in production
Frequent PEGA0001 and PEGA0005 alerts seen. Here is the alert log:
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- Proprietary information hidden:8444-6*PS1__TABTHREAD1*com.pega.pegarules.data.internal.access.DatabasePreparedStatementImpl*www-TEST-cm-prd.lb.macbank| Proprietary information hidden*[email protected]*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- Proprietary information hidden:8444-6*PS1__TABTHREAD1*com.pega.pegarules.session.internal.engineinterface.service.HttpAPI*www-TEST-cm-prd.lb.macbank| Proprietary information hidden*[email protected]*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;[email protected];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.*
Natwest
IN
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.
Pegasystems Inc.
IN
can you pls run the explain plan of the above query and see the cost for each operation..creating composite indexes for the where clause might help..also if it on oracle and then check whether this query comes in AWR report as well..
Pegasystems Inc.
IN
Hi Sneha,
I can see there are three join conditions & few filter conditions.It seems your RD contains lot of conditions.What's the size of below tables.
- pr_Index_TEST_Exception
- pc_TEST_Work
- pr_index_Jurisdictions
Did you run the same query with same bind values directly in DB?
Please Perform below steps & share result with me.
1.Create a new DSS :
Hi Sneha,
I can see there are three join conditions & few filter conditions.It seems your RD contains lot of conditions.What's the size of below tables.
- pr_Index_TEST_Exception
- pc_TEST_Work
- pr_index_Jurisdictions
Did you run the same query with same bind values directly in DB?
Please Perform below steps & share result with me.
1.Create a new DSS :
Pega-Engineas owning ruleset and prconfig/alerts/database/operationtimethreshold/suppressinserts/defaultin setting purpose and then finally value to false.
2.Restart system.
3.Run the report definition.
4.In alert logs you will find bind values along with pega0005 alert.
5.Form a complete query using these bind values.
6.Run it directly in database, Observe the execution time.Capture its query execution plan & verify Is it a full table scan or index scan.
7.If it is a full table scan then consult with your DBA & create appropriate index.If it is a index scan then your DB has scope of improvement you can try DB vaccum.
Thanks,
Abhinav