Question
Vodafone
IN
Last activity: 2 Jul 2019 11:16 EDT
query pr_sys_app_hierarchy_flat causing performance isssue
Team,
Our application uses pega 7.3.1 and below are the slow running queries observed in prod
query #1 : select 1 from pr_sys_app_hierarchy_flat - record count 647 -> taking 3 mins to run this query
Query #2 : on table PEGADATA.pr_perf_stats -> record count 12 millions - taking 3 mins to run this query
I followed the link https://community1.pega.com/community/product-support/question/long-running-queries-causing-system
wherein they have suggested to truncate cache tables . is this right practice to do this?
what should have caused the query2 too slower? any clue would be great help here.
***Edited by Moderator: Lochan to update platform capability tags***
Decision Consultant Solutions
GB
pr_sys_app_hierarchy_flat is the cache table and you can truncate it. Don't forget to take DB backup before doing any cache table truncations.
Vodafone
IN
we have facing challenging with sizing as we are using AWS-RDB -oracle. is it ok if i take backup of only pr_perf_stats table alone intead of entire DB?
Pegasystems Inc.
GB
Hi,
Sharing you the list of all cache table.
We always suggest to take DB backup before truncating any cache table. So that we have a backup to be restored if anything goes wrong in case.
Hi,
Sharing you the list of all cache table.
We always suggest to take DB backup before truncating any cache table. So that we have a backup to be restored if anything goes wrong in case.
TRUNCATE TABLE PR_SYS_APP_HIERARCHY_FLAT;
TRUNCATE TABLE PR_SYS_APP_RULESET_INDEX;
TRUNCATE TABLE PR_SYS_CLASS_ANCESTORS;
TRUNCATE TABLE PR_ASSEMBLEDCLASSES;
TRUNCATE TABLE PR4_RULE_SYSGEN;
TRUNCATE TABLE PR_SYS_APPCACHE_ENTRY;
TRUNCATE TABLE PR_SYS_APPCACHE_DEP;
TRUNCATE TABLE PR_SYS_APPCACHE_SHORTCUT;
TRUNCATE TABLE PR_SYS_CACHE_DEP;
TRUNCATE TABLE PR_SYS_CACHE_ENTRY;
TRUNCATE TABLE PR_SYS_CACHE_IMPL;
TRUNCATE TABLE PR_SYS_CACHE_DEP;
Note :-
Please take full DB backup before you clear cache tables.Regards,
Prakhar
Pegasystems Inc.
GB
Hi,
For Query 2 please refer below link.
https://collaborate.pega.com/question/database-space-full
Regards,
-Prakhar
PEG
GB
I'm not too sure whether it would be advisable to perform the cache truncation.
But I'm unclear why pr_sys_app_hierarchy_flat would be so slow if it only contains only 647 records
Do you mean the query is returning this many records?
Have you (or your DBA) run an Explain Plan against the query to see if you can identify any indexes, etc... which may help?