Jchem does not come back.

User f698d0529d

12-05-2005 17:13:24

Hi - I have a problem with Jchem cartridge. I am only using the cartridge and plain tables.





Red Hat Enterprise Linux AS release 3 (Taroon)


Kernel 2.4.21-4.ELsmp on an i686





Oracle version 10.1.0





Apache Tomcat/4.1.30





JChem version: 3.0.11


JChem Streams version: 3.0.11





I have a problem. I shut down the database using shutdown abort (I will explain why in a minute). Then I shutdown tomcat. Then I restart the db and then I restart tomcat.





On a toy table (1 row, indexed), a jchem query takes 11 seconds to run the first time and then 100ms thereafter. No problem.





But on a real table, a query which yesterday was taking 10s to run is now not returning at all. I wait for an hour - nothing. Plus, I have no idea during this time of what Jchem is doing, if anything.





The query is as follows, but other queries are showing the same behaviour.





select par.Parent_ID from Parent par where (jc_contains (par.ISOSMILES, 'N[C;r6]c1ccccc1') = 1 ) and


par.MOLECULAR_WEIGHT >= 500 and


par.MOLECULAR_WEIGHT <= 600 and rownum < 5001 order by par.molecular_weight;





The table has about 260K rows, and is indexed. Not only that, but I cannot cancel the query (TOAD has a cancel function which normally works) and I cannot use SYSTEM to kill the session either (TOAD hangs - this feature normally works). TOAD continues to list the session as active.





So I have no choice now but to do shutdown abort, and then we start all over again.





The tomcat java memory parameter is set to 360m.





I don’t see anything interesting in catalina.out.





I can send you catalina.sh and catalina.out if you wish.





Actually, I have just noticed that if I shutdown tomcat while the offending query is running, jchem eventually complains with “Please, check if Tomcat is running”





Can you help?


Thanks


Mark

ChemAxon aa7c50abf8

13-05-2005 11:38:01

Quote:
I shut down the database using shutdown abort (I will explain why in a minute). Then I shutdown tomcat. Then I restart the db and then I restart tomcat.





On a toy table (1 row, indexed), a jchem query takes 11 seconds to run the first time and then 100ms thereafter. No problem.





But on a real table, a query which yesterday was taking 10s to run is now not returning at all. I wait for an hour - nothing. Plus, I have no idea during this time of what Jchem is doing, if anything.
After restarting Oracle and Tomcat, are things working correctly again, or are you still having trouble with queries on the 260k table?





There is a known issue with symptoms similar to your problem: If Tomcat is shutdown as a cartridge operation is being processed, the pending operation never completes. However, after restarting Oracle, JChem Cartridge should normally operate again.





I tried to reproduce your problem by first shutting down Tomcat with a pending cartridge operation still in progress (known issue) then by shutdown-aborting Oracle -- to no avail. This time not even the known issue seems to be reproduceable: I consistently get the error message “Please, check if Tomcat is running”. (I guess the mentioned known issue appears only with Oracle 9i.)





Please, note that shutting down Oracle with the "abort" option may result in Oracle performing eventually lengthy recovery-related operations after restart which might slow down queries.





In case you're still having cartridge operations hanging, I suggest to look into the Oracle system views (eminently into sys.v$sql) to find out whether the calls are really hanging. (I can give you more specific advice on how to do this, if you need it.) (You can eventually also use the OEM console.)





Also, if this problem persists, it might be interesting to look at the query plan of the offending statement(s). I may be more successful in reproducing the problem with statements having query plans similar to yours.





Not closely connected but... For the query in your posting, I suggest to consider the jc_compare operator with the filterQuery option (http://www.jchem.com/doc/guide/cartridge/cartapi.html#jc_compare_filterQuery). Depending on the selectivity of the conditions on the parent.MOLECULAR_WEIGHT column, the following reformulation of the original query might produce better performance:





Code:
select par.parent_id from parent par where jc_compare(par.isosmiles, 'N[C;r6]c1ccccc1', 'sep=! t:s!filterQuery=select rowid from parent where par.MOLECULAR_WEIGHT >= 500 and par.MOLECULAR_WEIGHT <= 600') = 1 and rownum < 5001 order by par.molecular_weight






Peter

ChemAxon aa7c50abf8

18-05-2005 06:34:38

Please, check the JDBC driver version your Tomcat installation is using. It is the classes12.jar file found in <tomcat-home>/shared/lib. I attached a small java library file which you can use to check the JDBC driver versions. Start it on the command line in the following way:





java -classpath <oracle-driver-jar-to-check>:<path-to-chxutil.jar> chemaxon.jchem.GetJdbcDriverVersion -h <host> -p <port> -s <sid>





The version of the driver used by Tomcat must match the version of the JDBC driver bundled with the Oracle server (in $ORACLE_HOME/jdbc/lib) Tomcat is communicating with.





For example, for the driver bundled with Oracle:


java -classpath /opt/oracle/product/10.1.0/Db_1/jdbc/lib/classes12.jar:/home/pkovacs/tmp/chxutil.jar chemaxon.jchem.GetJdbcDriverVersion -h morgo -p 1521 -s orcl





For the driver in Tomcat:


java -classpath /opt/java/tomcat/shared/lib/classes12.jar:/home/pkovacs/tmp/chxutil.jar chemaxon.jchem.GetJdbcDriverVersion -h morgo -p 1521 -s orcl

ChemAxon aa7c50abf8

18-05-2005 16:50:30

Sorry, I uploaded a new corrected jar file.





In addition to the arguments mentionned in my previous posting, you also have to specify -u <username> and -pass <password> on the command line:


Code:



java -classpath <oracle-driver-jar-to-check>:<path-to-chxutil.jar> chemaxon.jchem.GetJdbcDriverVersion -h <host> -p <port> -s <sid> -u <username> -pass <password>