Re: Separate cursor for bind value assignment, is this normal

Hi!

That doesn't look normal to me.  It looks like a SQL*Plus problem. Have you tried scripting it with some scripting language and doing an array bind? BTW, what  Oracle version is being used here?

Regards

On 09/21/2018 05:56 AM, kunwar singh wrote:
> my inserts are like this:
>
> variable b1 number;
> exec :b1:=1;
> insert into mytab values(:b1,'TTT');
> exec :b1:=11;
> insert into mytab values(:b1,'TTT');
> exec :b1:=21;
> insert into mytab values(:b1,'TTT');
> exec :b1:=31;
> insert into mytab values(:b1,'TTT');
> exec :b1:=41;
> insert into mytab values(:b1,'TTT');
> exec :b1:=51;
> ....
>
> On Fri, Sep 21, 2018 at 5:51 AM kunwar singh <krishsingh.111_at_gmail.com
> <mailto:krishsingh.111_at_gmail.com>> wrote:
>
> hi all,
>
> I am trying to generate some load by executing many OLTP-like inserts
> insert into mytab values( :b1);  ( purpose of using :b1 is to not
> flush shared pool)
>
> SQL> select sql_id , sql_fulltext from v$sql where rownum <30;
>
> SQL_ID       |SQL_FULLTEXT
> -------------|--------------------------------------------------------------------------------
> 6hrwjapxkw001|BEGIN :b1:=1974764; END;
> 8gnkm19fh0004|BEGIN :b1:=1964843; END;
> 88dnrt2984007|BEGIN :b1:=1980965; END;
> 1qqk2h4u2000a|BEGIN :b1:=1966014; END;
> b1qvjscn4h00c|BEGIN :b1:=1956268; END;
> 7pxamcspqn00c|BEGIN :b1:=1992032; END;
> 9nqpjvwaa800h|BEGIN :b1:=1961924; END;
> 7vdnnakbdh00j|BEGIN :b1:=1969642; END;
>
>
> But i am getting 1000s of cursors like above and it is thrashing
> the shared pool.
> Is it a expected behavior, never seen like this earlier.
> SQL> select count(9) from v$sql where sql_text like 'BEGIN :b1%';
>
>   COUNT(9)
> ----------
>      38325
> --
> Cheers,
> Kunwar
>
>
>
> --
> Cheers,
> Kunwar

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217


--
http://www.freelists.org/webpage/oracle-l