?
Solved

what are host variables in oracle?

Posted on 2010-11-21
4
Medium Priority
?
654 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 2000 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to recover a database from a user managed backup

771 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