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:
  1. Shut down the cluster
  2. Truncate the current usage data
  3. Update the values in the table as below
  4. 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 3, 2017 - Updated May 5, 2017

Have a question? Get answers now.

Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.