Solved

what are host variables in oracle?

Posted on 2010-11-21
4
636 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
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…

758 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

21 Experts available now in Live!

Get 1:1 Help Now