Almost every Oracle database uses PLSQL codes like packages/procedures/functions etc. Complex systems with complex codes pass many arguments (parameters) many times between plsql objects. Passing parameters might be the most executed operations in a database. So how well do you pass your parameters?
I assume you know about types of parameters like IN or OUT so I won’t explain it all over. Basically out let you modify the parameter inside a plsql block and return that value back to caller variable. You might think this is kind of a pointer operation but it is not. when you modify a parameter inside a block that new value won’t be affecting caller variable. it will store on a temporary variable and after plsql block completed modified value will be assign back to caller variable.
adding a NOCOPY to your in out variable actually make it a kind of pointer and whenever you modifed your parameter in plsql block it will affect the caller variable even if plsql block raise an exception! without nocopy, since data is copied to a new variable every procedure call, it will increase your memory consumption and timing. especially if you are using big variables like CLOB or PLSQL Collections (arrays).
So Let’s test different variable types, call them many times and see if there is a difference when we use NOCOPY.
I didn’t think about the sample code so it is a little bit messy. Basically, I am just using overloaded functions with different variable types and pass them to a procedure many times.
PS: I am setting plsql_optimize_level to 1 to prevent Oracle modify my sloppy code. Since I don’t actually run a business logic and Oracle can remove many of those code like assigning a dummy variable which is never used.
this is my package code:
I will test VARCHAR2, DATE, NUMBER, CLOB and a COLLECTION of Varchar2. Call every each of them with and without NOCOPY and check the timings.
My sp_main procedure gets 3 arguments, first is the tested type parameter, second is calling type, COPY or NOCOPY and third one is number of calls.
So big types like clob and collection has the biggest difference. What surprises me are NUMBER and DATE types. they have almost no difference when used NOCOPY.
Here are the results:
as you can see Varchar2 has almost twice of the difference and please consider, when you work with bigger variable data and more than one variable, difference will be much more than this.
As I said before, CLOB and the COLLECTION has the biggest difference. I mostly use NOCOPY with my collection and clob parameters even if I won’t be modifying them. Of course that requires a little bit more attention. Modifying by mistake it can cause headaches.
Using NOCOPY for those parameters is definitely increase your performance but also can cause some inconsistent situations. Let’s say you pass a collection using NOCOPY to a procedure and started to modify it in the procedure (in a loop maybe) after doing some job your plsql raise an unexpected error. In this case your variable will be partially modified and this is not something we desire. you must be careful using NOCOPY against this kind of situations. When you did not use a NOCOPY, you won’t be dealing with this and your collection (or other type of variable) will be consistent.
I hope this help you increase your codes performance.
I wish you healthy days.
Since number is one of the smallest type it is not much possible to demonstrate performance gain with just one parameter. I just added 15 more number parameter to my test procedures and result is like this: