| bindvariable [message #59493] |
Wed, 26 November 2003 09:46  |
anoop
Messages: 15
Registered: October 2001
|
Junior Member |
|
|
why we use bind variable .how can we define it using :
what happend internally and what's the performance gain for it
anoop
|
|
|
|
| Re: bindvariable [message #59501 is a reply to message #59493] |
Thu, 27 November 2003 03:03  |
Frank Naude
Messages: 4580
Registered: April 1998
|
Senior Member |
|
|
Hi,
Bind variables are very good for database performance because it allows you statements (cursors) to be shared/reused between users and sessions. The net effect is fewer hard parses (easier on CPU) and better utilization of the SHARED_POOL (memory).
The implementation of bind variables are dependant on the programming language you use to develop your code.
Best regards.
Frank
|
|
|
|
| Re: bindvariable [message #59502 is a reply to message #59493] |
Thu, 27 November 2003 03:09 |
Daljit Singh
Messages: 290
Registered: October 2003
Location: Texas
|
Senior Member |
|
|
Hi,
Basically bind variables r those variables which we declare at sql prompt and we can use them in our pl/sql prog through the bind variables we can also exchange the data among different pl/sql progs. The syntax of declaring the bind variable is :
repadm@STAGING> var a char(20)
After declaring it we can use it any pl/sql prog here is an example :
repadm@STAGING> ed abc
It will open the editor. Make a small pl/sql block like this one:
begin
:a:='Daljit Singh';
end;
/
Execute it on sql prompt like :
repadm@STAGING> @ abc
After that u can see the value in variable using :
repadm@STAGING> print a
A
--------------------------------
Daljit Singh
To share information i m going to create an another pl/sql block :
repadm@STAGING> ed tt
And add the following code :
set serveroutput on
begin
dbms_output.put_line(:a);
end;
/
set serveroutput off
Run it using :
repadm@STAGING> @ tt
Daljit Singh
PL/SQL procedure successfully completed.
Now u can see that the same variable is used in two diff pl/sql blocks through this we can exchange the data among diff pl/sql progs.
Well according to the performance issue we should not use the bind variables in our query bcz they r only the containers of the values not the actual value so CBO makes a blind guess abt the data while making the execution plan for the query, which may not as good enough to satisfy the performance issues.
I hope it will clear all the thing to u.
|
|
|
|
| Re: bindvariable [message #59518 is a reply to message #59502] |
Fri, 28 November 2003 05:56 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Daljit,
Bind variables are available not just with Sqlplus ,but with a host of other environments. They are primarily used as placeholders in SQL statements to accept(bind) different values at run time , enabling the sql statements to be 'shared' more vigorously in the shared pool that reduces the expensive hard parsing/latch contention and the resulting cpu consumption , allowing the OLTP production applications to scale up.
They may not be so useful in datawarehousing databases or when the tables have skewed data, where the execution plans could vary drastically depending upon the values.In those cases you can create histograms and let the CBO devise the best execution plan based on data values.
But when you have thousands and thousands of queries looking like ' select blah from table where id=1 ;
select blah from table where id=2; ...'
and where the data distribution is pretty uniform, using bind variables will GREATLY improve your performance and scalability as they eliminate the expensive hard parsing which is a VERY COSTLY OPERATION. Also reusing the sql reduces library cache and Shared pool latch contention greatly . Note that Latch contention is a great scalability inhibitor.
In cases where you could not use bind variables,you have the option of letting Oracle implement cursor sharing by setting CURSOR_SHARING=FORCE or better CURSOR_SHARING=SIMILAR at the session level.
-Thiru
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
