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

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 16 Nov 1999 08:12:24 -0500
Message-ID: <a1cxONcOcZ5mZnxX=j+u5Ty+2kQH@4ax.com>

A copy of this was sent to Martin Haltmayer <Martin.Haltmayer_at_0800-einwahl.de> (if that email address didn't require changing) On Mon, 15 Nov 1999 02:15:12 +0200, you wrote:

>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
>
>

Martin, I ran this:

declare

   i number default 0;
   c number;
begin

   loop

      execute immediate 'select count(*) from t where x = ''' || i || '''' 
                        into c;   
      i := i+1;  

   end loop;
end;

for a day on 8i/solaris (about 10 million iterations). It generates a unique query each time as the number I is concatenated into the query -- not bound. Other then causing lots of aging out of queries in the shared pool, nothing else happened. It just ran and ran and ran.

Any other information to go with that? What was the client program? What did you init.ora look like? etc...

>
>Thomas Kyte wrote:
>

--
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 Tue Nov 16 1999 - 07:12:24 CST