Oracle Static SQL vs Dynamic SQL and Bind Variables
  Home
 

Recently I did note that quite many people are using in Oracle database packages queries as the dynamic SQL. They have red in somewhere that the bind variables will make the query run faster and save some perfomance. To clear some basics about database I had to write the following examples that I did post here too. Don’t understand me incorrectly the bind variables are fast with dynamic SQL but only for an external programming languages like C or Java. To keep your code in database the dynamic SQL is needed only some extreme circumnstanses. To keep your queries in 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 error 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;

online tech support online computer help computer technician computer problems computer oracle dynamic sql oracle dynamic sql in oracle sql dynamic oracle sql dynamic in oracle sql database sql retirement planning retirement oracle bind variables bind oracle execute dynamic sql execute oracle

The first PL/SQL anonymous block will execute 50000 times 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;

online tech support online computer help computer technician computer problems computer oracle dynamic sql oracle dynamic sql in oracle sql dynamic oracle sql dynamic in oracle sql database sql retirement planning retirement oracle bind variables bind oracle execute dynamic sql execute oracle

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

The second query is a dynamic SQL query without the bind variables and we will execute the same query 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;

online tech support online computer help computer technician computer problems computer oracle dynamic sql oracle dynamic sql in oracle sql dynamic oracle sql dynamic in oracle sql database sql retirement planning retirement oracle bind variables bind oracle execute dynamic sql execute oracle

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 is the same as on the last two 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;

online tech support online computer help computer technician computer problems computer oracle dynamic sql oracle dynamic sql in oracle sql dynamic oracle sql dynamic in oracle sql database sql retirement planning retirement oracle bind variables bind oracle execute dynamic sql execute oracle

The 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 procedure or package.

The following group of examples are with Oracle Insert 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));

online tech support online computer help computer technician computer problems computer oracle dynamic sql oracle dynamic sql in oracle sql dynamic oracle sql dynamic in oracle sql database sql retirement planning retirement oracle bind variables bind oracle execute dynamic sql execute oracle

The first insert is done to make sure the table has needed tablespace size taken and the inser statements wouldn’t 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 truncate table 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;

online tech support online computer help computer technician computer problems computer oracle dynamic sql oracle dynamic sql in oracle sql dynamic oracle sql dynamic in oracle sql database sql retirement planning retirement oracle bind variables bind oracle execute dynamic sql execute oracle

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;

online tech support online computer help computer technician computer problems computer oracle dynamic sql oracle dynamic sql in oracle sql dynamic oracle sql dynamic in oracle sql database sql retirement planning retirement oracle bind variables bind oracle execute dynamic sql execute oracle

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

The next anonymous block is with a dynamic SQL and without the bind variables. The insert statement is the same and we will truncate 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;

online tech support online computer help computer technician computer problems computer oracle dynamic sql oracle dynamic sql in oracle sql dynamic oracle sql dynamic in oracle sql database sql retirement planning retirement oracle bind variables bind oracle execute dynamic sql execute oracle

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 other 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;

online tech support online computer help computer technician computer problems computer oracle dynamic sql oracle dynamic sql in oracle sql dynamic oracle sql dynamic in oracle sql database sql retirement planning retirement oracle bind variables bind oracle execute dynamic sql execute oracle

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 can’t say that a dynamic SQL with the bind variables is quicker than a static SQL.

In short, keep your code in database static and don’t write it as dynamic unless this is something extreme and can’t be written as static. 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 Count Oracle Group By Oracle Having Online Tech Support Home

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)

*