Shulou(Shulou.com)06/01 Report--

Performance comparison of dynamic SQL, static SQL and bound variables

1. Test sample

The following three stored procedures respectively use three programming methods: dynamic SQL, binding variables and static SQL. The details of the stored procedure are as follows:

L) dynamic SQL

Create or replace procedure proc1 asbegin for i in 1.. 100000 loop execute immediate 'insert into t values (' | | I | |')'; commit; end loop;end proc1

After performing the proc1:

The Proc1 stored procedure uses dynamic SQL, so that every insert statement is hard parsed each time it is executed, which increases the hard parsing overhead of the shared pool. Here is the result in the v$sqlarea view.

19:23:20 SYS@ prod > select sql_text, PARSE_CALLS, EXECUTIONS from v$sqlarea where sql_text like 'insert into t%'

SQL_TEXT PARSE_CALLS EXECUTIONS

-

Insert into t values (56386) 1 1

Insert into t values (58271) 1 1

Insert into t values (57503) 1 1

Insert into t values (58762) 1 1

Insert into t values (58158) 1 1

Insert into t values (57081) 1 1

Insert into t values (57574) 1 1

Insert into t values (56146) 1 1

Insert into t values (58674) 1 1

Insert into t values (56186) 1 1

Insert into t values (56548) 1 1

Insert into t values (57817) 1 1

Insert into t values (56534) 1 1

Insert into t values (56678) 1 1

Insert into t values (56758) 1 1

Insert into t values (57501) 1 1

Insert into t values (56959) 1 1

2) 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

Perform proc2:

The Proc2 stored procedure uses binding variables so that during execution, it reduces the overhead of hard parsing and reduces contention for shared pools. During execution, the result in the v$sqlarea view is as follows

19:29:21 SYS@ prod > select sql_text, PARSE_CALLS, EXECUTIONS from v$sqlarea where sql_text like 'insert into t%'

SQL_TEXT PARSE_CALLS EXECUTIONS

-

Insert into t values (: X) 1 100000

Elapsed: 00:00:00.08

Instead of parsing once and executing once, the insert statement parsed once and executed 100000 times.

3) static SQL

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

Perform proc3:

The Proc3 stored procedure uses static SQL, so that the insert statement is parsed during compilation, unlike the proc2 stored procedure, which needs to be parsed during execution, which saves some time. The specific test results are as follows

19:40:46 SYS@ prod > select sql_text, PARSE_CALLS, EXECUTIONS from v$sqlarea19:40:59 2 where sql_text like 'INSERT INTO T% doubt:

SQL_TEXT PARSE_CALLS EXECUTIONS

-

INSERT INTO T VALUES (: B1) 0 100000

Elapsed: 00:00:00.05

In static SQL, the insert statement also uses bound variables, so it is also parsed once and then executed multiple times.

2. Test summary

Dynamic SQL is suitable for situations where table names and query field names are unknown. When the query field name and table name are known, using dynamic SQL (string concatenation) will increase the overhead of hard parsing, in which case, static SQL is recommended, which can improve execution efficiency. Using pieced dynamic sql in stored procedures is not efficient, and sometimes it is not as efficient as the program to pass sql directly. Static SQL is a precompiled binding, while dynamic SQL compiles the binding when it is executed later.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.