Question
Verizon Wireless
US
Last activity: 20 Jul 2017 14:29 EDT
AES DB Tuning recommendations.
Hi Team,
Could you please provide AES Database tuning recommendations.
AES Version : 7.1.7
Oracle Version : 12 C
Regards,
Pradeep Pydi,
Pegasystems Inc.
US
The same recommendations in PDN article below for Oracle DB is applicable to AES tables too.
https://docs-previous.pega.com/performance-guidance-production-applications-database
Verizon Wireless
US
Hi Ankur,
Thanks for the article.But I am looking for AES Oracle DB specific tuning recommendations (Indexes to be added on specific tables,Reorg tasks,DSS settings and Purge policies to be defined).
Regards,
Pradeep Pydi.
Pegasystems Inc.
US
Hi Pradeep,
We do not have specific recommendations on additional indexes, Update statistics task frequency and purge policies as all these are data volume driven and are different at different client deployments. Your Oracle DBA would need to monitor and review the Oracle AWR to see which queries are slow / needs additional indexes (apart from the OOTB ones) and determine Update stats jobs based on how frequently the indexes are getting fragmented.
Pegasystems Inc.
US
Thank you for your question.
First off - please note that you should plan to upgrade your AES infrastructure and software. Whilst AES 717 is supported, as shipped it does not support monitoring PRPC versions newer than 717. AES 72 is the most current version of the product and it is compatible but not fully optimized with Pega 72 and 73; AES 73 is in development for release later this year.
As to your specific questions - purge policies are defined by the AES Administration page. This controls how long raw alerts, exceptions, usage statistics are kept; how long action/exception work items are kept after resolution; and how long an issue must 'stop' before an item is automatically resolved.
Tables pegaam_alert, pegaam_exception and pegaam_node_stats tables by far have the greatest 'churn' and 'fragmentation' as they are the primary 'raw data' tables for AES data is added and purged from these tables regularly. These tables should be reorganized (alter table move) to reclaim BLOB storage from deleted rows on a regular basis.
AES 72 shipped with a reasonably good set of OOTB indexes, but like any Pega application, the performance will be unique to your input data and usage. Treat AES like anything else - lower the alert threshold from 500ms to 200ms; regularly review table sizes and the Oracle AWR (especially the 'top' queries) and look at the alerts.
Thank you for your question.
First off - please note that you should plan to upgrade your AES infrastructure and software. Whilst AES 717 is supported, as shipped it does not support monitoring PRPC versions newer than 717. AES 72 is the most current version of the product and it is compatible but not fully optimized with Pega 72 and 73; AES 73 is in development for release later this year.
As to your specific questions - purge policies are defined by the AES Administration page. This controls how long raw alerts, exceptions, usage statistics are kept; how long action/exception work items are kept after resolution; and how long an issue must 'stop' before an item is automatically resolved.
Tables pegaam_alert, pegaam_exception and pegaam_node_stats tables by far have the greatest 'churn' and 'fragmentation' as they are the primary 'raw data' tables for AES data is added and purged from these tables regularly. These tables should be reorganized (alter table move) to reclaim BLOB storage from deleted rows on a regular basis.
AES 72 shipped with a reasonably good set of OOTB indexes, but like any Pega application, the performance will be unique to your input data and usage. Treat AES like anything else - lower the alert threshold from 500ms to 200ms; regularly review table sizes and the Oracle AWR (especially the 'top' queries) and look at the alerts.
In a large enterprise deployment, it is best to have two AES systems -- production and 'non-production'. A 'non-production' system serves three roles
a- it is used to monitor 'non production' systems. Whilst unlikely, one does not want to allow any risk that rogue test or load system might impair monitoring for production systems.
b- It is used to develop/test customizations to AES as well as to verify Pega or AES upgrades or hotfixes.
c- Let 'AES use AES'. Non-production AES should monitor production AES and create action items for any performance issues in AES.
Pega717 shipped with a bug in implementation of the FTSIndexer agent queue which can impact DB load for applications that use full text search. AES uses full text search. Look for DB action items for the ftsindexer table.
If you would like assistance reviewing performance of your AES installation, please share alert and AWR; let's see what we can find
Pegasystems Inc.
US
Hi Pradeep,
In addition to the points mentioned in the AES installation guide (Optimizing performance & avoiding deadlocks), I found below article from Oracle:
https://docs.oracle.com/cd/B10501_01/server.920/a96533/stats.htm#31058
So, as per my understandings, you should be running the statistics on AES tables which will help you to estimate the cost of each execution plan. Once you collect the statistics you can analyze it and use the data for fine tuning of individual DB object.
I hope this helps.
Thanks,
Mukul