Re: RE: RE: Re: Can Bind variables be an issue with respect to network transfer?

From: Mikhail Velikikh <mvelikikh_at_gmail.com>
Date: Wed, 14 Oct 2020 19:23:20 +0100
Message-ID: <CALe4Hpkt0-HsOMMHm48Q3Xg0vBqhOLPX_HGNtbyQzHA2n73vTA_at_mail.gmail.com>

Rogel,

> And it seems to show that 1e5 is not the upper bound, at least 2 * 1e5
> still seems to work:
>

Indeed, I have played with your code a little bit and was able to run it with 232K IN lists. I applied the TO_CHAR function to make it work (it is one of the versions I tested - it uses 3-column sets):

declare
  stmt clob default 'select null from dual where (0,0,0) in ('; begin
  for i in 1..1e5 loop
    stmt:=stmt||*to_char*('(10,'||i||',0),');   end loop;
  stmt:=stmt||*to_char*('(0,-1,0))');
  dbms_output.put_line(dbms_lob.getlength(stmt));   execute immediate stmt;
end;
/

Unless it’s a multi-value IN list, which is limited to 1e5 elements.

I see only a StackOverflow thread that mentions 1e5: https://stackoverflow.com/questions/400255/how-to-put-more-than-1000-values-into-an-oracle-in-clause#comment85743305_17019130 Nehad, where did you get that information about 1e5 elements? The documentation mentions no restriction since 9.2: https://docs.oracle.com/cd/B10501_01/server.920/a96540/expressions14a.htm#1029285

> A comma-delimited list of expressions can contain no more than 1000
> expressions. A comma-delimited list of sets of expressions can contain any
> number of sets, but each set can contain no more than 1000 expressions.
>

I am not getting consistent results across my database instances for Rogel's sample code, but I have been able to run queries as long as 232K IN lists in 12.2.
It is very inconsistent, though. Some successful attempts suddenly ended up in:
ORA-07445: exception encountered: core dump [kkqojeanl()+13] [SIGSEGV] [ADDR:0x7FFF3A51AFE8] [PC:0x10F75B3D] [Address not mapped to object] [] or
ORA-07445: exception encountered: core dump [expCheckExprEquiv()+641] [SIGSEGV] [ADDR:0x7FFF9F4EBF88] [PC:0x10EB9341] [Address not mapped to object] []

*-- works with 232293 2-column sets*

SQL> _at_inlist 232292
SQL> set echo on
SQL>
SQL> conn / as sysdba

Connected.
SQL>
SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.02

SQL>
SQL> set serverout on
SQL>
SQL> declare

  2 stmt clob default 'select null from dual where (0,0) in (';   3 begin
  4 for i in 1..&1. loop
  5 stmt:=stmt||to_char('(0,'||i||'),');   6 end loop;
  7 stmt:=stmt||to_char('(0,-1))');
  8 dbms_output.put_line(dbms_lob.getlength(stmt));   9 execute immediate stmt;
10 end;
11 /
old 4: for i in 1..&1. loop
new 4: for i in 1..232292 loop
2444152

PL/SQL procedure successfully completed.

Elapsed: 00:00:09.99
SQL> *-- fails with 232294 2-column sets*

SQL> _at_inlist 232293
SQL> set echo on
SQL>
SQL> conn / as sysdba

Connected.
SQL>
SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.00

SQL>
SQL> set serverout on
SQL>
SQL> declare

declare
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel Process ID: 27742
Session ID: 747 Serial number: 5064

Elapsed: 00:00:34.74
SQL> *-- works with 232293 2-column sets, when the first column is '10' rather than '0' - I am checking if it is a sql_text length limit.*

SQL> _at_inlist 232292
SQL> set echo on
SQL>
SQL> conn / as sysdba

Connected.
SQL>
SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.01

SQL>
SQL> set serverout on
SQL>
SQL> declare

  2 stmt clob default 'select null from dual where (0,0) in (';   3 begin
  4 for i in 1..&1. loop
  5 stmt:=stmt||to_char('(*10*,'||i||'),');   6 end loop;
  7 stmt:=stmt||to_char('(0,-1))');
  8 dbms_output.put_line(dbms_lob.getlength(stmt));   9 execute immediate stmt;
10 end;
11 /
old 4: for i in 1..&1. loop
new 4: for i in 1..232292 loop
2676444

PL/SQL procedure successfully completed.

Elapsed: 00:00:20.31
SQL> *-- works with 232293 3-column sets*

SQL> _at_inlist 232292
SQL> set echo on
SQL>
SQL> conn / as sysdba

Connected.
SQL>
SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.02

SQL>
SQL> set serverout on
SQL>
SQL> declare

  2 stmt clob default 'select null from dual where (0,0,0) in (';   3 begin
  4 for i in 1..&1. loop
  5 stmt:=stmt||to_char('(10,'||i||',0),');   6 end loop;
  7 stmt:=stmt||to_char('(0,-1,0))');   8 dbms_output.put_line(dbms_lob.getlength(stmt));   9 execute immediate stmt;
10 end;
11 /
old 4: for i in 1..&1. loop
new 4: for i in 1..232292 loop
3141032

PL/SQL procedure successfully completed.

Elapsed: 00:00:23.44
SQL> *-- fails with 232294 3-column sets*

SQL> _at_inlist 232293
SQL> set echo on
SQL>
SQL> conn / as sysdba

Connected.
SQL>
SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.01

SQL>
SQL> set serverout on
SQL>
SQL> declare

declare
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel Process ID: 29500
Session ID: 258 Serial number: 32984

Elapsed: 00:00:10.67

On Wed, 14 Oct 2020 at 19:17, <rogel_at_web.de> wrote:

> The correct way might be to use dbms_lob.append instead of ||.
> And it seems to show that 1e5 is not the upper bound, at least 2 * 1e5
> still seems to work:
>
>
> *SQL> set timi on sqlprompt "" variable n number*
> *exec :n := 1*
> *PL/SQL procedure successfully completed.*
> *Elapsed: 00:00:00.03*
>
>
>
>
>
>
>
>
> *declare stmt clob default 'select null from dual where (0,0) in ('; 2
> begin 3 for i in 1 .. :n * 1e5 - 1 loop 4
> dbms_lob.append(stmt,'(0,'||i||'),'); 5 end loop; 6
> dbms_lob.append(stmt,'(0,-1))'); 7 execute immediate stmt; 8 end;
> 9 /*
> *PL/SQL procedure successfully completed.*
> *Elapsed: 00:00:03.95*
> *exec :n := 2*
> *PL/SQL procedure successfully completed.*
>
> *Elapsed: 00:00:00.02 /*
> *PL/SQL procedure successfully completed.*
> *Elapsed: 00:00:08.95*
> *exec :n := 3*
> *PL/SQL procedure successfully completed.*
>
>
>
>
>
>
>
> *Elapsed: 00:00:00.02 / declare stmt clob default 'select null from dual
> where (0,0) in ('; * ERROR at line 1: ORA-03113: end-of-file on
> communication channel Process ID: 32310 Session ID: 30 Serial number: 31943*
>
> *Elapsed: 00:01:48.09*
>
>
> *exec :n := 10 ERROR: ORA-03114: not connected to ORACLE*
>
>
>
>
>
>
> *Gesendet:* Mittwoch, 14. Oktober 2020 um 19:15 Uhr
> *Von:* "Noveljic Nenad" <nenad.noveljic_at_vontobel.com>
> *An:* "tim.evdbt_at_gmail.com" <tim.evdbt_at_gmail.com>, "rogel_at_web.de" <
> rogel_at_web.de>
> *Cc:* "jlewisoracle_at_gmail.com" <jlewisoracle_at_gmail.com>, "Oracle L" <
> oracle-l_at_freelists.org>
> *Betreff:* RE: Aw: RE: Re: Can Bind variables be an issue with respect to
> network transfer?
>
> The exception was raised even before execute immediate.
>
> The correct way to try this would be to spool the statement into the file
> and run it with _at_file from sqlplus.
>
>
>
> *From:* Tim Gorman <tim.evdbt_at_gmail.com>
> *Sent:* Mittwoch, 14. Oktober 2020 19:11
> *To:* rogel_at_web.de
> *Cc:* Noveljic Nenad <nenad.noveljic_at_vontobel.com>; jlewisoracle_at_gmail.com;
> Oracle L <oracle-l_at_freelists.org>
> *Subject:* Re: Aw: RE: Re: Can Bind variables be an issue with respect to
> network transfer?
>
>
>
> Even though you declared the variable as CLOB, I'm guessing that there
> might be a VARCHAR2(32767) limitation for EXECUTE IMMEDIATE?
>
> Can you measure the length of "stmt" before EXECUTE IMMEDIATE and add an
> exception handler that shares that length as well as the error, perhaps
> something like...
>
> len := length(stmt);
> execute immediate stmt;
> exception
> when others then
> dbms_output.put_line('len = '||len);
> raise;
> end;
> /
>
> Just an idea...
>
>
>
> On 10/14/2020 9:07 AM, rogel_at_web.de wrote:
>
> ok, "line 4" != "line 7"
>
>
>
>
>
> *Gesendet:* Mittwoch, 14. Oktober 2020 um 18:04 Uhr
> *Von:* rogel_at_web.de
> *An:* nenad.noveljic_at_vontobel.com
> *Cc:* "jlewisoracle_at_gmail.com" <jlewisoracle_at_gmail.com>
> <jlewisoracle_at_gmail.com> <jlewisoracle_at_gmail.com>, "Oracle L"
> <oracle-l_at_freelists.org> <oracle-l_at_freelists.org>
> *Betreff:* Aw: RE: Re: Can Bind variables be an issue with respect to
> network transfer?
>
> I am not able to reproduce this 1e5, for me it seems to be 3760 instead,
> see (note: execute immediate select-stmt just parses the select-stmt)
>
>
>
> *declare stmt clob default 'select null from dual where (0,0) in (';*
>
>
>
>
>
>
>
> *begin for i in 1..3760 loop stmt:=stmt||'(0,'||i||'),'; end loop;
> stmt:=stmt||'(0,-1))'; execute immediate stmt; end; /*
>
> *PL/SQL procedure successfully completed.*
>
> *3*
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> * 3* for i in 1..3760 loop c/0/1/ 3* for i in 1..3761 loop l 1
> declare stmt clob default 'select null from dual where (0,0) in ('; 2
> begin 3 for i in 1..3761 loop 4 stmt:=stmt||'(0,'||i||'),'; 5 end
> loop; 6 stmt:=stmt||'(0,-1))'; 7 execute immediate stmt; 8* end; /
> declare stmt clob default 'select null from dual where (0,0) in ('; * ERROR
> at line 1: ORA-06502: PL/SQL: numeric or value error ORA-06512: at line 4*
>
>
>
>
>
>
>
>
>
> *Gesendet:* Mittwoch, 14. Oktober 2020 um 16:04 Uhr
> *Von:* "Noveljic Nenad" <nenad.noveljic_at_vontobel.com>
> <nenad.noveljic_at_vontobel.com>
> *An:* "jlewisoracle_at_gmail.com" <jlewisoracle_at_gmail.com>
> <jlewisoracle_at_gmail.com> <jlewisoracle_at_gmail.com>, "Oracle L"
> <oracle-l_at_freelists.org> <oracle-l_at_freelists.org>
> *Betreff:* RE: Re: Can Bind variables be an issue with respect to network
> transfer?
>
> “An IN list is limited to 1,000”
>
>
>
> Unless it’s a multi-value IN list, which is limited to 1e5 elements.
>
>
>
> Best regards,
>
>
>
> Nenad
>
>
>
> *From:* oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org>
> <oracle-l-bounce_at_freelists.org> *On Behalf Of *Jonathan Lewis
> *Sent:* Mittwoch, 14. Oktober 2020 16:00
> *To:* Oracle L <oracle-l_at_freelists.org> <oracle-l_at_freelists.org>
> *Subject:* Re: Re: Can Bind variables be an issue with respect to network
> transfer?
>
>
>
> Lothar,
>
>
>
> Comparing the ela for the "PGA allocate memory" with the matching "tim=" I
> think what you're seeing is the time it takes Oracle to build an array and
> copy data from the SQL*Net packet to the array. Obviously some of the
> difference is about the time to write to the trace file, even so there's a
> lot of other CPU to account for.and it's happening between the memory
> allocations. I doubt if "traffic" (i.e. movement across the network) is
> relevant - the ns calls are probably a layer above that moving the data
> between the sql*net layer and the session layer.
>
>
>
> I am curious about the internal_function() and 4,700 variables. An IN
> list is limited to 1,000 and I wouldn't have expected an OR of IN lists to
> turn into a single internal_function(), Perhaps there's something about
> data types and character set conversion (or some other conversion) that's
> adding to the CPU time.
>
>
>
> Regards
>
> Jonathan Lewis
>
>
>
> ____________________________________________________
>
> Please consider the environment before printing this e-mail.
>
> Bitte denken Sie an die Umwelt, bevor Sie dieses E-Mail drucken.
>
>
> Important Notice
>
> This message is intended only for the individual named. It may contain
> confidential or privileged information. If you are not the named addressee
> you should in particular not disseminate, distribute, modify or copy this
> e-mail. Please notify the sender immediately by e-mail, if you have
> received this message by mistake and delete it from your system.
> Without prejudice to any contractual agreements between you and us which
> shall prevail in any case, we take it as your authorization to correspond
> with you by e-mail if you send us messages by e-mail. However, we reserve
> the right not to execute orders and instructions transmitted by e-mail at
> any time and without further explanation.
> E-mail transmission may not be secure or error-free as information could
> be intercepted, corrupted, lost, destroyed, arrive late or incomplete. Also
> processing of incoming e-mails cannot be guaranteed. All liability of
> Vontobel Holding Ltd. and any of its affiliates (hereinafter collectively
> referred to as "Vontobel Group") for any damages resulting from e-mail use
> is excluded. You are advised that urgent and time sensitive messages should
> not be sent by e-mail and if verification is required please request a
> printed version.
> Please note that all e-mail communications to and from the Vontobel Group
> are subject to electronic storage and review by Vontobel Group. Unless
> stated to the contrary and without prejudice to any contractual agreements
> between you and Vontobel Group which shall prevail in any case,
> e-mail-communication is for informational purposes only and is not intended
> as an offer or solicitation for the purchase or sale of any financial
> instrument or as an official confirmation of any transaction.
> The legal basis for the processing of your personal data is the legitimate
> interest to develop a commercial relationship with you, as well as your
> consent to forward you commercial communications. You can exercise, at any
> time and under the terms established under current regulation, your rights.
> If you prefer not to receive any further communications, please contact
> your client relationship manager if you are a client of Vontobel Group or
> notify the sender. Please note for an exact reference to the affected group
> entity the corporate e-mail signature. For further information about data
> privacy at Vontobel Group please consult www.vontobel.com.
>
> -- http://www.freelists.org/webpage/oracle-l
>
> -- http://www.freelists.org/webpage/oracle-l
>
>
>
>
> Important Notice
>
> This message is intended only for the individual named. It may contain
> confidential or privileged information. If you are not the named addressee
> you should in particular not disseminate, distribute, modify or copy this
> e-mail. Please notify the sender immediately by e-mail, if you have
> received this message by mistake and delete it from your system.
> Without prejudice to any contractual agreements between you and us which
> shall prevail in any case, we take it as your authorization to correspond
> with you by e-mail if you send us messages by e-mail. However, we reserve
> the right not to execute orders and instructions transmitted by e-mail at
> any time and without further explanation.
> E-mail transmission may not be secure or error-free as information could
> be intercepted, corrupted, lost, destroyed, arrive late or incomplete. Also
> processing of incoming e-mails cannot be guaranteed. All liability of
> Vontobel Holding Ltd. and any of its affiliates (hereinafter collectively
> referred to as "Vontobel Group") for any damages resulting from e-mail use
> is excluded. You are advised that urgent and time sensitive messages should
> not be sent by e-mail and if verification is required please request a
> printed version.
> Please note that all e-mail communications to and from the Vontobel Group
> are subject to electronic storage and review by Vontobel Group. Unless
> stated to the contrary and without prejudice to any contractual agreements
> between you and Vontobel Group which shall prevail in any case,
> e-mail-communication is for informational purposes only and is not intended
> as an offer or solicitation for the purchase or sale of any financial
> instrument or as an official confirmation of any transaction.
> The legal basis for the processing of your personal data is the legitimate
> interest to develop a commercial relationship with you, as well as your
> consent to forward you commercial communications. You can exercise, at any
> time and under the terms established under current regulation, your rights.
> If you prefer not to receive any further communications, please contact
> your client relationship manager if you are a client of Vontobel Group or
> notify the sender. Please note for an exact reference to the affected group
> entity the corporate e-mail signature. For further information about data
> privacy at Vontobel Group please consult www.vontobel.com.
> -- http://www.freelists.org/webpage/oracle-l

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

Received on Wed Oct 14 2020 - 20:23:20 CEST

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message