Question
Capgemini America Inc
US
Last activity: 17 Jun 2018 20:15 EDT
Number of Pega queries per transaction very high
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.
Questions
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.
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.
Questions
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.
Context.xml
<Resource
description="Process Commander PegaRULES Database DataSource"
auth="Container"
name="jdbc/PegaRULES"
scope="Shareable"
type="javax.sql.DataSource"
username="xxxx"
password="xxxx"
url="jdbc:oracle:thin:@xxxxxxxxxxx-prod.xxxxxx.com:1521:xxxxx"
driverClassName="oracle.jdbc.OracleDriver"
initialSize="5"
maxActive="200"
minIdle="5"
maxWait="30000"
validationQuery="select 1 from dual"
testOnBorrow="true"
testOnReturn="false"
testWhileIdle="true"
timeBetweenEvictionRunsMillis="30000"
numTestsPerEvictionRun="64"
removeAbandoned="true"
removeAbandonedTimeout="300"
logAbandoned="true"
>
Message was edited by: Marissa Rogers - added Category; SR Exists
**Moderation Team has archived post**
This post has been archived for educational purposes. Contents and links will no longer be updated. If you have the same/similar question, please write a new post.
Updated: 17 Jun 2016 13:32 EDT
Perficient
US
"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.
Global Payments
US
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) ?
Pegasystems Inc.
US
Sachin,
Just a sanity check, validationInterval has the unit of ms not secs - I hope this is not your problem here, but please double check.
validationInterval |
(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 |
Cognizant Technology Limited
IN
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.
Example
factory="org.apache.tomcat.jdbc.pool.DataSourceFactory".
configure the attribute fairQueue to be true.
Global Payments
US
Hi Udhay,
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
Thanks,
Sachin
Pegasystems Inc.
US
Please see the detailed description of the tomcat conneciton pool (versus dbcp connection pool), you will find all the info here: https://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html