JOC FAQ

Related documents and FAQs

Information about JChem search performance
JChem Cartridge is integrated with JChem Base, you can find the JChem Base FAQ here .
What computer resources are required by JChem Cartridge?
- CPU : The faster the better. Parallel processing capabilities of the host are also utilized, if available. See our benchmarks with a 3GHz dual Xeon machine for indexing and searching.
- Memory :
  - Oracle
    
    The memory requirement of the JChem Cartridge-specific stored procedures depends on the size of the result set returned by JChem Server. Currently, the processing of the results is started on the Oracle end only after the entire result set has been returned by JChem Server. This requires temporarily buffering the entire result set. In the case of structure searching, the temporary memory requirement is about 8 bytes per hit. We are working to remove this bottleneck, so that results from JChem Server will be instantaneously returned for processing (which will result in both decreased memory buffer need and improved response times).

Not directly required by, but related to, JChem Cartridge is the size of the Oracle buffer cache that speeds up the SELECT SQL statement whereby the JChem-internal compound identifiers of the hits are converted to Oracle-internal ROWIDs.

No memory is required (directly or indirectly) to buffer the structures themselves in Oracle, since target compounds are cached in the JChem Cartridge Server. See below

JChem Server

JChem Server uses a mechanism that is highly optimized for speed and memory footprint to cache molecular structures. The memory requirement of the structure cache depends primarily on the number of structures to be cached (the number of molecular structures in your structure tables) and on the size of the fingerprint used to capture the main attributes of the target molecular structures. As a rule of thumb, 100 MB of main memory can hold the cache for 1 million structure. (See here a detailed description of how to calculate the memory requirement of the structure cache.)

- Storage capacity :
  
  The domain index information for the NCI 2000 data set (250251 molecular structures) (index table together with the auxiliary indexes on the index table) requires - with default Oracle storage settings - 96 megabyte disk space (regardless of the format in which the molecular structures are stored in the base table).
Which environments are supported by JChem Cartridge?

JChem Cartridge is made up of two major components:

Stored procedures running in the Oracle server. Oracle stored procedures require Oracle 9i or later (tested with Oracle 11.2, Oracle 12c).
JChem Server

JChem Server requires JDK 1.6 or later. Please, see Supported System Configurations - Java SE 6 for a the list of platforms support by JDK.

The two components can be run on separate hosts which adds the flexibility of running them on different platforms.

Note that Oracle Express Edition is not supported.

How to setup JChem Cartridge in environments with "one-way" virtual IP adresses?

In environments using "one-way" network virtualization (where virtual IP addresses exist only as destination addresses with the physical addresses being used as source addresses – such as in SunClusters), the url property has to be set in jchem/cartridge/conf/jcart.properties using the Oracle JDBC URL notation. Setting this property will override the oracle.server.host, oracle.server.port and oracle.server.instance settings and will disable IP sanity checking in the JChem Cartridge (JCC) server. IP sanity checking to make sure that the JCC server allows only the Oracle server to connect to it that is specified in its configuration

Which molecular formats are supported by JChem Cartridge?

All major molecular formats can be stored (formats created by MDL, Daylight, Chemaxon) in structure tables. The formats supported in structure tables are the same as with JChem Base.

In addition to the formats for structure tables, a number of other formats are supported for various operations.

How can I make searching with JChem Cartridge faster?
- Try setting the CURSOR_SHARING Oracle parameter to SIMILAR. It can be set either globally or in the session scope:
```
ALTER SESSION SET CURSOR_SHARING = SIMILAR
```
  This will avoid the reparsing of SQL queries with literal parameters of which JChem Cartridge uses potentially many.
- Gather table/schema statistics to prevent the Oracle Optimizer from executing sampling queries which might take much longer than the actual queries (executed internally by JChem Cartridge) which they are supposed to make faster.
- In many cases, enabling cost estimation can help. It may have the most dramatic effect with Oracle Enterprise Edition, when JChem Cartridge operators are called in the inner loop of a nested loop like:
```
-----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               |     1 |  2041 |    27   (4)| 00:00:01 |
|   1 |  VIEW                               | VM_NWVW_1     |     1 |  2041 |    27   (4)| 00:00:01 |
|   2 |   HASH UNIQUE                       |               |     1 |    86 |    27   (4)| 00:00:01 |
|   3 |    NESTED LOOPS                     |               |       |       |            |          |
|   4 |     NESTED LOOPS                    |               |     1 |    86 |    26   (0)| 00:00:01 |
|*  5 |      TABLE ACCESS FULL              | HIGHSAMP      |     1 |    26 |     7   (0)| 00:00:01 |
|   6 |      BITMAP CONVERSION TO ROWIDS    |               |       |       |            |          |
|   7 |       BITMAP AND                    |               |       |       |            |          |
|   8 |        BITMAP CONVERSION FROM ROWIDS|               |       |       |            |          |
|*  9 |         INDEX RANGE SCAN            | SYS_C0032145  |     1 |       |     1   (0)| 00:00:01 |
|  10 |        BITMAP CONVERSION FROM ROWIDS|               |       |       |            |          |
|  11 |         SORT ORDER BY               |               |       |       |            |          |
|* 12 |          DOMAIN INDEX               | JCXPBCH_4500K |     1 |       |            |          |
|  13 |     TABLE ACCESS BY INDEX ROWID     | PBCH_4500K    |     1 |    60 |    26   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------                                      
```
- Set _optimizer_rownum_pred_based_fkr Oracle parameter to false, if the Oracle Optimizer generates an execution plan similar to the above for SQL queries which include ROWNUM as a condition. (The parameter setting is the solution suggested by Oracle for problem ID 833286.1.)
- Use the filterQuery search option to move the evaluation of non-chemical search conditions from the top level SQL statement into the jc_compare operator. It is worthwhile considering the use of this search option, if, broadly speaking, the number of rows to be moved into the filter query is likely to be less than the number of rows the jc_compare operator would return without the filter query.
- Use the maxHitCount or the maxTime options to restrict the number of hits to be processed or the time the search can take. The maxHitCount option might especially be useful for similiarity searches: the structures with the highest scores will be returned, so users will not have to wait for the remaining, less interesting matches of the search.
- Create the index with the structuralfp_config parameter, if there are query structures which are likely to be searched for many times in the given column.
- Create the index with the autoCalcCt parameter, if there are Chemical Terms expressions which are likely to be used often in searches.
- See also the Fingerprint settings section of this forum topic . Note that with regular structure tables, you will want to use this SQL function for finding out fingerprint statistics:
  
  jchem_core_pkg.get_idx_stats(idx_schema varchar2, idx_name varchar2, idx_partition varchar2) return varchar2
  
  Also, you can use the t:na search type option with the select jchem_core_pkg.get_hit_count function to find out the number of structures which were marked positive during fingerprint screening. For example:
```
select jchem_core_pkg.get_hit_count('PKOVACS_TRUNK_USER', 'PBCH_4500K', 'STRUCTURE', 'c1nc2cncnc2n1', 't:na') from dual;
```
How can cache loading be included into the service startup script ?

After creating an index, the structure cache must be loaded. This will be done automatically at the beginning of the next search, so it can take a very long time depending on the number and the characteristics of the new structures. To avoid the slowdown of the first search, load the data cache into the jchem server at the service startup script.

Use jchem_core_pkg.load_cache function to load all the tables you want and there is no need for dummy searches. E.g.:

call jchem_core_pkg.load_cache('jchem', 'structures_idx', null, 'haltOnError:n');
Why do I get the log message "Name of the connected Oracle instance could not be obtained."? Why do I get security violations in audit log with return code 2004 for v$instance?

JChem Cartridge tries to select from the v$instance view, but can't access it. It is not necessary to have access to this view, the application is fully prepared to work without this access, only uses it to make some extra checks for the connection between the JChem Server and the Oracle instance and to provide more detailed error messages if it was set up incorrectly. So you can disregard these messages. Until version 5.11., the severity of this message was WARNING, but in the newer versions we decreased the severity of the log message to DEBUG level, so it probably won't disturb you anymore. However, if you wish to remove these messages, you can do it by adding select privileges for the v$instance view to the user who uses JChem Cartridge and also to the JChem Server user, if it is different. To add this privilege you have to call as SYS user:

grant select on sys.v_$instance to username

What to do in case of occasional "ORA-12519, TNS:no appropriate service handler found" messages?

The number of opened sessions/processes/transactions is limited in the database settings.

check current values


select name, value from v$parameter where name in ('processes', 'sessions', 'transactions')

increase the values, for example:


alter system set processes = 300 scope = spfile;
alter system set sessions = 600 scope = spfile;
alter system set transactions = 350 scope = spfile;

restart server
```
shutdown immediate;
startup;
```

How to execute a JChem Cartridge upgrade test?

In case you want to test the JChem Cartrigde upgrade process before running it on your production system, you can select one of the following methods. Both methods result a new database schema with upgraded indexes while the old production database schema remains untouched. The 1st solution gives only an example for the new version of JChem Cartridge; the 2nd solution demonstrates the upgrade process as well.
1. Create a new installation with the new version of JChem Cartridge on a new database. Copy the old database tables there, and create an index on them. For example:
  - your old production database schema contains the original structure table(s), installed JChem Cartridge 5.9, indexes created with JChem Cartridge 5.9
  - create a new database schema with JChem Cartridge 6.3, copy the original structure table(s) from the old schema, create indexes with JChem Cartridge 6.3
2. Create a new installation with the old version of JChem Cartridge on a new database. Copy the old database tables there, and create an index on them. Afterwards upgrade the database to the new version. For example:
  - your old production database schema contains the original structure table(s), installed JChem Cartridge 5.9, indexes created with JChem Cartridge 5.9
  - create a new database schema with JChem Cartridge 5.9, copy the original structure table(s) from the old schema, create indexes with JChem Cartridge 5.9
  - run the upgrade process in that new database schema with JChem Cartridge 6.3
How to failover outage of a JChem Server in RAC systems with multiple JChem Servers?

When JChem Server service is down on a node, and restart of that service cannot be executed, please, disconnect the relevant node from the RAC system. So the load balancer will not distribute tasks to the stopped node.
Why do I get 'class chemaxon/jchem/cartridge/oraresident/JFunctions does not exist' message?

Possibly, the rights of the <jchem_user> are not correctly set. Please run the following:
```
grant JCC_BASIC_ROLE to <jchem_user>;
```
Then recreate the public synonyms with this script:
```
call <jchem_owner>.PRIVMAN_PKG.SYNS_FOR_JCOBJS('<jchem_owner>','<jchem_user>');
```
What can cause java.rmi.NoSuchObjectException?

A very possible reason is that the user did not reopen their session. An other solution can be - beside reconnect - clearing the cache:
```
call rmi.clear_directory_cache();
```
After restarting Oracle, should I restart JChem Server as well?

Yes, each restart of the Oracle database must be followed by restarting the JChem Server.
Possible solution for ORA-00600: internal error code, arguments: [peshmgel: Table size] occurring in Oracle 12.1

Oracle bugfix (patch number 21560838) solves this error, however the patch needs Oracle Support account.

JChem Oracle Cartridge doesn't work on Oracle databases (11gR2,12cR1,12cR2) patched with OJVM_UPDATE_171018 (2017 Oct). It is a confirmed Oracle bug. Do not install this Oracle JVM patch. The following is a typical error message:


Error starting at line : 1 in command -
exec jchem_core_pkg.use_password('pw')
Error report -
ORA-29532: Java call terminated by uncaught Java exception: java.lang.RuntimeException: java.lang.Exception: Problem connecting to JChemServer: rmi://10.32.0.12:1099: the Permission ("java.net.SocketPermission" "10.32.0.12:1099" "connect,resolve") has not been granted to -. The PL/SQL to grant this is dbms_java.grant_permission( '-', 'SYS:java.net.SocketPermission', '10.32.0.12:1099', 'connect,resolve' )
ORA-06512: at "JCHEM.JCHEM_CORE_PKG", line 70
ORA-06512: at "JCHEM.JCHEM_CORE_PKG", line 66
ORA-06512: at line 1
29532. 00000 -  "Java call terminated by uncaught Java exception: %s"
*Cause:    A Java exception or error was signaled and could not be
           resolved by the Java code.
*Action:   Modify Java code, if this behavior is not intended.

Oracle fixed this bug. Patch number: PATCH 27357599. The bug is described on Oracle Support page under Doc ID 2371942.1.
First solution: installing the patch which is available for Oracle versions 11R2, 12.1 (12cR1) 12.2 (12cR2).
Second solution: granting the following privilege for PUBLIC to the appropriate host:port solves the issue.

exec dbms_java.grant_permission('PUBLIC', 'SYS:java.net.SocketPermission','155.250.158.114:2099','connect,resolve');

Error java.lang.BootstrapMethodError caused by Oracle JVM patch July 2019

The following workaround is recommended. Please execute as SYS or SYSTEM:

exec dbms_java.grant_permission('<username>','SYS:java.lang.RuntimePermission', 'accessClassInPackage.sun.rmi.transport', '' );
exec dbms_java.grant_permission('PUBLIC', 'SYS:java.net.SocketPermission','<jchemserver_host>:<jchemserver_port>','connect,resolve');

What to check in case of connection problem between the database and the jchem server?
- If the connection started from the jchem server in the direction of the database is closed, the following reasons can be in the background: Some cartridge-independent network issue or There is a connection timeout set in the database globally or only in relation with the jchem server. If this is the case, please remove this connection timeout relating the jchem server and apply the connection pool timeout parameters of the cartridge. These two parameters can be set in your <jchem_home>/cartridge/conf/jcart.properties file (the numbers mean seconds):
```
    oracle.connection.cache.inactiveConnectionTimeout = 10
    oracle.connection.cache.timeoutCheckInterval = 10
```
- If the connection from the database in the direction of the jchem server is closed, it is recommended to check this connection by running the following command on the database machine: telnet <jchem server host> <jchem server host port>
Error java.security.AccessControlException caused by Oracle JDK versions 1.7.0_321 and 1.8.0_311 java.security.AccessControlException: the Permission ("java.lang.reflect.ReflectPermission" "suppressAccessChecks") has not been granted to <username> exception is thrown at specific select statements of JOC when the database has the above Java JDK versions. Oracle JDK version 1.8.0_311 is part of Oracle patch 19.13. We do not recommend to apply patch 19.13.
The error is fixed in Oracle JDK version 1.8.0_331. which is available in Oracle patch 19.15.
If the Oracle patch 19.15 is not available, the recommended workaround is to grant the following: exec dbms_java.grant_permission( '<username>, 'SYS:java.lang.reflect.ReflectPermission', 'suppressAccessChecks', '' );
Can I use JChem Oracle Cartridge in cloud environment?
In Amazon environment, JChem Oracle Cartridge requires an Oracle database installed on an EC2 instance, it does not work with Amazon Oracle RDS.
The jchem-server has to be installed on the same or on a separate EC2 instance.
JChem Oracle Cartridge is tested in Amazon environment. Other cloud provider's environment was not tested, but we see no reason why they shouldn't work.