Support Article
sppr_aggregate_usage taking long time to execute
SA-37405
Summary
Getting the following alert. Stored procedure took 91 seconds.
2017-03-22 00:02:30,286 GMT*6*PEGA0005*91944*2000*03a07a1dfe9fe0306238a988dc2bc4de*B832FFD2BC28BD90D084B412A9F152C27*none*PegaSample*null*5c89200298f259493e14772fd3b7ba22*N*86*4853*java.lang.ThreadGroup[name=PRPCWorkManager: your_node,maxpri=10]*STANDARD*com.pega.pegarules.data.internal.access.DatabasePreparedStatementImpl*NA*(License Daemon)pxCommitCount=1;pxOtherIOCount=16;pxCommitRowCount=16;pxTotalReqCPU=0.02;pxRDBRowWithoutStreamCount=1;pxInteractions=1;pxConnectElapsed=91.98;pxAlertCount=1;pxConnectCount=2;pxCommitElapsed=0.27;pxTotalReqTime=92.25;pxRDBWithoutStreamCount=1;*NA*NA*NA*NA*NA*NA*Database operation took more than the threshold of 2000 ms: 91,944 ms SQL: {call sppr_aggregate_usage}*
select * from <schema>.pr_license_parameters;
PYNAME PYUSERTYPE PYVALUE
-------------------------------- -------------------------------- --------------------------------
pzSignature SYSTEM 5dd7a7bb51768b7f9ef2ae5c08050cb0
pyMeasurementPeriod SYSTEM M
pyTimeZoneName SYSTEM GMT
pyFirstDayOfWeek SYSTEM 1
pyRuleUserForcesRegular SYSTEM 1
pyRetainHourly SYSTEM 200
pyRetainDaily SYSTEM 366
pyMaxHoursPerPeriod sporadic 0
pyMinHoursPerDay occasional 0
pyMaxHoursPerDay occasional 3
pyMinHoursPerPeriod occasional 0
pyMaxHoursPerPeriod occasional 9000
pyMinHoursPerDay regular 4
pyMaxHoursPerDay regular 25
pyMinHoursPerPeriod regular 9001
15 rows selected.
select count(*) from <schema>.pr_daily_usage;
COUNT(*)
----------------
788824
1 row selected.
select count(*) from <schema>.pr_hourly_usage;
COUNT(*)
----------------
4216403
1 row selected.
Elapsed: 00:00:04.03
select count(*) from <schema>.pr_usage_summary;
COUNT(*)
----------------
68
select count(*) from <schema>.pr_license_parameters;
COUNT(*)
----------------
15
1 row selected.
Error Messages
Not Applicable
Steps to Reproduce
Not Applcable
Root Cause
A defect or configuration issue in the operating environment
The retention of hourly and daily usage is high enough to cause this alert when the data is groomed each day.
Resolution
Perform the following local-change:
- Shut down the cluster
- Truncate the current usage data
- Update the values in the table as below
- Restart the cluster.
truncate table data.pr_hourly_usage;
truncate table data.pr_daily_usage;
select * from data.pr_license_parameters where pyName in('pyRetainHourly', 'pyRetainDaily'); -- to view current settings
update data.pr_license_parameters set pyvalue = 50 where pyname = 'pyRetainHourly'; -- 50 is an example. You may set it differently if needed.
update data.pr_license_parameters set pyvalue = 91 where pyname = 'pyRetainDaily'; -- 91 is an example. You may set it differently if needed.
Published May 6, 2017 - Updated October 8, 2020
Have a question? Get answers now.
Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.