[OCI] Invoke a stored procedure taking array parameters

MLVJ

Hello!

I currently have an OCI application that makes thousands of stored procedure calls per second. The stored procedure performs some business logic of which I want my client application to remain unaware.  I want to reduce database chat, and instead of calling the stored procedure thousands of times per second, call the stored procedure (say) once per second, but with a thousand values.

I have used OCIBindArrayOfStruct successfully to perform bulk inserts, where my OCI application performs an INSERT INTO () operation using the "iters" function, and it might be possible to do something similar with the stored procedure, but I want to do something slightly different, because the stored procedure does something like this:

* If a particular condition is set, call a secondary stored procedure

* If another particular condition is set, insert a row into a certain table

What I was therefore wanting to do was to pass an array (or a series of arrays) into the stored procedure, and then for the stored procedure to use whatever optimal methods are available, such as FORALL, to perform each of the tasks in the most performant manner.

I was thinking therefore of creating a type, e.g. as a TABLE OF VARCHAR2, or as a VARRAY, and having parameters to the stored procedure defined according to that type, something like:

create or replace TYPE "NTT_VARCHAR2" AS TABLE OF VARCHAR2(32700);

create or replace FUNCTION           bulk_process_values (

      p_error_message             OUT NOCOPY VARCHAR2,

      p_module_context_name       OUT NOCOPY VARCHAR2,

      p_sql_error                 OUT NOCOPY VARCHAR2,

      p_count                      IN NUMBER,

      p_non_array_var_1            IN VARCHAR2,

      p_non_array_var_2            IN VARCHAR2,

      p_array_var_1                IN NTT_VARCHAR2,

      p_array_var_2                IN NTT_VARCHAR2,

      p_array_var_3                IN NTT_VARCHAR2

      <etc. for all array variables>)

    RETURN NUMBER

...but I am struggling to find an example of how to call the "bulk_process_values" stored procedure, and indeed whether I should be using a TABLE OF VARCHAR2, or a VARRAY as the parameter type.

I have searched the Internet, and found this: - and Binding array of structs (OCIBindArrayOfStruct) example code

- but they take different approaches, and a little confusing (the second example introduces talk of the OTT).

I think that the first approach is more likely to be the solution that I need - but I cannot help thinking - this is an awful lot of stuff to go through if it does not end up significantly improving the performance!

My questions therefore are:

* Is moving from a call-SP-thousands-of-times-per-second to call-SP-fewer-times-but-with-array-parameters sensible?

* Should the array parameters be VARRAY, TABLE OF VARCHAR2, or something else?

* Do I need to use the OTT for my case?

* The examples I found are both quite old - and the second example hints that the optimal approach evolves with OCI releases - I am on 11.2, what is the optimal approach these days?

* <added afterwards> Why are there no useful official examples?!

Thank you very much

Much appreciated. https://community.oracle.com/message/4528528#4528528 did in fact work, with VARRAY, with a proof of concept. Now I just need to shake it about a little bit and then get my PL/SQL experts to provide optimal SP. I think I was really daunted by the lack of official documentation, with the (really fabulous) community being the only way to get things done.

MLVJ

Thank you DD.

I was considering using array binding and array execution, and thereby avoid N round trips, but I do not then get the opportunity to do anything clever in the PL/SQL.

It is in the PL/SQL that there is logic occurring - I do call the same SP tons of times, but I then want to be able to use PL/SQL optimisations such as FORALL, BULK COLLECT, reduce the number of commits, and the like.  There is a commit after each SP run, because otherwise the nature of the application means that application deadlocks occur; however, there is a way (I think) to be smart about the part that is likely to deadlock (performing a sort on the array first, then applying the updates in the sorted order, and then cannot get a deadlock theoretically), and then do FORALL insert for the second part of the stored procedure.

I suppose I could implement the single round trip, passing in an array (and put a commit into the SP) - and that will reduce round trips, so boost performance, but it will not reduce the number of commits that I am doing - and Oracle tells me that too frequent commits are not good for performance. I already have code in my framework which does this - when doing a simple INSERT INTO.

Good to hear that OTT not explicitly needed - and VARRAY sounds like the approach to try first.

ddevienne

Yes, changing your PL/SQL procedure to table collections is sensible, and has the potential for the best performance in the end, but it's not the only solution.

You apparently already have a procedure that takes scalars instead of collections, so instead of calling it N times, you could be using a single round-trip using array-execution and array-binding, rather than the scalar binding of collections you outlined. That way you still avoid the N round-trips, don't have to change your procedure, and it's a little easier to code IMHO. If OTOH as you hint, you don't really call N times the same procedure, but it depends on conditional code client side, and you want to move the conditional code server-side in PL/SQL, then taking collections as inputs in your recoded procedure is the only solution indeed.

You don't need to use OTT for collections, no. Even for aggregate types you don't, because what OTT generates can be easily replicated manually, provided you don't have too many UDTs.

I've found issues with Nested Tables in OCI, in conjunction with the TABLE() SQL operator, so I've used VARRAYs instead, but that was in a different context, logically equivalent to a "WHERE IN Collection", so independent of PL/SQL. Really you should try both, and see which one is faster for you, and if you can only work it out for one, then that's your answer . --DD

MLVJ

Thank you DD.

I was considering using array binding and array execution, and thereby avoid N round trips, but I do not then get the opportunity to do anything clever in the PL/SQL.

It is in the PL/SQL that there is logic occurring - I do call the same SP tons of times, but I then want to be able to use PL/SQL optimisations such as FORALL, BULK COLLECT, reduce the number of commits, and the like.  There is a commit after each SP run, because otherwise the nature of the application means that application deadlocks occur; however, there is a way (I think) to be smart about the part that is likely to deadlock (performing a sort on the array first, then applying the updates in the sorted order, and then cannot get a deadlock theoretically), and then do FORALL insert for the second part of the stored procedure.

I suppose I could implement the single round trip, passing in an array (and put a commit into the SP) - and that will reduce round trips, so boost performance, but it will not reduce the number of commits that I am doing - and Oracle tells me that too frequent commits are not good for performance. I already have code in my framework which does this - when doing a simple INSERT INTO.

Good to hear that OTT not explicitly needed - and VARRAY sounds like the approach to try first.

ddevienne

Binding collection to right-hand-side of WHERE num_col in (:1) clause?

Gives some info on binding a VArray in a where clause, which should be no different from binding it for your stored proc.

We wrap OCI in our own C++ helper layer, so I can't share raw OCI code that demonstrate how it's done, but if you get stuck, and share you own code, I'll try to help.

Good luck.

PS: Regarding your latest comment, remove the commit from the SP, and do it explicitly after executing the N calls. Unless your SP uses an autonomous transaction (few SPs should), all your inserts are visible across calls, since in the same session, and you can explicitly commit whenever you want, from the client side. IMHO, it's always better to wrap the transaction explicitly on the client side, rather than "auto-commit" in your SP. That way you both avoid RTs and too many COMMITs. Yes, having an SP taking collections arg opens the doors to more performance and optimum server-side processing, so by all means, you should pursue that as well, but the array exec + explicit commit from the client side is simpler IMHO, and easier to implement. My $0.02

MLVJ

Much appreciated, DD.

There is a reason for having a commit in the SP - which is to reduce deadlocks - these thousands of transactions per second are distributed over several client applications running active-active, and multi-threaded, so it is possible for row A to be updated by application 1, then row B by application 1, then row B by application 2, then row A by application 2, resulting in your classic deadlock.

My thought about reducing commits was to wait until we get (say) a batch of 1000 updates, then sort the records, so any update will always do row A, then row B, and so on, and thereby avoid deadlocks.

So from your experience, a quick win will be to:

* Batch the updates <need to do this in any case for all solutions>

* Sort the updates in record order to avoid deadlocks <need to do this in any case for all solutions>

* Change the existing "execute SP once" to "execute SP many times, here is an array of struct"

* Handle row by row failures appropriately <need to do this in any case for all solutions>

Naturally, this is only dealing with the classic type of deadlock - we also get instant-fail deadlocks due to some sort of other issue, and we just immediately retry those transactions.