Had submitted SR-A77087 but was asked to post the query here.
Please see below, for every transaction the number of Pega generated queries in Production is very high, which we think is one of the reason for the performance issues.
1.Select 1 from DUAL – this is configured in context.xml (snippet below) – It’s being called 3297 times – trying to understand why is it being called so many times, is it because of the missing attribute validationInterval ? What is the purpose of this ?
2.The pr4_fieldvalue – 1641 calls are being made in one transaction, shouldn’t these be cached. We have not restarted the Server for a longer time now.
"SELECT 1 FROM DUAL" is a validation query for your JDBC connection pool. The JDBC pool is configured to reuse database connections, and the validation query is run as a test to ensure that the connection is still valid. If the query fails, the driver will purge the stale connection from the pool and create a new one. You can tweak the way JDBC validation works (for instance, using a validation interval (https://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html) to test connections instead of testing every time one is retrieved.
Have you checked your Pega Alerts log for warnings? I'd examine that for performance issues first before examining individual database queries.
We have already checked the Pega logs and have run the Oracle Optimization wizard to get the index suggestions.
We also added the validationinterval attribute for 60s, what we obeserved post this is - within a business transaction if there 100 queries being executed the Select 1 FROM DUAL is being invoked 100 times.
Can anyone let us know why SELECT 1 FROM DUAL get's called for every query that gets executed ? as the validation should not occur for 60s. Can this be modified to executed all 100 calls by invoking SELECT 1 FROM DUAL once (assuming all 100 calls take 60 s to complete) ?
Just a sanity check, validationInterval has the unit of ms not secs - I hope this is not your problem here, but please double check.
(long) avoid excess validation, only run validation at most at this frequency - time in milliseconds. If a connection is due for validation, but has been validated previously within this interval, it will not be validated again. The default value is 30000 (30 seconds).
Actually Select 1 from dual should not get called if we are using an attribute validationinterval, It's really wondering. Could you please share the complete Resource Tag attributes and it's values.
Kindly try the below:
you just have to write your DataSourceFactory that extends either org.apache.tomcat.jdbc.pool.DataSourceFactory or org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory depending on the pool you are using, restart the server and try.
I have shared the Resource tag in the first question that I have posted (only addition would be the validationInterval="60000"). Should I add factory="org.apache.tomcat.jdbc.pool.DataSourceFactory".to context.xml and then test this out ? Please let me know