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

It seems to be the recursion this time around: ORA-07445 [kkqojeanl()] Internal Error for Query with Large Number of Elements in an IN List (Doc ID 1577011.1)
The call stack is as follows:

[TOC00006]

----- Call Stack Trace -----

calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
-------------------- -------- --------------------
----------------------------
....
__sighandler()       call     sslsshandler()       00000000B 7FA6C86304B0
                                                   7FA6C8630380
7FA6C8630380 ?
                                                   7FA6C86302C0 ? 000000083
?
kkqojeanl()+13       signal   __sighandler()       7FA6C7B8FC10 7FA6B11C1740
                                                   7FFFAE426A10 000000000 ?
                                                   000000000 ? 000000000 ?
.......
kkqojeanl()+1262     call     kkqojeanl()          7FA6C7B8FC10 ?
7FA6B3193E20 ?
                                                   7FFFAE426A10 ? 000000000
?
                                                   000000000 ? 000000000 ?
kkqojeanl()+1262     call     kkqojeanl()          7FA6C7B8FC10 ?
7FA6B31942D8 ?
                                                   7FFFAE426A10 ? 000000000
?
                                                   000000000 ? 000000000 ?

• At frame 60 recursion pattern of size 1 found, for return address kkqojeanl()+1262 suppressing printing.*** 2020-10-14T18:58:35.096350+00:00**** At frame 232303 recursion pattern broken, last return was* kkqojeanl() 000000000358FDB3 call 0000000010F7601E 7FA6C7B8FC10 ? 7FA6C7B8FB20 ?

The PGA allocations don't ring any bells yet:

• failed execution
  [oracle_at_rac1 trace]$ grep "WAIT #139775539487712: nam='PGA memory
  operation'" racdb1_ora_16214.trc | awk '{s+=substr($8,4)} END {print s}' 357040128
• successful execution
  [oracle_at_rac1 trace]$ grep "WAIT #140518643268912: nam='PGA memory
  operation'" racdb1_ora_15778.trc | awk '{s+=substr($8,4)} END {print s}' 18939904

On Wed, 14 Oct 2020 at 19:49, Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

>
> With things like this it's sometimes the maximum unit size for memory
> allocation that is the issue.
> 16MB or 64MB might be a relevant figure.
> Possibly if you enabled sql_trace with waits and summed the PGA allocate
> memory sizes you might spot a significant number at the point of failure.
>
> Regards
> Jonathan Lewis
>
>
> On Wed, 14 Oct 2020 at 19:23, Mikhail Velikikh <mvelikikh_at_gmail.com>
> wrote:
>
>> 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
>>   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..232293 loop
>> ERROR:
>> ORA-03114: not connected to ORACLE
>>
>>
>> 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
>>   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..232293 loop
>> ERROR:
>> ORA-03114: not connected to ORACLE
>>
>>
>> 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