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

I found Oracle8i crashing when using too many execution plans. ORA-00600 [17112] or [17148] or [17128] or similar. You cannot even do a "flush shared_pool".

To reproduce do the following:

create an empty table with a varchar2 (50) column. Now issue in a loop dynamic sql "select * from that_table where that_column = 'random string';". You need not even execute the command, just parse it.

The longest time our instance (Solaris 2.6 Oracle 8i 8.1.5.0.0) could resist was 3.5 hours. NT 4.0 very often crashed after ten minutes.

Martin

Thomas Kyte wrote:

> 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 Sun Nov 14 1999 - 18:15:12 CST