Databases Reference
In-Depth Information
There's more...
Many DBAs recommend the use of
NOCOPY
when passing large objects to functions/
procedures, to obtain a performance gain, when there is no need to copy the value in and out
from the function. In our test, the results indicated a small dip in performance (13ms after 10
million executions, the number of loop iterations), and executing the same script many times
leads to an average value that tends to zero (so there are no improvements nor worsening).
Even the example used in the Oracle documentation, about the use of
NOCOPY
, shows the
same timing with or without the use of
NOCOPY
.
The reason is that
NOCOPY
is a hint to the PL/SQL engine, that "suggests" the use of the
call by-reference for the parameter. This can lead to a theoretical performance gain, but not
in all situations.
There are two issues associated with the use of
NOCOPY
:
F
When we pass a parameter by reference, if we make changes to the value of the
parameter inside the function, the changes are made to the copy only, leading to
possible side-effects.
We say that a function has side-effects when some state—for example, a
variable—external to the function is changed by the function itself; the
behavior of the function in these cases depends on history, so the order
of evaluation matters.
F
The other issue is related to exceptions. If the function fails, the value of the original
parameter can be in an inconsistent state, as the function was interrupted but the
changes already made to the parameter would not be reverted.
Due to these considerations, the
NOCOPY
hint should be used with extreme care after
analyzing if there would be performance gain for each particular situation.
Using short-circuit IF statements
In this recipe, we will see how the order in which we evaluate a compound
IF
statement of
more than one condition, may affect performance.
How to do it...
The following steps will demonstrate compound
IF
statements:
1.
Connect to the
SH
schema:
CONNECT sh@TESTDB/sh
