Solved

what are host variables in oracle?

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

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
null value 15 102
MULTIPLE DATE QUERY 15 79
How to Gracefuly recover in Racle stored procedure 1 30
grant user/role question 11 28
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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
Via a live example, show how to take different types of Oracle backups using RMAN.

809 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