OCI*ML: Minor updates

A few minor updates to OCI*ML:

  • oraprefetch to set rows pre-fetched and buffered by OCI. This is set per-statement handle, with a default for newly-created handles set in .ocamlinit.
  • oradeqtime to set the number of seconds to wait on oradequeue operations. Set to -1 (the default) for indefinite wait. Raises Not_found if no message is received within the timeout period.
  • orabindexec changed to take a list of Col_value arrays
  • rows_affected populated in statement handles for DML
  • Included .ocamlinit for use with ocimlsh.

Using a representative test for pre-fetching, querying a 5000-row result set over a VPN connection from a 1G Debian VM on my Mac to a test database at corporate HQ about 20 miles away† (with a pre-warmed block buffer cache):

Prefetch Time (s) Rows/sec Speedup factor
1 94.93 52.6 1
10 20.41 244.98 4.65
50 8.50 588.24 11.17
100 7.10 704.23 13.37
500 4.84 1033.06 19.61
1000 5.99 834.72 15.85

This shows a nearly 20× improvement from 10 lines of code and a bit of experimental tuning! The tradeoff is obviously memory consumption; two copies of the rows will exist in memory when using orafetchall – one in the OCI cache, and one in a list in OCaml.

With some assistance on SO, I have also added a prompt to the toplevel, this is a feature I always use in SQL*Plus™ to make sure I don’t run the wrong command on the wrong database! Of course, unlike sqlplus, ocimlsh can have many open connections; I am not sure how to best represent that.


† As the crow flies; it’s ~36ms measured by ping and ~70ms measured by tnsping due to firewalls most likely.

About Gaius

Jus' a good ol' boy, never meanin' no harm
This entry was posted in Ocaml, OCIML, Oracle. Bookmark the permalink.

3 Responses to OCI*ML: Minor updates

  1. Pingback: OCI*ML: Support for RETURNING | So I decided to take my work back underground

  2. Pingback: OCI*ML: Bulk Operations | So I decided to take my work back underground

  3. Pingback: OCaml 4 beta | So I decided to take my work back underground

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s