Calibrator Error

User c5c63b5c6a

15-01-2010 15:54:46

Hi,


Am trying to make use of the calibrator.sh functionality that is available with JChem running with 10.2.0.4 and have received the followign error. Looks like there might be an issue with the version of the code and the definition of the plan table as it changed between 9i and 10G.


-bash-3.00$ bash calibrate.sh calibrator.properties
+ /GWD/appbase/projects/chemaxon/Jchem/linux_jre/jre1.6.0_14x64/bin/java -cp ./../lib/jchem.jar:/oracle/app/oracle/product/10204LX64DB1_PAR/jdbc/lib/ojdbc14.jar chemaxon.jchem.cartridge.costestim.calibra.Calibrator calibrator.properties
Connecting to jdbc:oracle:thin:@hostname.corpnet2.com:1521:db_name...
2010-01-15 15:44:37.907 [UNKNOWN, CODS, CODS]: DEBUG - chemaxon.jchem.cartridge.costestim.calibra.Calibrator: setting volatile cost estimations: sql=call jchem_opti_pkg.set_volatile_cost_factors(?, ?, ?, ?, ?, ?, ?)
2010-01-15 15:44:38.990 [UNKNOWN, CODS, CODS]: DEBUG - chemaxon.jchem.cartridge.costestim.calibra.IdxCalibrator: getActualTime: sql=select count(*) from ucs_structure where jc_compare(isomeric_smiles, ?, ?) = 1
2010-01-15 15:44:50.979 [UNKNOWN, CODS, CODS]: DEBUG - chemaxon.jchem.cartridge.costestim.calibra.IdxCalibrator: getActualTime: sql=select count(*) from ucs_structure where jc_compare(isomeric_smiles, ?, ?) = 1
2010-01-15 15:44:59.438 [UNKNOWN, CODS, CODS]: DEBUG - chemaxon.jchem.cartridge.costestim.calibra.IdxCalibrator: actual time: 8 sec(s)
2010-01-15 15:44:59.438 [UNKNOWN, CODS, CODS]: DEBUG - chemaxon.jchem.cartridge.costestim.calibra.IdxCalibrator: getActualTime: sql=select count(*) from ucs_structure where jc_compare(isomeric_smiles, ?, ?) = 1
2010-01-15 15:45:07.985 [UNKNOWN, CODS, CODS]: DEBUG - chemaxon.jchem.cartridge.costestim.calibra.IdxCalibrator: getActualTime: sql=select count(*) from ucs_structure where jc_compare(isomeric_smiles, ?, ?) = 1
2010-01-15 15:45:16.533 [UNKNOWN, CODS, CODS]: DEBUG - chemaxon.jchem.cartridge.costestim.calibra.IdxCalibrator: actual time: 8 sec(s)
2010-01-15 15:45:16.536 [UNKNOWN, CODS, CODS]: DEBUG - chemaxon.jchem.cartridge.costestim.calibra.Calibrator: setting volatile cost estimations: sql=call jchem_opti_pkg.set_volatile_cost_factors(?, ?, ?, ?, ?, ?, ?)
2010-01-15 15:45:16.556 [UNKNOWN, CODS, CODS]: DEBUG - chemaxon.jchem.cartridge.costestim.calibra.IdxCalibrator: getPlannedTime: sql=explain plan for select count(*) from ucs_structure where jc_compare(isomeric_smiles, 'N=C=O', 't:s') = 1 (query=N=C=O, options=t:s
java.sql.SQLException: ORA-00904: "TIME": invalid identifier


        at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:74)
        at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:131)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:204)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
        at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1034)
        at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:194)
        at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:791)
        at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:866)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1186)
        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3387)
        at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3431)
        at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1203)
        at chemaxon.jchem.cartridge.costestim.calibra.Calibrator.getTimeFromPlanTable(Calibrator.java:122)
        at chemaxon.jchem.cartridge.costestim.calibra.IdxCalibrator.getPlannedTime(IdxCalibrator.java:68)
        at chemaxon.jchem.cartridge.costestim.calibra.Calibrator.test(Calibrator.java:84)
        at chemaxon.jchem.cartridge.costestim.calibra.FineTuner.tune(FineTuner.java:45)
        at chemaxon.jchem.cartridge.costestim.calibra.Calibrator.calibrate(Calibrator.java:57)
        at chemaxon.jchem.cartridge.costestim.calibra.Calibrator.main(Calibrator.java:328)
+ set +x

ChemAxon 9c0afc9aaf

15-01-2010 19:12:40

Hi,


 


Have you tried re-creating the plan table ?


http://download.oracle.com/docs/cd/B10500_01/server.920/a96533/ex_plan.htm#21502


 


Oracle Corporation recommends that you drop and rebuild the PLAN_TABLE table after upgrading the version of the database because the columns might change. This can cause scripts to fail or cause TKPROF to fail, if you are specifying the table.

Best regards,


 


Szilard

ChemAxon aa7c50abf8

15-01-2010 19:12:48

Hi Rachel,


I was not immediately able to reproduce the problem on Oracle 10.2.0.4.0 . Please, could you execute two statements similar to the following (EXPLAIN PLAN and SELECT time FROM PLAN_TABLE) to see if there is indeed no TIME column in the resulting plan table?


20:06:02 SQL> explain plan for select count(*) from nci_plain where jc_compare(structure, 'N=C=O', 't:s') = 1;

Explained.

Elapsed: 00:00:00.17

20:06:02 SQL> select time from plan_table where id = 0;

      TIME

----------

         3

Elapsed: 00:00:00.00


Thank you,


Peter

ChemAxon aa7c50abf8

15-01-2010 19:14:20

PS:


I tested with JChem 5.2.6.

User c5c63b5c6a

18-01-2010 13:53:12

Apologies, should have thought of that. 10G plan_table has now resolved the issue.