Oracle init.ora parameters

Hello,





I see from your website the quote:


"Inserting 10000 structures into a JChem table: 7 minute 4 seconds


jc_insert('<structure>', 'mytable', null, 'false', 'false');"





I wonder if it would be possible to provide the Oracle init.ora parameter file that was used for this test?





Currently, our system is approx an order of magnitude slower than this specified value and we would very much like to trouble shoot our way to the rates above! We are using the jc_equals and jc_insert (dups check false) functions in our pl/sql build and so perhaps we might expect a halving in your estimated time but currently we are at about 2 molecules processing a second. I will be running statspack and possibly tkprof tomorrow in order to help determine bottle necks, waits...etc...although looking in v$session_waits I notice that "sqlnet message from client" is causing a large amount of wait time - we would really appreciate any help in isolating the Oracle parameters that we may need to alter to get our system working faster.





Thanks for your support,


Daniel.

Our server specification is at least equivalent to the one you state and I'm sure these settings are at at least matched...





Oracle Version DB Buffer Cache Size (MB) Shared Pool Size (MB) Java Pool Size (MB) SGA Max Size (MB) Large Pool Size (MB)


9.2.0.7.0 1024 160 160 1385 16

Hi Daniel,





You can find attached the init parameters.





How long does it take to insert 10k on you machine?





How isolated your tests have been so far? Have you been testing with a test table? Is your test table serving other test purposes as well? Any "custom" indexes created on the test table? (An additional structural index from a competing database product in particular is capable, for example, of significantly slowing down the insert.) Have you tried on another, pristine machine?





Cheers,


Peter

Hi Peter,





I think we have essentially determined that the cause of our problem is an old 2.4 linux kernel which gives poor context change performance between Oracle (http) and tomcat and so we are going to migrate to w2003 server and possibly a 2.6 kernel version (in order to leave our options open. My laptop running xp gives double the write performance of one of our Oracle servers.





Any esoteric Oracle parameters that you can comment upon that will squeeze that last drop of performance out of Oracle would be really helpful. Also do you think the nologging/nocache option would yield any improvement on a jchem table jc_insert?





Cheers,


Daniel.

Thanks for the parameters.





Also I would be interested to here your comments on attempting to use the jchem api in java stored procedures...would the searcher object implemented as a java stored procedure call out to tomcat (we guess not) also, do you think Oracle 10g jvm might show a marked improvement over Oracle 9i.





Thanks for the support,





Cheers,


Daniel.

Hi Daniel,

Quote:

Also do you think the nologging/nocache option would yield any improvement on a jchem table jc_insert?

I did not experiment with these ones. Let us know if you find these options give a significant improvement.

Quote:

Also I would be interested to here your comments on attempting to use the jchem api in java stored procedures...would the searcher object implemented as a java stored procedure call out to tomcat (we guess not)

You're apparently aspiring for a position as an apprenti sorcier. :-) Please, read the JChem Cartridge docs (http://www.chemaxon.com/jchem/doc/guide/cartridge/index.html#archi_highlevel). You will probably find there about 90% of the answer to this question. For the remaining 10%, please, ask more specific questions.

Quote:

do you think Oracle 10g jvm might show a marked improvement over Oracle 9i

The JVM of 10gR1 is more stable but somewhat slower than 9i's. 10gR2's is currently less stable (we hope it will improve).





Cheers,


Peter