More hanging queries using JChem cartridge

JChem version: 3.0.13


JChem Streams version: 3.0.13





Red Hat Enterprise Linux AS release 3 (Taroon)


Kernel 2.4.21-4.ELsmp on an i686





java version "1.5.0"


Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0-b64)


Java HotSpot(TM) Server VM (build 1.5.0-b64, mixed mode)





Oracle Database 10g Release 10.1.0.3.0 - Production





jakarta-tomcat-4.1.30





Hi - I am having another problem with Jchem, similar to a problem I raised before on this forum (“Jchem does not come back” - 12 May 2005). This previous problem was not satisfactorily resolved as it was not completely repeateable, and went away by itself. However, these problems are preventing me from completing my testing of Jchem in preparation for using it in my application.

Code:

SELECT   par.Parent_ID     FROM PARENT par    WHERE (jc_contains(par.ISOSMILES, 'N[C;r0,r1,r2,r3,r4,r5,r6]c1ccccc1') = 1)

is hanging when connected as my data owner schema.





This statement has worked in the past (took 17 seconds to come back, and there were 31983 matches), so I don’t know why it is not working now.





I can see that the connection is not doing anything (physical reads, etc is not changing).


However, I do not think that we are out of memory (this next query is executed during the hang, connected as system)

Code:

SQL Statement which produced this data:   select * from v$sgastat where POOL = 'java pool' or NAME = 'free memory' POOL   NAME   BYTES shared pool   free memory   11323032 large pool   free memory   16760032 java pool   joxs heap   1040256 java pool   free memory   100679488 java pool   joxlod exec hp   15720768

Furthermore, these numbers do not appear to be changing during the hang.





During the hang, even trying to execute simplest possible queries as another schema (Jchem) such as

Code:

select jc_equals(‘C’, ‘C’) from dual;

hang indefinitely. You can see them waiting in the system browser.





I tried shutting down tomcat first, to see if that breaks it more cleanly than what I have seen before.





Shutting down tomcat does not do anything - the connections remain active and do not complain about no tomcat - maybe I left them to run too long.





Disconnecting the connections in Oracle marks them as killed. But then restarting tomcat and trying a simple query (C, C) still hangs.





Next thing tried was shutdown tomcat and database, and restart.





Then, you can connect to the jchemstreams page, and the test.sh script executes correctly. The jc_equals C C simple query also executes correctly, as does

Code:

select par.PARENT_ID, par.ISOSMILES from parent par where jc_equals(par.ISOSMILES, 'c1ccccc1') = 1;

But then, this query hangs it again (you can see that no IO is happening)

Code:

select par.PARENT_ID, par.ISOSMILES from parent par where jc_contains(par.ISOSMILES, 'ccc1ccncc1') = 1;

However, I know that there are around 160 matches for this





This time, shutting down tomcat does cause Jchem to error and complain about tomcat not running.





This query does exactly the same thing - it hangs, and complains when you shut down tomcat.

Code:

select par.PARENT_ID, par.ISOSMILES from parent par where jc_contains(par.ISOSMILES, 'c1ccncc1') = 1;

So too do this

Code:

select par.PARENT_ID, par.ISOSMILES from parent par where jc_contains(par.ISOSMILES, 'CC1=CC2=C(C=C1)C=C(N)C=C2') = 1;

And this

Code:

select par.Parent_ID from Parent par   where (jc_contains (par.ISOSMILES, 'CF') = 1 ) and par.MOLECULAR_WEIGHT >= 100 and par.MOLECULAR_WEIGHT <= 110 and rownum < 5001 order by par.molecular_weight

Now I know that this last query took 14 seconds before, with this same version of Jchem.

Sorry for not replying to your posting earlier. I had a day off work yesterday.





Is it so that queries like

Code:

select jc_equals(‘C’, ‘C’) from dual;

complete successfully as many as you execute them provided that you issue only this kind of queries? Is it so that the second time you issue a query involving cartridge search on a table the query hangs (and all subsequent queries hang)?





Is this particular Tomcat installation exclusively used by JChem Cartridge, or do you have other application using it?





Have you been always using java version "1.5.0"? Would it be possible to use the latest sub-version of JDK 1.4.2 instead? In our experience, JDK 1.4.2 is still more stable for our purposes than the 1.5.x series.

Hi Peter.

Quote:

Is it so that queries like select jc_equals(‘C’, ‘C’) from dual; complete successfully as many as you execute them provided that you issue only this kind of queries?

select jc_equals('C', 'C') from dual;





select jc_contains('NCc1ccccc1', 'N[C;r0,r1,r2,r3,r4,r5,r6]c1ccccc1') from dual;





Yes - these are both fine, and the second one is the same input SMARTS which seemed to be causing the problem.

Quote:

Is it so that the second time you issue a query involving cartridge search on a table the query hangs (and all subsequent queries hang)?

No.


Only certain table search queries seem to cause the problem, and the problem is not consistent.





I have rebooted the machine in an attempt to reset the problem, and jchem after the reboot behaves fine for all the queries in the first post I made to open this thread, including the query which I suspected caused the problem.

Quote:

Is this particular Tomcat installation exclusively used by JChem Cartridge, or do you have other application using it?

Yes - it is used exclusively by Jchem.

Quote:

Have you been always using java version "1.5.0"? Would it be possible to use the latest sub-version of JDK 1.4.2 instead? In our experience, JDK 1.4.2 is still more stable for our purposes than the 1.5.x series.

Yes - we have always used Java 1.5.0, because that is the version of Java which ChemAxon recommended to us to install when we installed Jchem. There was some performance reason at the time which ChemAxon mentioned for choosing 1.5.0 rather than 1.4.2





However, as we have Oracle 10g installed, it comes with a jre 1.4.2 which is also installed.





java version "1.4.2_02"


Java(TM) 2 Runtime Environment, Standard Edition (build 1.4.2_02-b03)


Java HotSpot(TM) Client VM (build 1.4.2_02-b03, mixed mode)





Would that be suitable, or would you prefer me to install a different sub-version? In any case, to use a different version, would I just make sure that the JAVA_HOME was pointing at the right location, or are there other things I must do?





There would be a problem with using 1.4.2, in that all the testing we have ever done with Jchem, including the performance acceptance tests, has been done with 1.5.0. So I would be keen to avoid using 1.4.2 unless there was a clear indication that it would solve the problem.

Please, notify me next time the problem occurs and I will send you a debug version of JChem Cartridge.

Hi,





If you use Java 1.5.0 (which should be a bit faster than 1.4.2) it is recommended to use the latest version of it (currently 1.5.0_04).


Sun has fixed numerous bugs since the initial release of 1.5.





Best regards,





Szilard

It seems that the problem can be solved by using the latest version of the Oracle JDBC driver.