Not using binds .. banging an obsolete drum ?

January 20, 2016

2 minutes

We’ve all seen the mantra – “you should be using binds”. And this is a demo that’s been rolled out for decades now, showing the performance overhead of not using them:



SQL> drop table T purge;

Table dropped.

SQL> create table T (x primary key) as
  2  select rownum x from dual
  3  connect by level <= 100000;

Table created.

SQL> set timing on
SQL> declare
  2    res int;
  3  begin
  4    for i in 1 .. 100000 loop
  5      select x into res from t where x = i;
  6    end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.61

SQL> set timing on
SQL> declare
  2    res int;
  3  begin
  4    for i in 1 .. 100000 loop
  5      execute immediate 'select x from t where x = '||i into res ;
  6    end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:58.72

And the conclusion is pretty straightforward. The parsing costs added nearly a 40-fold overhead to the execution time.

But in the middle of a discussion like this recently, someone responded to me: “Yeah, but I’m still getting 1700 executions per second. That’s plenty fast enough for me”

And this is true…even with the overhead of parsing, we can still crank out a pretty good execution rate.

Which leads me to thinking that the demo, which we’ve used for so long, is actually flawed. Because in reality, we don’t run queries that are “select from one_table”. The moment you have any sort of real application code in play, there are joins, there are views, there are security predicates…things are a lot more complicated.

So what happens to our parsing costs when we look at something closer to real application code. Here’s a demo joining two of the Oracle dictionary views, both of which have complex definitions. Let me stress two things in this case

we are not even running the SQL like the demo above. All we are doing is parsing the query
we are only doing 10,000 calls, not 100,000. This is only 10% of the size of the initial demo. Why ? Well… see for yourself below



SQL> set timing on
SQL> declare
  2    c number := dbms_sql.open_cursor;
  3  begin
  4    for i in 1 .. 10000 loop
  5      dbms_sql.parse(c,'select * from dba_objects o, dba_segments s
  6                        where object_id = '||i||'
  7                        and o.owner = s.owner
  8                        and o.object_name = s.segment_name' ,
  9                            dbms_sql.native);
 10    end loop;
 11    dbms_sql.close_cursor(c);
 12  end;
 13  /

PL/SQL procedure successfully completed.

Elapsed: 00:39:33.15

That’s nearly 40 minutes of my server being eaten alive by parsing costs. Not using binds when you think about real application code is probably going to kill your app.

But to be fair – in a future post, I’ll talk about not using binds Smile

5 responses to “Not using binds .. banging an obsolete drum ?”

Mike Smithers

January 21, 2016 at 8:58 pm

Maybe the “traditional” reason for using binds is what’s out of date ?
Yes, using bind variables does have a major beneficial impact on performance. However, you could argue that this is, in fact, only a secondary reason for using them.
I’d suggest that the primary reason is security.
Using bind variables pretty much guarantees your application against SQL Injection, unless you get rather creative with your application code.
In light of this, maybe the discussion would be better framed as “You should use bind variables because it makes your application more secure, oh and by the way, faster” ?

Reply
stewashton

January 28, 2017 at 7:05 pm

Both performance and security are great reasons to bind. As arguments, performance may be more persuasive because its benefit is more tangible.

Connor, you are parsing in one session. Parsing requires access to lots of shared resources. I suspect that in a multi-user environment that hard parsing would prevent scaling and might even slow things down because of serialization problems.

I would also add the “cloud” argument. If I have a machine on my premises, I don’t care if the CPU is used at 15% or 30%. Once I move to the cloud, doubling the CPU usage might double my cost. The closer we get to paying for the resources we actually use, the more management should understand the benefit of efficient code.

Reply
Bryn Llewellyn

May 28, 2017 at 6:29 pm

Why do we keep on hearing the discussion expressed this way. All insert, update, delete, and select should come from database PL/SQL. It’s very rare indeed that static SQL is insufficient for the purpose. The classic case when it isn’t possible is to implement QBE with lots of optional column predicates. 20 such give (2**20 – 1) ~ 1 million combinations. And that’s when all predicates are identity. So dynamic SQL is essential here. There’s lots to be said about how to program this and the old Tom did. Briefly, this:

function Where_Clause(
q in The_Table%rowtype)
return varchar2
is
w varcar2(32767) not null := ‘ ‘;
begin
if q.n1 is null then
w := ‘where (1=1 or :n1 is null)’;
else
w := ‘where (n1 = :n1)’;
end if;

if q.v1 is null then
w := w||’ and (1=1 or :v1 is null)’;
else
w := w||’ and (v1 = :v1)’;
end if;

….

This is what you should focus on!

Reply
1. connormcdonald
  
  May 28, 2017 at 9:11 pm
  
  Saying “All insert, update, delete, and select should come from database PL/SQL” is akin to saying “Everyone should eat 2 pieces of fruit and 5 veg each day”.
  
  It doesn’t really matter that it is common knowledge that we should eat healthy, or that we know it is what we should be doing. That reality is that very very few people do it – they keep chuffing down sugary drinks and chocolate bars. I just got back from a conference where when the question was posed:
  
  “Who is wrapping their data access layer in a PL/SQL API”
  
  not a *single* hand was raised in a room of approx 400. So we cant approach the issue from a position of assuming that the world is full of learned IT professionals using static SQL, only resorting to dynamic SQL for QBE. Because they’re not.
  
  That is the “why” in “Why do we keep on hearing the discussion expressed this way”. Because there’s a truckload of people who have never even considered the difference between a bind and a literal, let alone the security and performance impacts, and certainly not the benefits of PL/SQL enshrouding the data.
  
  Reply
Stephan Borsodi

May 29, 2017 at 4:48 pm

I re-run your blocks on my Windows7 (core i7) based Oracle EE 12.1.0.2 and I saw following timings:
1. 1.74s
2. 54.21s
3. not run

I was missing following variation (when using binds):

declare
res int;
begin
for i in 1 .. 100000 loop
execute immediate ‘select x from t where x = :x’ into res using i;
end loop;
end;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.54

I ran this block several times and got timings between 1.49s and 1.59s. So, I would be pretty happy using binds this way and avoid hard parsing.

Reply