Thanks for the question, Thiruvaiyaru .

Asked: October 31, 2000 - 12:07 pm UTC

Last updated: October 22, 2008 - 8:04 am UTC

Version: 8.1.6

Viewed 1000+ times

You Asked

Tom,

What is the use of Bind Variables on 8.1.6?
How can the usage of this improve the performance
of the Database and also how do I implement
bind variables on the database ie. Should I need to add
a parameter on the init.ora to implement this ?

Please Explain.

Thanks in Advance
Sai

and Tom said...

Bind variables are in all releases of the database. they are a MAJOR way to increase performance, reduce memory needs and free up tons of CPU time.

Consider a system that queries employees by empno. You could generate 10's of thousands of queries like:

select * from emp where empno = 1;
select * from emp where empno = 2;
...
select * from emp where empno = N;

Each query is unique -- never before seen -- because the values are hard coded into it. Each needs to be parsed, security checked, optimized and so on. In many cases -- the time to parse/validate/optimizer the query is an order of MAGNITUDE greater then the time to actually execute the query. You can find your self spending 90% of your runtime parsing queries -- not executing them!

Using bind variables - a good system would code the query as:

select * from emp where empno = :x;

Then, everyone would use the same query plan -- saving the parsing, the optimizing and so on (its done once and saved in the shared pool). Now, we all execute the query but use a bind variable to set the empno before we open the cursor.

Now, in 8.1.6, they added an "auto binder" feature whereby the kernel will automagically rewrite the queries with literals in them to use bind variables. That is, the system would see the query select * from emp where empno = 1 and rewrite that with a bind variable in place of x and bind for you.

I am of mixed feelings on this feature. While it is better then NOT using bind variables -- it is a far cry from using bind variables. It is not as bad as not using them, not as good as using them. The system still must do a ton of work to rewrite the query -- work you could *easily* do yourself (and your code will execute faster if you do).

To test this out, you can set the session or system parameter

cursor_sharing = force

That can be set in the init.ora file or set via an alter session statement.

Rating

(18 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Good ideas for scalable databases

Kristy, April 05, 2001 - 11:59 am UTC

Hard coded values and bind variables

Nag, November 01, 2001 - 6:14 pm UTC

Can you give me a small example from which the perils of using hard coded values can be demonstrated.
and similarly if you can show how the with the same example how the performance improves using bind variables, that would be great.

November 01, 2001 - 7:37 pm UTC

Nag -- you have my book don't you (if you don't, you of all people should given the number of questions you ask). It's the common, recurring theme throughtout all 1,297 pages.

Read page 27 of my book for the first example.

Your Book

Ak, November 02, 2001 - 12:14 am UTC

Hi tom,
I am Not getting your book in india,and i remember
Telling You that you are talking with WROX in the regard.
Can you update me on this ...??

Thanks,

Regards
Ak

November 02, 2001 - 7:24 am UTC

The book is in india -- people have bought it. You should be able to go into a book store with the ISBN and they can order it (its shipping and available in india for sure)

binds vs no-binds

Connor, November 02, 2001 - 4:23 am UTC

For the previous poster:

Try this:

No-bind
-------
set timing on
declare
x number;
begin
for i in 1 .. 50000 loop
execute immediate
'select 1 from dual where dummy = '||i
into x;
end loop;
end;

with-bind
-------
set timing on
declare
x number;
begin
for i in 1 .. 50000 loop
select 1 into x from dual where dummy = i;
end loop;
end;

and see which is quicker. This is a SINGLE-USER test. Now try firing up three SQLPlus sessions and run three concurrent tests... Things get much much worse...

binds vs no-binds

Connor, November 02, 2001 - 4:24 am UTC

Oops (a fix added) For the previous poster:

Try this:

No-bind
-------
set timing on
declare
x number;
begin
for i in 1 .. 50000 loop
execute immediate
'select count(*) from dual where dummy = '||i
into x;
end loop;
end;

with-bind
-------
set timing on
declare
x number;
begin
for i in 1 .. 50000 loop
select count(*) into x from dual where dummy = i;
end loop;
end;

Bind variables

an, November 02, 2001 - 2:28 pm UTC

it's what i am looking for. thank you!

ugly hack that will die eventually

Mikito Harakiri, November 02, 2001 - 8:00 pm UTC

They are just extra level of comlexity for application writer. Consider this:

SQL>explain plan for select 1 from dual where TRUNC(:1)=Trunc(SYSDATE);
explain plan for select 1 from dual where TRUNC(:1)=Trunc(SYSDATE)
                                                   *
ERROR at line 1:
ORA-00932: inconsistent datatypes

However:

SQLREPO@DB1>explain plan for select 1 from dual where 'A'=Trunc(SYSDATE);

Explained.

Literal substitution should take care of all the performance problems bind variables were introduced for.

November 02, 2001 - 8:46 pm UTC

Won't die, won't go away.

Bind variables are the way to go.  

You want to do the above?  This is easy:

ops$tkyte@ORA717DEV.US.ORACLE.COM> variable dt varchar2(25)
ops$tkyte@ORA717DEV.US.ORACLE.COM> set autotrace traceonly
ops$tkyte@ORA717DEV.US.ORACLE.COM> select 1 from dual where trunc(to_date(:n)) = trunc(sysdate);

no rows selected


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   FILTER
   2    1     TABLE ACCESS (FULL) OF 'DUAL'



(tkprof would be even better).  Explain plan is sort of weak and I personally *never ever* use it.  Not for anything.

Anyway, the reason it won't "go away" is that you want:

select * from t where x = :x;

to be optimized "generically" -- assume some normal value for :x and optimze.  the queries:

select * from t where x = 1;
select * from t where x = 2;

on the other hand, will each be optimized differently if you have stats that include column stats.  One might use an index, the other not.  They are very different queries.

You see, bind variables allow us to parse, optimize, etc etc etc -- ONCE.  Literals -- they are the way to make us do it specifically for that query.  I guess once CPU's reach the speed of light maybe this won't be as necessary....  But we are very very very far from that now.

Bind variables -- not hard and you need to use them.  Don't use them and you will fail.

cursor sharing

Mikito Harakiri, November 02, 2001 - 10:13 pm UTC

By literal substitution I meant substituting literals with binds automatically by server available in version 9. Wouldn't this feature render explicit binds obsolete?

November 03, 2001 - 8:27 am UTC

That is a feature of Oracle8i release 2 (for a couple of years now) version 816.

It is called "cursor_sharing=force".

It exists NOW however -- it is a crutch you can use until you do it properly. It gets you partially there, it does NOT get you all you can get.

For example, people who do NOT use bind variables do code like this:

Statement stmt = conn.createStatement();
for( int i = 0; i < 100; i++ )
{
stmt.execute( "insert into t values ( " + i + ")" );
}

Ok, so each time through the loop that does a HARD parse of the statement. The cursor sharing = force will turn that into a sharable cursor so that the first time through it does a hard parse and then 99 soft parses.

The ONLY proper way to write that is:

PreparedStatement ps =
conn.prepareStatement ("insert into t values ( ? )" );

for( int i = 0; i < 100; i++ )
{
ps.setInt( 1, s );
ps.execute();
}

Now that does 1 parse and 100 executes. The soft parse, while much better then a hard parse, is still incredibly inefficient as compared to "not parsing at all". An autobinding feature does not, cannot, will not remove the soft parse you must do. The soft parse -- latches the library cache just like the hard parse -- limiting scalability, decreasing response time and wasting resources.

There are other nasty side effects to "auto binding" (i have a complete discussion on this in chapter 10 in my book). I personally hesitate to tell people about this particular "feature" as it is NOT the correct solution. It gets them over the hump and then they ignore the problem and go back to their old ways. There is only one way to do this -- the right way.

Should be DUMMY=VARCHAR2

A reader, December 28, 2001 - 10:19 am UTC

HI, Cornor,

I think maybe it should be dummy=TO_CHAR(i), otherwise
it can not work. Mine DB is 8.1.6

Take care in the Deepest Darkest Scotland

bind variables

mo, December 20, 2002 - 6:11 pm UTC

Tom:

1. DO the use of bind variables only works in queries. Does it apply also for insert or update statements?

2. If I have a cursor like this in a web app Am I usin bind vriable or not"

FOr x in (select * from table where col=i_lname)
LOOP
..
END LOOP;

or would you re-write it like

FOR x in (select * from table where col=:lname)
LOOP
..
END LOOp;

Thank you,

December 20, 2002 - 8:21 pm UTC

1) DML, all DML. DML = select, insert, update, delete, merge.

2) plsql autobinds. if I_LNAME is a variable, it is a bind variable in plsql. In Pro*C, it would be where col = :lname, jdbc/odbc "where col = ?", in plsql, just where col = plsql_variable....

bind

mo, December 21, 2002 - 9:27 am UTC

TOm:

as a followup actually i_lname is the input parameter to the stored procedure.

create or replace proc2
( i_lname IN VARCHAR2 DEFAULT NULL )
as

FOR x in (select * from table where col1=i_lname)
LOOP
..
END LOOP;

Since this is a parameter, i gues it will be treated as a bind variable. Correct?

2. Can you see the SQL statements (hard/soft parsed) in shared pool for specific session or for all system users?

Thank you,

Functions of few statements

Neeraj Ranjan Rath,Hyderabad, May 04, 2005 - 12:43 am UTC

Hi Tom,
I have few statement recieved from few notes.I am trying to know the usefulness of these statements.Please tell the job of these statements.

EXEC SQL ALTER SESSION SET SQL_TRACE=TRUE;

EXEC SQL ALTER SESSION SET CURSOR_SHARING=FORCE;

EXEC SQL ALTER SESSION SET SESSION_CACHED_CURSORS=250;

EXEC ORACLE OPTION (MAXOPENCURSORS=100);

EXEC ORACLE OPTION (HOLD_CURSOR=YES);
EXEC ORACLE OPTION (RELEASE_CURSOR=NO);

May 04, 2005 - 8:53 am UTC

they are all documented? do you have a specific question?

Need descriptive ans or related discussed docs

Neeraj Ranjan Rath,Hyderabad, May 04, 2005 - 11:30 am UTC

EXEC SQL ALTER SESSION SET SESSION_CACHED_CURSORS=250;

EXEC ORACLE OPTION (MAXOPENCURSORS=100);

EXEC ORACLE OPTION (HOLD_CURSOR=YES);
EXEC ORACLE OPTION (RELEASE_CURSOR=NO);

Basically these are some recommendations made to application teams.I need to know what are the purpose of these declarations.Dp they solve specific purpose.Or in which condition we will put these type of statements.

May 04, 2005 - 1:40 pm UTC

they are documented, have you checked that out?

(and it sesms that if the applications team made them, they would have supplied or be able to supply a small blurb of why they will solve a problem you have?)

session cached cursors
</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a96536/ch1187.htm#1023765

(helps solve a bug in the developed code where the developers over parse SQL)

http://docs.oracle.com/docs/cd/B10501_01/appdev.920/a97269/pc_10opt.htm#2781

maxopencursors

http://docs.oracle.com/docs/cd/B10501_01/appdev.920/a97269/pc_10opt.htm#5203

hold_cursor

http://docs.oracle.com/docs/cd/B10501_01/appdev.920/a97269/pc_10opt.htm#3010 <code>
release_cursor

A reader, November 12, 2005 - 12:47 pm UTC

Good Information

Khurram Naseem, November 10, 2006 - 3:45 am UTC

again very good information Tom!

asktom.oracle.com
it's actually
thankstom.oracle.com.

Which of those values are wrong???? help me

Paula, November 28, 2006 - 11:18 am UTC

Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 608M Std Block Size: 8K
Shared Pool Size: 352M Log Buffer: 5,120K

Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 7,871.50
Logical reads: 3,588.36
Block changes: 44.74
Physical reads: 200.60
Physical writes: 25.82
User calls: 37.33
Parses: 17.01
Hard parses: 4.28
Sorts: 4.30
Logons: 1.00
Executes: 23.61
Transactions:

% Blocks changed per Read: 1.25 Recursive Call %: 83.01
Rollback per transaction %: 0.02 Rows per Sort: ########

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 98.69 Redo NoWait %: 100.00
Buffer Hit %: 95.09 In-memory Sort %: 99.72
Library Hit %: 91.98 Soft Parse %: 74.87
Execute to Parse %: 27.95 Latch Hit %: 99.77
Parse CPU to Parse Elapsd %: 0.14 % Non-Parse CPU: 98.45

Shared Pool Statistics Begin End
------ ------
Memory Usage %: 7.52 47.31
% SQL with executions>1: 46.03 92.92
% Memory for SQL w/exec>1: 28.98 69.64

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
latch free 3,990,536 1,667,529 50.30
library cache lock 432,078 1,192,773 35.98
buffer busy waits 13,761,500 206,806 6.24
CPU time 120,744 3.64
db file scattered read 5,862,313 62,509 1.89
-------------------------------------------------------------

November 28, 2006 - 12:08 pm UTC

none of them are wrong, the report always accurately prints only correct numbers.

Now, if you are looking for "read the tea leaves that is this statspack report", I'll say this

your soft parse % is horrible, your developers need to be swatted soundly about the head and the phrase "use bind variables" whispered in their ear.

That is the likely cause of

a) latch free waits
b) library cache waits
c) a large percentage of your CPU

wonder why your per second numbers are missing.

Report of the error

Paula, November 28, 2006 - 11:22 am UTC

ERROR: Snapshots chosen span an instance shutdown: RESULTS ARE INVALID
STATSPACK report for

DB Name DB Id Instance Inst Num Release Cluster Host
------------ ----------- ------------ -------- ----------- ------- ------------
ZANTHUSH 2404139991 zanthush 1 9.2.0.5.0 NO marisa26 :ela := ;
*
ERROR at line 4:
ORA-06550: line 4, column 17:
PLS-00103: Encountered the symbol ";" when expecting one of the following:
( - + case mod new not null <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current exists max min prior sql stddev sum variance
execute forall merge time timestamp interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL string> pipe
The symbol "null" was substituted for ";" to continue.
ORA-06550: line 6, column 16:
PLS-00103: Encountered the symbol ";" when expecting one of the following:
( - + case mod new not null <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current exists max min prior sql stddev su

Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 608M Std Block Size: 8K
Shared Pool Size: 352M Log Buffer: 5,120K

Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 7,871.50
Logical reads: 3,588.36
Block changes: 44.74
Physical reads: 200.60
Physical writes: 25.82
User calls: 37.33
Parses: 17.01
Hard parses: 4.28
Sorts: 4.30
Logons: 1.00
Executes: 23.61
Transactions:

% Blocks changed per Read: 1.25 Recursive Call %: 83.01
Rollback per transaction %: 0.02 Rows per Sort: ########

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 98.69 Redo NoWait %: 100.00
Buffer Hit %: 95.09 In-memory Sort %: 99.72
Library Hit %: 91.98 Soft Parse %: 74.87
Execute to Parse %: 27.95 Latch Hit %: 99.77
Parse CPU to Parse Elapsd %: 0.14 % Non-Parse CPU: 98.45

Shared Pool Statistics Begin End
------ ------
Memory Usage %: 7.52 47.31
% SQL with executions>1: 46.03 92.92
% Memory for SQL w/exec>1: 28.98 69.64

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
latch free 3,990,536 1,667,529 50.30
library cache lock 432,078 1,192,773 35.98
buffer busy waits 13,761,500 206,806 6.24
CPU time 120,744 3.64
db file scattered read 5,862,313 62,509 1.89
-------------------------------------------------------------

November 28, 2006 - 12:12 pm UTC

did you read the first line up there....

do not use those two snapshot id's, you shutdown in between them.

Report of the error ...

Harschil Kaparwan, October 21, 2008 - 4:11 pm UTC

Hi Tom,

Thanks a lot for enlightening me as always.

If
.....ERROR: Snapshots chosen span an instance shutdown: RESULTS ARE INVALID
STATSPACK report for....

Then
why do Oracle generates a STATSPACK report which is invalid. It should stop the report generation after writing the first line.

Regards.

October 22, 2008 - 8:04 am UTC

because it is a very very simple scripting language that sqlplus has, the simpliest of simple.

and there is no harm done.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Good ideas for scalable databases

Hard coded values and bind variables

Your Book

binds vs no-binds

binds vs no-binds

Bind variables

ugly hack that will die eventually

cursor sharing

Should be DUMMY=VARCHAR2

bind variables

bind

Functions of few statements

Need descriptive ans or related discussed docs

Good Information

Which of those values are wrong???? help me

Report of the error

Report of the error ...

More to Explore

Performance

Use of Bind Variables on 8.1.6

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Good ideas for scalable databases

Hard coded values and bind variables

Your Book

binds vs no-binds

binds vs no-binds

Bind variables

ugly hack that will die eventually

cursor sharing

Should be DUMMY=VARCHAR2

bind variables

bind

Functions of few statements

Need descriptive ans or related discussed docs

Good Information

Which of those values are wrong???? help me

Report of the error

Report of the error ...

More to Explore

Performance