what is the use of bind variable in oracle
Each time an SQL statement is sent to the database, an exact text match is performed to see if the statement is already present in the shared pool. If no matching statement is found a hard parse is performed, which is a resource intensive process. If the statement is found in the shared pool this step is not necessary and a soft parse is performed.
Note:- oracle perform hard parse only when it does't find the sql hash value on the memory for the statement it want to execute.
Concatenating variable values into an SQL statement makes the statement unique, forcing a hard parse. By contrast, using bind variables allow reuse of statements as the text of the statement remains the same. Only the value of the bind variable changes.
Consider the following example,
A table is created.
SQL> create table bind_ex (
2 col_1 number,
3 col_2 varchar2(10)
4 );
Table created.
and filled with some values..
SQL> insert into bind_ex values (1, 'one' );
1 row created.
SQL> insert into bind_ex values (2, 'two' );
1 row created.
SQL> insert into bind_ex values (3, 'three');
1 row created.
Although the three insert statements do conceptually the same thing, they are not the same statement. This can be verified by examing v$sql ,
SQL> set linesize 100
SQL> select substr(sql_text, 1, 100) from v$sql where lower(sql_text) like '% bind_ex %';
SUBSTR(SQL_TEXT,1,100)
----------------------------------------------------------------------------------------------------
select substr(sql_text, 1, 100) from v$sql where lower(sql_text) like '% bind_ex %'
insert into bind_ex values (2, 'two' )
insert into bind_ex values (3, 'three')
insert into bind_ex values (1, 'one' )
The line returned is the statement to retrieve the other three. Since these three other statements are different, Oracle had to analyse them first in order to execute them. This is a costly operation and referred to as hard parse.
Since it is (sometimes) desirable to prevent such hard parses, bind variables can be used. In an SQL statements, bind variables are indicated by a colon (:) followed by a name or a number. So, these insert statements rewritten with bind variables will then look like: insert into bind_ex values(:bind_var_1, :bind_var_2).
In PL/SQL, execute immediate can be used for bind variables:
SQL> declare
2 stmt constant varchar2(52) := 'insert into bind_ex values(:bind_var_1, :bind_var_2)';
3 begin
4 execute immediate stmt using 4, 'four';
5 execute immediate stmt using 5, 'five';
6 execute immediate stmt using 6, 'six' ;
7 end;
8 /
PL/SQL procedure successfully completed.
Again checking v$sql:
SQL> select substr(sql_text, 1, 100) from v$sql where lower(sql_text) like '% bind_ex %';
SUBSTR(SQL_TEXT,1,100)
----------------------------------------------------------------------------------------------------
select substr(sql_text, 1, 100) from v$sql where lower(sql_text) like '% bind_ex %'
insert into bind_ex values (2, 'two' )
insert into bind_ex values (3, 'three')
insert into bind_ex values (1, 'one' )
insert into bind_ex values(:bind_var_1, :bind_var_2)
declare stmt constant varchar2(52) := 'insert into bind_ex values(:bind_var_1, :bind_var_2)'; begi
6 rows selected.
SQL>
Each time an SQL statement is sent to the database, an exact text match is performed to see if the statement is already present in the shared pool. If no matching statement is found a hard parse is performed, which is a resource intensive process. If the statement is found in the shared pool this step is not necessary and a soft parse is performed.
Note:- oracle perform hard parse only when it does't find the sql hash value on the memory for the statement it want to execute.
Concatenating variable values into an SQL statement makes the statement unique, forcing a hard parse. By contrast, using bind variables allow reuse of statements as the text of the statement remains the same. Only the value of the bind variable changes.
Consider the following example,
A table is created.
SQL> create table bind_ex (
2 col_1 number,
3 col_2 varchar2(10)
4 );
Table created.
and filled with some values..
SQL> insert into bind_ex values (1, 'one' );
1 row created.
SQL> insert into bind_ex values (2, 'two' );
1 row created.
SQL> insert into bind_ex values (3, 'three');
1 row created.
Although the three insert statements do conceptually the same thing, they are not the same statement. This can be verified by examing v$sql ,
SQL> set linesize 100
SQL> select substr(sql_text, 1, 100) from v$sql where lower(sql_text) like '% bind_ex %';
SUBSTR(SQL_TEXT,1,100)
----------------------------------------------------------------------------------------------------
select substr(sql_text, 1, 100) from v$sql where lower(sql_text) like '% bind_ex %'
insert into bind_ex values (2, 'two' )
insert into bind_ex values (3, 'three')
insert into bind_ex values (1, 'one' )
The line returned is the statement to retrieve the other three. Since these three other statements are different, Oracle had to analyse them first in order to execute them. This is a costly operation and referred to as hard parse.
Since it is (sometimes) desirable to prevent such hard parses, bind variables can be used. In an SQL statements, bind variables are indicated by a colon (:) followed by a name or a number. So, these insert statements rewritten with bind variables will then look like: insert into bind_ex values(:bind_var_1, :bind_var_2).
In PL/SQL, execute immediate can be used for bind variables:
SQL> declare
2 stmt constant varchar2(52) := 'insert into bind_ex values(:bind_var_1, :bind_var_2)';
3 begin
4 execute immediate stmt using 4, 'four';
5 execute immediate stmt using 5, 'five';
6 execute immediate stmt using 6, 'six' ;
7 end;
8 /
PL/SQL procedure successfully completed.
Again checking v$sql:
SQL> select substr(sql_text, 1, 100) from v$sql where lower(sql_text) like '% bind_ex %';
SUBSTR(SQL_TEXT,1,100)
----------------------------------------------------------------------------------------------------
select substr(sql_text, 1, 100) from v$sql where lower(sql_text) like '% bind_ex %'
insert into bind_ex values (2, 'two' )
insert into bind_ex values (3, 'three')
insert into bind_ex values (1, 'one' )
insert into bind_ex values(:bind_var_1, :bind_var_2)
declare stmt constant varchar2(52) := 'insert into bind_ex values(:bind_var_1, :bind_var_2)'; begi
6 rows selected.
SQL>
| The statment returns two new Statements: the PL/SQL block and the one that was (three times) executed within the block. Note:- Thus the use of bind variable will reduce the hard parse and will improve the database performance to a great extend . |
More good Links..
No comments:
Post a Comment