Performance Comparison of dynamic SQL, variable binding, and static SQL

Performance Comparison of dynamic SQL, bind variables, and static SQL 1 the three stored procedures in the test example use three programming methods: dynamic SQL, bind variable, and static SQL. The specific content of the stored procedure is as follows: l dynamic SQL

create or replace procedure proc1 asbegin  for i in 1 .. 100000 loop    execute immediate 'insertinto t values (' || i || ')';    commit;  end loop;end proc1;

 

L bind variables

create or replace procedure proc2 asbeginfor i in 1 .. 100000 loop execute immediate 'insert into t values(:X)' using i; commit;end loop;end proc2;

 

L static SQL

create or replace procedure proc3 asbeginfor i in 1 .. 100000 loopinsert into t values(i); commit;end loop;end proc3;

 

2. The test procedure is as follows:

1)    drop table t purge;2)    create table t (x int);3)    alter system flush shared_pool;4)    set timing on5)    exec procxxx;6)    select count(*) from t;

 

In the above test process, because the tables during each test are all new and the sharing pool is refreshed, the test results are correct. This test is performed on a physical machine instead of a virtual machine. 3 Test Result 3.1 dynamic SQL the Proc1 stored procedure uses dynamic SQL, so that each insert statement will be hard parsed each time it is executed, this increases the hard parsing overhead of the shared pool. The following is the result in the v $ SQL view.

SQL _TEXT EXECUTIONS parse_call------------------------------------------------------------ ----------- BEGINproc1; END; 1 insertinto t values (99280) 1 1 insertinto t values (99310) 1 1 insertinto t values (99362) 1 1 insertinto t values (99377) 1 1 insertinto t values (99399) 1 1 insertinto t values (99474) 1 1 insertinto t values (99544) 1 1 insertinto t values (99592) 1 1 insertinto t values (99601) 1 1 insertinto t values (99634) 1 1 insertinto t values (99696) 1 1 insertinto t values (99746) 1 1 insertinto t values (99804) 1 1 insertinto t values (99807) 1 1 insertinto t values (99859) 1 1 insertinto t values (99861) 1 1 insertinto t values (99930) 1 1 insertinto t values (99936) 1 1 insertinto t values (99956) 1 1 proc1 stored procedure, used for 24.25 seconds in this test. The results are as follows: SQL> exec proc1; PL/SQL procedure successfully completed. elapsed: 00:00:24. 25

 

3.2 The Proc2 stored procedure uses the Bind Variable. In this way, the overhead of hard parsing will be reduced during execution and the competition for sharing pool will be reduced. The results in the v $ SQL view are as follows:

SQL_TEXT                                          EXECUTIONS PARSE_CALLS------------------------------------------------------------ -----------BEGIN proc2;END;                                          1           1insert intot values (:X)                             100000           0

 

The insert statement is not parsed once and executed once, but parsed once and executed 0.1 million times. The Proc2 stored procedure takes 20.94 seconds in this test. The execution result is as follows:

SQL>exec proc2;PL/SQL proceduresuccessfully completed.Elapsed:00:00:20.94

 

3.3 static SQL the Proc3 stored procedure uses static SQL, so that the insert statement can be parsed during compilation, unlike the proc2 stored procedure, which requires parsing during execution, which saves some time, the test results are as follows:

SQL_TEXT                                          EXECUTIONS PARSE_CALLS------------------------------------------------------------ -----------BEGINproc3; END;                                          1           1INSERTINTO T VALUES(:B1 )                            100000           0

 

In static SQL, the insert statement also uses the bound variable, so it is parsed once and then executed multiple times. The Proc3 stored procedure is executed for 17.82 seconds. The specific results are as follows:

SQL>exec proc3;PL/SQL proceduresuccessfully completed.Elapsed:00:00:17.82

 

4. Test Summary dynamic SQL statements are applicable to situations where the table name and query field name are unknown. When the field name and table name are known to be queried, using dynamic SQL (String concatenation) increases the overhead of hard parsing. In this case, static SQL is recommended, this improves the execution efficiency. The efficiency of dynamic SQL statements that are pieced together in the process is not high. Sometimes it is worse to pass SQL statements directly as a program. Static SQL statements are pre-compiled and bound, and dynamic SQL statements are compiled and bound only during later execution.