Improving Performance

Improving Performance of Dynamic SQL with Bind Variables

When you code INSERT, UPDATE, DELETE, and SELECT statements directly in PL/SQL,
PL/SQL turns the variables into bind variables automatically, to make the statements work efficiently with SQL. When you build up such statements in dynamic SQL, you need to specify the bind variables yourself to get the same performance.

 In the example below, Oracle opens a different cursor for each distinct value of emp_id. This can lead to resource contention and poor performance as each statement is parsed and cached.  

CREATE PROCEDURE del_employee (emp_id NUMBER) AS 
BEGIN 
EXECUTE IMMEDIATE 'DELETE FROM emp WHERE empno = ' || TO_CHAR(emp_id); 
END;

You can improve performance by using a bind variable, which allows Oracle to reuse the same cursor for different values of emp_id  

CREATE PROCEDURE delt_employee (emp_id NUMBER) AS 
BEGIN 
EXECUTE IMMEDIATE 'DELETE FROM emp WHERE empno = :num' USING emp_id; 
END; 

----