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

If you're running this:

exec :b1:=1;
insert into mytab values(:b1,'TTT');
exec :b1:=11;
insert into mytab values(:b1,'TTT');

You're essentially submitting three different statements for Oracle to execute. The "exec" is turned into a begin :b1 := 1; end; - which is another "statement" that the database has to parse and execute. Sqlplus' exec is just a shorthand for that.

If on the other hand you're doing this via an OCI client (Java, Perl, or whatever it may be) , the "exec" in your script will effectively be a BIND call, which occurs as part of the cursor.

On Fri, Sep 21, 2018 at 4:56 PM, kunwar singh <krishsingh.111_at_gmail.com> 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>
> 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
>

-- 
//
zztat - The Next-Gen Oracle Performance Monitoring and Reaction Framework!
Visit us at zztat.net | _at_zztat_oracle | fb.me/zztat | zztat.net/blog/

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