Thursday, May 22, 2014

How to increase performance of PL/SQL code using IN/OUT Parameters and NOCOPY directive?

While wring PL/SQL code, keep in mind that 
  • if I need any calculations, create a function. 
  • if any DB operations which does not return any values from it, create a procedure.

Following ways we can use the parameters passed and returned from functions and procedures:
  • All IN parameters of procedure are passed by pointer. Variable is not copied when program block is called.However PL/SQL block is not allowed to modify the variable value.
  • All OUT and IN/OUT parameters are passed by value. Variable is copied when program block is called, so the pl/sql code can modify a copy of the variable.When pl/sql block completes without error, the original variable is overwritten with the modified copy of variable. If the program block raises exception, the original value remains unchanged.
  • The OUT and IN/OUT parameters can be passed by reference if we use the NOCOPY directive for them.However its not available for function return value.
A function that returns variable as pass by value.

CREATE OR REPLACE FUNCTION func_VarIN(p1 IN VARCHAR2) RETURN VARCHAR2 IS
BEGIN
  RETURN p1;
END;
/
An empty procedure using the IN/OUT parameter.

CREATE OR REPLACE PROCEDURE prc_VarINOUT(p2 IN OUT VARCHAR2) IS
BEGIN
  NULL;
END;
/

An empty procedure using IN/OUT parameter with NOCOPY directive.

CREATE OR REPLACE PROCEDURE prc_VarINOUT_NOCOPY(p3 IN OUT NOCOPY VARCHAR2) IS
BEGIN
  NULL;
END;
/

PL/SQL Code Observations: 
  • Function calls perform similar to procedure calls with IN/OUT parameters.
  • NOCOPY directive reduces the copy overhead and causes the call to be twice as fast then the function or regular procedure.
  • using NOCOPY, bigger value of variable gives bigger boost on performance.
Using of procedures with NOCOPY rather than functions, it pefroms really good - upto 20 -25 times faster and gives us good practices for PL/SQL code clarity.