Design Considerations – Enhanced Bulk Binding in PL/SQL

The Bulk Binding is relatively old technology,
but unfortunatelly rarely used by developers.
Basically the Bulk Binding is buffer technology.
Whenever you want to execute an SQL Statement or Fetch statement in loop,
please consider the Bulk Binding.
When you communicate withmthe SQL Engine from your PL/SQL program,
then Oracle creates a - so called - context switch.
This consumes pretty amount of time in loop.
We can reduce it applying the buffer technology.
Lets see the following example.
1. We create a relatively big table (ca.1 200 000 rows)

CREATE TABLE big_emp(empno,last_name,first_name,salary)
AS
SELECT ROWNUM,E.last_name||ROWNUM,E.first_name||ROWNUM,E.salary
FROM employees E,employees D,employees F;

2. Run this procedure, which uses the traditional technology:
CREATE OR REPLACE PROCEDURE trad_fetch_open
IS
CURSOR C1 IS SELECT * FROM BIG_EMP;
S NUMBER:=0;
n1 number;
n2 number;
EMP2 BIG_EMP%ROWTYPE;
BEGIN
  n1:=dbms_utility.get_cpu_time;
  OPEN c1;
  LOOP
    FETCH c1 INTO EMP2 ;
    EXIT WHEN c1%NOTFOUND ;
    S:=S+ EMP2.SALARY;
  END LOOP;
  CLOSE c1;
  n2:=dbms_utility.get_cpu_time;
  DBMS_OUTPUT.PUT_LINE('diff:'||to_char((n2-n1)/100)||'S:='||S);
END;
/
EXEC trad_fetch_open __will use more than 15 seconds CPU time!

3. We can use explicit Bulk Binding with the following procedure.
   Observe that we can control the size of the buffer with the LIMIT clause!
CREATE OR REPLACE PROCEDURE
BULK_LIMIT(tname VARCHAR2,rows NUMBER := 10)
IS
type emp_cur_type IS REF CURSOR ;
type c_type is table of  BIG_EMP%rowtype;
c1 emp_cur_type; j number:=0; emp c_type;
S NUMBER:=0;
n1 number;
n2 number;
stmt CLOB;
BEGIN
  stmt:='SELECT * FROM '||tname;
  n1:=dbms_utility.get_cpu_time;
  OPEN c1 FOR stmt;
  LOOP
      FETCH c1 BULK COLLECT INTO EMP LIMIT rows;
      EXIT WHEN c1%NOTFOUND and emp.count=0;
      FOR I IN 1..EMP.COUNT LOOP
        S:=S+ EMP(I).SALARY;
      END LOOP;
  END LOOP;
  CLOSE c1;
  n2:=dbms_utility.get_cpu_time;
  DBMS_OUTPUT.PUT_LINE('diff:'||to_char((n2-n1)/100)||'S:='||S);
END;
/
EXEC BULK_LIMIT('BIG_EMP',1000)
We can see that with relatively small buffer size(for example with 1000)
we can achieve very good result.
If we increase the size above 1000 the performance can be worse
according to enlarged memory handling!

4. In Oracle 10g R2 and later we can use implicit Bulk Binding.
Using the traditional FOR rec_var IN cursor_name LOOP statement
we can achieve the Bulk Binding with appropriate
setting of PLSQL_OPTIMIZE_LEVEL.
Consider the following example:
a. ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL =2;
   Note this is the default value of this parameter in Oracle 10g and later.
b. Create the following procedure:
CREATE OR REPLACE PROCEDURE bulk_fetch_for
IS
CURSOR C1 IS SELECT * FROM BIG_EMP;
S NUMBER:=0;  n1 number;   n2 number;
BEGIN
 n1:=dbms_utility.get_cpu_time;
 FOR EMP2 IN C1 LOOP
   S:=S+ EMP2.SALARY;
 END LOOP;
 n2:=dbms_utility.get_cpu_time;
 DBMS_OUTPUT.PUT_LINE('diff:'||to_char((n2-n1)/100)||'S:='||S);
END;
/
c. EXEC bulk_fetch_for -- will use less than 2 seconds CPU time!
   This is called AUTOBULKIFICATION!
d. Issue the following statement:
   ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL =1;
e. Recompile the bulk_fetch_for procedure!
   ALTER PROCEDURE  bulk_fetch_for COMPILE;
f. EXEC bulk_fetch_for -- will use more than 15 seconds CPU time!

g. If we use the traditional OPEN FETCH technology we will get poor performance
   regardless the value of the PLSQL_OPTIMIZE_LEVEL parameter
CREATE OR REPLACE PROCEDURE trad_fetch_open IS
CURSOR C1 IS SELECT * FROM BIG_EMP;
S NUMBER:=0;
n1 number;
n2 number;
EMP2 BIG_EMP%ROWTYPE;
BEGIN
  n1:=dbms_utility.get_cpu_time;
  OPEN c1;
  LOOP
    FETCH c1 INTO EMP2 ;
    EXIT WHEN c1%NOTFOUND ;
    S:=S+ EMP2.SALARY;
  END LOOP;
  CLOSE c1;
  n2:=dbms_utility.get_cpu_time;
  DBMS_OUTPUT.PUT_LINE('diff:'||to_char((n2-n1)/100)||'S:='||S);
END;
/
h. EXEC trad_fetch_for -- will use more than 15 seconds CPU time!
The conclusion is:
- Use the explict Bulk Binding if You want specify the size of the buffer.
- Use the implicit Bulk Binding if You want to rely on the Oracle
  to rewrite your program to improve the performance.

Adaptive Cursors in Oracle 11g

Up to Oracle 1og we have an algorythm, called Bind Peeking.

Briefly, this means the following:

• In SQL statement we use a bind variable with not null value.
• First time, when SQL statement was executed, the Oracle will apply Hard Parsing to establish the best execusion plan for this statement, using (“peeking”) the value of the bind variable.
• Next time,when we use the same SQL, but with differernt bind value, the same execution plan will be used(called Soft Parsing)
• This is very good for OLTP systems, where the data is dispersed approximately evenly.

In OLAP environment the Bind Peeking typically is not optimal, because there is no guarantee, that the data-according to the bind variable-is spreaded approximately evenly.

In Oracle 11g we can ask the server to see the subsequent values of the bind variable to determine whether the previous plan was optimal or Oracle has to create a new, better execution plan using relevant statistics (typically histograms).

Let’s see the following case study.

• First, we create a simple table with an index for the record_type column.
• Second, we populate it with the natural numbers from 1 to 10000.
• Third, we modifiy the even numbers for the number 2. So, the half part of the table contains the same values and the other half part of the table contains different (odd) values.

CREATE TABLE ac_test
(id NUMBER, record_type NUMBER);
CREATE INDEX ac_test_ix ON ac_test(record_type);

DECLARE
TYPE test_tab_type IS TABLE OF ac_test%ROWTYPE INDEX BY PLS_INTEGER;
test_tab test_tab_type;
BEGIN
FOR i IN 1 .. 100000 LOOP
test_tab(I).id := i;
test_tab(I).record_type := i;
END LOOP;
FORALL i IN 1 .. test_tab.COUNT
INSERT INTO ac_test VALUES test_tab(i);
UPDATE ac_test SET record_type =2 WHERE MOD(record_type ,2)=0;
COMMIT;
END;
/

• We clear the Shared Pool and make this feature enabled.
• Using the DBMS_STATS package we create statistics (including histograms)

ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SESSION set “_optimizer_adaptive_cursor_sharing”= TRUE;
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(USER, ‘ac_test’,
method_opt=>’for all indexed columns size skewonly’, cascade=>TRUE);
END;
/

• Now we create and populate a bind variable with 1 and execute an SELECT statement
• Examine the execution plan
• Observe that that cursor is Bind Sensitive, but not Bind Aware

VARIABLE v_rec_type NUMBER;
EXEC :v_rec_type := 1
SELECT MAX(id) FROM ac_test WHERE record_type = :v_rec_type;
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor);
COL IS_BIND_SENSITIVE FORMAT A20
COL IS_BIND_AWARE FORMAT A20
SELECT sql_id, child_number, is_bind_sensitive, is_bind_aware
FROM v$sql WHERE sql_text =
‘SELECT MAX(id) FROM ac_test WHERE record_type = :v_rec_type’;

The result is similar to the following:

-------------------------------------------------------------------------
| Id | Operation                        | Name       |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT
| 1 | SORT AGGREGATE
| 2 | TABLE ACCESS BY INDEX ROWID       | AC_TEST    |
|* 3 | INDEX RANGE SCAN                 | AC_TEST_IX |
-------------------------------------------------------------------------


SQL_ID           CHILD_NUMBER    IS_BIND_SENSITIVE    IS_BIND_AWARE
43ydhqbsh1t4x    0                               Y                N

• Now we can repeat the previous process  with the value 2 of the bind variable

• We can observe  that the execution plan is the same.

• Repeat the process again with the value 2 of the bind variable!

• We can observe  that the execution plan is different!  

   

  ----------------------------------------------
  | Id  | Operation                  | Name    |
  ----------------------------------------------
  |   0 | SELECT STATEMENT           |         | 
  |   1 |  SORT AGGREGATE            |         | 
  |*  2 |   TABLE ACCESS FULL        | AC_TEST |

   

  
  SQL_ID        CHILD_NUMBER       IS_BIND_SENSITIVE    IS_BIND_AWARE
  ------------- ------------        ----------------    --------------
  43ydhqbsh1t4x            0                         Y               N
  43ydhqbsh1t4x            1                         Y               Y
  

   

   

   

   

   

   

   

   

   

SQl Injection in PL/SQL Program

 

SQL injection is a technique for maliciously exploiting applications that use client-supplied data in SQL statements.

Let’s  consider the following example.

Developer wanted to allow for the End Users to select data  

from an arbitrary (employees’s like) table for ‘IT_PROG’ job category.

CREATE OR REPLACE PROCEDURE  SQL_INJ(tname VARCHAR2) IS
   type emp_cur_type IS REF CURSOR;
   c1 emp_cur_type; j number:=0;
   type c_type is table of  employees%rowtype;
   emp c_type;
stmt varchar2(200);
BEGIN
  stmt:='SELECT * FROM '||tname||Q'# WHERE JOB_ID='IT_PROG'#';
  OPEN c1 FOR stmt;
    FETCH c1 BULK COLLECT INTO emp;
    FOR I IN 1..emp.COUNT LOOP
    dbms_output.put_line
    (emp(I).LAST_NAME||' salary :'||EMP(I).SALARY);
    END LOOP;
CLOSE c1;
END SQL_INJ;
/
With the following SQL*Plus command
we will get the suitable result:
EXECUTE SQL_INJ('EMPLOYEES')

However, if we issue the next command:
EXEC SQL_INJ(Q'#EMPLOYEES WHERE JOB_ID='AD_VP' --#')
In this case we could rewrite the intended SQL statement
and selected the AD_VP's data!

Using the DBMS_ASSERT package (introduced in Oracle 11g)
we can avoid the vulnerability of our program.
For example, using the  SQL_OBJECT_NAME function
we can achieve the desired behavior:

CREATE OR REPLACE PROCEDURE  SQL_INJ2(tname VARCHAR2) IS
   type emp_cur_type IS REF CURSOR;
   c1 emp_cur_type; j number:=0;
   type c_type is table of  employees%rowtype;
   emp c_type;
stmt varchar2(200);
BEGIN
  stmt:='SELECT * FROM '||DBMS_ASSERT.SQL_OBJECT_NAME(tname)||
        Q'# WHERE JOB_ID='IT_PROG'#';
  OPEN c1 FOR stmt;
      FETCH c1 BULK COLLECT INTO emp;
      FOR I IN 1..emp.COUNT LOOP
      dbms_output.put_line
      (emp(I).LAST_NAME||' salary :'||EMP(I).SALARY);
      END LOOP;
CLOSE c1;
END SQL_INJ2;
/

Let's issue again this command:
EXEC SQL_INJ2(Q'#EMPLOYEES WHERE JOB_ID='AD_VP' --#')

ERROR at line 1:
ORA-44002: invalid object name
ORA-06512: at "SYS.DBMS_ASSERT", line 293
ORA-06512: at "HR.SQL_INJ2", line 8
ORA-06512: at line 1

Using the following command we get the proper result:
EXEC SQL_INJ2(Q'#EMPLOYEES#')