Bind Variable

- Bind Variable is one of the major cause of performance issues and a major inhabitor of scalability in the performance problem
- Bind variable is one of the placeholder in a query

- Example 1 : No bind variable
  SELECT * FROM emp WHERE empno = 123;

- In the above example, the query employee 123 maybe once and then never again. Later, you may query the employee 456, than 786 and so on. If we use literals in the query then each and every quey is a brand new query, in the oracle shared pool. It will have to be parsed, qualified , securyt checked, optimized, and so on. In short each and every unique statement we execute will have to be compiled every time it is executed.

 
- Example 2 : By using bind variable
  SELECT * FROM emp WHERE empno = :empno;

- In the above example, query uses bined variable :empno, the value which is supplied at query execution time. Thiw query is compiled once and then query plan is stored in a shared pool fron which it can be retrieved and reused.

- The difference between the two in terms of performance and scalability is huge, dramatic even. By parsing a state,emt wotj jard-coded variables(called a
hard parse
) will take longer and consume many more resources than reusing an already parsed query plan(called a
soft parse
).

SQL> alter system flush shared_pool;
SQL> set serveroutput on;

declare
      type rc is ref cursor;
      l_rc rc;
      l_dummy all_objects.object_name%type;
      l_start number default dbms_utility.get_time;
  begin
      for i in 1 .. 1000
      loop
          open l_rc for
          'select object_name
             from all_objects
            where object_id = ' || i;
          fetch l_rc into l_dummy;
          close l_rc;
          -- dbms_output.put_line(l_dummy);
      end loop;
      dbms_output.put_line
       (round((dbms_utility.get_time-l_start)/100, 2) ||
        ' Seconds...' );
  end;
/
101.71 Seconds...

... and here is the Performance Winner:

declare
      type rc is ref cursor;
      l_rc rc;
      l_dummy all_objects.object_name%type;
      l_start number default dbms_utility.get_time;
  begin
      for i in 1 .. 1000
      loop
          open l_rc for
          'select object_name
             from all_objects
            where object_id = :x'
          using i;
          fetch l_rc into l_dummy;
          close l_rc;
          -- dbms_output.put_line(l_dummy);
      end loop;
      dbms_output.put_line
       (round((dbms_utility.get_time-l_start)/100, 2) ||
        ' Seconds...' );
end;
/
1.9 Seconds...

- By concluding with the above example, one bad apple may spoil the entire barrel.
- By using the bind variable we can increases the preformance and greatly increses the scablability also.