A STATIC SQL vs DYNAMIC SQL with BIND Variables in Oracle

Not long ago I did note that quite many developers are using in Oracle database packages SQL statements as the dynamic SQL. They have learned in somewhere that the bind variables will make the query run faster and save some perfomance. To make clear some basics about Oracle database I wrote the following examples. Please don’t understand me incorrectly the bind variables are fast with dynamic SQL but only in external programming languages like ProC, C or Java. To store your code into a database the dynamic SQL is needed only in some extreme circumnstanses. To keep your queries as a static way in your prodecures and packages will save the compiling time before executing and any missing column or table will be raised as an Oracle exception during you are compiling the code.

To make sure all SQL queries have the same “starting point” and none of them will be cached empty the share_pool. Do it before running any of the following queries. You may need to use your SYS user to execute it.

 ALTER SYSTEM FLUSH SHARED_POOL;

The first PL/SQL anonymous block will be executed 50000 times with SQL query “SELECT rownum INTO v_cnt FROM dual WHERE rownum = 1;” and to make sure we will get the closest timing we will execute the block 5 times. To measure the execution time we are using SYSDATE and not some fancy procedure that can have bugs. In other words we will keep the block as simple as possible.

DECLARE 
  v_starttime DATE; 
  v_endtime DATE; 
BEGIN 
  v_starttime := SYSDATE; 
  FOR i IN 1..50000 
  LOOP 
    DECLARE 
     v_cnt PLS_INTEGER; 
    BEGIN 
      SELECT rownum 
        INTO v_cnt 
        FROM dual 
       WHERE rownum = 1; 
    END; 
  END LOOP; 
  v_endtime := SYSDATE; 

  dbms_output.put_line('time in seconds:'||((v_endtime - v_starttime) * 86400)); 
END;

The output shows execution time in seconds as 4, 4, 4, 5 and 4 and that makes in average total: 4.2.

The second code is written as a dynamic SQL query without the bind variables and we will execute the same statement as above.

DECLARE 
  v_starttime DATE; 
  v_endtime DATE; 
BEGIN 
  v_starttime := SYSDATE; 
  FOR i IN 1..50000 
  LOOP 
    EXECUTE IMMEDIATE 'DECLARE v_cnt PLS_INTEGER;  
                       BEGIN SELECT rownum 
                               INTO v_cnt 
                               FROM dual 
                              WHERE rownum = '|| 1 ||';  
                       END;'; 
  END LOOP; 
  v_endtime := SYSDATE; 

  dbms_output.put_line('time in seconds:'||((v_endtime - v_starttime) * 86400)); 
END;

The SQL output is very different from the first anonymous block and the times are 9, 9, 10, 9 and 9. That makes the average total: 9.2.

The third anonymous block is a dynamic sql with the bind variables and the query statement is same as previous examples.

DECLARE 
  v_starttime DATE; 
  v_endtime DATE; 

BEGIN 
  v_starttime := SYSDATE; 
  FOR i IN 1..50000 
  LOOP 
    DECLARE 
      v_cnt PLS_INTEGER; 
    BEGIN 
      EXECUTE IMMEDIATE 'SELECT rownum 
                           FROM dual 
                          WHERE rownum = :v' INTO v_cnt USING 1; 
    END; 
  END LOOP; 
  v_endtime := SYSDATE; 

  dbms_output.put_line('time in seconds:'||((v_endtime - v_starttime) * 86400)); 
END;

The third output shows 4, 5, 4, 4 and 5 and the average is 4.4. The last average is not so far from the first anonymous block with a static SQL but still in long run it will make some difference and even less sense makes to write all your queries dynamically into the database procedures or packages.

The following group of examples are done with Oracle INSERT statement and to try them on your database you would need to create a new table so it wouldn’t break anything existsing.

 CREATE TABLE my_oracle_test 
   (id   NUMBER(17) NOT NULL,  
    text VARCHAR2(2000));

The first insert is done to make sure the table has needed tablespace size taken and the INSERT statements would NOT loose any time by extending the tablespace. Once the insert is done we will delete all lines using TRUNCATE TABLE command and don’t worry about the COMMIT the TRUNCATE TABLE statement does commit.

BEGIN 

FOR i IN 1..150000 
LOOP 
  INSERT INTO my_oracle_test (id, text) 
  VALUES (i, 'This is My Row '||i); 
END LOOP;

END; 
/

TRUNCATE TABLE my_oracle_test;

Now we are ready to execute the first INSERT examples and this is static SQL inserting into just created my_oracle_test table and it does it 150000 times. We are keep measuring the time as on the example above and after the anonymous block is done it will truncate the table data. Once again I am running the test 5 times to get the average time.

DECLARE 
  v_starttime DATE; 
  v_endtime DATE; 
BEGIN 
  v_starttime := SYSDATE; 
  FOR i IN 1..150000 
  LOOP 
    INSERT INTO my_oracle_test (id, text) 
         VALUES (i, 'This is My Row '||i); 
  END LOOP; 
  v_endtime := SYSDATE; 

  dbms_output.put_line('time in seconds:'||((v_endtime - v_starttime) * 86400)); 
END; 
/ 

TRUNCATE TABLE my_oracle_test;

The output shows 15.99, 16.99, 14.99, 15.99 and 16.99 and that makes the average 16.19.

The second INSERT anonymous block is with a dynamic SQL and WITHOUT the bind variables. The insert statement will stay the same and we will do truncate on the rows after the block is done.

DECLARE 
  v_starttime DATE; 
  v_endtime DATE; 
BEGIN 
  v_starttime := SYSDATE; 
  FOR i IN 1..150000 
  LOOP 
    EXECUTE IMMEDIATE 'INSERT INTO my_oracle_test (id, text) 
                       VALUES ('||i||', ''This is My Row ''||'||i||')'; 
  END LOOP; 
  v_endtime := SYSDATE; 

  dbms_output.put_line('time in seconds:'||((v_endtime - v_starttime) * 86400)); 
END; 
/ 

TRUNCATE TABLE my_oracle_test;

The second insert is way slower than the static SQL and the times are 107, 107, 105, 105 and 105. The average in seconds is 105.8.

The third INSERT is a dynamic SQL and with the bind variables. The rest of code has left as on the previous INSERT examples above.

DECLARE 
  v_starttime DATE; 
  v_endtime DATE; 
BEGIN 
  v_starttime := SYSDATE; 
  FOR i IN 1..150000 
  LOOP 
    EXECUTE IMMEDIATE 'INSERT INTO my_oracle_test (id, text) 
                       VALUES (:v, ''This is My Row ''||:v)' using i, i; 
  END LOOP; 
  v_endtime := SYSDATE; 

  dbms_output.put_line('time in seconds:'||((v_endtime - v_starttime) * 86400)); 
END; 
/ 

TRUNCATE TABLE my_oracle_test;

The SQL output shows times as 15.99, 16.99, 16.99, 15.99 and 15.99. The average is 16.36 and again the timing is not so far from the static SQL (16.19) but we cannot say that a dynamic SQL with the bind variables is quicker than a static SQL.

In short, you should keep your code in database as static and don’t write it as dynamic unless this is something extreme and can’t be written any other way. When you are using C or Java application USE bind variables calling out the database procedure or in SQL queries.

See Also:
Oracle Select Oracle Substr Oracle Instr Home