JChem Cartrige seems stuck.

Hi,

The test query below did not return in over an hour. Further, when the query was running the database would not open new connections.  As soon as I killed the query, the other connections opened.  query and explain plan are below:

 

What I mean by not opening other connections is that in a seperate connection while the original query was runningSQLPlus would  get as far as:

C:\Documents and Settings>sqlplus senobase/xxxxx@jchem_test

 

SQL*Plus: Release 11.2.0.2.0 Production on Fri Aug 24 11:10:41 2012

 

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

 

<<and it was stuck Like this.  so I went back to the window running the orignal session on a seperate machine  and terminated that, and as soon as I did that, SQLPlus returned the following lines with a prompt:>>

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL>

 

 

<<The query that was causing problems was:>>

SELECT cd_smiles

FROM structure_jc1

WHERE jc_compare (cd_structure, 'CCCCCCCCCC[N+](C)(C)CCCS([O-])(=O)=O', 't:d') = 1

 

Plan

SELECT STATEMENT  CHOOSECost: 3  Bytes: 600,500,432  Cardinality: 149,527          

    2 TABLE ACCESS BY INDEX ROWID TABLE SENOBASE.STRUCTURE_JC1 Object Instance: 1  Cost: 3  Bytes: 600,500,432  Cardinality: 149,527      

        1 DOMAIN INDEX INDEX (DOMAIN) SENOBASE.JC_IDX 

 

I am really confused about why a search on a domain index would take so long, especially when it is an exact match with smiles taken directly from the structure_jc1 table.

 

Thanks,
~mike 

Hi Mike,

Thank you for this detailed description.

The one wierd thing sticking out is Oracle being unresponsive and which I very vaguely remember having seen. Are you using Toad, perhaps, to connect to Oracle and running queries?

Thanks

Peter

Hi Peter,

I forgot to do a:

exec jchem_core_pkg.use_password('xxx');

at the start of my session.  Doing this resolved my problem! oops!

You asked about TOAD.  The original "Stuck" query was running on the Linux database server through an ssh terminal session.  I then tried to access the database with TOAD and that stuck, so I tried to access the database through SQLPlus on Windows, and that stuck, so I killed the session on the database server, and that freed up both the Toad and SQLPlus windows sessions.

~mike