Question5Replies690Views PonnurangamN2827 Member since 2019 43 posts Vodafone Posted: July 1, 2019Last activity: July 2, 2019 Closed query pr_sys_app_hierarchy_flat causing performance isssueTeam,Our application uses pega 7.3.1 and below are the slow running queries observed in prodquery #1 : select 1 from pr_sys_app_hierarchy_flat - record count 647 -> taking 3 mins to run this queryQuery #2 : on table PEGADATA.pr_perf_stats -> record count 12 millions - taking 3 mins to run this queryI followed the link https://community1.pega.com/community/product-support/question/long-running-queries-causing-systemwherein 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*** System Administration ×Close popoverFacebookTwitterLinkedinEmail Copy Link Copied! Moderation Team has archived post This thread is closed to future replies. Content and links will no longer be updated. If you have the same/similar Question, please write a new Question. Posted: 1 year agovetsg PEGA replied to PonnurangamN2827pr_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. Posted: 1 year agoPonnurangamN2827 Vodafone replied to vetsgwe 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? Posted: 1 year agoPrakhar_GCS PEGA replied to PonnurangamN2827Hi, 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 Posted: 1 year agoPrakhar_GCS PEGA replied to PonnurangamN2827Hi, For Query 2 please refer below link. https://community1.pega.com/community/pega-support/question/database-space-full Regards, -Prakhar Posted: 1 year agoLaurenceB_GCS PEGA replied to PonnurangamN2827I'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?