Solved

what are host variables in oracle?

Posted on 2010-11-21
4
647 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 74

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 74

Expert Comment

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

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

695 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