Separate cursor for bind value assignment, is this normal

[kunwar singh]

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

[kunwar singh]

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

Post by kunwar singh
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

[Mladen Gogala]

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

Post by kunwar singh
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;
....
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

[Stefan Knecht]

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.

Post by kunwar singh
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;
....

Post by kunwar singh
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

[Andy Sayer]

This has been addressed in later versions of sql*plus, you can use the new
syntax
var x number = 31
It will do all the bind assignment locally rather than on the DB, so you
don’t do the extra parse

In
https://ctandrewsayer.wordpress.com/2017/03/29/sqlplus-12-2-so-close/ , I
mention that it lacks the ability to assign a value by defined variable,
this feature is still lacking in SQL*PLUS 18.2

Hope this helps,
Andy

[kunwar singh]

hi Andy,
That is very interesting to know. I am on 12.1.0.2 , so out of luck there.
You mentioned PL/SQL in your blog , i will test it out.
Very nice writeup!

Thanks!

Post by Andy Sayer
This has been addressed in later versions of sql*plus, you can use the new
syntax
var x number = 31
It will do all the bind assignment locally rather than on the DB, so you
don’t do the extra parse
In
https://ctandrewsayer.wordpress.com/2017/03/29/sqlplus-12-2-so-close/ , I
mention that it lacks the ability to assign a value by defined variable,
this feature is still lacking in SQL*PLUS 18.2
Hope this helps,
Andy

[Stefan Koehler]

Hello Kunwar,
you can use it with RDBMS version 12.1.0.2 as well - just use a newer client (e.g. instant client) version. Here is the proof with a 11.2.0.3 database on Linux and 12.2.0.1 instant client on MacOS.


SQL*Plus: Release 12.2.0.1.0 Production on Sun Sep 23 11:26:07 2018
Copyright (c) 1982, 2017, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

***@T11DB:137> var nNumber number=10
***@T11DB:137> select count(*) from dba_objects where object_id=:nNumber;
COUNT(*)
----------
1

***@T11DB:137> select sql_id, sql_fulltext from v$sql where upper(sql_fulltext) LIKE '%NNUMBER';
SQL_ID SQL_FULLTEXT
------------- --------------------------------------------------------------------------------
2hu91bb3d80nj select count(*) from dba_objects where object_id=:nNumber


Best Regards
Stefan Koehler

Independent Oracle performance consultant and researcher
Website: http://www.soocs.de

Post by kunwar singh
hi Andy,
That is very interesting to know.  I am on 12.1.0.2 , so out of luck there. You mentioned PL/SQL in your blog , i will test it out.
Very nice writeup!
 
Thanks!

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

[Dragutin Jastrebic]

Hello
In this old thread=>
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::p11_question_id:113012348062
Tom Kyte explained
==>
.... in sqlplus the exec :x := &x will be parsed each time,
those will be unavoidable (sqlplus is not a programming environment, it is
just a simple command line tool)
Yes, I wish sqlplus had a way to initialize a bind variable -- it would be
nice.
<===
So possibly that it was event a design choice of Oracle development team,
since sqlplus was more seen a command line tool, not a programming tool.
Dragutin

[Dragutin Jastrebic]

"even a design choice ..."

[Mark W. Farnham]

hehe, which started as the ufi (user friendly interface), which some customers called the uvi (user viscious interface) for things like its lack of a history buffer and so forth.



You are correct that it was never intended as a programming tool. Oracle had Pro*C for amateurs and the C call libraries for professionals. And at 300 baud, you had better figure out a way to avoid telecom line turn-arounds in the middle of a transaction, too, and with 25 MHz cpus and table locking you better avoid parses in the middle of transactions, too.

UFI was competitive with its competition as a quick way to ask the database quick questions.



mwf



From: oracle-l-***@freelists.org [mailto:oracle-l-***@freelists.org] On Behalf Of Dragutin Jastrebic
Sent: Monday, October 08, 2018 2:18 PM
To: ***@soocs.de
Cc: ***@gmail.com; Oracle - L
Subject: Re: Separate cursor for bind value assignment, is this normal




Hello

In this old thread=>

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::p11_question_id:113012348062

Tom Kyte explained

==>

.... in sqlplus the exec :x := &x will be parsed each time,

those will be unavoidable (sqlplus is not a programming environment, it is just a simple command line tool)

Yes, I wish sqlplus had a way to initialize a bind variable -- it would be nice.

<===

So possibly that it was event a design choice of Oracle development team, since sqlplus was more seen a command line tool, not a programming tool.

Dragutin

[Jeff Smith]

If you want to write some code and do some oracle, SQLcl has access to javascript, jython, groovy, etc. I’m sure there’s a way to init a bind there

 

From: Mark W. Farnham <***@rsiz.com>
Sent: Monday, October 8, 2018 5:26 PM
To: ***@gmail.com; ***@soocs.de
Cc: ***@gmail.com; Oracle - L <oracle-***@freelists.org>
Subject: RE: Separate cursor for bind value assignment, is this normal

 

hehe, which started as the ufi (user friendly interface), which some customers called the uvi (user viscious interface) for things like its lack of a history buffer and so forth.

 

You are correct that it was never intended as a programming tool. Oracle had Pro*C for amateurs and the C call libraries for professionals. And at 300 baud, you had better figure out a way to avoid telecom line turn-arounds in the middle of a transaction, too, and with 25 MHz cpus and table locking you better avoid parses in the middle of transactions, too.

UFI was competitive with its competition as a quick way to ask the database quick questions.

 

mwf

 

From: HYPERLINK "mailto:oracle-l-***@freelists.org"oracle-l-***@freelists.org [mailto:oracle-l-***@freelists.org] On Behalf Of Dragutin Jastrebic
Sent: Monday, October 08, 2018 2:18 PM
To: HYPERLINK "mailto:***@soocs.de"***@soocs.de
Cc: HYPERLINK "mailto:***@gmail.com"***@gmail.com; Oracle - L
Subject: Re: Separate cursor for bind value assignment, is this normal

 

Hello

In this old thread=>

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::p11_question_id:113012348062

 Tom Kyte explained 

==>

.... in sqlplus  the exec :x := &x will be parsed each time, 

those will be unavoidable (sqlplus is not a programming environment, it is just a simple command line tool)

Yes, I wish sqlplus had a way to initialize a bind variable -- it would be nice. 

<===

So possibly that it was event a design choice of Oracle development team, since sqlplus was more seen a command line tool, not a programming tool.

Dragutin

 


 

[Mark W. Farnham]

Nods, due to you and your team as well as Bryn, et. al, Oracle has made huge advances in the availability of program interfaces and environments.



As a practical matter if someone has a list of bind variables to be used in the execution of a procedure serially, the trivial thing to do is stuff them all into a table and have the procedure iterate through the list from the table with nary a context shift. (Having many users each using a procedure one variable at a time because that is all they want to process is another matter where no matter what you do there will be some cost to setting the value from wherever the user is sitting and getting it available to the processing routine. Doing it by one of the methods you’ve mentioned that probably can skip doing a sql parse just to set the variable is probably a smart choice.)



My comment was intended purely as an historical note agreeing with the notion that what has grown into SQLPLUS was not originally intended to be more than a generic user interface to launch queries, let alone an efficient process control engine. Client-side only PL/SQL was an answer to user demand for more programming features than were available natively to SQLPLUS and of course then someone realized that putting that in the database enhanced volume processing. To the extent SQLPLUS can be used pretty efficiently for volume processing now is simply amazing.



But it remains best to do actual programming for heavy loads. The shift it what it means TO BE a heavy load sets the bar for when it is worthwhile to do actual coding rather than trick a mere very capable user interface into doing some pretty heavy lifting.



mwf



From: oracle-l-***@freelists.org [mailto:oracle-l-***@freelists.org] On Behalf Of Jeff Smith
Sent: Monday, October 08, 2018 6:50 PM
To: ***@rsiz.com; ***@gmail.com; ***@soocs.de
Cc: ***@gmail.com; Oracle - L
Subject: RE: Separate cursor for bind value assignment, is this normal



If you want to write some code and do some oracle, SQLcl has access to javascript, jython, groovy, etc. I’m sure there’s a way to init a bind there



From: Mark W. Farnham <***@rsiz.com>
Sent: Monday, October 8, 2018 5:26 PM
To: ***@gmail.com; ***@soocs.de
Cc: ***@gmail.com; Oracle - L <oracle-***@freelists.org>
Subject: RE: Separate cursor for bind value assignment, is this normal



hehe, which started as the ufi (user friendly interface), which some customers called the uvi (user viscious interface) for things like its lack of a history buffer and so forth.



You are correct that it was never intended as a programming tool. Oracle had Pro*C for amateurs and the C call libraries for professionals. And at 300 baud, you had better figure out a way to avoid telecom line turn-arounds in the middle of a transaction, too, and with 25 MHz cpus and table locking you better avoid parses in the middle of transactions, too.

UFI was competitive with its competition as a quick way to ask the database quick questions.



mwf



From: oracle-l-***@freelists.org [mailto:oracle-l-***@freelists.org] On Behalf Of Dragutin Jastrebic
Sent: Monday, October 08, 2018 2:18 PM
To: ***@soocs.de
Cc: ***@gmail.com; Oracle - L
Subject: Re: Separate cursor for bind value assignment, is this normal




Hello

In this old thread=>

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::p11_question_id:113012348062

Tom Kyte explained

==>

.... in sqlplus the exec :x := &x will be parsed each time,

those will be unavoidable (sqlplus is not a programming environment, it is just a simple command line tool)

Yes, I wish sqlplus had a way to initialize a bind variable -- it would be nice.

<===

So possibly that it was event a design choice of Oracle development team, since sqlplus was more seen a command line tool, not a programming tool.

Dragutin

[kunwar singh]

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

Post by kunwar singh
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.

Post by kunwar singh
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;
....

Post by kunwar singh
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!