literals vs bind variables

[Giada Bonfà]

Hi,

What are the diffences between literals and bind variables?

How do they work?

A DBA told me that I have a high parse rate because the application is using literals variable. Is it true? Why?

thank you

[Yogeeraj_1 · Accepted solution]

hi,

Bind variables are a MAJOR way to increase performance, reduce memory needs and free up tons of CPU time.

Consider a system that queries employees by empno. You could generate 10's of thousands of queries like:

select * from emp where empno = 1;
select * from emp where empno = 2;
...
select * from emp where empno = N;

Each query is unique -- never before seen -- because the values are hard coded into it. Each needs to be parsed, security checked, optimized and so on. In many cases -- the time to parse/validate/optimizer the query is an order of MAGNITUDE greater then the time to actually execute the query. You can find your self spending 90% of your runtime parsing queries -- not executing them!

There is also an "auto binder" feature (cursor_sharing = force) whereby the kernel will automagically rewrite the queries with literals in them to use bind variables. That is, the system would see the query select * from emp where empno = 1 and
rewrite that with a bind variable in place of x and bind for you.


Using bind variables - a good system would code the query as:

select * from emp where empno = :x;

Then, everyone would use the same query plan -- saving the parsing, the optimizing and so on (its done once and saved in the shared pool). Now, we all execute the query but use a bind variable to set the empno before we open the cursor.


For you second question, consider these examples:

For example, people who do NOT use bind variables do code like this:

Statement stmt = conn.createStatement();
for( int i = 0; i < 100; i++ )
{
stmt.execute( "insert into t values ( " + i + ")" );
}

Ok, so each time through the loop that does a HARD parse of the statement.

The ONLY proper way to write that is:

PreparedStatement ps = conn.prepareStatement ("insert into t values ( ? )" );

for( int i = 0; i < 100; i++ )
{
ps.setInt( 1, s );
ps.execute();
}

Now that does 1 parse and 100 executes. The soft parse, while much better then a hard parse, is still incredibly inefficient as compared to "not parsing at all".

The soft parse -- latches the library cache just like the hard parse -- limiting scalability, decreasing response time and wasting resources.

hth
Yogeeraj

[Brian Crabtree]

To understand literal and bind variables, you have to look at it from the SQL statement level.

Take the following SQL statements:
select * from employees where name = "BRIAN";
select * from employees where name = "GLORIA";

Because in each statement, I am specificly specifying the name (BRIAN and GLORIA), they are two distinct statements, and the database will have to parse each statement before it can be executed.

By passing bind variables with plsql package DBMS_SQL, you can give it a different statement:

declare
sqlstatement varchar2(50);
username varchar2(10);
tmpcursor integer;
retcursor integer;
begin
username := 'BRIAN';
tmpcursor:=dbms_sql.open_cursor;
dbms_sql.parse(tmpcursor, 'select * from employees where username = :x;', dbms_sql.native);
dbms_sql.bind_variable(tmpcursor, ':x', username);
retcursor:=dbms_sql.execute(tmpcursor);
end;
/

This is of course a simple statement, but is just to show you the path that it takes. Oracle would then see the statement parsed as "select * from employees where username = :x". Because this statement would already be in memory from a previous execution, it would not have to be parsed again, but executed with a new entry for the bind variable.

A possibly simpler solution is to set "cursor_sharing = force" in the init.ora, which will force Oracle to substitute bind variables into statements. There is an associated cost with doing this though, although it can sometimes be less than the cost of rewriting portions of the application to not use bind variables.

Hope this helps,

Thanks,

Brian

[H.Merijn Brand (procura]

FY the Perl/DBI interface not only supports bind variables, but the documentation that comes with it promotes it. It's bloody fast compared to passing by value. Binding variables is like pass by reference.

All major database interfaces support this scheme.

Enjoy, have FUN! H.Merijn