what are host variables in oracle?

What are host variables in oracle
how that increases performances?
Please answer with examples of host variables.
Who is Participating?
sdstuberConnect With a Mentor Commented:
host variables do not improve performance by themselves.

however if you write a sql statement like this...

select * from your_table where id = :your_host_variable;

that statement is parsed only once even if you call it with id 1, id 2, id 3, id 4 etc.

but if you do this...

select * from your_table where id = 1;
select * from your_table where id = 2;
select * from your_table where id = 3;
select * from your_table where id = 4;

each of those is 4 distinct sql statements so each must be parsed individually.
parsing is expensive in terms of cpu and requires latching in the SGA.
latching is type of lock,  locks mean you can't scale because they will block other sessions from obtaining the same latch.
So,  failure to bind your host variables into your sql means you will consume more resources than you need to and will inhibit the scalability of your application.

Also note,  if you are using variables for sql from within pl/sql  you get the binding effect automatically.

see this link...this may be help ful...it also has examples...

host variables or bind variables?
bind variables are host variables.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.