Home -> Mailing Lists -> Oracle-L -> Re: Weird "ORA-03113: end-of-file on communication channel" during parse

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

From: Laurentiu Oprea <laurentiu.oprea06_at_gmail.com>
Date: Thu, 25 Mar 2021 16:40:27 +0200
Message-ID: <CA+riqSU0Sp+Dww0Tyy9kRc8E=2swGp+RECUez_5-LrwC6qQiZA_at_mail.gmail.com> 








Does it make any difference if you add /*+ dynamic_sampling(0) */ ?
(completely out of curiosity)



În joi, 25 mar. 2021 la 16:30, Andy Sayer <andysayer_at_gmail.com> a scris:



> 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


Received on Thu Mar 25 2021 - 15:40:27 CET












Original text of this message