Solved

what are host variables in oracle?

Posted on 2010-11-21
4
639 Views
Last Modified: 2013-12-18
What are host variables in oracle
how that increases performances?
Please answer with examples of host variables.
0
Comment
Question by:sakthikumar
  • 2
4 Comments
 
LVL 3

Expert Comment

by:mpaladugu
ID: 34184017
see this link...this may be help ful...it also has examples...

http://download.oracle.com/docs/cd/B10500_01/appdev.920/a97269/pc_04dat.htm#27300
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 34184268
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.








0
 
LVL 2

Expert Comment

by:jiruiz
ID: 34187007
host variables or bind variables?
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 34187262
bind variables are host variables.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now