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

Thanks Stefan, i am using shell script , so will have to change to Perl then.

On Fri, Sep 21, 2018 at 7:04 AM Stefan Knecht <knecht.stefan_at_gmail.com> wrote:

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

-- 
Cheers,
Kunwar

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