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

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

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