RE: Weird "ORA-03113: end-of-file on communication channel" during parse
Date: Thu, 25 Mar 2021 12:46:08 -0400
Message-ID: <0df001d72196$5b509e20$11f1da60$_at_rsiz.com>
On the theory that one off the end might be a fubared end of leaf marking on the index for the range scan, I offer that at this size recreating the index is cheap enough to try for the .042% chance that is the problem.
Adding and deleting one more -1 row might not affect the leaf holding the last in order of the range scan.
I suppose updating all the -1 to -2 and then setting them all back to -1 might also work, but that is more work than drop create.
mwf
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis
Sent: Thursday, March 25, 2021 12:33 PM
To: Kim Berg Hansen; Oracle L
Subject: Re: Weird "ORA-03113: end-of-file on communication channel" during parse
Execution Plan
Plan hash value: 1924021054
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |Predicate Information (identified by operation id):
------------------------------------------------------------------------------------------------------------
| 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 |
------------------------------------------------------------------------------------------------------------
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 343251 (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 |Predicate Information (identified by operation id):
------------------------------------------------------------------------------------------------------------
| 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 |
------------------------------------------------------------------------------------------------------------
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>
kibeha_at_kibeha.dk
_at_kibeha <http://twitter.com/kibeha>
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Mar 25 2021 - 17:46:08 CET