Re: Weird "ORA-03113: end-of-file on communication channel" during parse

I did have an example with value in (-1,2), yes. For that example it worked with rownum <= {count of -1} + {count of 2} - 1, and failed with rownum <= {count of -1} + {count of 2}. Here's what I wrote:

  Hmmm... If I query for two values, I get again that it works for one less row than it would return:

SQL> explain plan for

  2 select sd.iddelivery

  3 from arrow.splitdelivery sd

  4 where sd.delpackoutloc in (-1, 2)

  5 and rownum <= 343251 + 525 - 1;

Explained.

And it fails at the exact number of rows for the two values together:

SQL> explain plan for

  2 select sd.iddelivery

  3 from arrow.splitdelivery sd

  4 where sd.delpackoutloc in (-1, 2)

  5 and rownum <= 343251 + 525;

where sd.delpackoutloc in (-1, 2)

                               *

ERROR at line 4:

ORA-03113: end-of-file on communication channel

Process ID: 9016

Session ID: 69 Serial number: 1934

I'll try other combinations.

Hinting dynamic sampling to zero didn't help.

There are 13 indexes on the table.
One peculiarity is that the primary key (two columns) is created USING an index with 5 columns (the two PK columns plus suffixed with 3 other columns.)
The column that is queried in the select is the first column of the PK, and it is indexed by itself in a separate index (the one that the index-join access plan on the table copy is using.)

But the column in the predicate only exists in a single index - which is non-unique index containing only that column.

Cheerio
/Kim

On Fri, Mar 26, 2021 at 12:00 PM Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

>
> I think you had an example with "value in (-1, 2) and rownum <= {count} -
> 1" that worked.
>
> If it's an index defect then I'd consider the possibility that the problem
> is at the end of the -1 range, did you have some comment (I've lost the
> trail) about getting failures with other "single value" queries? What about
> two-value queries. The other thing I'd try (since there's a gap between -1
> and 2 is to query value in (-1,1) which I think was the two consecutive
> values.
>
> (I'd have to day that I would be a little surprised if it were a physical
> index problem as the crash also appears on the explain plan - but maybe
> there's a dynamic-sampling effect that's causing the crash in that case).
>
> Are there any other indexes on the table that Oracle might be considering
> as possible alternatives ?
>
>
> Regards
> Jonathan Lewis
>
>
>
> On Fri, 26 Mar 2021 at 10:35, Kim Berg Hansen <kibeha_at_gmail.com> wrote:
>
>> A short update:
>>
>>
>> _at_Mark:
>> I'm guessing too that an index rebuild or similar could fix the issue.
>> At the moment my main interest is to diagnose (mostly in order to be able
>> to tell if this might be an issue in other of our environments or tables),
>> so at the moment I don't want to "destroy the error-causing scenario" by
>> fixing it ;-)
>>
>>
>>

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