We’ve noticed that this statement has been running excessively on our production machine:
SELECT ‘x’ FROM DUAL
It ran over 400,000 times between 4 and 4:30 pm today. We’ve raised this issue to support and they say it is caused by the JBOSS application server and the Oracle JDBC driver (maintaining a connection to the database). We’re running Rx 6.5.2 on Linux Red Hat 5.3 with Oracle 10.
Has anyone else seen this and have a way to stop it.
Thanks,
Chris
The Percussion support staff is correct that the issue is with JBOSS and the Oracle JDBC driver. There is no call made to Oracle for “SELECT X FROM DUAL” in Rhythmyx.
This is a known issue for the Oracle JDBC driver. As of Oracle 10g, Oracle has optimized calls that execute this statement. The performance penalty may not be that significant.
You might be able to reduce the amount of these calls if you are willing to write (or find) some code that manages that connection in a different manner. (see notes below).
Here is some more technically detailed information:
When using Oracle with Rhythmyx, the connection to the DB is configured in a file “rx-ds.xml” (in the directory <rx_root>/AppServer/server/rx/deploy).
In that file is an element named: “valid-connection-checker-class-name”.
This determines a class used by JBOSS to test the connection to the database before returning a connection to the connection pool. (Oracle is known for killing idle connections from the server side, without the client knowing they have been closed.)
The source for this class is available:
http://docs.jboss.org/jbossas/javadoc/4.0.2/org/jboss/resource/adapter/jdbc/vendor/OracleValidConnectionChecker.java.html
If you examine the code, you will see that it invokes a method named: “pingDatabaseDriver()” in the class “oracle.jdbc.driver.OracleConnection”. It is this method (inside the Oracle JDBC driver) that executes the SELECT X FROM DUAL" on the DB.
Theoretically, you could replace the reference to this method and class with a different one that would reduce or eliminate the SELECT statement. I would suggest some research on the JBOSS or the Oracle Tech Support site as there is good information out there about this issue.