Ecto Oracle adapter

See if this look interesting oranif
An ecto wrapper is also possible. Please let me know if that would be useful.
TIA

4 Likes

Welcome on-board, I will check the library out. Thanks.

Hi, and welcome to the forum.

Are you saying you can provide an Ecto adapter for Oracle? Some of us have been waiting for something like that for months or even years. As I said above, jamdb is the closest we have.

1 Like

Thanks,

I am new to elixir and learning. If ecto adapter is in demand I will give it a try and my learning exercise.

Oranif is pure erlang NIF wrapper of Oracle ODPI-C thin OCI wrapper. This deprecates erloci (I wrote a while ago) the old port driver to access directly OCI.

Oranif has has out performed in throughput of insert/select/update of erloci and Erlang ODBC quite significantly so far.

So if you are looking for performance and compiling NIFs in deps isn’t an issue then oranif should help.

I haven’t performance compare against jamdb or odbc though yet!

3 Likes

Thank you. Other than ETS I haven’t really used Erlang modules directly at all, but I’ll give oranif a try.

I don’t know if you already aware of this two resources:

Article series on latest MySQL Ecto adapter:

http://blog.plataformatec.com.br/2018/11/building-a-new-mysql-adapter-for-ecto-part-i-hello-world/

There’s a talk at this year Elixir conference as well, and:

Also, I’m not experienced in Elixir enough to help you with the code, but let me know if you need help testing.

2 Likes

I looked into postgres elixir driver and postgres ecto adapter together with ecto_sql.
If my understanding is correct so far, I need to first wrap oranif in elixir which will integrate eventually ecto Oracle adapter.
So if I manage to write a functional a elixir wrapper driver in the mean time I will let you know and you can try that out instead oranif Erlang calls.
Will much appreciate a code review and test when the driver is ready to use.
TIA

2 Likes

Some updates on oracle ecto adapter dev activity (using oranif)

I made some progress in implementing a basic DBConnect interface for ecto and managed to connect to an test oracle DB succesfully. It seems I can follow jamdb_oracle ecto adapter design for an oranif ecto adapter. It is, of course, not as simple as just replacing all :jamdb_oracle*.* calls with oranif but I am making progress slowly (while also learning elixir).

In the mean time, out of curiosity and in parallel, I thought I would just try to compare some basic performance between jamdb_oracle and oranif so wrote ora_bench. A priliminary result shows that:

JAMDB inserted 22186 rows in 29.338234 seconds # ~750 rows / second
JAMDB selected 22185 rows in 29.619194 seconds # ~750 rows / second

ORANIF inserted 14000000 rows in 67.489112 seconds  # ~210000 rows / second
ORANIF selected 13946410 rows in 115.466077 seconds # ~121000 rows / second

In each of the tests, I tried to insert into a varchar2(4000) column 10 decimal digits of an incremental counter till DB limits are reached.

In case of oranif I could successfully get ORA-01653: unable to extend table SCOTT.TEST by 1024 in tablespace SYSTEM out of DB so my test stopped due to system limits (as I hoped for).

However, for jamdb I got ORA-01000: maximum open cursors exceeded around 22K rows insert/select (what I wasn’t expecting)! Furthermore, to make the jamdb test better I wanted to use COMOFF; (auto commit off) but that made cursor overflow happen even much earlier (at barely around ~10K rows).

@Cruz when you used jamdb (with ecto) did you ran into any cursor overflow? Do you know what is the right way to close cursors with jamdb so I can avoid this overflow. All I am doing in my test is :jamdb_oracle.sql_query(ConnRef, "select ITEM from test"). Googling didn’t help to know what I might be missing!

@kodepett did you ever got around trying jamdb with your 18m rows use case? How did that perform for you?

This config will sort of show the basic setup I need to run these test.

I would like bring jabdb test to its best possible performance so the comparison with oranif happens on fair ground. I look forword to any advices to improve this test.

TIA for your comments.

5 Likes

I didn’t find that issue, but I didn’t try so many queries. I took a look directly into the github repo of jamdb, and found the issue was reported and supposedly fixed before:

But since you’re finding the issue with the current version, the fix might not apply to all scenarios. There are three changesets mentioned there. I took a quick look but it’s Erlang code. Perhaps they’ll be more useful to you.

2 Likes

BTW, the author of jamdb might appreciate knowing about the defect reappering in this version. Do you want me to open an issue, or do you prefer doing it on your own? He might also be able to help you on this project.

1 Like

Thanks a lot. The thing is, I am not sure if my code is correct. Though it quite simple still wondering if I did something silly. Will try get in touch with them.

1 Like

This is slightly off-topic but since this project is of interest to me, does anybody have a tried-and-tested Oracle XE 18 Docker container or a VMWare / VirtualBox VM? I’d like to test this library with Oracle XE 18 but I am not willing to use my [now secondary] Windows PC for it. I’m operating on a Mac and Oracle XE doesn’t work natively on it.

I am aware that Oracle themselves provide these:

But I was wondering if somebody could report success by using those, or other, images?

1 Like

Perhaps https://github.com/cbandy/travis-oracle/issues/25 can help you if you would like do some quick automated test in a CI like Travis. Currently I am using 11g for my driver tests I mentioned above but sometime in future I will also like to upgrade (or have in parallel) to18 etc.

Asked in jamdb

I am more interested in a home lab that can also host an Oracle XE 18c container or a VM. A CI/CD-oriented solution is for the moment not a priority since I am not working with clients that require Oracle. It’s more of a data integration and cross-DB transfer hobby pursuit.

A final update before I move on from performance tests. Added C (ODPI-C) to comparison and results are quite intersting!

JAMDBO	INSERT	22186 rows in 28.821038 seconds (769.7849050405471 rows/sec)
JAMDBO	SELECT	22185 rows in 28.516178 seconds (777.9794332887108 rows/sec)
ORANIF	INSERT	14000000 rows in 68.944727 seconds (203061.21452914012 rows/sec)
ORANIF	SELECT	13946410 rows in 114.605359 seconds (121690.73175714235 rows/sec)
ODPI-C	INSERT	13950000 rows in 6.853266 seconds (2035525.835419 rows/sec)
ODPI-C	SELECT	13946375 rows in 13.352952 seconds (1044441.334021 rows/sec)

native ODPI-C is ~10 times faster than oranif
oranif is ~260 times faster than jamdb_oracle.

I will however come back to oranif/jamdb comparison once I get some help form jamdb authors to fix/improve my jamdb part of the testing

3 Likes

@Cruz Thanks for the mysql tutorial links made quite a good progress following them with DBConnection implementation for Oracle. I don’t seem to understand the following though:

Following MyXSQL-III design it seems like MyXQL.prepare_execute prepares, executes and also fetches all rows (which is three different API calls in Oracle - prepareStmt, execute and fetch).

iex> {:ok, pid} = MyXQL.start_link([])
iex> MyXQL.prepare_execute(pid, "SELECT ?", [42], [])
{:ok, %MyXQL.Query{statement: "SELECT ? + ?", statement_id: 1},
%MyXQL.Result{columns: ["? + ?"], rows: [[5]]}}

The fetch from Oracle is sort of buffered as on every call it returns a few rows and a boolean indicating if EOT. Is that some how differently handled from ecto? This get-everything prepare_execute can’t be it, right?

TIA

Yes, that function suppose to work the way you’ve described:

https://hexdocs.pm/ecto_sql/Ecto.Adapters.SQL.Connection.html#c:prepare_execute/5

but, as you can see on the same page, there are other functions (and ways) to retrieve data. Perhaps, what you’re wondering is how the current drivers implement that particular function. Maybe someone else with more experienced on this can comment.

1 Like

Yup, that’s exactly what happens under the hood. Worth mentioning that an Ecto adapter is a pretty high level abstraction so it abstracts out these steps. Otherwise the Repo, which uses an Ecto adapter, would have to fetch until EOF, and that step might vary between data stores.

3 Likes

As per @Cruz’s suggestion, we have moved these posts into a dedicated thread :003:

Great job @c-bik, welcome :023: when you’re ready, feel free to create a dedicated thread for it in our #your-libraries-projects:libraries section (you could link to this thread for some background too :D)

2 Likes

@AstonJ Thanks for splitting the thread. I hope, eventually to come to address the original topic : “billion of recods from Oracle” but for now I am far from it. About the thread name should it rather be “Ecto Oracle Adapter”?

@wojtekmach and @Cruz thanks a lot for the inputs they lead me to prepare_stream/4.

Here is my understding about how prepare_execute should work (pseudo code) with oranif/Oracle-DPIC:

def prepare_execute(conn, query, params, opts) do
  stmt = dpiConn_prepareStmt(query)
  dpiStmt_execute(stmt)
  rows = []
  do
    {rowsFetched, isEOT} = dpiStmt_fetch(stmt)
    rows = rowsFetched ++ rows
 while !isEOT
 {:ok, query, rows}
end

To have that effect I need to implement the DBConnection callbacks handle_prepare/3 and handle_execute/4 correctly.

This API will prepare-execute-fetch all rows from table (if SQL is like so) so perhaps app developers are adviced not to call this for large tables.

The large number of rows are handled through the DBConnection stream interface APIs - related callbacks are handle_declare (instead of handle_execute/4 as next call after handle_prepare/3), handle_fetch/4 etc.

Is this understanding correct so far?

TIA

2 Likes