RULEUSAGESNAPSHOT query takes more than 20 seconds
Pega RuleUsageSnapshot query takes more than 20 seconds.
Database operation took more than the threshold of 300 ms: 20,602 ms SQL: delete from DATA.pr4_log_rule_usage_details where pxObjClass = 'Log-RuleUsage-Details' and pxSystemNode = ? and pyLabel = ?
Steps to Reproduce
A defect or configuration issue in the operating environment. Explain plan for the delete statement shows it is using table scan and that's why the query takes upwards of 20 seconds.
The cardinality of the three columns in where condition is very low and thus an index won't help too much.
For pxObjClass & pyLabel, only one distinct value; For pxSystemNode, 8 distinct values.
Since the query is executed only once daily and that too during off hours at 1am, the execution time is acceptable and doesn't cause any lasting performance impact on the application. When appropriate, create the necessary index if that avoids a table scan.