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

You’re going to need access to the alert log to see what’s going on. There will be an ORA-7445 being hit which is ending your connection.

Thanks,
Andrew

On Thu, 25 Mar 2021 at 14:24, Kim Berg Hansen <kibeha_at_gmail.com> 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
>
>
>
> Hmm... ???
>
>
>
> Regards
>
>
> Kim Berg Hansen
> Senior Consultant at Trivadis
> Oracle ACE Director
>
> Author of Practical Oracle SQL
> <https://www.apress.com/gp/book/9781484256169>
> http://www.kibeha.dk
> kibeha_at_kibeha.dk
> _at_kibeha <http://twitter.com/kibeha>
>
>
>
> On Thu, Mar 25, 2021 at 3:12 PM Kim Berg Hansen <kibeha_at_gmail.com> wrote:
>
>> Hi, List
>>
>> I have an ORA-03113 error on a Windows 12.1.0.2 that seems to happen
>> during parse?
>> I don't have a good idea about what happens, so any ideas would be nice.
>>
>> Database version:
>>
>> SQL> select banner
>> 2 from v$version;
>> BANNER
>>
>> --------------------------------------------------------------------------------
>> Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit
>> Production
>> PL/SQL Release 12.1.0.2.0 - Production
>> CORE 12.1.0.2.0 Production
>> TNS for 64-bit Windows: Version 12.1.0.2.0 - Production
>> NLSRTL Version 12.1.0.2.0 - Production
>>
>>
>>
>> I do a simple query and my connection breaks with an ORA-03113:
>>
>> SQL> select sd.iddelivery
>> 2 from splitdelivery sd
>> 3 where sd.delpackoutloc = -1;
>> select sd.iddelivery
>> *
>> ERROR at line 1:
>> ORA-03113: end-of-file on communication channel
>> Process ID: 3964
>> Session ID: 38 Serial number: 45993
>>
>>
>>
>> Adding a superfluous NVL makes it work - the data can be retrieved
>> (doesn't look like corrupted data):
>>
>> SQL> set autotrace traceonly
>> SQL> select sd.iddelivery
>> 2 from arrow.splitdelivery sd
>> 3 where nvl(sd.delpackoutloc, 0) = -1;
>> 343251 rows selected.
>>
>> Execution Plan
>> ----------------------------------------------------------
>> Plan hash value: 87431401
>>
>> -----------------------------------------------------------------------------------
>> | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
>> Time |
>>
>> -----------------------------------------------------------------------------------
>> | 0 | SELECT STATEMENT | | 343K| 3352K| 3075 (1)|
>> 00:00:01 |
>> |* 1 | TABLE ACCESS FULL| SPLITDELIVERY | 343K| 3352K| 3075 (1)|
>> 00:00:01 |
>>
>> -----------------------------------------------------------------------------------
>> Predicate Information (identified by operation id):
>> ---------------------------------------------------
>> 1 - filter(NVL("SD"."DELPACKOUTLOC",0)=(-1))
>>
>> Statistics
>> ----------------------------------------------------------
>> 67 recursive calls
>> 0 db block gets
>> 33210 consistent gets
>> 0 physical reads
>> 0 redo size
>> 4592111 bytes sent via SQL*Net to client
>> 251949 bytes received via SQL*Net from client
>> 22885 SQL*Net roundtrips to/from client
>> 1 sorts (memory)
>> 0 sorts (disk)
>> 343251 rows processed
>>
>>
>>
>> But the erroneous statement gives ORA-03113 even when trying just to
>> explain it:
>>
>> SQL> explain plan for
>> 2 select sd.iddelivery
>> 3 from splitdelivery sd
>> 4 where sd.delpackoutloc = -1;
>> explain plan for
>> *
>> ERROR at line 1:
>> ORA-03113: end-of-file on communication channel
>> Process ID: 11760
>> Session ID: 72 Serial number: 52015
>>
>>
>>
>> The column in the predicate is very skewed - 343 thousand out of 350
>> thousand have the value -1:
>>
>> SQL> select delpackoutloc, count(*)
>> 2 from splitdelivery
>> 3 group by rollup(delpackoutloc)
>> 4 order by delpackoutloc;
>> DELPACKOUTLOC COUNT(*)
>> ------------- ----------
>> -1 343251
>> 1 417
>> 2 525
>> 3 5865
>> 350058
>>
>>
>> The column has no NULL values, but it is *not *defined as NOT NULL.
>>
>> The query above that fails searches for the value -1. If I search for
>> values 1, 2 or 3, there is no issue at all, works fine.
>>
>> The table/index statistics are recently gathered - gathered number of
>> rows fit exactly:
>>
>> SQL> select num_rows
>> 2 from user_tables
>> 3 where table_name = 'SPLITDELIVERY';
>> NUM_ROWS
>> ----------
>> 350058
>>
>>
>> And column histogram also fit exactly the current values:
>>
>> SQL> select endpoint_value, endpoint_number, endpoint_number -
>> lag(endpoint_number) over (order by endpoint_number) as num
>> 2 from user_histograms
>> 3 where table_name = 'SPLITDELIVERY'
>> 4 and column_name = 'DELPACKOUTLOC'
>> 5 order by endpoint_number;
>> ENDPOINT_VALUE ENDPOINT_NUMBER NUM
>> -------------- --------------- ----------
>> -1 343251
>> 1 343668 417
>> 2 344193 525
>> 3 350058 5865
>>
>>
>> There's an index on the column and stats are current here as well:
>>
>> SQL> select blevel, leaf_blocks, distinct_keys, avg_leaf_blocks_per_key,
>> avg_data_blocks_per_key, clustering_factor, num_rows, sample_size
>> 2 from user_indexes
>> 3 where table_name = 'SPLITDELIVERY'
>> 4 and index_name = 'SPLITDELIVERY_IND01';
>> BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY
>> AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR NUM_ROWS SAMPLE_SIZE
>> ---------- ----------- ------------- -----------------------
>> ----------------------- ----------------- ---------- -----------
>> 2 864 4 216
>> 3393 13573 350058 350058
>>
>>
>>
>> I noticed it worked by adding AND ROWNUM <= xxx, so I tried different
>> values and found that 343250 (one less than the actual row count) worked
>> fine:
>>
>> SQL> set autotrace traceonly
>> SQL> select sd.iddelivery
>> 2 from splitdelivery sd
>> 3 where sd.delpackoutloc = -1
>> 4 and rownum <= 343250;
>> 343250 rows selected.
>>
>> Execution Plan
>> ----------------------------------------------------------
>> Plan hash value: 1924021054
>>
>> ------------------------------------------------------------------------------------------------------------
>> | Id | Operation | Name | Rows
>> | Bytes | Cost (%CPU)| Time |
>>
>> ------------------------------------------------------------------------------------------------------------
>> | 0 | SELECT STATEMENT | |
>> 343K| 3352K| 14182 (1)| 00:00:01 |
>> |* 1 | COUNT STOPKEY | |
>> | | | |
>> | 2 | TABLE ACCESS BY INDEX ROWID BATCHED| SPLITDELIVERY |
>> 343K| 3352K| 14182 (1)| 00:00:01 |
>> |* 3 | INDEX RANGE SCAN | SPLITDELIVERY_IND01 |
>> | | 852 (1)| 00:00:01 |
>>
>> ------------------------------------------------------------------------------------------------------------
>> Predicate Information (identified by operation id):
>> ---------------------------------------------------
>> 1 - filter(ROWNUM<=343250)
>> 3 - access("SD"."DELPACKOUTLOC"=(-1))
>>
>> Statistics
>> ----------------------------------------------------------
>> 0 recursive calls
>> 0 db block gets
>> 56495 consistent gets
>> 0 physical reads
>> 0 redo size
>> 4592104 bytes sent via SQL*Net to client
>> 251962 bytes received via SQL*Net from client
>> 22885 SQL*Net roundtrips to/from client
>> 0 sorts (memory)
>> 0 sorts (disk)
>> 343250 rows processed
>>
>>
>>
>> That worked fine, though I find it weird that it chooses an INDEX RANGE
>> SCAN + TABLE ACCESS BY INDEX when it knows that it is going to retrieve 98%
>> of the rows? A full table scan would use fewer gets as seen in the
>> autotrace above?
>>
>> Anyway, ORA-03113 appears when I add 1 to 343250 making it 34325*1*
>> (which is the exact number of rows having the value -1, which the optimizer
>> knows from the histogram):
>>
>> SQL> select sd.iddelivery
>> 2 from splitdelivery sd
>> 3 where sd.delpackoutloc = -1
>> 4 and rownum <= 343251;
>> select sd.iddelivery
>> *
>> ERROR at line 1:
>> ORA-03113: end-of-file on communication channel
>> Process ID: 11164
>> Session ID: 72 Serial number: 37013
>>
>>
>>
>> Same for just calling EXPLAIN PLAN - it works for 343250:
>>
>> SQL> explain plan for
>> 2 select sd.iddelivery
>> 3 from splitdelivery sd
>> 4 where sd.delpackoutloc = -1
>> 5 and rownum <= 343250;
>> Explained.
>>
>> SQL> select * from table(dbms_xplan.display);
>> PLAN_TABLE_OUTPUT
>>
>> ----------------------------------------------------------------------------------------------------------------------------------------------------------------
>> Plan hash value: 1924021054
>>
>> ------------------------------------------------------------------------------------------------------------
>> | Id | Operation | Name | Rows
>> | Bytes | Cost (%CPU)| Time |
>>
>> ------------------------------------------------------------------------------------------------------------
>> | 0 | SELECT STATEMENT | |
>> 343K| 3352K| 14182 (1)| 00:00:01 |
>> |* 1 | COUNT STOPKEY | |
>> | | | |
>> | 2 | TABLE ACCESS BY INDEX ROWID BATCHED| SPLITDELIVERY |
>> 343K| 3352K| 14182 (1)| 00:00:01 |
>> |* 3 | INDEX RANGE SCAN | SPLITDELIVERY_IND01 |
>> | | 852 (1)| 00:00:01 |
>>
>> ------------------------------------------------------------------------------------------------------------
>> Predicate Information (identified by operation id):
>> ---------------------------------------------------
>> 1 - filter(ROWNUM<=343250)
>> 3 - access("SD"."DELPACKOUTLOC"=(-1))
>> 16 rows selected.
>>
>>
>>
>> But it fails for 343251:
>>
>> SQL> explain plan for
>> 2 select sd.iddelivery
>> 3 from splitdelivery sd
>> 4 where sd.delpackoutloc = -1
>> 5 and rownum <= 343251;
>> explain plan for
>> *
>> ERROR at line 1:
>> ORA-03113: end-of-file on communication channel
>> Process ID: 10888
>> Session ID: 457 Serial number: 32869
>>
>>
>>
>> At the moment I have no access to trace files or alert log on this
>> server. Might get it after the weekend.
>> Also it is a development server, nothing urgent to solve. I'm only diving
>> a bit deeper for curiosity as well as check if this potentially could
>> happen in production database as well.
>>
>> It'll be easier if/when I get trace access, but do anyone have an idea
>> about what might be happening here?
>>
>>
>> Thanks in advance.
>>
>> Cheerio
>> /Kim
>>
>>
>> Regards
>>
>>
>> Kim Berg Hansen
>> Senior Consultant at Trivadis
>> Oracle ACE Director
>>
>> Author of Practical Oracle SQL
>> <https://www.apress.com/gp/book/9781484256169>
>> http://www.kibeha.dk
>> kibeha_at_kibeha.dk
>> _at_kibeha <http://twitter.com/kibeha>
>>
>>

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