Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: There is NOT value in using BOUND variables!!! ??? (WRONG -- this is ALOT of value)

Re: There is NOT value in using BOUND variables!!! ??? (WRONG -- this is ALOT of value)

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 11 Nov 1999 12:22:11 -0500
Message-ID: <EtsqOJOd3O=bBEnRTOoilbhnPD5l@4ax.com>


A copy of this was sent to Deja User <dejacom_at_my-deja.com> (if that email address didn't require changing) On Wed, 10 Nov 1999 22:09:52 GMT, you wrote:

>we are using Oracle 8.01 on NT4/SP5, 512MB RAM, Dual Pentium Pro 200,
>RAID array level 3.
>
>I was intrigued by the suggestions made in Oracle Performance Tuning
>about using bound variables where possible. I always had the hunch that
>bound variable queries actually run slower but I decided to test it out
>in a more disciplined fashion.
>
>I ran following query 100 times, each time criteria coming from a
>random number generator so it was guanrenteed to be unique in my case.
>Select x, y, z from ABC where field in (set of 6 values).
>
>And then I ran following query
>select x, y , z from ABC where field in (:1, :2, :3, :4, :5, :6)
>and then did the binding.
>
>The first query (without bound variables) took 10 seconds to execute
>100 versions. The second one (with bound variables) took 13 seconds!
>
>Then I changed the bound variable query to use all 255 bound variables
>in the *IN* clause (to have a more generic query,
>i.e. :1, :2, ..., :255) and then assigned random values to first 6
>variables and '0' to the rest of the 249. The resultant query just
>slowed to a crawl. I did not have the patience to see it run 100 times.
>
>So what does it tell me? I remember that the perception about bound
>variables forced developers in our shop to change queries NOT to use
>bound variables (a few months ago) and todays test just confirms it.
>
>Yours thoughts?
>

very different from yours. 180 degrees the OTHER way. This first thing I would do is make everyone go back and use bind variables.

  1. I cannot reproduce ANY results like yours. In my testing, bind variables either go *much* faster or just marginally slower with your example. I'm providing my entire test case for review below. My findings go more like this:
6 random bind variables                               0.68 seconds
6 random bind variables, 244 0's bound in            11.90 seconds
6 random bind variables, 244 NON-Zero's bound in     40.21 seconds

6 random hard coded values                            3.49 seconds
6 random hard coded values, 244 more 0's hard coded   8.55 seconds
6 random hard codes values, 244 mone NON-Zero's     1 minute 4.45 seconds


(Sun E450)

So, in my testing, with 6 inputs in the in list -- bind variables ran 5 times FASTER. With 250 inputs -- 244 = 0, bind variables ran 30% slower.

with 250 inputs -- 244 <> 0, bind variables ran 60% faster.

2) you tested in single user mode. This is the biggest mistake in this test. the major benefit of Oracle's shared sql (which is only really possible with bind variables) is in a multi-user environment. My test case, when run in many concurrent sessions, would show bind variables doing even better then the static ones, much better.

Watch what happens to library locks when you have dozens of people all trying to parse their unique statements simultaneously.

Watch (even worse) what happens to your CPU utilization as they do so. PARSING a unique query is extremely cpu intensive -- extremely. For most queries (indexed reads of a few rows) -- the time spent parsing the query is many times the amount of time spent executing it. I've see query response times drop by as much as 90% when moving over to bind variables.

Watch what happens to your shared pool utilization (it'll be very very bad)

3) you didn't take into account that select .... where field in (:1,:2,:3,....) would generate a different plan then where field in (1,2,3,4,5,6,0,0,0,0,0,0,0....). The optimizer sees the dup zeros in the second on and turns it into the more simple (1,2,3,4,5,6,0). The differences in run times were due to differences in optimizer plans. A little tuning and the bind variable query will run faster.

Bind variable are *EXTREMELY* important. Cannot be over emphasized in my opinion. I recently was benchmarking an application. It used lots of dynamic sql. The dynamic sql generator would put in explicit values and not use bind variables. In single user mode, this work acceptably -- response times ok, everything all right. Now, I through a couple (not lots, just a couple, less then a dozen) conncurrent users hitting the enter key together for extended periods -- it completely and totally falls apart. 100% cpu -- through the roof. waits like you would not believe. parse time becomes more then 90% of total run time (and parse times are very high). shared sql area is torn to shreds and we never reuse a query.

I make them go back and rewrite that part. use bind variables. All of a sudden I go from 10's of thousands of statements in the shared pool to less then 500. I have idle cpus. run times are down 5, 6, 7, 8, 9 times depending on function. Waits -- they are gone. Life is good. It scales once again.

The one exception to the bind variable rule would be in a data warehouse type situation. If I submit a query like: "select count(distinct y) from T where gender = :x", i've not given the optimizer much to go on. It'll make some assumptions about the gender column and come up with a plan, a good generic plan. The problem is -- perhaps in my table, 90% of the rows have gender = 'M' and 10% have it set to 'F'. If I submit "select count(distinct y) from T where gender = 'M'", we should definitely full scan T. OTOH if I submit "select count(distinct y) from T where gender = 'F'" I should maybe index range scan/table access by rowid. I want a different plan depending on the inputs. In general -- this is *not* the case. In some specialized cases, it is.

The rule is: use bind variables unless you know there is some skewed data and you would benefit massively from NOT using them.

>Regards,
>Mike.
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

Test case:
Generate test data, 10,000 rows of random data. You can find my random package on dejanews at http://www.deja.com/getdoc.xp?AN=460372935&fmt=text

tkyte_at_8i> drop table abc;
tkyte_at_8i> create table abc ( field int, x int, y int, z int );
tkyte_at_8i> begin
tkyte_at_8i> 	  for i in 1 .. 10000 loop
tkyte_at_8i> 	      insert into abc values ( random.rand, i, i, i );
tkyte_at_8i> 	  end loop;
tkyte_at_8i> end;
tkyte_at_8i> /
tkyte_at_8i> 
tkyte_at_8i> create index abc_field_idx on abc(field)
tkyte_at_8i> /
tkyte_at_8i> 
tkyte_at_8i> select count(*) from abc
tkyte_at_8i> /


this package holds pre-generated queries and random data. this is so the generation of the queries/data doesn't skew the results

tkyte_at_8i> create or replace package t_pkg   2 as
  3 type randNumbers is table of number index by binary_integer;   4

  5  	 r1    randNumbers;
  6  	 r2    randNumbers;
  7  	 r3    randNumbers;
  8  	 r4    randNumbers;
  9  	 r5    randNumbers;
 10  	 r6    randNumbers;
 11  
 12  	 type sqlStmt is table of varchar2(4096) index by binary_integer;
 13  
 14  	 s     sqlStmt;
 15  	 s2    sqlStmt;
 16  	 s3    sqlStmt;
 17  
 18  	 procedure init( p_iterations in number );
 19
 20
 21 end;
 22 /
Package created.

tkyte_at_8i> create or replace package body t_pkg   2 as
  3
  4
  5 procedure init( p_iterations in number )   6 is
  7 l_rand_string varchar2(4000);   8 begin

  9  	     for i in 1 .. 245 loop
 10  		     l_rand_string := l_rand_string || ',' || random.rand;
 11  	     end loop;
 12  
 13  	 for i in 1 .. p_iterations loop
 14  	     r1(i) := random.rand;
 15  	     r2(i) := random.rand;
 16  	     r3(i) := random.rand;
 17  	     r4(i) := random.rand;
 18  	     r5(i) := random.rand;
 19  	     r6(i) := random.rand;
 20  	     s(i) := 'select x, y, z from abc where field in ( ' ||
 21  		      r1(i) || ',' || r2(i) || ',' || r3(i) || ',' ||
 22  		      r4(i) || ',' || r5(i) || ',' || r6(i) || ')';
 23  
 24  	     s2(i) := 'select x, y, z from abc where field in ( ' ||
 25  		      r1(i) || ',' || r2(i) || ',' || r3(i) || ',' ||
 26  		      r4(i) || ',' || r5(i) || ',' || r6(i);
 27  	     for j in 1 .. 245 loop
 28  		 s2(i) := s2(i) || ',0 ';
 29  	     end loop;
 30  	     s2(i) := s2(i) || ')';

 31
 32
 33  	     s3(i) := 'select x, y, z from abc where field in ( ' ||
 34  		      r1(i) || ',' || r2(i) || ',' || r3(i) || ',' ||
 35  		      r4(i) || ',' || r5(i) || ',' || r6(i) ||
 36  				      l_rand_string || ')';
 37  
 38  	 end loop;

 39 end;
 42
 43 end;
 44 /

Package body created.

We will run 1,000 iterations. This generates the 1,000x3 queries and 1,000 x 6 sets of random data

tkyte_at_8i> exec t_pkg.init( 1000 )
PL/SQL procedure successfully completed.

tkyte_at_8i> set timing on

First run the query with 6 bind variables. PLSQL is a 'autobinding' language. If you use sql_trace you can verify in the trace file that the query submited is in fact "field in (:b1,:b2,....:b6)"

tkyte_at_8i> begin

  2  	 for i in 1 .. t_pkg.r1.count loop
  3  	     for y in ( select x, y, z
  4  			  from abc
  5  			 where field in ( t_pkg.r1(i),
  6  					  t_pkg.r2(i), t_pkg.r3(i),
  7  					  t_pkg.r4(i), t_pkg.r5(i),
  8  					  t_pkg.r6(i) ) )
  9  	     loop
 10  		 null;
 11  	     end loop;
 12  	 end loop;
 13  	 dbms_output.put_line( 'executed ' || t_pkg.r1.count );
 14 end;
 15 /
executed 1000

PL/SQL procedure successfully completed. Elapsed: 00:00:00.68

Now, lets do it with 250 bind variables, 244 of which are 0

tkyte_at_8i> declare

  2  	 x1 number default 0;
  3  	 x2 number default 0;

  [lots of lines snipped]

240  	 x239 number default 0;
241  	 x240 number default 0;
242  begin
243  	 for i in 1 .. t_pkg.r1.count loop
244  	     for y in ( select x, y, z
245  			  from abc
246  			 where field in ( t_pkg.r1(i),
247  					  t_pkg.r2(i), t_pkg.r3(i),
248  					  t_pkg.r4(i), t_pkg.r5(i),
249  					  t_pkg.r6(i)
250  ,x1,x2,x3,x4,x5,x6,x7,x8,x9,x10,x11,x12,x13,x14,x15
251  ,x16,x17,x18,x19,x20,x21,x22,x23,x24,x25,x26,x27,x28,x29,x30
252  ,x31,x32,x33,x34,x35,x36,x37,x38,x39,x40,x41,x42,x43,x44,x45
253  ,x46,x47,x48,x49,x50,x51,x52,x53,x54,x55,x56,x57,x58,x59,x60
254  ,x61,x62,x63,x64,x65,x66,x67,x68,x69,x70,x71,x72,x73,x74,x75
255  ,x76,x77,x78,x79,x80,x81,x82,x83,x84,x85,x86,x87,x88,x89,x90
256  ,x91,x92,x93,x94,x95,x96,x97,x98,x99,x100,x101,x102,x103,x104,x105
257  ,x106,x107,x108,x109,x110,x111,x112,x113,x114,x115,x116,x117,x118,x119,x120
258  ,x121,x122,x123,x124,x125,x126,x127,x128,x129,x130,x131,x132,x133,x134,x135
259  ,x136,x137,x138,x139,x140,x141,x142,x143,x144,x145,x146,x147,x148,x149,x150
260  ,x151,x152,x153,x154,x155,x156,x157,x158,x159,x160,x161,x162,x163,x164,x165
261  ,x166,x167,x168,x169,x170,x171,x172,x173,x174,x175,x176,x177,x178,x179,x180
262  ,x181,x182,x183,x184,x185,x186,x187,x188,x189,x190,x191,x192,x193,x194,x195
263 ,x196,x197,x198,x199,x200,x201,x202,x203,x204,x205,x206,x207,x208,x209,x210 264 ,x211,x212,x213,x214,x215,x216,x217,x218,x219,x220,x221,x222,x223,x224,x225 265
,x226,x227,x228,x229,x230,x231,x232,x233,x234,x235,x236,x237,x238,x239,x240))
266  	     loop
267  		 null;
268  	     end loop;
269  	 end loop;
270  	 dbms_output.put_line( 'executed ' || t_pkg.r1.count );
271 end;
272 /
executed 1000

PL/SQL procedure successfully completed. Elapsed: 00:00:11.90

Now, do it with 244 NON-Zero values

tkyte_at_8i> declare

  2  	 x1 number default random.rand;
  3  	 x2 number default random.rand;

  [lots of lines snipped]

240  	 x239 number default random.rand;
241  	 x240 number default random.rand;
242  begin
243  	 for i in 1 .. t_pkg.r1.count loop
244  	     for y in ( select x, y, z
245  			  from abc
246  			 where field in ( t_pkg.r1(i),
247  					  t_pkg.r2(i), t_pkg.r3(i),
248  					  t_pkg.r4(i), t_pkg.r5(i),
249  					  t_pkg.r6(i)
250  ,x1,x2,x3,x4,x5,x6,x7,x8,x9,x10,x11,x12,x13,x14,x15
251  ,x16,x17,x18,x19,x20,x21,x22,x23,x24,x25,x26,x27,x28,x29,x30
252  ,x31,x32,x33,x34,x35,x36,x37,x38,x39,x40,x41,x42,x43,x44,x45
253  ,x46,x47,x48,x49,x50,x51,x52,x53,x54,x55,x56,x57,x58,x59,x60
254  ,x61,x62,x63,x64,x65,x66,x67,x68,x69,x70,x71,x72,x73,x74,x75
255  ,x76,x77,x78,x79,x80,x81,x82,x83,x84,x85,x86,x87,x88,x89,x90
256  ,x91,x92,x93,x94,x95,x96,x97,x98,x99,x100,x101,x102,x103,x104,x105
257  ,x106,x107,x108,x109,x110,x111,x112,x113,x114,x115,x116,x117,x118,x119,x120
258  ,x121,x122,x123,x124,x125,x126,x127,x128,x129,x130,x131,x132,x133,x134,x135
259  ,x136,x137,x138,x139,x140,x141,x142,x143,x144,x145,x146,x147,x148,x149,x150
260  ,x151,x152,x153,x154,x155,x156,x157,x158,x159,x160,x161,x162,x163,x164,x165
261  ,x166,x167,x168,x169,x170,x171,x172,x173,x174,x175,x176,x177,x178,x179,x180
262  ,x181,x182,x183,x184,x185,x186,x187,x188,x189,x190,x191,x192,x193,x194,x195
263 ,x196,x197,x198,x199,x200,x201,x202,x203,x204,x205,x206,x207,x208,x209,x210 264 ,x211,x212,x213,x214,x215,x216,x217,x218,x219,x220,x221,x222,x223,x224,x225 265
,x226,x227,x228,x229,x230,x231,x232,x233,x234,x235,x236,x237,x238,x239,x240))
266  	     loop
267  		 null;
268  	     end loop;
269  	 end loop;
270  	 dbms_output.put_line( 'executed ' || t_pkg.r1.count );
271 end;
272 /
executed 1000

PL/SQL procedure successfully completed.

Elapsed: 00:00:40.21

Now, lets try this without bind variables..... This is 6 randon values...

tkyte_at_8i> declare

  2  	 type rc is ref cursor;
  3  	 c    rc;
  4  	 x    number;
  5  	 y    number;
  6  	 z    number;
  7  begin
  8  	 for i in 1 .. t_pkg.s.count loop
  9  	     open c for t_pkg.s(i);
 10  	     loop
 11  		 fetch c into x, y, z;
 12  		 exit when c%notfound;
 13  	     end loop;
 14  	     close c;
 15  	 end loop;
 16  	 dbms_output.put_line( 'executed ' || t_pkg.s.count );
 17 end;
 18 /
executed 1000
PL/SQL procedure successfully completed.

Elapsed: 00:00:03.49

Now, 6 random values with 244 zeroes....

tkyte_at_8i> declare

  2  	 type rc is ref cursor;
  3  	 c    rc;
  4  	 x    number;
  5  	 y    number;
  6  	 z    number;
  7  begin
  8  	 for i in 1 .. t_pkg.s2.count loop
  9  	     open c for t_pkg.s2(i);
 10  	     loop
 11  		 fetch c into x, y, z;
 12  		 exit when c%notfound;
 13  	     end loop;
 14  	     close c;
 15  	 end loop;
 16  	 dbms_output.put_line( 'executed ' || t_pkg.s2.count );
 17 end;
 18 /
executed 1000

PL/SQL procedure successfully completed.

Elapsed: 00:00:08.55

and lastly, 6 random values with 244 NON zero values...

tkyte_at_8i> declare

  2  	 type rc is ref cursor;
  3  	 c    rc;
  4  	 x    number;
  5  	 y    number;
  6  	 z    number;
  7  begin
  8  	 for i in 1 .. t_pkg.s3.count loop
  9  	     open c for t_pkg.s3(i);
 10  	     loop
 11  		 fetch c into x, y, z;
 12  		 exit when c%notfound;
 13  	     end loop;
 14  	     close c;
 15  	 end loop;
 16  	 dbms_output.put_line( 'executed ' || t_pkg.s3.count );
 17 end;
 18 /
executed 1000

PL/SQL procedure successfully completed.

Elapsed: 00:01:04.45
tkyte_at_8i> spool off

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Nov 11 1999 - 11:22:11 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US