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

Thomas,

many thanks for your answer.

The last time I did these test was begin of October. The sun we had at that time is productive now so I should not repeat the test there. But as far as I can remember we had something like shared_pool_size = 110M. When we lowered this parameter the crash occurred faster. So I do not have a definitive proof what was going wrong but I always got some ORA-00600 [either 17112 or 17114 or 17126 or 17148]. When the instance was in that state I tried "alter system flush shared_pool" which sometimes delayed the crash a bit. Support suspected it was "cursor_space_for_time = true". I changed it to "false", the instance crashed an hour later. I suspected it was the use of the new feature "index organized table" (because I knew from my experience with Solaris 2.5.1, Oracle 7.3.4 Parallel Server that Oracle is very stable - so maybe it was one of the new features or Oracle 8i). Support finally got a trace file (10235 trace name context forever, level 12) with some 25 MB compressed and guessed it was the "timed_statistics = true". It was not. I switched to "false", the instance crashed again the next day even if I did not compile any packages or doing batch updates.

Maybe this is the difference to your try. Could you please try with several processes?

I asked our development to use bind variables after my tests and our instance never crashed again no matter what weird settings of parameters we had. So I admit that this is not a proof that the lots of execution plans are guilty but sometimes (especially if support has no clue) I have to use my feelings. And my guess was right.

Martin

Thomas Kyte wrote:

> 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 Wed Nov 17 1999 - 18:47:04 CST